topic Re: mismatch of date formats with SQL date() function and qlikview variable in QlikView

mismatch of date formats with SQL date() function and qlikview variable

Tue, 11 Dec 2012 12:13:58 GMT

Hi everybody,

I am trying to use the where clause in SQL to select data from ODBC. The a.sh_sf_dt field is a timestamp the expression date(a.sh_sf_dt) gives me the DD.MM.YYYY format. I try running the following code and get the attached error message. Any idea how I can solve this??

My goal is to put this in a loop so that I can get the inventory quantities by item at the end of each date. That's why it's important for me to be able to say $(vSnapShot) + i.

let vSnapShot = Date#('01.01.2012', DD.MM.YYYY);
Load *, brutStok-standartStok as kMetrajStok;
  SQL 
  SELECT   a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,
  sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok, 
  sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt 
  WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt 
  ELSE 0*a.sh_qty_mt END)as standartStok
        FROM DBA.stkhar AS a 
        LEFT JOIN DBA.mam AS b
        ON a.sh_ma_kod = b.ma_kod
    WHERE date(a.sh_sf_dt) <= $(vSnapShot) 
        GROUP BY   snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry 
        order by   snapDate, ma_kod, desen, varyant
  ;

Thanks for helping.

Re: mismatch of date formats with SQL date() function and qlikview variable

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 12:17:54 GMT

Hi,

Try just wraping the variable with single quotes:

WHERE date(a.sh_sf_dt) <= '$(vSnapShot)'

Hope that helps.

Miguel

Re: mismatch of date formats with SQL date() function and qlikview variable

Tue, 11 Dec 2012 12:23:47 GMT

Hi Miguel I've already tried that but I get the following error messge:

It looks like even though I am using the date() function in SQL it still stays as a timestamp.

SQL##f - SqlState: 07006, ErrorCode: 4294967139, ErrorMsg: [Sybase][ODBC Driver][Adaptive Server Anywhere]Cannot convert 01.01.2012 to a timestamp

SQL

SELECT a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

ELSE 0*a.sh_qty_mt END)as standartStok

FROM DBA.stkhar AS a

LEFT JOIN DBA.mam AS b

ON a.sh_ma_kod = b.ma_kod

WHERE date(a.sh_sf_dt) <= '01.01.2012'

GROUP BY snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

order by snapDate, ma_kod, desen, varyant

Re: mismatch of date formats with SQL date() function and qlikview variable

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 12:44:17 GMT

Hi,

Is the "a.sh_sf_dt" field a datetime field or a timestamp field? If the former, then make sure that the input format "MM.DD.YYYY" is the one that field expects (instead of i.e.: "MDY" or "MM-DD-YYYY"). If the latter, timestamp data types have nothing to do with actual date or time fields, so you will not be able to use that field for comparing...

Hope that helps.

Miguel

Re: mismatch of date formats with SQL date() function and qlikview variable

Tue, 11 Dec 2012 13:37:15 GMT

Hi Miguel,

Thanks for the information. I was able to solve the problem by using the following at the end. Strings are easier to manage than dates .

let vSnapShot=Date#('01.01.2012', DD.MM.YYYY);

stok:

Load *, brutStok-standartStok as kMetrajStok;

SQL

SELECT a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

ELSE 0*a.sh_qty_mt END)as standartStok

FROM DBA.stkhar AS a

LEFT JOIN DBA.mam AS b

ON a.sh_ma_kod = b.ma_kod

WHERE a.sh_yil = 2012 AND substr(a.sh_sf_dt,9,2)||'.'||substr(a.sh_sf_dt,6,2)||'.'||substr(a.sh_sf_dt,1,4) <= '$(vSnapShot)'

GROUP BY snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

order by snapDate, ma_kod, desen, varyant

;

Now I am trying to put this in a loop and I use the following but I get an error message saying:

Script line error:

for i=0

Any ideas for this one??

let vSnapShot=Date#('01.01.2012', DD.MM.YYYY);

for i=0

do while $(vSnapShot) <= Date#('05.01.2012', DD.MM.YYYY)/*date(Date#(now()))*/

let vSnapShot =date(Date#('01.01.2012', DD.MM.YYYY)+$(i));

stok:

//sh_sf_hk_kod > 30 stok çıkışı; sh_sf_hk_kod < 30 stok girişi

Load *, brutStok-standartStok as kMetrajStok;

SQL

SELECT a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, date(a.sh_sf_dt) as snapDate,

sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

ELSE 0*a.sh_qty_mt END)as standartStok

FROM DBA.stkhar AS a

LEFT JOIN DBA.mam AS b

ON a.sh_ma_kod = b.ma_kod

//WHERE a.sh_yil = 2012 AND ma_kod = 'ME1050-01' AND a.sh_sf_ay = 8

WHERE a.sh_yil = 2012 AND substr(a.sh_sf_dt,9,2)||'.'||substr(a.sh_sf_dt,6,2)||'.'||substr(a.sh_sf_dt,1,4) <= '$(vSnapShot)'

//WHERE a.sh_yil = 2012 AND left(a.sh_sf_dt,10) <= '$(vSnapShot)'

GROUP BY snapDate, a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

order by snapDate, ma_kod, desen, varyant

;

let i =$(i)+1;

loop;

Thanks again for all your help.

Re: mismatch of date formats with SQL date() function and qlikview variable

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 14:40:14 GMT

Hi,

Glad to know that that was helpful and now it works.

And yes, the FOR loop in QlikView has a different syntax than in your script. Actually, you are using a DO WHILE loop, so you don't need to use the FOR line. Anyway, should you would, the syntax is

FOR i = 0 TO 10
... // script here
NEXT

You don't have the TO part in the FOR, that's why QlikView is showing the error.

Hope that helps.

Miguel