Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
Add SQL before the SELECT and see if it works.
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';
Adding SQL before SELECT is not helping either..
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')