App Development

[jusrober]

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.

[YoussefBelloum · Accepted solution]

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()" ?

[jusrober]

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);