CASE, LIKE AND DECODE 1

[Nunina]

Hi Guys!

I have a table that has the reasons for loosing a game. This table contains the following fields: ID, Reason.

ID     REASON 
1      FRAUD
2      LACK OF UNIFORM
3      BAD HITTER
4      COLD BAT
5      BAD COLD

Now, I'm trying to make a query (I will integrate it into an ETL procedure) that adds a CLASIFICATION. But this clasification is based on the reason. If the reason is Cold Bat or Bad Cold, the Clasification is Weather. If it's Lack of Uniform, the Clasification is Voluntary. And so on.

So far, this is the query I got.

SELECT ID, REASON,
CASE WHEN REASON LIKE '%COLD%' THEN 'WEATHER' AS CLASIFICATION 
FROM TABLE_A
ORDER BY ID(+)

But I get a missing keyword error. Can you guys help? Should I use Decode instead of Case? If I do, how do I integrate the Like into the Decode? Considering that not all the Reasons have the same length, or "keywords" in the same place (taking reason cold bat and bad cold as an example).

Any help will be appreciated.

As usual, thanks a lot, guys!



Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[LKBrwnDBA]

Try this:

SELECT ID, REASON,
CASE WHEN REASON LIKE '%COLD%' 
THEN 'WEATHER' 
END AS CLASIFICATION 
FROM TABLE_A
ORDER BY ID(+)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
1: CASE is Only in 9i
2: if in 9i then

You need to END the case statement:
for instance this one works:

select empl_nm,case brgng_unit_nbr when '220' then 'Manager' else 'NotM' end
from mndot_employee;

If not 9i the use Decode instead..

 

[Nunina]

Thanks! That one, I figured it out. But the problem is that I could have more than 2 classif...

In other words, I suposse my case needs another else so that it can "grab" the other ones. This is what I mean:

If like '%cold%' then 'WEATHER' else if '%bad%' then 'PLAYER' else if '%fraud%' then 'FRAUD' ELSE 'VOLUNTARY'

I tried it with the case, but I got an error.

Any other suggestions?

Thanks again!



Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[carp]

While Oracle started making more noise about CASE in 9i, I believe it was actually available in 8i.

 

[Nunina]

Guys!

We have 8i and the gang have been using CASE without any problems.

My actual problem is not the CASE statement. My problem is to be able to assign the Classification to each record based not on the id, but on the Reason...

Please help!

Thanxs!



Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[SantaMufasa]

So, Issys,

Are you looking for something like the following code and results?:

SELECT ID, REASON,
CASE WHEN REASON LIKE '%COLD%' THEN 'WEATHER'
     WHEN REASON LIKE '%UNIFORM%' THEN 'VOLUNTARY'
     ELSE REASON
END AS CLASIFICATION
FROM TABLE_A
ORDER BY ID(+);

        ID REASON          CLASIFICATION
---------- --------------- -------------
         1 FRAUD           FRAUD
         2 LACK OF UNIFORM VOLUNTARY
         3 BAD HITTER      BAD HITTER
         4 COLD BAT        WEATHER
         5 BAD COLD        WEATHER

5 rows selected.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 23:12 (04Nov04) UTC (aka "GMT" and "Zulu"),
@ 16:12 (04Nov04) Mountain Time

 

[Nunina]

Mufasa:

YES!!! That's exactly what I wanted. Thank you so much to you, and to all the guys that helped!

Here's a star for you!





Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[Turkbear]

Hi,
Introduced, I believe, in 8.1.7 ( I was looking at an 8.1.5 instance where it does not work:
Version 9.2:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

SQL> SELECT AVG(CASE WHEN e.cmptn_rt > 20.00 THEN e.cmptn_rt ELSE 20.00 END) from hr_cur_rptng e;

AVG(CASEWHENE.CMPTN_RT>20.00THENE.CMPTN_RTELSE20.00END)
-------------------------------------------------------
                                             22.9592842

In v 8.1.5 however:

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.1.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.1.0 - Production

SQL> SELECT AVG(CASE WHEN e.cmptn_rt > 20.00 THEN e.cmptn_rt ELSE 20.00 END) from hr_cur_rptng e;
SELECT AVG(CASE WHEN e.cmptn_rt > 20.00 THEN e.cmptn_rt ELSE 20.00 END) from hr_cur_rptng e
                *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL>

That's why specifying the precise version of Oracle can be important when posting questions..