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

[#1]

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

[nagaiank]

Add SQL before the SELECT and see if it works.

[#3]

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

[#4]

Adding SQL before SELECT is not helping either..

[#5 · Accepted solution]

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