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

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

Tue, 14 May 2013 09:37:46 GMT

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 !!

nagaiank — Tue, 14 May 2013 09:41:04 GMT

Add SQL before the SELECT and see if it works.

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

Tue, 14 May 2013 09:45:02 GMT

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 !!

Tue, 14 May 2013 09:46:22 GMT

Adding SQL before SELECT is not helping either..

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

Wed, 15 May 2013 05:17:50 GMT

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