Is there any way in DataStage, when it generate the query attached the table
alias with the column?
DSGenerate Query.
---------------
Selat a,a,b
from a,b
where a.a=b.a
Actual Query need is
--------------------
Select a.a,b.a,b.b
from a,b
where a.a=b.a
Workaround
-----------
In Colums tab, add alias to each line then I can get the results. But I have
too many fields.
above is the sample query, I have a long query where I have to add the table
alias.
Im using Oracle8 OCI
thanks in advance.
rahim
Rahim Ladha
e-mail : rahimladha@hotmail.com
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
column ambiguously - table alias
Rahim, do yourself a favor and either:
1) Write and perfect the sql using something like SQL-Navigator, then cut and and paste it into the user-defined SQL section of the OCI stage.
2) Write and perfect the sql using something like SQL-Navigator, then put it into an SQL script and define the OCI stage to use this SQL script.
3) Write and perfect the sql using something like SQL-Navigator, then spool the output to a sequential file and use a sequential file in DataStage to read the output.
Any of the above 3 choices is easier to manage that trying to make any of the ODBC/OCI stages do complex SQL!!!!!!
I repeat, write and perfect the SQL in a tool designed to do so. The only way you can test your SQL in DataStage is to hit the View Data button, and you cant cancel that!! If you didnt save the job before hitting View Data, you lose your changes when you have to KILL YOUR CLIENT when your SQL is doing a cartesian product on 4 billion rows of data.
Good luck!
-Ken
rahimladha@hotmail.com on 24-Jul-2001 18:16
Please respond to datastage-users@oliver.com
To: datastage-users
cc:
Subject: column ambiguously - table alias
Is there any way in DataStage, when it generate the query attached the table alias with the column?
DSGenerate Query.
---------------
Selat a,a,b
from a,b
where a.a=b.a
Actual Query need is
--------------------
Select a.a,b.a,b.b
from a,b
where a.a=b.a
Workaround
-----------
In Colums tab, add alias to each line then I can get the results. But I have too many fields.
above is the sample query, I have a long query where I have to add the table alias.
Im using Oracle8 OCI
thanks in advance.
rahim
Rahim Ladha
e-mail : rahimladha@hotmail.com
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
1) Write and perfect the sql using something like SQL-Navigator, then cut and and paste it into the user-defined SQL section of the OCI stage.
2) Write and perfect the sql using something like SQL-Navigator, then put it into an SQL script and define the OCI stage to use this SQL script.
3) Write and perfect the sql using something like SQL-Navigator, then spool the output to a sequential file and use a sequential file in DataStage to read the output.
Any of the above 3 choices is easier to manage that trying to make any of the ODBC/OCI stages do complex SQL!!!!!!
I repeat, write and perfect the SQL in a tool designed to do so. The only way you can test your SQL in DataStage is to hit the View Data button, and you cant cancel that!! If you didnt save the job before hitting View Data, you lose your changes when you have to KILL YOUR CLIENT when your SQL is doing a cartesian product on 4 billion rows of data.
Good luck!
-Ken
rahimladha@hotmail.com on 24-Jul-2001 18:16
Please respond to datastage-users@oliver.com
To: datastage-users
cc:
Subject: column ambiguously - table alias
Is there any way in DataStage, when it generate the query attached the table alias with the column?
DSGenerate Query.
---------------
Selat a,a,b
from a,b
where a.a=b.a
Actual Query need is
--------------------
Select a.a,b.a,b.b
from a,b
where a.a=b.a
Workaround
-----------
In Colums tab, add alias to each line then I can get the results. But I have too many fields.
above is the sample query, I have a long query where I have to add the table alias.
Im using Oracle8 OCI
thanks in advance.
rahim
Rahim Ladha
e-mail : rahimladha@hotmail.com
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
I just did some quick playing with this, using DataStage 4.1.1.
I have always thought that there is a problem with the OCI stage in relation to the derivation property. Why does it have a derivation property for each column if it doesnt use it? The ODBC stage uses the derivation as the expression in the SELECT clause of the generated SQL, but the OCI stage seems to ignore it. As a result, the ODBC stage generates the correct query for what you are trying to do.
How about it, Ascential, is this a bug in the OCI stage or is there some other explanation?
Unfortunately, Rahim, I suspect your only options at the moment are to use the ODBC stage (hey its an option, but it is not the one I would choose) or to use a user defined query (my recommended work-around).
Thanks,
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Rahim Ladha [mailto:rahimladha@hotmail.com]
Sent: Wednesday, 25 July 2001 8:16 AM
To: datastage-users@oliver.com
Subject: column ambiguously - table alias
Is there any way in DataStage, when it generate the query attached the table alias with the column?
DSGenerate Query.
---------------
Selat a,a,b
from a,b
where a.a=b.a
Actual Query need is
--------------------
Select a.a,b.a,b.b
from a,b
where a.a=b.a
Workaround
-----------
In Colums tab, add alias to each line then I can get the results. But I have too many fields.
above is the sample query, I have a long query where I have to add the table alias.
Im using Oracle8 OCI
thanks in advance.
rahim
Rahim Ladha
e-mail : rahimladha@hotmail.com
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
I have always thought that there is a problem with the OCI stage in relation to the derivation property. Why does it have a derivation property for each column if it doesnt use it? The ODBC stage uses the derivation as the expression in the SELECT clause of the generated SQL, but the OCI stage seems to ignore it. As a result, the ODBC stage generates the correct query for what you are trying to do.
How about it, Ascential, is this a bug in the OCI stage or is there some other explanation?
Unfortunately, Rahim, I suspect your only options at the moment are to use the ODBC stage (hey its an option, but it is not the one I would choose) or to use a user defined query (my recommended work-around).
Thanks,
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Rahim Ladha [mailto:rahimladha@hotmail.com]
Sent: Wednesday, 25 July 2001 8:16 AM
To: datastage-users@oliver.com
Subject: column ambiguously - table alias
Is there any way in DataStage, when it generate the query attached the table alias with the column?
DSGenerate Query.
---------------
Selat a,a,b
from a,b
where a.a=b.a
Actual Query need is
--------------------
Select a.a,b.a,b.b
from a,b
where a.a=b.a
Workaround
-----------
In Colums tab, add alias to each line then I can get the results. But I have too many fields.
above is the sample query, I have a long query where I have to add the table alias.
Im using Oracle8 OCI
thanks in advance.
rahim
Rahim Ladha
e-mail : rahimladha@hotmail.com
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp