Home » SQL & PL/SQL » SQL & PL/SQL » How to do a conditional query (Oracle 10g)
How to do a conditional query [message #448342] Mon, 22 March 2010 18:35 Go to next message
ajreynolds
Messages: 10
Registered: March 2005
Junior Member
I'm trying to figure out how to do a conditional range in a query. For example, I have two tables:

table A:
a1 char(1)
a2 char(1)

table B:
b1 char(1)
b2 int

In my query I want to range on a different value in the b2 field depending on what is in the fields in the A table. For example, if the a1 field is Y, I want to look up the record in B with a b2=20. If a2 is Y, I want to look up b2=5. Otherwise I want to look up b2=1. I attempted the following:

select b1 from b, a where b2=(if a1='Y' then 20; elsif a2='Y' then 5; else 1)

but that doesn't work. Can anyone help me with the correct syntax for this?

Thanks alot.

Andy
Re: How to do a conditional query [message #448343 is a reply to message #448342] Mon, 22 March 2010 18:42 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>but that doesn't work.
>Can anyone help me with the correct syntax for this?

When all else fail, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

For what it is worth, SQL does not have IF THEN syntax.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: How to do a conditional query [message #448348 is a reply to message #448342] Mon, 22 March 2010 19:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Don't mind @BlackSwan - s/he seems to be a bit tetchy at the moment.

SQL supports a full CASE syntax, so your code would be something like:
select b1 
from   b
      ,a 
where  b2= case when a1='Y' then 20
                when a2='Y' then 5
                else 1
                end
Re: How to do a conditional query [message #448349 is a reply to message #448342] Mon, 22 March 2010 19:29 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>For example, if the a1 field is Y, I want to look up the record in B with a b2=20. If a2 is Y, I want to look up b2=5.

What is result when A1=Y and A2=Y?
Re: How to do a conditional query [message #448352 is a reply to message #448342] Mon, 22 March 2010 19:48 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
AJ,

(I think Black swan is having a bad feather day)

It looks like your logic makes sense. Just use the CASE statement instead of IF:
create table jg_table_a (a1 char(1), a2 char(2));
insert into jg_table_a values('N','N');
insert into jg_table_a values('Y','N');
insert into jg_table_a values('N','Y');
insert into jg_table_a values('Y','Y');

create table jg_table_b (b1 varchar2(20), b2 int);
insert into jg_table_b values('Twenty',20);
insert into jg_table_b values('Five',5);
insert into jg_table_b values('One',1);

select  a1,a2,b1
from    jg_table_a, jg_table_b
where   b2 = CASE
            WHEN a1 = 'Y' THEN 20
            WHEN a2 = 'Y' THEN 5
            ELSE 1
        End;

Produces
A1	A2	B1
N	N 	One
Y	N 	Twenty
N	Y 	Five
Y	Y 	Twenty

Which is almost what I'll bet you are looking for.

Cheers,
Jeff

Re: How to do a conditional query [message #448353 is a reply to message #448342] Mon, 22 March 2010 19:52 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
@JRow beat me to it. Smile
re: what if Y,Y is what I was referring to when I said "almost" what you are looking for. I'll bet you can figure that case out.
Re: How to do a conditional query [message #448354 is a reply to message #448352] Mon, 22 March 2010 19:56 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
same logic but different results
SQL> select	a1,a2,b1
  2  from	jg_table_a, jg_table_b
  3  where	b2 = CASE
  4  		 WHEN a1 = 'Y' THEN 20
  5  		 WHEN a2 = 'Y' THEN 5
  6  		 ELSE 1
  7  	     End
  8  /

A A2 B1
- -- --------------------
N N  One
Y N  Twenty
N Y  Five
Y Y  Twenty

SQL> select	a1,a2,b1
  2  from	jg_table_a, jg_table_b
  3  where	b2 = CASE
  4  		 WHEN a2 = 'Y' THEN 5
  5  		 WHEN a1 = 'Y' THEN 20
  6  		 ELSE 1
  7  	     End
  8  /

A A2 B1
- -- --------------------
N N  One
Y N  Twenty
N Y  Five
Y Y  Five
Re: How to do a conditional query [message #448473 is a reply to message #448354] Tue, 23 March 2010 11:21 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
Different logic - different results. The order of WHEN statements is a legitimate implementation of business logic. Consider:
SELECT CASE
          WHEN AGE > 70 THEN 'Old'
          WHEN AGE > 40 THEN 'Middle Aged'
          WHEN AGE > 20 THEN 'Prime'
          ELSE 'Young'
       END ...

Of course changing the order of the lines would produce different results because it would simply be wrong.

Cheers,
jeff

Though arguing with trolls is fun, I guess I'll get back to work now.
Re: How to do a conditional query [message #448478 is a reply to message #448473] Tue, 23 March 2010 11:56 Go to previous message
ajreynolds
Messages: 10
Registered: March 2005
Junior Member
Thank you very much everyone. "Case" took care of my problem.

Andy
Previous Topic: how to build a query to retrieve the records in between the dates with same field?
Next Topic: Can a nested select be passed as a parameter?
Goto Forum:
  


Current Time: Mon Sep 26 17:54:03 CDT 2016

Total time taken to generate the page: 0.10804 seconds