Use of selfmade routine
Moderators: chulett, rschirm, roy
Use of selfmade routine
Hello,
I made a routine to determine end of month (YYYY-MM-DD) based on year and month in DataStage (typed Transform Function). Therefore I can use this function in the Transformer to drop rows. This works.
Actually I wanted to use this function in the WHERE-clause of my SQL statement as this looks more efficient to me, less rows to upload. This leads to the mesage: Function unknown. Probably because this is not a SQL-function.
Can someone help me to inform me how I can build routines to use in the SQL WHERE- clause when retrieving data via ODBC-stage from a MS SQL Server and via the DB2-stage?
Thanks in advance.
Jan
I made a routine to determine end of month (YYYY-MM-DD) based on year and month in DataStage (typed Transform Function). Therefore I can use this function in the Transformer to drop rows. This works.
Actually I wanted to use this function in the WHERE-clause of my SQL statement as this looks more efficient to me, less rows to upload. This leads to the mesage: Function unknown. Probably because this is not a SQL-function.
Can someone help me to inform me how I can build routines to use in the SQL WHERE- clause when retrieving data via ODBC-stage from a MS SQL Server and via the DB2-stage?
Thanks in advance.
Jan
Welcome aboard. If your database supports creating functions, that's where you need to do it. DS Functions are only good inside DS. When DS issues the SQL to the souce/target database the SQL is in the hands of the database, any use of functions in the SQL must be valid within the database itself.
If your date is a fixed value, consider deriving it first and passing it in as a job parameter. Then, your SQL can make use of a job parameter, which is resolved at the time the SQL is sent to the database.
If your date is a fixed value, consider deriving it first and passing it in as a job parameter. Then, your SQL can make use of a job parameter, which is resolved at the time the SQL is sent to the database.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can do it in SQL with SQL functions, not with DataStage functions as Ken indicated. One way is to determine whether the difference between the date and the first of the subsequent month is 1. If that is true, then your date is the last day of its month. Different databases use different date manipulations, but most - if not all - can achieve this result.
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.
You slight me sir I said no such thing. Please re-read my post.ray.wurlod wrote:You can do it in SQL with SQL functions, not with DataStage functions as Ken indicated.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Usually MSQL and any other database have quite similar syntax for case statments. You can do that in you SQL as well.
The error you posted seems to be that either you are not allowed to use the functions or your SQL is no right.
First test your SQL in the enterprise manager of MSSQL or the DB2connect for DB2.
The error you posted seems to be that either you are not allowed to use the functions or your SQL is no right.
First test your SQL in the enterprise manager of MSSQL or the DB2connect for DB2.
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:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Then you might like the "strict standards" enforced at the The Manly Daily ( newspaper in Sydney, Australia), which reported last week that Senator Bill Heffernan had rejected certain allegations as "b--lshit".chulett wrote:Can you say 'Shites' here? Hmm... guess so. I'll have to check it out.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Should still be possible.
Get the date of the first of next month from the three components:
Year = (If Month = 12 Then Year + 1 Else Year)
Month = (If Month = 12 Then 1 Else Month + 1)
Day = 1
Build these into a date, and use a DateDiff function to calculate the date of the day prior.
(I'm not fully au fait with SQL Server's SQL syntax, but that would be the algorithm. You may need CASE statements in an inner query to generate equivalents to the If tests given.)
Get the date of the first of next month from the three components:
Year = (If Month = 12 Then Year + 1 Else Year)
Month = (If Month = 12 Then 1 Else Month + 1)
Day = 1
Build these into a date, and use a DateDiff function to calculate the date of the day prior.
(I'm not fully au fait with SQL Server's SQL syntax, but that would be the algorithm. You may need CASE statements in an inner query to generate equivalents to the If tests given.)
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.
If doing this is SQL scares you then you can do it in DataStage, build a parameter file and then call the job by passing the parameters to it, via the parameter file.
You can also build the date inside a job and within that job itself, in the transformer, call the second job that takes in the date parameter and selects the required rows. Look into the UtilityRunJob() routine.
You can also build the date inside a job and within that job itself, in the transformer, call the second job that takes in the date parameter and selects the required rows. Look into the UtilityRunJob() routine.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.