conditional query results

[garwain]

OK, first of all, I am quite new to Oracle, and am having trouble with a query that I could easily design under MSSQL Server 2k.
under MS SQL Server, my query would be as follows.

select case when substr(field1,0,3)='111' then 'A', when substr(field1,0,3)='112' then 'B' else 'C' end

what would be the best method to do this under Oracle 8.1.5?

Thanks

 

[jimbopalmer]

select decode(substr(field1,0,3),'111','A','112','B','C') from table

I tried to remain child-like, all I acheived was childish.

 

[SantaMufasa]

Although I, too, would opt for JimBo's method, you can code using your method, as well, with your syntax plus these last three words:

select case when substr(field1,0,3)='111' then 'A'
when substr(field1,0,3)='112' then 'B'
else 'C'
end case
from test1;

 

[garwain]

Thanks. The decode function works perfectally. I'll have to try out the syntax for the case statement on monday as well.

 

[ddrillich]

SantaMufasa,

I think your method is fine for Oracle 9.
I don’t think it works for Oracle 8.

Cheers,
Dan

 

[carp]

Dan -
It works in 8.1.7, and I believe it worked in 8.0.* versions. However, it wasn't documented very well.

 

[ddrillich]

Thanks carp!

The following works just well in 8.1.6 -

select
case when substr('xxxx',0,3)='111' then 'A'
when substr('xxxx',0,3)='112' then 'B'
else 'C'
end case
from dual

 

[SantaMufasa]

Dan and Carp,
the "CASE" construct did not appear until Oracle 8i (8.1.x). I haven't an installation earlier than 8.1.6 to test, but here are the results from my 8.1.6 instance:

select * from v$version;
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production

select * from test1;
FIELD1
------
111
112
113
202

select case when substr(field1,0,3)='111' then 'A'
when substr(field1,0,3)='112' then 'B'
else 'C'
end case
from test1;

C
-
A
B
C
C

Cheers,

Dave

 

[ddrillich]

Good Day,

I have the following working query -

select
region_name, case when region_name like 'CAPITAL NY%' then 'NEW YORK'
when region_name like 'ISLAND METRO NY%' then 'NEW YORK'
when region_name like 'LIBERTY NY%' then 'NEW YORK' else region_name
end case
from my_table

The result set has two column titles - region_name and case.
How do I rename case to something more meaningful?

Thanks,
Dan

 

[SantaMufasa]

Dan, I guess I learn something new most days, as well:
Regardless of what the documentation may say, the "CASE" construct terminates with just "END", not "END CASE". If you terminate with "END CASE", then "CASE" becomes the column alias; if you terminate with "END YADA", then "YADA" becomes the column alias. Then, as with any column alias in SQL*Plus, you may create a SQL*Plus "column" definition such as:

col yada heading 'This is|My New|Heading' format a7

...and you display

This is
My New
Heading
-------

...for a new column heading over the "yada" column.

Hope this clears things up. (Let me know.)

Dave

 

[ddrillich]

Thanks Dave!

 

[ddrillich]

Good Day,

BTW, is it possible to use the case expression in the where clause?

Thanks,
Dan

 

[SantaMufasa]

Dan, I just tried a SELECT with a simple CASE statement in the WHERE clause, but as soon as the SQL parser encountered the "WHERE CASE...", it threw the error,
"ORA-00920: invalid relational operator",
...so it appears that the answer is 'No'.

Dave

 

[Sudhi78]

Case works in Where clause also, only thing is you have to remove the CASE with END, that is use only END and not END CASE


SELECT * FROM dual WHERE
(CASE WHEN SUBSTR('xxxx',0,3)='111' THEN 'A'
WHEN SUBSTR('xxxx',0,3)='112' THEN 'B'
ELSE 'C'
END )= 'C'


cheerz
sudhi