Serverjob - Parameters in column derivation

[michaelsarsnorum]

Hi

I'm trying to use use a job parameter in the derivation field for a column i a DB stage. My formula looks like this:

Code: Select all

ADD_MONTHS(TO_DATE('#Periode#', 'YYYYMM') -1, 1)

Somehow it seems that datastage does not replace #Periode# with the correct value set for the parameter.

The same parameter is used in the selection criterias and works fine there.

The generated SQL works if I switch the stage to "user defined sql" and use the wxact same query.

Does datastage support the use of job parameters in the derivation field?

brg
Michael Sars Norum

[SeRpEnTmInD]

try this code:

Code: Select all

ADD_MONTHS(TO_DATE(Periode, 'YYYYMM') -1, 1)

[vmcburney]

I would never put a sql function into a column derivation, no point have an ETL tool if you are going to do the T part in your SQL select statement. Better off doing it in a transformer.

I think you hit the nail on the head, you cannot use a job parameter within the columns derivation field but you can use it in user-defined SQL.

[ray.wurlod]

I would never put a sql function into a column derivation, simply because it doesn't work.

You can certainly use job paramter values in column derivations (I'm assuming that we're in a Transformer stage here). It's the SQL functions that you can't have. On the other hand, if you have an ADD_MONTHS and a TO_DATE routine or transform, then it would work. But I suspect you don't.

There have been some attempts posted here at an AddMonths routine, none of which is entirely satisfactory. And the DataStage equivalent of TO_DATE is Iconv. So, assuming you had an AddMonths routine, a derivation expression might look like

Code: Select all

AddMonths(Iconv(Periode, "DYMD"), -1) 

Note that, in expressions, job parameter references are not delimted by "#" characters; you should always choose them from the Expression Editor, to eliminate the possibility of typing mistakes.

[michaelsarsnorum]

This is not a transformer stage, it is in the column derivation field in the ouput pane of an Oracle8-stage.

SQL functions work smoothly here (the wiseness of using an sql function here is another discussion, I'm just modifying an old job created by someone else). When placing an SQL function here it is simply executed at query time in the database.

The real issue is that apparently datasatge parses generated and user-defined queries difefrently, only replacing #parameter_name# with the parameter value in user defined fields such as selection criterias and user defined queries, leaving queries generated from the column definitions unchanged.

brg
Michael

[ray.wurlod]

D'accord, please ignore my post which was based on an incorrect assumption. I can only suggest that you report this as a bug.

[chulett]

I can verify this - while sql functions work just fine in a column derivation of active stages like an OCI stage, for some reason Job Parameters cannot be part of the mix. Bug or feature I have no idea, but there have been a couple of times I really wanted to do that and have to keep remembering that it just don't play that game.

This usually after trying it again and wondering what the heck is going on.

[michaelsarsnorum]

I went ahead and tested this issue a bit more, and as far as I can tell the problem only surfaces when I press the view data button in the output pane of the stage's properties.

I have found that I can use parameters in the column derication field on compiled jobs that are sterted through DS Director and the DS Designer Debgugger. It is only when using the view data function that DS fails to replace the #param_name# instances with the correct value.

It seems as though the queries are parsed differently in the two cases.

[cmmurari]

Hi michaelsarsnorum

you can able to use job parameters in Derivation column no issue.

you should try this

TO_CHAR(ADD_MONTHS(TO_DATE('#Periode#', 'YYYYMM') -1, 1), 'YYYY-MM-DD HH24:MI:SS')

cheers,
kri