have problem with the sort stage

[ady]

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

[DSguru2B]

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

Code: Select all

Date() < ICONV(A.REDEPLOY_EFF_DATE,"<CONVERSION CODE>") AND Date() > ICONV(A.EFFECTIVE_DATE,"<CONVERSION CODE>")
 

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

[ray.wurlod]

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).