Use of selfmade routine

[jzijl]

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

[kcbland]

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.

[ray.wurlod]

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.

[kcbland]

You can do it in SQL with SQL functions, not with DataStage functions as Ken indicated.

You slight me sir I said no such thing. Please re-read my post.

[ray.wurlod]

Let me change the punctuation to indicate what I meant.

You can do it in SQL with SQL functions, not with DataStage functions, as Ken indicated.

[kcbland]

Thanks, that simple comma changes the tone.

[chulett]

Eats, Shoots and Leaves.

[DSguru2B]

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.

[ray.wurlod]

Eats, Shoots and Leaves.

The parody (Eats, Shites and Leaves) is funnier.

[chulett]

Can you say 'Shites' here? Hmm... guess so. I'll have to check it out.

[kduke]

Best 3 word answers ever. "Take. Adapt. Shine." pretty good too.

[ray.wurlod]

Can you say 'Shites' here? Hmm... guess so. I'll have to check it out.

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

[jzijl]

I understood that it can be solved by making your own User Defined Functions in MSS.
Using a job paramater is unfortunately not possible as I have only the year and month and have to determine the end of month.

Thanks all.

Jan

[ray.wurlod]

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

[DSguru2B]

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.