request with date of day
Moderators: chulett, rschirm, roy
request with date of day
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
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
What is the error that you are getting?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
X = OCONV(DATE(), "D/E")
X = "16/10/2006"
X = "16/10/2006"
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Unofortunately, this is a PX job and OCONV() is not available.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
Whoops - Sorry ... Still learning to read ...
How about:
MonthDayFromDate(CurrentDate()) : "/" :
MonthFromDate(CurrentDate()) : "/" :
YearFromDate(CurrentDate())
J.
How about:
MonthDayFromDate(CurrentDate()) : "/" :
MonthFromDate(CurrentDate()) : "/" :
YearFromDate(CurrentDate())
J.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
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
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
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
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:
J.
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')
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')
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>