I have a job where the i need to implement the query using stages
the query looks something like this
SELECT
A.RACCOUNT
C.REP_TO_ACC
FROM VB.R_M_LAST A, VA.M_H_LAST B, VA.M_H_LAST C WHERE A.M_ACCOUNT_NO = B.M_ACCOUNT_NO
AND B.REP_TO_ACC = C.M_ACC_NO
AND C.HQ_IND = 'Y'
AND CURRENT DATE < A.REDEPLOY_EFF_DATE
AND CURRENT DATE > A.EFFECTIVE_DATE FOR READ ONLY WITH UR
i have problem giving the constraint for the current date. could anyone let me know how to give this constraint?
i have tried giving Date() < A.REDEPLOY_EFF_DATE AND Date() > A.EFFECTIVE_DATE.
but the output is not right
have problem with the sort stage
Moderators: chulett, rschirm, roy
What format is your date in. Date() gives internal format (number of days since Dec 31, 1967). You need to do your comparison by changing your date into internal format.
SOmething like
Look at the datastage help for ICONV to get the conversion code that fits your date format.
P.S: How does your post subject relate to your post body![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
SOmething like
Code: Select all
Date() < ICONV(A.REDEPLOY_EFF_DATE,"<CONVERSION CODE>") AND Date() > ICONV(A.EFFECTIVE_DATE,"<CONVERSION CODE>")
P.S: How does your post subject relate to your post body
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need to discover what constant your particular database (you chose not to reveal which it was) uses to represent the current date. Is it SYSDATE, CURRENT_DATE or some other formulation. It is not a DataStage problem.
That said, the Sort stage does not have the capacity to execute SQL in any case (refer to the thread subject).
That said, the Sort stage does not have the capacity to execute SQL in any case (refer to the thread subject).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.