Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eduardo_dimperio
Specialist II
Specialist II

Oracle Select with Qlik Sense Exists()

Hi,

I have a table in qlik sense and i need to do a select in a sql table, but only when OID_SYSTEM_AGUA (Qlik Sense) = OID_SYSTEM (SQL).

AGUA:

Load

DISTINCT

OID_SYSTEM_AGUA,

NAME_TYPE_METER_AGUA,

QTDE_MED_AGUA

Resident TOTAL_AGUA;

RS_INDIVIDUAL_BILLING:

LOAD

*

;

SQL

SELECT

OID_SYSTEM

FROM RS_INDIVIDUAL_BILLING

WHERE ???????

7 Replies
sunny_talwar

May be where exists under load statement if you are not looking to do this specifically in the sql?

eduardo_dimperio
Specialist II
Specialist II
Author

Hi Sunny !

But if i use Exists under the Load, my SQL will bring all data and on that exists() will work. I need to extract only the lines that i 'll use.

sunny_talwar

Create a variable with all possible value OID_SYSTEM_AGUA and pass the variable to the SQL in that case

eduardo_dimperio
Specialist II
Specialist II
Author

Yeah I did that once, but the problem is that variable have a maximum number of characters, so for now it works but in a few months not anymore.

eduardo_dimperio
Specialist II
Specialist II
Author

Right now thats my code

TOTAL_AGUA:

Load

DISTINCT

OID_SYSTEM_AGUA,

NAME_TYPE_METER_AGUA,

QTDE_MED_AGUA

Resident TOTAL_AGUA;

FOR Each oid_system in FieldValueList('OID_SYSTEM_AGUA')

RS_INDIVIDUAL_BILLING:

LOAD

*

;

SQL

SELECT

*

FROM RS_INDIVIDUAL_BILLING

WHERE OID_SYSTEM =$(oid_system) AND DT_FIM >TO_DATE(SYSDATE-180);

NEXT

sunny_talwar

Oh really? how many characters can you have in a variable?

eduardo_dimperio
Specialist II
Specialist II
Author

I can do this too

//******************************************************************************

vWHERE='';

Aux='';

FOR Each condominio in FieldValueList('OID_SYSTEM_AGUA')

Aux='OID_SYSTEM='& $(condominio)&' OR ';

vWHERE=Aux& vWHERE;

NEXT

AUX=LEFT(vWHERE,LEN(vWHERE)-3);

//******************************************************************************

// FOR Each oid_system in FieldValueList('OID_SYSTEM_AGUA')

RS_INDIVIDUAL_BILLING:

LOAD

  *

;

SQL

SELECT

*

FROM RS_INDIVIDUAL_BILLING

WHERE $(AUX) AND DT_FIM >TO_DATE(SYSDATE-180);