I need to make a new table based on this datatable schema. I am not exactly sure if i can do that using nested sql select statements or else.
-
4You can start by writing a SQL statement. What have you tried?Gordon Linoff– Gordon Linoff2014-04-14 10:59:12 +00:00Commented Apr 14, 2014 at 10:59
-
Yes i have tried multiple times. I am dealing with DB2 IBM AS400 server queries and sometimes it gets messy. Sorry for not mentioning that.woooooot– woooooot2014-04-15 06:07:45 +00:00Commented Apr 15, 2014 at 6:07
Add a comment
|
2 Answers
select ColA, ColB, ColC, ColD, (select ColE from table where colA=120) ColE
from table
where colA = 122
Just using a nested select for colE and giving an Alias to the column.
2 Comments
Gordon Linoff
This will fail if
colA can take the value 120 in more than one row. Admittedly, the OP doesn't say what to do in this case.
Sandstorm
I suppose he is working this way, because ColA is PK, but if not, he can always add: AND ROWNUM = 1;
You haven't stated the condition to find ColE if there are multiple values in ColA. I'm assuming (based from your table) you want the smallest value?
select min(ColE)
from table
where ColA=120
group by ColA;
Now using the above you can create a nested select
select ColC, ColB, ColC,ColD , minColE
from table, (
select min(ColE) as minColE
from table
where ColA=120
group by ColA
) as TblA
where ColA=122;
