Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

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

Tags (3)
1 Solution

Accepted Solutions
Not applicable

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

4 Replies
nagaiank
Valued Contributor III

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

Add SQL before the SELECT and see if it works.

Not applicable

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

Not applicable

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

Adding SQL before SELECT is not helping either..

Not applicable

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