Link to home
Start Free TrialLog in
Avatar of mgferg
mgferg

asked on

select with conditional selects to determine output

Hi All,

I'm trying to produce some output which is dependant on a query of data within a column. The closest I've got to is using a case when exists, but this just gives a 1 or literal text not the value of the respective select.

So basically in following simpified example:
get
field1, field2, field3

where field2 is the result of a number of selects similar to if .. than

This is closest I have
select ec.field1,
(
case when exists (
    select ec0.field2 from table ec0 where ...
    )
then 'field2' else null end
) as field2,
ec.field3 ...
from table ec ...
where ...
group by ...

(so field2 should be the actual result of a select that passed a condition - for simplity just used a single select)

What's a better way to do this or use something similar to above to achieve this?

Thanks,
Mark

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I'm a little confused.  Some sample data and expected results would help me out a lot.

That said, why are you using the case statement?  As long as the inner query always returns 1 row you should be able to put it inline:

--return field2:
select 'a' field1, (select 'b' field2 from dual where dummy='X') field2, 'c' field3 from dual;

--return null field2:
select 'a' field1, (select 'b' field2 from dual where dummy='Y') field2, 'c' field3 from dual;
Avatar of mgferg

ASKER

ok (sorry for bad example, but trying to make simple to explain)
tablea with columns
field1 field2 field3

and data
running,X, blue
cricket, Y,white

tableb has data
C,shoes
D,bats

output needs to be:
 running, shoes, blue
 cricket, bats, white

so output of "field2" is based on the results of a select to another table
field2 = select .. if C... if this yields a value result (shoes) than use this
else select ... if D .. this yields a valid result (bats) than use this
else select ... etc
else use default

(obviously the result can be achieved differently but I'm more after a method to incorporate multiple selects as above in order to determine the final output)

Thanks!
I hope this is a typo but...

Are the values for field2 in tableA the same in tableB or does X=C and Y=D?
See if this is what you are after
drop table tab1 purge;
create table tab1(field1 varchar2(10),field2 char(1),field3 varchar2(10));


drop table tab2 purge;
create table tab2(field1 char(1), field2 varchar2(10));


insert into tab1 values('running','X','blue');
insert into tab1 values('cricket','Y','white');

insert into tab2 values('C','shoes');
insert into tab2 values('D','bats');
commit;


--if the same values
select field1, (select field2 from tab2 where field1=t1.field2) field2, field3 from tab1 t1;


--if it wasn't a typo
select field1, (select field2 from tab2 where decode(field1,'C','X','D','Y','Z')=t1.field2) field2, field3 from tab1 t1;

Open in new window

Avatar of mgferg

ASKER

Hi thanks for the help, but looks like my example didn't work too well. There's no relation between X,Y,C,D - just part of the example - getting field 2 is actually a complex query that has to do a number of different checks. I can find multiple references to achieve something similar using the case exists but this just gives literal output not the actual results of the query.
eg
in
http://www.akadia.com/services/ora_important_part_3.html
CASE WHEN EXISTS (
             SELECT id_user
               FROM participant
              WHERE accesstype = 2
                AND id_user = u.id_item
             )
          THEN 1 ELSE 0 END
          ) AS userstate

so instead of the "1" I want the result of id_user (in this example)

I understand the 'case exists' example but I'm still questioning why you need it.

Just take the select in the case statement and move it inline like in my example.

based on your original
change:
select ec.field1,
(
case when exists (
    select ec0.field2 from table ec0 where ...
    )
then 'field2' else null end
) as field2,
ec.field3 ...
from table ec ...
where ...
group by ...


to:
select ec.field1,
(    select ec0.field2 from table ec0 where ...    ) as field2,
ec.field3 ...
from table ec ...
where ...
group by ...




Avatar of mgferg

ASKER

because the "(    select ec0.field2 from table ec0 where ...    ) " doesn't allow me to do "if than else ..." )and if it does, I wish I knew how)
What if-then-else logic do you need to do?

