compare db2 year function with host variable
Posted: Wed Jan 16, 2013 9:12 pm
I have to compare a db2 date with a host variable. The date from db2 table could be any date of the month. The host variable can be defined easily as first of the month. How to compare them ? Plus, I have to compare them in select statment rather than as a predicate.
e.g.
I am getting errors such as the host variables WS-AC-START-DT-YY and WS-AC-START-DT-MM are not defined correctly. So, I don't know what is the correct way of defining such host variables. I am defining them as 9(04) comp.
Also is there any other way to do this ? Once this query goes through my DBAs, I know they will reject it as they do not like me using YEAR and MONTH (or even LAST_DAY) functions as it slows down the performance.
P.S. the query given above is a small part of a bigger query, so it cannot be done in COBOL also. I have to do it somehow in db2.
Any suggestion would be really grateful.
thanks a lot.
e.g.
SELECT CASE
WHEN YEAR(T1.AC_START_DT) =
:WS-AC-START-DT-YY
AND MONTH(T1.AC_START_DT) =
:WS-AC-START-DT-MM
THEN DATE(T1.AC_START_DT) - 1 DAY
ELSE :WS-SOME-OTHER-DT
END AS FROMDT
FROM Table1 T1
WHERE ACC_NO = :WS-AC-NO
WHEN YEAR(T1.AC_START_DT) =
:WS-AC-START-DT-YY
AND MONTH(T1.AC_START_DT) =
:WS-AC-START-DT-MM
THEN DATE(T1.AC_START_DT) - 1 DAY
ELSE :WS-SOME-OTHER-DT
END AS FROMDT
FROM Table1 T1
WHERE ACC_NO = :WS-AC-NO
I am getting errors such as the host variables WS-AC-START-DT-YY and WS-AC-START-DT-MM are not defined correctly. So, I don't know what is the correct way of defining such host variables. I am defining them as 9(04) comp.
Also is there any other way to do this ? Once this query goes through my DBAs, I know they will reject it as they do not like me using YEAR and MONTH (or even LAST_DAY) functions as it slows down the performance.
P.S. the query given above is a small part of a bigger query, so it cannot be done in COBOL also. I have to do it somehow in db2.
Any suggestion would be really grateful.
thanks a lot.