RE: IF in a SELECT--can you do this?

Tamara,

Based strictly on the case and rules you provided, a complex approach is not needed. You can group by the first 3 columns and do a MAX on the "A/P" column.

SQL> select color1, color2, color3, max(foo)   2 from colors
  3 group by color1, color2, color3
  4 /

COLOR1 COLOR2 COLOR3 M
---------- ---------- ---------- -

red        blue       yellow     P
white      black      green      A

The data is as follows:

SQL> select * from colors
  2 /

COLOR1 COLOR2 COLOR3 F
---------- ---------- ---------- -

red        blue       yellow     A
red        blue       yellow     P <--- We Want This One!!!
white      black      green      A \
white      black      green      A / -- We will take either one!!!

If your output truly is just those 4 columns you described, you should be able to apply this to your SQL statement that joins the 7 or 8 tables. Your "real world" issue may actually be more complex. Maybe the data doesn't always exist in pairs and you only want those that do -- "having(count(*)) = 2" might work. There are lots of other things that I can think of as well. Anyway, if your real world issue is more complex and the above doesn't work for you, get back with more info and we will see what we can do.

Regards,

Larry G. Elkins
elkinsl_at_flash.net

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Tamara Swilley
Sent: Friday, November 17, 2000 11:41 AM To: Multiple recipients of list ORACLE-L Subject: IF in a SELECT--can you do this?

Hi listers; happy Friday!!

I have a question.... can you put an IF on a SELECT statement? I have a query joining 7-8 tables and I'm getting back (technically) unique rows (four columns returned) that look something like:

red	blue	yellow	A
red	blue	yellow	P

The user says the fourth column values (for every pair of rows) can be "P and A" or "A and A". In the first case they want to see only the "P" row and in the second case, either row (since both would be "A"). How would I (1) join this table to itself with a WHERE clause to get the desired result, or (2) put a conditional IF in the SELECT so that I only get one row: a P in the case of two diff values or A in the case of two As (if this is possible)? In essence, I want to cut my results in half and only show one of two rows.

Oracle 8.0.5 on Solaris 2.6 (if it matters in this case).

Thanks in advance,

Tamara Swilley Received on Sun Nov 19 2000 - 07:48:39 CST