Can you try to come up with a better example that more closely mirrors your final result?
Hi mgferg,

referring to the below in your question ( the first post )

"so field2 should be the actual result of a select that passed a condition - for simplity just used a single select " ---> yes, you can do it as long as one value is returned for each record and only one column can be returned.

1) provide your complete sql statement
2) what is the error you are getting

Thanks
Avatar of mgferg

ASKER

Thanks for the comments and apologies for taking so long to respond. I haven't quite figured out the syntax yet which is what I've been asking and it's not practical to provide sample data as this will just confuse things, which is why I've tried to keep simple. However the example above - if done here will work for me:

http://www.akadia.com/services/ora_important_part_3.html
CASE WHEN EXISTS (
             SELECT id_user
               FROM participant
              WHERE accesstype = 2
                AND id_user = u.id_item
             )
          THEN 1 ELSE 0 END
          ) AS userstate

so instead of the "1" I want the result of id_user (in this example)
if there is more than one id_user returned by your select then what should be returned ?
Avatar of mgferg

ASKER

ideally depending on what id_user returned, give a different result, which could either be the id_user itself or a static value (manually defined), the other important one is if the id_user is null, return something else but not null. Thanks!
i am totally confused...

let me make you understand this way... what i am trying to say is that if your select can return only one value, then use it directly instead of 1. if you select is bring more records, you need to decide which one value you want out of them.

instead of the below...
CASE WHEN EXISTS (
             SELECT id_user
               FROM participant
              WHERE accesstype = 2
                AND id_user = u.id_item
             )
          THEN 1 ELSE 0 END
          ) AS userstate

try :

             SELECT id_user
               FROM participant
              WHERE accesstype = 2
                AND id_user = u.id_item ; --> please note this select should only return 1 value otherwise you will get an error.

>>not practical to provide sample data as this will just confuse things
Obviously not.  I believe we're all a little confused.

I feel a small test case will help us out.  If you can include a sample table definition, sample data and expected results, I'm sure we can help resolve this quicker than we can without it.
Avatar of mgferg

ASKER

sorry guys for not making this easy - (and sorry for delay, too much going on - I will respond)

and using a simple example is far better - I need to get the concept right before trying to applying it
so for the following extract, simple scenario:
SELECT id_user
               FROM participant
              WHERE accesstype = 2

so there has to be list of user_id's that meeting this initial requirement

a) this would help
if id_user is John, then write to the console output "John drives a BMW"
if id_user is Mark, then write to the console output "Mark drives a Porsche"
if id_user is null, then write to the console output "None Found"

The point to note is that the fact that John drives a BMW and Mark a Porsche is not defined anywhere or available through a lookup, it needs to be hardcoded

b) taking this a step further, it however might be (?) so let's allow some form of additional lookup

attempt to look this up in a table
if id_user is John and John/car is found in table A, then write to the console output "John drives a BMW (based on what is found in table A)"

c) but what is really required is

if id_user is John and John/car is found in table A, and John/colour is found in a table X, then write to the console output "John drives a BMW and the color is [color] "

BUT if not found (and only if not found), let's try something else which is not what we really want but may be useful
if id_user is John and John/car is found in table A and John/model is found in a table Y, then write to the console output "John drives a BMW and the model is [model]"

if not found
if id_user is John and John/car is found in table A, then write to the console output "John drives a BMW"

not found, just hardcode a value as we actually know that John drives a BMW
if id_user is John then write to the console output "John drives a BMW "

Hope this now makes more sense. Thanks for assistance.
i would suggest you to write a pl/sql block of code to get this done though i can come up with a sql but the complexity/readability will be gone and modifying it for future changes will be difficult and cumbersome.

is pl/sql block ok ?
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mgferg

ASKER

actually I found out also that doable in pl/sql - and this would be perfect - so the framwork around this would really help to get me going. Thanks!
let me know if you need anything else on this.
Avatar of mgferg

ASKER

Aologies for late response but had family emergency.
hope you had taken care of your family emergencies. Take care.