Multiple SQL statements in ODBC Stage

[Mai]

Hi there!

i really need to fix this one -- I have several SQL statements that need to be run on ODBC stage but it only executes the first one. I've set it to user-defined SQL, by the way. Is this really not possible?
Here is the sample of the SQL i want to run in ODBC Stage:
First, i need to create the view, then execute select, after that i need to drop the view..

create view View01 as
select Col01,
Col02,
Col03
from Table01;

select View01.Col01,
Table02.Col01,
Table02.Col02,
Table02.Col03,
from Table02, View01
where View01.Col03 = Table02.Col03
order by Table02.Col02

drop view View01;

would anyone have any suggestion?
thanks!
Mai

 

[quatermass]

Hi Mai,

I haven't tried this in datastage, but you should be able to rewrite the statements to be a single nested select statement. This should be within the capabilities of the ODBC driver you are using.


select t1.col01, t2.col01, t2.col02, t2.col03
from (select col01, col02, col03 from table01) t1, table02 t2
where t1.col03 = t2.col03
order by t2.col02


Hope it helps,

Kevin
**************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************