is it possible to get multiple values in sub query ???

is it possible to get multiple values in sub query ???

select  prod1,prod2, prod3, (select * from (select a1,a2,a3,a4 from x where b=10 order by create_time) where rownum=1) ,prod4 from prod_table 

Open in new window


I got ORA -00913  how to slove this issue . actually the sub query is important and get in different table
LVL 20
Sathish David  Kumar NArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sathish David Kumar NArchitectAuthor Commented:
is it possible to get multiple column in sub query ???
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
In Oracle I don't know but in SQL Server you can't.
I can't really understand what you want to be returned with that query but maybe a CROSS JOIN may help you:
select p.prod1, p.prod2, p.prod3, x.a1, x.a2, x.a3, x.a4,p.prod4 
from prod_table p
cross join x
where x.b=10
order by x.create_time

Open in new window

0
Naveen KumarProduction Manager / Application Support ManagerCommented:
try rewriting like this :

select  prod1,prod2, prod3, (select a1 from (select a1,a2,a3,a4 from x where b=10 order by create_time) where rownum=1)  a1,
(select a2 from (select a1,a2,a3,a4 from x where b=10 order by create_time) where rownum=1)  a2,
(select a3 from (select a1,a2,a3,a4 from x where b=10 order by create_time) where rownum=1)  a3,
(select a4 from (select a1,a2,a3,a4 from x where b=10 order by create_time) where rownum=1)  a4,
prod4
 from prod_table
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Naveen KumarProduction Manager / Application Support ManagerCommented:
if you have joining columns between your prod_table and x, then we can rewrite the sql using the WITH clause as well.

With my_table as ( select .... from .... )
Select ... from prod_table p, my_table m where p.col1=m.col1 and .....

Thanks,
0
Sathish David Kumar NArchitectAuthor Commented:
Sorry  I cant paste my quer y here that is the problem privacy problem

select  prod1,prod2, prod3, (select * from (select a1,a2,a3,a4 from prod_table  where b='10' order by create_time) where rownum=1) ,prod4 from where b='15'prod_table 

Open in new window


sorry for  th1 1st time i gave . This is simalarer to my query

subquery table name and main table are same but condition are diffrent

I want a1,a2,a3 value when b=10
prod1,prod2 value when b=15

is this possible ??
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this:

select b, a1,a2,a3,a4
from your_table
union
select a, prod1,prod2,prod3,prod4
from your_table

or try this :

select b,
case when b = 10 then a1 when b=15 then prod1 end a1_or_prod1,
case when b = 10 then a2 when b=15 then prod2 end a2_or_prod2,
case when b = 10 then a3 when b=15 then prod3 end a3_or_prod3
from your_table
where ....

Thanks,



select case when a = 1 then 10 when a=2 then c end  aaa
0
sarabhaiCommented:
according your query
(select * from (select a1,a2,a3,a4 from x where b=10 order by create_time) where rownum=1) ,prod4 from prod_table

did you use rownum=1 mean one record for the subquery then it's fine

or if subquery returns more rows than one then fix the problem using the TOP 1
0
awking00Commented:
>>Sorry  I cant paste my quer y here that is the problem privacy problem
subquery table name and main table are same but condition are diffrent <<
Can you post some sample input for prod1, prod2, prod3, prod4, a1, a2, a3, a4 with obfuscated data (just make them up) and some values of 10 and 15 for b along with what you would expect as output using that data? I sense there may be a use for the row_number() function, which will work with both Oracle and SQL Server, but providing test data could confirm or refute that.
0
Sathish David Kumar NArchitectAuthor Commented:
I put sub auery in the table list and named as a  its sloved my problem
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sathish David Kumar NArchitectAuthor Commented:
I put sub auery in the table list and named as a  its sloved my problem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.