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
user defined SQL query
Moderators: chulett, rschirm, roy
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.
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.
Kind Regards
Stephan
Stephan
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,
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,
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
thanks a lot ...it works perfect.