Page 1 of 1
request with date of day
Posted: Fri Oct 13, 2006 2:04 am
by incognico
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
Posted: Fri Oct 13, 2006 2:16 am
by ArndW
What is the error that you are getting?
Posted: Fri Oct 13, 2006 4:37 am
by ray.wurlod
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.
Posted: Fri Oct 13, 2006 5:13 am
by incognico
yeah i know but how ?
Could you give me my request with the good syntax ?
Thank's
Nick
Posted: Fri Oct 13, 2006 6:19 am
by kumar_s
Check if dt_saisie has data for current data and for the given condition.
Posted: Fri Oct 13, 2006 6:58 am
by incognico
dt_saisie : 28/09/2005 format Date under Oracle
Posted: Fri Oct 13, 2006 7:15 am
by kumar_s
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.
Posted: Mon Oct 16, 2006 2:21 am
by incognico
so my question is:
How can i have the date of the current day on this format : 16/10/2006
Thanks.
Nick
:D
Posted: Mon Oct 16, 2006 2:25 am
by incognico
so my question is:
How can i have the date of the current day on this format : 16/10/2006
Thanks.
Nick
:D
Posted: Mon Oct 16, 2006 3:55 am
by jhmckeever
X = OCONV(DATE(), "D/E")
X = "16/10/2006"
Posted: Mon Oct 16, 2006 4:08 am
by ArndW
Unofortunately, this is a PX job and OCONV() is not available.
Posted: Mon Oct 16, 2006 4:23 am
by jhmckeever
Whoops - Sorry ... Still learning to read ...
How about:
MonthDayFromDate(CurrentDate()) : "/" :
MonthFromDate(CurrentDate()) : "/" :
YearFromDate(CurrentDate())
J.
Posted: Mon Oct 16, 2006 4:54 am
by incognico
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
Posted: Mon Oct 16, 2006 7:25 am
by jhmckeever
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:
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.