Error using SQL query

[Amelia_96]

Hi, I have a query error when i tried to filter date and query data from oracle db.

I've tried the same query using data from csv file, and the query succeed. but not when i try to use the same data, but different source from oracle db straight away. 

Attached is the error shown.

Here's my query when i use csv file:

Let vStartDate = MakeDate(2021,9,17);

Let vAfterDate = Interval(Today() - Floor(vStartDate), 'D');

Let vBeforeDate = vStartDate - (vAfterDate + 1) ;

Proposal1:
LOAD STRPROPNBR,
Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss') as DTPROPENTRY,
Date(Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss'), 'DD/MM/YYYY') as DTPROPENTRY2,
If (Date(Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss')) < Date('$(vStartDate)','DD-MM-YYYY'), 'Before', 'After') as PERIOD,
DTPROPSIGNED,
STRCLIENTCD,
NPROPSTATCD,
DTPROPSTAT,
DTINFORCE,
STRCMPNYBRANCHCD
FROM
[C:\Users\00137174\Desktop\IUW\Data QV 2\IL_NB_BASE_PROP_DTL.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
where Date(Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss')) >= Date($(vBeforeDate),'DD-MM-YYYY');

Here's the query using data from oracle db:

Let vStartDate = MakeDate(2021,9,17);

Let vAfterDate = Interval(Today() - Floor(vStartDate), 'D');

Let vBeforeDate = vStartDate - (vAfterDate + 1) ;

ODBC CONNECT TO [Staging;DBQ=EBIPROD_SUN] (XUserId is XdMaNIZIQBMCTZcGRZNIDaD, XPassword is cfCRRDdNMDZeVYIGTBNcE);

Proposal1:
SQL select STRPROPNBR,
DTPROPENTRY,
Date(Date#(DTPROPENTRY, 'DDMMMYYYY:hh:mm:ss'), 'DD/MM/YYYY') as DTPROPENTRY2,
If ((DTPROPENTRY) < Date('$(vStartDate)','DD-MM-YYYY'), 'Before', 'After') as PERIOD,
DTPROPSIGNED,
STRCLIENTCD,
NPROPSTATCD,
DTPROPSTAT,
DTINFORCE,
STRCMPNYBRANCHCD
FROM "ADM_STG"."IL_NB_BASE_PROP_DTL"
where DTPROPENTRY >= >= Date($(vBeforeDate),'DD-MM-YYYY');

Really appreciate a fast response from anyone who can help me to fix this.

Thanks and regards,

Amelia

[PrashantSangle]

Date() and Date#() is qlik defined function. It won't work in Oracle

you have to see for equivalent function in oracle. I think to_date() and to_char() function work similar in oracle.

to debug you code first run your query in oracle ( if you have toad then run in toad or similar IDE)

Regards,

Prashant Sangle

[marcus_sommer]

An alternatively to the use of native database-functions would be not to transform the fields within the sql else within Qlik within a preceeding load on top of the sql. See here what is meant:

Preceding Load - Qlik Community - 1469534

- Marcus