topic Re: Month function in sql query where clause in QlikView

Month function in sql query where clause

Anonymous — Wed, 06 Apr 2016 08:30:41 GMT

Hi!

What is wrong in my where clause:

I have a ODBC connection and I'm trying to limit the data by year and month functions in the query.

This is my where limit and the year seems to work but it gives error with the month "invalid tag".

FROM IFSAPP."GEN_LED_VOUCHER_ROW"
WHERE YEAR(VOUCHER_DATE)=$(vYear) and MONTH(VOUCHER_DATE)=$(vMonth);

The variables are

And the form of voucher date in the database is

What am I doing wrong?

Regards,

Heidi

Kushal_Chawda — Wed, 06 Apr 2016 10:14:41 GMT

try with single quotes

WHERE YEAR(VOUCHER_DATE)='$(vYear)' and MONTH(VOUCHER_DATE)='$(vMonth)';

tamilarasu — Wed, 06 Apr 2016 10:17:21 GMT

Hi Heidi,

Change your variable like below,

Let vMonthStart = Date(Monthstart(today()),'D.M.YYYY h:mm:ss');

Let vMonthEnd = Date(Monthend(today()),'D.M.YYYY h:mm:ss');

And SQL statement like below

FROM IFSAPP."GEN_LED_VOUCHER_ROW"

WHERE VOUCHER_DATE> '$(vMonthStart)' and VOUCHER_DATE='$(vMonthEnd)';

avinashelite — Wed, 06 Apr 2016 10:19:47 GMT

Problem is the formats are Different i.e

WHERE YEAR(VOUCHER_DATE)=$(vYear) and NUM(MONTH(VOUCHER_DATE))=$(vMonth);

MONTH() will return a text string and your comparing with the num

Kushal_Chawda — Wed, 06 Apr 2016 10:50:20 GMT

or try this where instead of creating variables

WHERE VOUCHER_DATE BETWEEN trunc (sysdate,'mm') AND SYSDATE

Anonymous — Wed, 06 Apr 2016 11:04:20 GMT

FROM IFSAPP."GEN_LED_VOUCHER_ROW"
WHERE YEAR(VOUCHER_DATE)='$(vYear)' and MONTH(VOUCHER_DATE)='$(vMonth)';

Before reloading it is better debug your query so that u can know which values are getting populated into $(vYear) and $(vMonth)

tamilarasu — Wed, 06 Apr 2016 12:24:42 GMT

Sorry. I have wrongly mentioned as = instead of <=. Correct statement should be

FROM IFSAPP."GEN_LED_VOUCHER_ROW"

WHERE VOUCHER_DATE>= '$(vMonthStart)' and VOUCHER_DATE<='$(vMonthEnd)';

Anonymous — Wed, 06 Apr 2016 17:12:12 GMT

Thanks all for help!

I ended up doing a bit like Tamil adviced, using the month start and end dates to limit my data into a spesific month with between function.