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
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
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!
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?
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;
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)
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 ...
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 ...
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?
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
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
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)
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 ?
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.
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.
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.
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.
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 ?
is pl/sql block ok ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Aologies for late response but had family emergency.
hope you had taken care of your family emergencies. Take care.
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;