topic Re: sql syntax error in QlikView

sql syntax error

Tue, 16 Sep 2014 06:13:39 GMT

Hi QlikView Friends,

Please help me with this strange issue.

i'm running this syntax in qlikview on oracle database, but gives me error.

SQL SELECT * FROM MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE CPUDT >$(LASTRELOADTIME));

Thanks

vishwaranjan

Re: sql syntax error

sujeetsingh — Tue, 16 Sep 2014 06:17:39 GMT

Check for timestamp issue or Variable value?

Re: sql syntax error

ajaykumar1 — Tue, 16 Sep 2014 06:17:44 GMT

Hi,

Is single code required?

>'$(LASTRELOADTIME)');

Regards,

Ajay

Re: sql syntax error

jagan — Tue, 16 Sep 2014 06:24:15 GMT

Hi,

Include single quotes for variable names

TableName:

SQL SELECT * FROM MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE CPUDT >'$(LASTRELOADTIME)');

Regards,

jagan.

Re: sql syntax error

Anonymous — Tue, 16 Sep 2014 06:47:22 GMT

Try this:

SQL SELECT * FROM MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE CPUDT > '$(LASTRELOADTIME)' );

Re: sql syntax error

Tue, 16 Sep 2014 06:57:24 GMT

thanks for response,but i had tried but not get success.

i havve also try according to this

SQL SELECT * FROM MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE TO_DATE(CPUDT ,'DD-MM-YYYY')>'$(LASTRELOADTIME)');

************************************************************************************************

and also try according to this

let LASTRELOADTIME = date(date#(today()-1,'DD-MM-YYYY'),'DD-MM-YYYY');

let date_key = 'to_date(CPUDT,'DD-MM-YYYY')';

SQL SELECT * FROM MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE '$(date_key)' >'$(LASTRELOADTIME)');

Thanks

Re: sql syntax error

Tue, 16 Sep 2014 07:37:35 GMT

hi sir

your syntax execute successfully. but not compare both date in

WHERE CPUDT >'$(LASTRELOADTIME)');

here fetch all dates of CPUDT

Thanks

Re: sql syntax error

jagan — Tue, 16 Sep 2014 07:54:44 GMT

Hi,

Check date format LASTRELOADTIME and CPUDT.

Also use this statement

let LASTRELOADTIME = date(today()-1,'DD-MM-YYYY');

Regards,

Jagan.

Re: sql syntax error

dinuwanbr — Tue, 16 Sep 2014 08:07:13 GMT

Hi,

Check out this

SQL SELECT * FROM MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE CPUDT >date('$(LASTRELOADTIME)'));

SQL SELECT * FROM MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE date(CPUDT) >'$(LASTRELOADTIME)');

BR,

-dinu1

Re: sql syntax error

Tue, 16 Sep 2014 09:25:58 GMT

hi sir

thanks for your response, i checked that both format are same as DD-MM-YYYY

problem is that, both date (CPUDT and LASTRELOADTIME ) are not match.

Re: sql syntax error

er_mohit — Tue, 16 Sep 2014 11:17:03 GMT

Hi Try this way

Istly make Date variable in number format.

Let vToday = num(Date#('01-01-2008', 'DD-MM-YYYY'));

SQL SELECT * FROM MSEG WHERE MBLNR IN

(SELECT MBLNR FROM MKPF WHERE CPUDT >= $(vToday ) );

Re: sql syntax error

Tue, 16 Sep 2014 11:34:38 GMT

hi mohit

thanks for response,

but my CPUDT field in 'DD.MM.YYYY' format in oracle database

Re: sql syntax error

dinuwanbr — Tue, 16 Sep 2014 11:36:41 GMT

Hi,

Use this then and try

Let vToday = num(Date#('01-01-2008', 'DD.MM.YYYY'));

SQL SELECT * FROM MSEG WHERE MBLNR IN

(SELECT MBLNR FROM MKPF WHERE CPUDT >= $(vToday ) );

BR,

dinu1

Re: sql syntax error

er_mohit — Tue, 16 Sep 2014 11:36:56 GMT

ok so make variable with this

Let vToday = num(Date#('01-01-2008', 'DD.MM.YYYY'));

Re: sql syntax error

Wed, 17 Sep 2014 05:48:09 GMT

hi friends

thanks for all,

i got solution, the problem is that our CPUDT date field by default in 'YYYYMMDD' that is in system format. so i have use this syntax.

let LASTRELOADTIME = date(date#('20140915','YYYYMMDD'),'YYYYMMDD');

SQL SELECT * FROM MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE CPUDT >'$(LASTRELOADTIME)');

Thanks & Regards

vishwaranjan kumar