DSXchange

hi,
i have to execute this SQL query which works fine in SQL+ in datastage ORA 9i plugins
i use the 'User-defined SQL query
but i dont know how to define columns for table x1 and x2 ?

ZZZ is the name of the table

select xc.nudoss,xc.datent,xc.datsor,xc.rsstat,
decode(xc.datent,x1.datent,null,x1.datent),
decode(xc.datent,x1.datent,null,x1.datsor),
decode(xc.datent,x1.datent,null,x1.rsstat),
decode(x1.datent,x2.datent,null,x2.datent),
decode(x1.datent,x2.datent,null,x2.datsor),
decode(x1.datent,x2.datent,null,x2.rsstat)
from ZZZ xc,ZZZ x1,ZZZ x2
where xc.nudoss=x1.nudoss and xc.nudoss=x2.nudoss
and xc.datent <= SYSDATE and xc.datsor > SYSDATE
and decode(xc.nuligp,0,xc.nulign,x1.nuligs) = xc.nulign
and decode(x1.nuligp,0,x1.nulign,x2.nuligs) = x1.nulign
and x1.datent <= xc.datent and x2.datent <= x1.datent

thanks to help me

Jauthier,

Looks like you can do in the columns page of the ouput tab. Introduce new column names under 'Column Names' and type the derivation under 'Derivation', e.g.

ColumnName=columneight
Derivation=decode(x1.datent,x2.datent,null,x2.datent)

Specifying 'ZZZ xc,ZZZ x1,ZZZ x2' as Table Names on the General tab and typing the where clause in the Section tab, it should even be possible to have the whole thing as a generated instead of userdefined query.

Well... just define them like you would any other column.

You'll need to give them a name, similar to an alias, and then pick an appropriate type to hold the data. It should match the original type of the fields you are decoding, more than likely, but I would think it could all be "decoded" into a generic varchar if you wanted. Completely guessing here, but you could do somthing like (after the first four columns):

Decode1 varchar 10,
Decode2 varchar 10... etc.

BTW, this doesn't need to be "user defined". It would be simple to set up as "column generated", for example. Put the aliased field names for the first four fields, and the decodes statements for the others, into the Derivation column. Put your "three" table names (with aliases) in the From field and your where clause (minus the 'where' of course) in the Where field. The stage will build your query for you.

My two cents - avoid the "fully generated" option, if possible. Yuck.

Hope this helps,

thanks a lot ...it works perfect.