request with date of day

incognico · Post by **incognico** » Fri Oct 13, 2006 2:04 am

hello!!

i want to compare a field and the date of the day ni datastage 7.01PX

so my request is

select * from #TA_DWH_FT_ECRITURE# where id_cdf like 'K%' and to_char(dt_saisie,'dd/mm/yyyy')=#DATE_JOUR#
dt_saisie is in a date format.

DATE_JOUR is a parameter of this job: Oconv(Date(), "D4/DMY")

it's not ok, i have an error
how can i make this request ?

Thank's

Nick

ArndW · Post by **ArndW** » Fri Oct 13, 2006 2:16 am

What is the error that you are getting?

ray.wurlod · Post by **ray.wurlod** » Fri Oct 13, 2006 4:37 am

Then, quite simply, the value of the DATE_JOUR must also be in the same format. You will also need to convert both to date or both to string if necessary so that the comparison will be valid.

incognico · Post by **incognico** » Fri Oct 13, 2006 5:13 am

yeah i know but how ?

Could you give me my request with the good syntax ?
Thank's

Nick

kumar_s · Post by **kumar_s** » Fri Oct 13, 2006 6:19 am

Check if dt_saisie has data for current data and for the given condition.

incognico · Post by **incognico** » Fri Oct 13, 2006 6:58 am

dt_saisie : 28/09/2005 format Date under Oracle

kumar_s · Post by **kumar_s** » Fri Oct 13, 2006 7:15 am

If you got any error, pls provide the same.
If you are getting any outpout, check if dt_saisie consit any data with todays date (system date) value. As that is what you are checking for.

incognico · Post by **incognico** » Mon Oct 16, 2006 2:21 am

so my question is:

How can i have the date of the current day on this format : 16/10/2006

Thanks.

Nick

:D

incognico · Post by **incognico** » Mon Oct 16, 2006 2:25 am

so my question is:

How can i have the date of the current day on this format : 16/10/2006

Thanks.

Nick

:D

jhmckeever · Post by **jhmckeever** » Mon Oct 16, 2006 3:55 am

X = OCONV(DATE(), "D/E")
X = "16/10/2006"

ArndW · Post by **ArndW** » Mon Oct 16, 2006 4:08 am

Unofortunately, this is a PX job and OCONV() is not available.

jhmckeever · Post by **jhmckeever** » Mon Oct 16, 2006 4:23 am

Whoops - Sorry ... Still learning to read ...

How about:

MonthDayFromDate(CurrentDate()) : "/" :
MonthFromDate(CurrentDate()) : "/" :
YearFromDate(CurrentDate())

J.

incognico · Post by **incognico** » Mon Oct 16, 2006 4:54 am

My result :

query is: select * from dwh.ta_dwh_ft_ecriture where id_cdf like 'K%' and to_char(dt_saisie,'dd/mm/yyyy')=MonthDayFromDate(CurrentDate()) : "/" : MonthFromDate(CurrentDate()) : "/" : YearFromDate(CurrentDate())
sqlcode is: -933
esql complaint: ORA-00933: la commande SQL ne se termine pas correctement

jhmckeever · Post by **jhmckeever** » Mon Oct 16, 2006 7:25 am

incognico,

Sorry - I misunderstood the intent of your original post. I thought you wanted to perform the comparison in DataStage. You can't submit the XXXFromDate() functions to Oracle as these are DataStage functions. The Oracle equivalent is:

Code: Select all

to_char(sysdate,'dd/mm/yyyy')

If you want to always compare against the current date (on the Oracle server's clock) you can avoid having to pass the date as a parameter by using:

Code: Select all

select * from #TA_DWH_FT_ECRITURE# where id_cdf like 'K%' and to_char(dt_saisie,'dd/mm/yyyy')=to_char(sysdate,'dd/mm/yyyy')

J.