topic Re: Where Exists() within a Oracle connection in App Development

Where Exists() within a Oracle connection

jusrober — Tue, 17 Apr 2018 15:23:19 GMT

I am stumped how to get this to work. I have searched for hours. I am trying to limit the records I pull from the oracle database with a where exists() on a table loaded into memory. This is the code I currently have.

[V_F_STO_INV_Temp]:

LOAD

"PROD_SEQ",

"STO_SEQ",

"SN",

"ON",

"ON_DY",

"MIN",

"MIN_S",

"MAX"

;

SQL SELECT

"PROD_SEQ",

"STO_SEQ",

"SN",

"ON",

"ON_DY",

"MIN",

"MIN_S",

"MAX"

FROM Oracle.File

Where

SNAPSHOT_DT >= TO_DATE('$(v.MSTRT)','DD/MM/YYYY HH24:MI:SS')

and SNAPSHOT_DT <= TO_DATE('$(v.MEND)','DD/MM/YYYY HH24:MI:SS')

and ON = '0'

and EXISTS(PROD_SEQ) and EXISTS(STO_SEQ)

;

The problem here is that it wants the Oracle version of Where Exists(). So it is looking for a sub-query. The table I need to constrain it against is in memory. Is there a way for me to make it use the QL version of Exists() or maybe a Oracle function that would allow me to only pulling matching records to STO_SEQ.

Re: Where Exists() within a Oracle connection

YoussefBelloum — Tue, 17 Apr 2018 15:49:57 GMT

Hi,

I think you're trying to place your WHERE.. in a wrong place, which is your SQL LOAD.

If you don't know the functions and the syntax of your database engine, you can replace all the existing where clause used above with a QLIK WHERE condition that you should put under the LOAD statement

Also, what do yo mean by "The problem here is that it wants the Oracle version of Where Exists()" ?

Re: Where Exists() within a Oracle connection

jusrober — Tue, 17 Apr 2018 16:36:30 GMT

Will the answer you provided only pull records from the data base that match the loaded fields in memory? The table I am pulling from is huge and I'm trying to avoid pulling everything that matches my SQL LOAD where.

It was asking for the Oracle version of the Exists function. Below is an example of the statement it was looking for. I kept getting an error message like this " ErrorCode: 928, ErrorMsg:[Oracle][ODBC][Ora]ORA-00928: missing SELECT keyword". This makes sense being that I just had a field in the Exists() function but it was looking for a sub-query.

Example:

SELECT *

FROM customers

WHERE EXISTS (SELECT *

FROM order_details

WHERE customers.customer_id = order_details.customer_id);