Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try with single quotes
WHERE YEAR(VOUCHER_DATE)='$(vYear)' and MONTH(VOUCHER_DATE)='$(vMonth)';
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)';
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
or try this where instead of creating variables
WHERE VOUCHER_DATE BETWEEN trunc (sysdate,'mm') AND SYSDATE
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)
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)';
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.