4 Replies Latest reply: May 15, 2013 1:17 AM by Vishnu S RSS

    SQL Select query at qlikview script side returning 0 records !!

      Hi QlikView Friends,

       

      Please help me with this strange issue that has taken almost a day of mine in vain.

       

      I have an oracle query which runs perfectly in SQL developer and returns 2000+ rows. But The same  query when I am including at the script side of my qvw retrives 0 records from database. The reload happens succesfully without showing any error but the record set loaded is 0 !

       

      I have ensured that the connection string is perfect and it works. I tried loading individual tables and in that case the records gets loaded to qvw. This issue is when I am trying to load the below query which has a few joins.

       

      Please help me with this as soon as possible.

       

       

       

      OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=stg_set;Data Source=DWDEVI;Extended Properties=""] (XPassword is dOABUZROHbXGXUNOaE);

       

      SELECT

      "BP"."PATIENT_ID" as PATIENT_ID,

      "BP"."CARE_EVENT_COUNTER" as CARE_EVENT_COUNTER

         

       

      FROM    

                            (  

                                ("BP" "BP"

                                 INNER JOIN

                                    "PE" "PE"

                                 ON (("BP"."PATIENT_ID" = "PE"."PATIENT_ID")

                                 AND ("BP"."MODULE_ID" =  "PE"."MODULE_ID"))

                                 AND ("BP"."CARE_EVENT_COUNTER" = "PE"."CARE_EVENT_COUNTER")

                                 )

                                

                             LEFT OUTER JOIN

                                "CAS" "CAS"

                                ON ("BP"."PATIENT_ID" = "CAS"."PATIENT_ID")

                                AND ("BP"."CARE_EVENT_COUNTER" =  "CAS"."CARE_EVENT_COUNTER")

                             )                

                                               

                           INNER JOIN

                           "PE" "PE"

                           ON ("BP"."PATIENT_ID" = "PE"."PATIENT_ID")

                           AND ("BP"."CARE_EVENT_COUNTER" = "PE"."CARE_EVENT_COUNTER")

        WHERE

      "PE"."PROCEDURE_DATE_DT" >= '01-Dec-2012'

         AND "PE"."PROCEDURE_DATE_DT" <= '31-Dec-2012'

         AND "BP"."MODULE_ID"='1040'

         AND "PE"."PRIMARY_TF"='1';

       

      Regards,

      Vishnu S

        • Re: SQL Select query at qlikview script side returning 0 records !!
          Nagaian Krishnamoorthy

          Add SQL before the SELECT and see if it works.

          • Re: SQL Select query at qlikview script side returning 0 records !!

            Sorry, there was an error in the query posted .. the table named PE was used twice.

            The correct query is posted here. The issue still exists. Please help

             

            OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=stg_sis;Data Source=HDWDEV;Extended Properties=""] (XPassword is dOABUZROHbXGXUNOaE);

            SELECT
            "BP"."PATIENT_ID" as PATIENT_ID,
            "BP"."CARE_EVENT_COUNTER" as CARE_EVENT_COUNTER
               

            FROM    
                                  (  
                                      ("BP" "BP"
                                       INNER JOIN
                                          "PE" "PE"
                                       ON (("BP"."PATIENT_ID" = "PE"."PATIENT_ID")
                                       AND ("BP"."MODULE_ID" =  "PE"."MODULE_ID"))
                                       AND ("BP"."CARE_EVENT_COUNTER" = "PE"."CARE_EVENT_COUNTER")
                                       )
                                      
                                   LEFT OUTER JOIN
                                      "CAS" "CAS"
                                      ON ("BP"."PATIENT_ID" = "CAS"."PATIENT_ID")
                                      AND ("BP"."CARE_EVENT_COUNTER" =  "CAS"."CARE_EVENT_COUNTER")
                                   )                
                                                     
                                 INNER JOIN
                                 "PEI" "PEI"
                                 ON ("BP"."PATIENT_ID" = "PEI"."PATIENT_ID")
                                 AND ("BP"."CARE_EVENT_COUNTER" = "PEI"."CARE_EVENT_COUNTER")
              WHERE
            "PE"."PROCEDURE_DATE_DT" >= '01-Dec-2012'
               AND "PE"."PROCEDURE_DATE_DT" <= '31-Dec-2012'
               AND "BP"."MODULE_ID"='1040'
               AND "PEI"."PRIMARY_TF"='1';

            • Re: SQL Select query at qlikview script side returning 0 records !!

              I could solve this by using to_date function and mentioning the date format in filter condition as

               

              PATIENT_CARE_EVENT"."PROCEDURE_DATE_DT" >= to_date('01-DEC-2012','DD-MON-YYYY')

                 AND "PATIENT_CARE_EVENT"."PROCEDURE_DATE_DT" <= to_date('31-DEC-2012','DD-MON-YYYY')