2 Replies Latest reply: Apr 17, 2018 12:36 PM by Justin Roberts RSS

    Where Exists() within a Oracle connection

    Justin Roberts

      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
          Youssef Belloum

          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
              Justin Roberts

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