Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, play.

Asked: August 23, 2001 - 3:24 pm UTC

Last updated: November 01, 2013 - 9:49 pm UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Tom

1.I usually run into a situation where I run it queries against huge tables, and they run for an hour. some times when I want to discontinue the query , the only way is to kill the sqlplus window, i.e. kill the session.
Is there anything I can do from an another session to kill the query only in my other session. Can this be done.

Further, When I kill the query inbetween, how can I determine whether the table or the rows in the table which I was updating are still locked or not.

Are the rows locked untill, rollback is completed.

2.which oracle manual describes all the data dictionary tables and explains each column?

3.can we kill a session, when a rollback is going on, if no , why.

4.In what way case is better than decode performance wise, and logic wise.


thank you

and Tom said...

1) you can kill sessions. You cannot kill the query without killing the session.

You should be able to "ctl-c" from sqlplus (works for me) to kill a long running query. If your "query" is really an update -- it can take a long time to rollback (even longer then it took to do the update) so be patient with the ctl-c. Its working.

The rows are locked until your rollback completes.

2) The Oracle Reference manual:

</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76961/toc.htm <code>

3) sure you can kill a session when its rolling back however since killing a session INVOLVES rolling back -- don't expect it to go away any faster.

4) You know, performance wise I haven't benchmarked it properly. CASE seems to be marginally slower in 8i doing things that DECODE can do (eg:

( case when object_type = 'INDEX' then 1
when object_type = 'TABLE' then 1
when object_type = 'PROCEDURE' then 1
else 0
end )

is better as:

decode( object_type, 'INDEX', 1, 'TABLE', 1, 'PROCEDURE', 1, 0 )

but in 9i with the searched cased expression:

( case object_type
when 'INDEX' then 1
when 'TABLE' then 1
when 'PROCEDURE' then 1
else 0
end )

they are about the same when doing the same things.

Logic wise, it is night and day. Decode is somewhat obscure -- CASE is very very clear. Things that are easy to do in decode are easy to do in CASE, things that are hard or near impossible to do with decode are easy to do in CASE. CASE, logic wise, wins hands down.




Rating

  (172 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

9i...

Connor, August 23, 2001 - 7:30 pm UTC

Interestingly, somewhere in the 9i doco is that "decode" is deprecated and new applications should use CASE exclusively.

question

Akhilesh, September 18, 2001 - 2:36 am UTC

If someone asks you a question ' what is the difference between decode and case, how will you answer it briefly'



Tom Kyte
September 18, 2001 - 8:54 am UTC

decode is the function Oracle originally provided with releases of the database prior to 8.1.6. Starting with 816, CASE is the standard way to achieve the same results - with more meaning (case is easier to read and understand) and is the recommended function to use.

Everything decode can do, CASE can.
CASE can do many things decode either cannot or you would be hard pressed to make it do so.


USING case to select columns for insert

Alex, September 24, 2001 - 7:52 pm UTC

Tom , Iam doing an insert into select from


In that I a situation where in

(CASE when p.btd='A' THEN sum(p.pk_value)
when (p.btd='B'
and p.int_cd='SP_OPT') THEN sum(p.gPD_VALUE)
else 0
end) GPD
IF P.BTD='A', THEN i HAVE TO SELECT sum(p.pk_value), and if p.btd='B' and p.int_cd='SP_OPT' , i have to select
sum(p.gPD_VALUE)

lets say the whole statement is like this
select p.btd,
(CASE when p.btd='A' THEN sum(p.pk_value)
when (p.btd='B'
and p.int_cd='SP_OPT') THEN sum(p.gPD_VALUE)
else 0
end) GPD

from params_table p

Kindly provide me with the solution , I have also tried


sum(CASE when p.btd='A' THEN p.pk_value
when (p.btd='B'
and p.int_cd='SP_OPT') THEN p.gPD_VALUE
else 0
end) GPD





Tom Kyte
September 25, 2001 - 6:32 am UTC

Well, it definitely can be done:


1 select deptno, case when job = 'CLERK' then sum(sal)
2 when job = 'ADMIN' then sum(comm)
3 else 0
4 end sum_something,
5 job
6 from emp
7* group by deptno, job
scott@ORA817DEV.US.ORACLE.COM> /

DEPTNO SUM_SOMETHING JOB
---------- ------------- ---------
10 1300 CLERK
10 0 MANAGER
10 0 PRESIDENT
20 0 ANALYST
20 1900 CLERK
20 0 MANAGER
30 950 CLERK
30 0 MANAGER
30 0 SALESMAN

9 rows selected.

Since you don't really mention at all what issue you might possibly be running into.... I cannot rely suggest a solution (my crystal ball is in the shop this week ;)



A reader, July 29, 2002 - 3:21 pm UTC

Tom, Is there any way to use an ELSE clause in a decode expression like in CASE?

Tom Kyte
July 29, 2002 - 4:29 pm UTC

yes,


select decode( x, 5, 'five', 6, 'six', 'Else I just don''t know!' )
from t


the last entry is the "else return this"

select case not working

Ashwani Singh, August 05, 2002 - 8:27 am UTC

Dear Tom,
      we wanted to user case. But in our case the query does'nt seem to work. Following quer gives an error while executing.

  1  SELECT distinct I.ISSUE_ID,I.FDDI_NO,I.IP_ADDRESS,C.NAME DEPTT_NAME,
  2  I.ISSUE_DATE,E.EMP_NAME,
  3  T.CATEGORY,T.SOFTWARE_ID,T.INVENTORY_ID,
  4  DECODE(T.CATEGORY,'T',S.SCID,'P',P.PERIPHERAL_NAME,'A',
  5  A.ACCESSORY_NAME,'S',W.SOFTWARE_NAME) NAME,
  6   CASE WHEN T.CATEGORY='A' and t.inventory_id is null THEN 'A'
  7         WHEN T.CATEGORY='A'  and t.inventory_id is not null THEN 'AS' END ,
  8  V.OEM_NO
  9  FROM ISSUE_MAIN I,ISSUE_TRANS T,SYS_COFIG_MAST_MAIN S,
 10  ACCESSORY_NAME_MAST A,PERIPHERAL_MAST P,RECEIVE_DETAIL_OEM v,
 11  SOFTWARE_MAST W,COST_CENTERS_JOIN C,PROF_ILE E,
 12  SYS_INVENTORY u
 13  WHERE
 14  I.ISSUE_ID=T.ISSUE_ID AND I.DEPTT_ID=C.ID AND
 15  T.SOFTWARE_ID=A.ACCESSORY_ID(+) AND
 16  T.SOFTWARE_ID=P.PERIPHERAL_ID(+) AND
 17  T.SOFTWARE_ID=W.SOFTWARE_ID(+) and
 18  I.EMP_CODE=E.EMP_CODE and
 19  v.RECEIVE_ID(+)=u.RECEIVE_ID
 20  and v.NAME_ID(+)=u.NAME_ID
 21  and v.BRAND_ID(+)=u.BRAND_ID
 22  and v.OEM_ID(+)=u.OEM_ID
 23  and u.INVENTORY_ID(+)=t.INVENTORY_ID
 24  and t.SOFTWARE_ID=u.name_id(+)
 25* ORDER BY i.issue_id;
SQL> /
 CASE WHEN T.CATEGORY='A' THEN 'A'
           *
ERROR at line 6:
ORA-00923: FROM keyword not found where expected.

Could you kindly guide us as to how can we use and clause and get the desired output.
We are using 8.1.5.0.0

Is case statement possible only with Oracle 9i.

regards,
Ashwani 

Tom Kyte
August 05, 2002 - 11:44 am UTC

You do not have 816 and up -- that is what is needed for case.


you can simply:

decode( t.category || '/' || t.inventory_id, 'A/', 'A' 'AS' )


in your instance.

Select Case

Ashwani Singh, August 06, 2002 - 12:36 am UTC

Dear Tom,
 We r facing a new Situation now the t.category_id in the following query may have value 'P' as well since case is not working in 815 there fore we are still in the soup. Kindly guide us.

SELECT distinct I.ISSUE_ID,I.FDDI_NO,I.IP_ADDRESS,C.NAME DEPTT_NAME,
  2  I.ISSUE_DATE,E.EMP_NAME,
  3  T.CATEGORY,T.SOFTWARE_ID,T.INVENTORY_ID,
  4  DECODE(T.CATEGORY,'T',S.SCID,'P',P.PERIPHERAL_NAME,'A',
  5  A.ACCESSORY_NAME,'S',W.SOFTWARE_NAME) NAME,
  6   CASE WHEN T.CATEGORY='A' and t.inventory_id is null THEN 'A'
  7         WHEN T.CATEGORY='A'  and t.inventory_id is not null THEN 'AS' 
            when t.category='P' then 'P' end,
  8  V.OEM_NO
  9  FROM ISSUE_MAIN I,ISSUE_TRANS T,SYS_COFIG_MAST_MAIN S,
 10  ACCESSORY_NAME_MAST A,PERIPHERAL_MAST P,RECEIVE_DETAIL_OEM v,
 11  SOFTWARE_MAST W,COST_CENTERS_JOIN C,PROF_ILE E,
 12  SYS_INVENTORY u
 13  WHERE
 14  I.ISSUE_ID=T.ISSUE_ID AND I.DEPTT_ID=C.ID AND
 15  T.SOFTWARE_ID=A.ACCESSORY_ID(+) AND
 16  T.SOFTWARE_ID=P.PERIPHERAL_ID(+) AND
 17  T.SOFTWARE_ID=W.SOFTWARE_ID(+) and
 18  I.EMP_CODE=E.EMP_CODE and
 19  v.RECEIVE_ID(+)=u.RECEIVE_ID
 20  and v.NAME_ID(+)=u.NAME_ID
 21  and v.BRAND_ID(+)=u.BRAND_ID
 22  and v.OEM_ID(+)=u.OEM_ID
 23  and u.INVENTORY_ID(+)=t.INVENTORY_ID
 24  and t.SOFTWARE_ID=u.name_id(+)
 25* ORDER BY i.issue_id;
SQL> /
 CASE WHEN T.CATEGORY='A' THEN 'A'

Regards and thanx in advance,

Ashwani Singh
 

Tom Kyte
August 07, 2002 - 8:54 am UTC



decode can do most of the stuff case can do -- especially with equals. Just think about what you are asking for and apply DECODE techniques to it.

You are saying:


decode( category,
'P', 'P', -- when p, return p
decode( category, -- else, when no p, do this
'A', decode(inventory_id, NULL, 'A', 'AS' )
)

decode or case for groups of columns

A reader, September 09, 2002 - 3:45 pm UTC

hi tom

in 8.1.7 can we do decode((column1, column2), ....) ? I tried with case and decode but no luck

Basically what I am trying to do is

if a = x and b = y and c = z then value

Tom Kyte
September 09, 2002 - 7:59 pm UTC


decode( A, x, decode( B, Y, decode( C, Z, VALUE, null ), NULL ), NULL )


says

if a = z
then
if b = y
then
if c = z
then
value
else
null
end if
else
null
......


and case would be

select case when (a=x and b=y and c=z) then value else null end,
.....


problems with CASE when used inside a procedure

Adrian, September 11, 2002 - 7:07 am UTC

Will CASE work inside a procedure, if so what am I doing wrong in my test case?

Is it a version specific thing?

My test case is below on 8.1.6 database running on win NT

12:12:47 rdb2 on NEIQT> ed
Wrote file afiedt.buf

1 select accounting_class_id, (case when accounting_class_id = 1 then 'ONE'
2 when accounting_class_id = 99 then 'NINE NINE'
3 else 'WOOWOO'
4 end) test
5* from accountingclass
12:12:56 rdb2 on NEIQT> /

ACCOUNTING_CLASS_ID TEST
------------------- ---------
2 WOOWOO
5 WOOWOO
4 WOOWOO
1 ONE
3 WOOWOO
99 NINE NINE

6 rows selected.

Elapsed: 00:00:00.50
12:12:57 rdb2 on NEIQT> begin
12:13:15 2 for rec_acc in (
12:13:27 3 select accounting_class_id, (case when accounting_class_id = 1 then 'ONE'
12:13:27 4 when accounting_class_id = 99 then 'NINE NINE'
12:13:27 5 else 'WOOWOO'
12:13:27 6 end) txt
12:13:27 7 from accountingclass) loop
12:13:27 8 dbms_output.put_line(rec_acc.txt);
12:13:27 9 end loop;
12:13:27 10 end;
12:13:27 11 /
select accounting_class_id, (case when accounting_class_id = 1 then 'ONE'
*
ERROR at line 3:
ORA-06550: line 3, column 30:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>


Elapsed: 00:00:00.11
12:13:30 rdb2 on NEIQT>

How to use case in where clause

A reader, January 30, 2003 - 3:21 am UTC

How can case be used in where clause
I mean
select (case when objecy_type = 'XYZ' then 'ABC else 'DEF) test
where test not in select (test from tableb)

This returns an error invalid operation how can this be done using case

Tom Kyte
January 30, 2003 - 8:53 am UTC

either:


select (case when objecy_type = 'XYZ' then 'ABC' else 'DEF') test
where (case when objecy_type = 'XYZ' then 'ABC' else 'DEF')
not in (select test from tableb)
from t

or

select test
from ( select (case when objecy_type = 'XYZ' then 'ABC' else 'DEF') test
from t
)
where test not in (select test from tableb)

Case vs decode

Barry, February 03, 2003 - 10:54 pm UTC

Tom: I tell my clients that CASE is basically a Decode on steroids wearing spandex. Steroids because its more powerful than Decode was and Spandex because CASE is a lot more flexible. For small things, I still use Decode because it was a BIG help when it came along. For situations with lots of possible "else's" I use CASE. I also tell clients CASE is like it's own little query in the select statement. Thanks for your insightful explanation.

is this a cool case case?

j., May 20, 2003 - 3:36 pm UTC

select count(decode(Grp, 'A', 1)) Count1,
count(case when Grp = 'A' then 1 end) Count2
from (
select 'A' Grp from dual
union all
select 'A' Grp from dual
union all
select null Grp from dual
)

or would you call this an undocumented way to count non null entries only?

Tom Kyte
May 21, 2003 - 7:21 am UTC

why not just

select count(*) from (whatever) where grp = 'A';



A reader, May 21, 2003 - 11:23 am UTC

this "pattern" is more useful if one wants to generate counts for SEVERAL groups with just ONE select:

select count(case when Grp = 'A' then 1 end) Count1,
count(case when Val = 1 then 1 end) Count2
from (
select 'A' Grp, 1 Val from dual
union all
select 'A' Grp, to_number(null) Val from dual
union all
select null Grp, 2 Val from dual
)

but would you RELY on that behaviour? I've never found such kind of "expression" documented for count ...

Tom Kyte
May 21, 2003 - 11:28 am UTC

count( expression ) returns a count of non-null values.

So, as long as expression returns anything NOT NULL when you want to count something and returns NULL otherwise, it is valid.



One place where decode does better then case

Raj, May 21, 2003 - 2:22 pm UTC

Hi Tom,

Thanks for the information and making it clear what we should use because DECODE came first I am use to using it all time but I think it's a time to change to CASE.

But, one place I see decode doing better is 

SQL> select case when null = null
  2              then 1
  3     else 2 
  4      end ,  
  5         decode(null,null, 1, 2) from dual
  6  /

CASEWHENNULL=NULLTHEN1ELSE2END DECODE(NULL,NULL,1,2)
------------------------------ ---------------------
                             2                     1

SQL> 

You can compare nulls in decode for true but not in case.
Which means if two variables are compared in decode for = then it returns true if both are null. Which is not possible directly in case. 

Query

praveen, May 30, 2003 - 8:35 am UTC

Hi Tom,
    How to get the sum if difference between created and last_ddl_time is greater than 100 grouping by object_type
It should display as

x                 Y
----------------  -------------
PROC                         36
TAB                        9090
                            190


I tried the query below but it gives the error.

  1  select case when object_type in ('PROCEDURE','FUNCTION') then
  2              'PROC'
  3              when object_type in ('TABLE','VIEW') then
  4              'TAB' else null
  5              end x,
  6         sum(case when created-last_ddl_time > 100 then
  7             sum(object_id)
  8              end) y
  9  from t
 10* group by object_type
SQL> /
select case when object_type='PROCEDURE' then
                 *
ERROR at line 1:
ORA-00937: not a single-group group function

Why am I getting this error when I am using group by at the end.

Can you please explain 

Tom Kyte
May 30, 2003 - 8:42 am UTC

you have to group by "case when object_type in ......"

but i don't understand the question you are actually trying to answer -- sum( sum(object_id) )????

Reply : One place where decode does better then case

Mita, May 30, 2003 - 12:00 pm UTC

You can't do NULL = NULL, do NULL IS NULL And works.

> select case when null is null
2 then 1
3 else 2
4 end ,
5 decode(null,null, 1, 2) from dual;

CASEWHENNULLISNULLTHEN1ELSE2END DECODE(NULL,NULL,1,2)
------------------------------- ---------------------
1 1



CASE and DECODE have subtle differences in usage?

Adrian Billington, August 06, 2003 - 12:06 pm UTC

Tom

I've identified (i.e. not sure if documented) a small difference in using CASE and DECODE in a MERGE statement in PL/SQL. The background is :-

1) I want to make the INSERT part of the MERGE keep a counter going ( i.e. to decompose SQL%ROWCOUNT for auditing ). To do this I need to "piggy-back" an inserted column and CASE or DECODE calling a counter function is a nice way to do this.

2) The dummy CASE or DECODE must return the true value to be inserted into the target column, so whatever is evaluated during the counter function execution, the return value from CASE or DECODE will be the same.

I have some output, demoing the principle below. What is strange is that when using CASE, Oracle seems to know that whatever CASE does, I'm going to get the same value back, so it doesn't bother executing the counter function (some special optimization maybe ??? ). But when using DECODE, the counter function gets fired. The only way to make CASE fire the counter function is to make the WHEN-THEN and the ELSE returns different.

The code below should make this clearer. Any ideas?

920>
920> create package decase as
2 function set_int return pls_integer;
3 function get_int return pls_integer;
4 procedure reset_int;
5 end;
6 /

Package created.

920>
920> create package body decase as
2
3 n pls_integer not null := 0;
4
5 function set_int return pls_integer is
6 begin
7 n := n + 1;
8 return 0;
9 end set_int;
10
11 function get_int return pls_integer is
12 begin
13 return n;
14 end get_int;
15
16 procedure reset_int is
17 begin
18 n := 0;
19 end reset_int;
20
21 end;
22 /

Package body created.

922>
920> create table t ( x int, y char(1) );

Table created.

920> insert into t select object_id,'X' from user_objects where rownum <= 5;

5 rows created.

920> commit;

Commit complete.

920>
920> --
920> -- Using CASE to execute the counter function...
920> --
920> merge into t
2 using ( select object_id as x, 'Y' as y from user_objects where rownum <= 10 ) s
3 on ( t.x = s.x )
4 when matched then
5 update
6 set y = s.y
7 when not matched then
8 insert ( t.x, t.y )
9 values ( case decase.set_int when 0 then s.x else s.x end, s.y );

10 rows merged.

920>
920> exec dbms_output.put_line('Integer is at ' || to_char(decase.get_int));
Integer is at 0

PL/SQL procedure successfully completed.

920> exec decase.reset_int;

PL/SQL procedure successfully completed.

920> rollback;

Rollback complete.

920>
920> --
920> -- Using DECODE to execute the counter function...
920> --
920> merge into t
2 using ( select object_id as x, 'Y' as y from user_objects where rownum <= 10 ) s
3 on ( t.x = s.x )
4 when matched then
5 update
6 set y = s.y
7 when not matched then
8 insert ( t.x, t.y )
9 values ( decode( decase.set_int, 0, s.x, s.x ), s.y );

10 rows merged.

920>
920> exec dbms_output.put_line('Integer is at ' || to_char(decase.get_int));
Integer is at 5

PL/SQL procedure successfully completed.

920> exec decase.reset_int;

PL/SQL procedure successfully completed.

920> rollback;

Rollback complete.

920>
920> --
920> -- Making CASE execute the counter function...
920> --
920> merge into t
2 using ( select object_id as x, 'Y' as y from user_objects where rownum <= 10 ) s
3 on ( t.x = s.x )
4 when matched then
5 update
6 set y = s.y
7 when not matched then
8 insert ( t.x, t.y )
9 values ( case decase.set_int when 0 then s.x else null end, s.y );

10 rows merged.

920>
920> exec dbms_output.put_line('Integer is at ' || to_char(decase.get_int));
Integer is at 5

PL/SQL procedure successfully completed.

Thanks
Adrian


Tom Kyte
August 09, 2003 - 11:40 am UTC

the laws of SQL will be against you. You are attempting to force procedural nuances on a DECIDELY and purposeful "non procedural" language.

In the next release, there is no reason why the behaviour of the two functions would be reversed -- or that entier does it -- or that both do.

but decode does short circut. Look closer at your decode:

decode( decase.set_int, 0, s.x, s.x )

that is the case as:

case when decase.set_int = 0
then s.x
ELSE S.X
end




Killing The Session

Bipin Ganar, August 09, 2003 - 12:22 pm UTC

Hi Tom,
I have one procedure ,inserting one table on conditions.But as this is taking so much time i was not able to trace whether it was working or not.so I kill the session but as soon as i kill the session the status becomes KILLED
Now my problem , i was not able to work as it says resource is busy. It continues for the full day.I could work on the table next day.Can u tell me how i can the session and freed the resources at earliest cost.
Second thing what is criteria for relesing the resources and time for realesing the resource?

Exactly my point ...

Adrian Billington, August 11, 2003 - 10:04 am UTC

Tom

>> Look closer at your decode:
>> decode( decase.set_int, 0, s.x, s.x )
>> that is the case as:
>> case when decase.set_int = 0
>> then s.x
>> ELSE S.X
>> end

You correctly point out that the DECODE and the CASE I've quoted above are the same semantically. This is my point. Despite this saying that no matter what the DECASE.SET_INT function returns you're going to get the S.X column anyway, the DECODE still executes the function but CASE seems to be a little smarter and just uses the S.X without bothering itself to evaluate the function call. Is this a deliberate optimization that made it into the CASE expression - I can only assume that it is ?

Regards
Adrian
Adrian

Tom Kyte
August 11, 2003 - 10:14 am UTC

ok, i see now, yes they optimized away the constant - since CASE is a language construct, not a function -- they recognized that


case <CONSTANT>
when <value1> then S.X
else S.X
end

is the same as S.X


Case Statement Not working in PL/SQL

Gaurav Thakur, August 26, 2003 - 2:50 am UTC

I have been using your site for lot of references and it had helped me a lot. I was trying if i could get the result of case statement in the variable or i could use it in the PL/SQL Block i was not able to do that. Please Help.

I am using oracle database 8.1.7
DECLARE
v_val NUMBER;
BEGIN
select case when job = 'CLERK' then sum(sal)
when job = 'ADMIN' then sum(comm)
else 0
end sum_something INTO v_val;
from scott.emp
WHERE empno = 7131
group by deptno, job;
dbms_output.put_line(v_val);
END;

Gives The following Error:
ERROR at line 4:
ORA-06550: line 4, column 8:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( * - + all mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current distinct max min prior sql stddev sum
unique variance execute the forall time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string>

Thanks and regards
Gaurav Thakur

no difference between case and decode

dm, September 14, 2003 - 5:34 pm UTC

  1* select case when null = null then 1 else 2 end from dual
SQL> /

CASEWHENNULL=NULLTHEN1ELSE2END
------------------------------
                             2

SQL> ed
Wrote file afiedt.buf

  1  select case when null = null then 1
  2*             when null is null then 0 else 2 end from dual
SQL> /

CASEWHENNULL=NULLTHEN1WHENNULLISNULLTHEN0ELSE2END
-------------------------------------------------
                                                0

SQL> 

to check if something is null it's null is null and
 not null = null.
 

Tom Kyte
September 14, 2003 - 10:16 pm UTC

er? your point is?

Can case do this?

Jennifer Chen, September 16, 2003 - 9:33 pm UTC

I wrote a stored procedure that updates a table. If a null is passed in for the field, then the field is not updated (I set it back to its original value), if a ' ' is passed in, then the field is set to null, if a value is passed in, then the field is set to the new value. I don't know how to use case for this. Also, I can use dynamic sql to accomplish the same thing. Would you recommend dynamic sql or decode (case) from performance perspective.

Your response is greatly appreciated and very helpful.

CREATE OR REPLACE PROCEDURE alias.sp_mod_people_index
(p_MPINumber IN alias.people_index.mpi_number%TYPE,
p_EYE IN alias.people_index.eye%TYPE,
p_FPC IN alias.people_index.fpc%TYPE,
p_HAI IN alias.people_index.hai%TYPE,
p_HGT IN alias.people_index.hgt%TYPE,
p_SKN IN alias.people_index.skn%TYPE,
p_WGT IN alias.people_index.wgt%TYPE)
/*
Description : This procedure modifies a record in the PeopleIndex table. It uses the MPINumber
: to identify the record and set null to the fields that are ' '.
Input : MPINumber, SEX, RAC, HGT, WGT, EYE, HAI, SKN, POB, FPC
Output : None
Author : Jennifer Chen
Creation Date : 06/20/2003
*/
AS

BEGIN
-- check for unallowable nulls.
IF p_MPINumber IS NULL THEN
RAISE_APPLICATION_ERROR(-20000,'MPINumber is Required');
END IF;

UPDATE alias.people_index
SET eye = DECODE(p_EYE, ' ', null, null, eye, p_EYE),
fpc = DECODE(p_FPC, ' ', null, null, fpc, p_FPC),
hai = DECODE(p_HAI, ' ', null, null, hai, p_HAI),
hgt = DECODE(p_HGT, ' ', null, null, hgt, p_HGT),
skn = DECODE(p_SKN, ' ', null, null, skn, p_SKN),
wgt = DECODE(p_WGT, ' ', null, null, wgt, p_WGT)
WHERE mpi_number = p_MPINumber;

COMMIT;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
IF SQLCODE != 0 THEN
RAISE_APPLICATION_ERROR(-20001, SQLERRM);
END IF;

END sp_mod_people_index;
/
SHOW errors

Tom Kyte
September 16, 2003 - 9:40 pm UTC

why use case?

1) always use static sql whenever possible

2) use that which works and works well (decode falls into that, you don't have to run out and convert to decode)

3) TRANSACTIONAL control belongs in the client -- that commit in there scares me to death!!!

4) no_data_found will never be raised UNLESS You put a trigger on this table and that trigger does a select into. In that case -- do you really want to "just ignore it??????" (rhetorical question -- answer is of course "no way"). that NO_DATA_FOUND must be removed, it is a bug

5) why bother with that when others??? all you are doing is hiding the actuall ORA-xxxxx error message from the client!!! you make it harder to diagnose issues.



code should be no more then:

BEGIN
-- check for unallowable nulls.
IF p_MPINumber IS NULL THEN
RAISE_APPLICATION_ERROR(-20000,'MPINumber is Required');
END IF;

UPDATE alias.people_index
SET eye = DECODE(p_EYE, ' ', null, null, eye, p_EYE),
fpc = DECODE(p_FPC, ' ', null, null, fpc, p_FPC),
hai = DECODE(p_HAI, ' ', null, null, hai, p_HAI),
hgt = DECODE(p_HGT, ' ', null, null, hgt, p_HGT),
skn = DECODE(p_SKN, ' ', null, null, skn, p_SKN),
wgt = DECODE(p_WGT, ' ', null, null, wgt, p_WGT)
WHERE mpi_number = p_MPINumber;
end;


period....


THANK YOU

A reader, September 17, 2003 - 9:16 am UTC

Good Morning, Tom!

Really appreciate your quick response. That helped a lot. The exceptional code was generated by the Oracle migration tool, which converts PL/SQL code from SQL Server to Oracle. Of course, the tool is not smart enough to convert the long dynamic SQL to a couple of lines by using DECODE JÂ…

I have the habit to use commit in the code. In fact, all my developers have the same habit. I got that from books like ‘Oracle8i Advanced PLSQL Programming’, ‘Oracle8 PLSQL Programming’, etc. It’s really fresh and good to learn something new.

Thanks again for your time and help.


Jennifer.

OK

Peter, September 22, 2003 - 12:58 am UTC

Dear Sir,
Oracle doc. says coalesce and nullif functions can be used
instead of "case" logic.will that work?Could you please provide an example.
Thanks in advance.

Tom Kyte
September 22, 2003 - 7:42 am UTC

what would be better to say is

"case can perform the same functionality as nullif or coalesce. However, it is easier for you to use nullif and coalesce. Additionally, it is more meaningful to someone reading your query later as to what your intention was"


nullif and coalesce are not replacements for CASE.

CASE can be a replacement for them. CASE is more "flexible", generic, not as specific in goal as they are.

Using Case in Where Clause

Sridhar, October 02, 2003 - 10:27 am UTC

Tom,
I have written something like this in the where clause of my select statement.after the code review i was asked to simplify this can you please tell me an alternate way of doing this.I have tried it but could not think of anything.

AND (eoio.SOT LIKE (case when eoio.OG='C' THEN 'FL%' ELSE '%' END) OR eoio.SOT LIKE (case when eoio.OG='C' THEN 'PG%' ELSE '%' END))

Thanks
M S Reddy

Tom Kyte
October 02, 2003 - 11:20 am UTC



what a "silly" request. really. not from you, from the people who told you to "simplify it"

is

where nvl(eoio.OG,'x') <> 'C'
or ( eoio.og = 'C' and ( eoio.sot like 'FL%' or eoio.sot like 'PG%' ) )

considered "simplier"

Group by and CASE together

Sridhar, October 02, 2003 - 11:44 am UTC

Tom,
Thanks for the Quick reply and i was happy to see your reply in my favour.I have one more question regarding case and group by together.
consider this example
SELECT abc.oon
,xyz.ion
,eic.cid
,SUM(NVL(eod.da,0))
,(SELECT sum(NVL(pa,0))
FROM ejp
WHERE ejp.ion=xyz.ion) tp
,CASE WHEN abc.cbo IS NULL THEN
xyz.son
ELSE
(select a.son from a
where a.bon = abc.cbo)
END as pson
,CASE WHEN abc.cobo IS NULL THEN
xyz.ion
ELSE
(SELECT a.ion FROM a
WHERE a.cid=xyz.cid
and a.bon=abc.cbo)
END as pion
if i include these in the Group by
abc.oon
xyz.ion
eic.cid
abc.cbo
xyz.cid

will that give me correct results or else should i include the whole case statement in the group by clause.

Thanks for the Help!
M S Reddy.

Performace in my case

David Schwartz, October 17, 2003 - 3:05 pm UTC

Can you comment on this query, regarding performace? The tables have about 12,000 rows. Will the case statements with select kill the perf?

Select
b.Emp_id,
b.first_name,
(Case
When ( Select rn.Emp_ID From Roster_Names rn Where rn.Emp_ID = b.Emp_ID ) = b.Emp_ID
Then ( Select rn.Last_Name From Roster_Names rn Where rn.Emp_ID = b.Emp_ID )
Else ( Case
When ( Select rr.Emp_ID From Roster rr Where rr.Emp_ID = b.Emp_ID ) = b.Emp_ID
Then ( Select rr.Last_Name From Roster rr Where rr.Emp_ID = b.Emp_ID )
Else ( Select bb.Last_Name From Broker bb Where bb.Emp_ID = b.Emp_ID )
End
)
End ) As Last_Name
From broker b

here's the tables...

Create Table Broker (
Emp_ID Number (6,0) Not Null,
First_Name varchar(30) Not Null,
Last_Name varchar(30) Not Null,
Constraint Broker_PK Primary Key ( Emp_ID ) ) ;

Create Table Roster (
Emp_ID Number (6,0) Not Null,
First_Name varchar(30) Not Null,
Last_Name varchar(30) Not Null,
Constraint Roster_PK Primary Key ( Emp_ID ) ) ;

Create Table Roster_Names (
Emp_ID Number (6,0) Not Null,
First_Name varchar(30) Not Null,
Last_Name varchar(30) Not Null,
Constraint Roster_Names_PK Primary Key ( Emp_ID ) ) ;

Tom Kyte
October 17, 2003 - 4:04 pm UTC

why not

nvl( (Select rn.Last_Name From Roster_Names rn Where rn.Emp_ID = b.Emp_ID),
nvl( (Select rr.Last_Name From Roster rr Where rr.Emp_ID = b.Emp_ID),
(Select bb.Last_Name From Broker bb Where bb.Emp_ID = b.Emp_ID) )

instead? don't hit the tables twice.

Looking pretty darn like a "data model that is broken" if you want my opinion tho. seems to me that first_name, last_name are in the wrong tables here.

Broken Data Model

David Schwartz, October 17, 2003 - 4:54 pm UTC

>>Looking pretty darn like a "data model that is broken"

It is!
There are 2 tables that store the names. One deptartment maintains their own version of half of the employees. Emp_ID is the common column. I want to get the names from Roster for the half that are there.

Is there a better way?

Tom Kyte
October 19, 2003 - 5:47 pm UTC

redesign the schema to correctly hold your data?

is this possible using case?

A reader, November 03, 2003 - 7:49 am UTC

Hi

I have this result set,

DURATION is in seconds
CURRENT_DAY stores number of seconds elapsed of current date, for example from start_date to end_date there are 82 seconds and all of them occured in start_date, in fourth line 89970 seconds elapsed and 82 of them offured in start date and 89888 occurd in the day after

COD_ACC START_DATE DURATION END_DATE CURRENT_DAY NEXT_DAY
-------------- --------------- ------------- ---------------- ------------ -------------
N09381005H0001 20030731235838 82 20030801000000 82 0
N09381005H0001 20030704161416 218 20030704161754 218 0
N09381005H0001 20030730235838 82 20030731000000 82 0
N09381005H0001 20030831235838 89970 20030902005808 82 89888


I need to calculate the aggreate so the output would be

COD_ACC END_MONTH AGG_Month
-------------- ---------- -----------
N09381005H0001 200307 382
N09381005H0001 200308 82
N09381005H0001 200309 89888

can I used case for this? seems impossible :-0


Tom Kyte
November 03, 2003 - 2:08 pm UTC

ops$tkyte@ORA920PC> select cod_acc,
  2         decode( gsd, 0, sd, ed ) end_month,
  3         sum(decode( gsd, 0, scd, snd ) ) agg_month
  4    from (select cod_acc,
  5                 sd,
  6                             sum(current_day) scd,
  7                             ed,
  8                             sum(next_day) snd,
  9                 grouping(sd) gsd
 10            from (select cod_acc,
 11                                 trunc(start_date/100000000) sd,
 12                                             current_day,
 13                                             trunc(end_date/100000000) ed,
 14                                             next_day
 15                    from t)
 16           group by cube(cod_acc,sd,ed)
 17          having (grouping(cod_acc)=0 and grouping(sd)=0 and grouping(ed) = 1) or
 18                 (grouping(cod_acc)=0 and grouping(sd)=1 and grouping(ed) = 0)
 19                  )
 20    group by cod_acc, decode(gsd,0,sd,ed)
 21  /
 
COD_ACC          END_MONTH  AGG_MONTH
--------------- ---------- ----------
N09381005H0001      200307        382
N09381005H0001      200308         82
N09381005H0001      200309      89888
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select cod_acc,
  2         dt,
  3         sum(agg_month) agg_month
  4    from (
  5  select cod_acc,
  6         decode(x,1,trunc(start_date/100000000), trunc(end_date/100000000)) dt,
  7         decode(x,1,current_day,next_day) agg_month
  8    from t, ( select 1 x from dual union all select 2 x from dual )
  9         )
 10   group by cod_acc, dt
 11  /
 
COD_ACC                 DT  AGG_MONTH
--------------- ---------- ----------
N09381005H0001      200307        382
N09381005H0001      200308         82
N09381005H0001      200309      89888
 

A reader, January 07, 2004 - 4:23 pm UTC


CASE or temp table?

John, February 18, 2004 - 2:59 pm UTC

Hi Tom,

If I have a lot of values, say 50, to check in a CASE statement, would it be beter to store the values in a table and join to it? Seems to me CASE would win since it does not have to go to the disk to get data.

Also, if the same CASE construct appears twice in the same statement, would ORALCE go through the same logic twice? From the autotrace I can not really tell.

My thinking is that if you need to go through the same CASE logic several times in the same query, then you mighe be better off using a table to store those values. Since you will have to retrieve the data only once. Can yu comment on this? Thanks!

John

Tom Kyte
February 18, 2004 - 9:33 pm UTC

benchmark it for us :)

case with exists

wasey, February 19, 2004 - 12:22 am UTC

I have three simple tables
t1, t2, t3 and each have a date column dt1, dt2 and dt3 respectively.
I would like to do the following

select * from t1
where exists
( select case when to_number(to_char(sysdate, 'DD') = 19
then
select 'x' from t2
where dt2 = dt1
else
select 'x' from t3
where dt3 = dt1
end)

Can you please let me know the best way to implement the above scenerio.

Thanks in advance
Wasey

Tom Kyte
February 19, 2004 - 10:37 am UTC

the best way would be to get a new schema, that isn't going to perform "very well" (seriously)



ops$tkyte@ORA920PC> create table t1 ( x int );
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into t1 values ( 0 );
1 row created.

pretend all_users is your t2 and all_objects is your t3 and that it is the 19th.  I have a user_id 0, but no object_id 0

 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select *
  2    from t1
  3   where exists ( select null
  4                    from dual
  5                   where case
  6                         when to_char(sysdate,'dd')='19'
  7                         then (select 1 from all_users where user_id=t1.x and rownum=1)
  8                         else (select 1 from all_objects where object_id=t1.x and rownum=1)
  9                         end = 1 )
 10  /
 
         X
----------
         0
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select *
  2    from t1
  3   where exists ( select null
  4                    from dual
  5                   where case
  6                         when to_char(sysdate,'dd')='20'
  7                         then (select 1 from all_users where user_id=t1.x and rownum=1)
  8                         else (select 1 from all_objects where object_id=t1.x and rownum=1)
  9                         end = 1 )
 10  /
 
no rows selected


 

case in where

John, February 23, 2004 - 11:09 pm UTC

I would like to ask the question in similar to the earlier
lets say t(id number(10), description varchar2(30)
select *
from t
where (case when day =1
then
t.id = 1
else
t.id = 2
end)
How do I achieve this.
I would like to retrieve multiple rows depending upon there ocurrences

Appreciate your response

Regards,
John


Tom Kyte
February 24, 2004 - 6:38 am UTC

where t.id = decode( day, 1, 1, 2 );



case in where

John, February 23, 2004 - 11:46 pm UTC

I would like to ask the question in similar to the earlier
lets say t(id number(10), description varchar2(30)
select *
from t
where (case when day =1
then
t.id = 1
else
t.id = 2 and t.description like 'a%'
end)
How do I achieve this.
I would like to retrieve multiple rows depending upon there ocurrences

Appreciate your response

Regards,
John


merge rows

A reader, March 18, 2004 - 9:54 am UTC

Hi

I have this following output after running a query against two table joins

select YY.BO_ID,
YY.CM_ID,
Y.ROLE_TYPE_ID,
YY.CM_TYPE_ID
from (select bo_id,
cm_id,
PROFILE_CM_SEQ,
CM_TYPE_ID
from sysadm.PS_BO_CM_PROFL_DTL
where cm_type_id in (1, 2)
and primary_ind = 'Y'
and effdt < sysdate) YY,
(select BO_ID,
PROFILE_CM_SEQ,
ROLE_TYPE_ID
from sysadm.PS_BO_ROLE_CM
where ROLE_TYPE_ID in (1, 9)) Y
where YY.BO_ID = Y.BO_ID
and YY.PROFILE_CM_SEQ = Y.PROFILE_CM_SEQ
and ((cm_type_id = 1 and role_type_id = 9) OR
(cm_type_id = 2 and role_type_id = 2))

BO_ID CM_ID ROLE_TYPE_ID
-------- ------- --------------
1002 1 2
1002 2 2


I need to merge them so they become

BO_ID CM_ID1 CM_ID2 ROLE_TYPE_ID
-------- ------- -------- -------------
1002 1 2 2

CM_ID means contact method, basically I am getting a customer´s contact method, either 1 or 2 (phone and address), if I dont merge this into one row I get duplicate customer ids (BO_ID)

Maximum there will be two contact methods per customer so there is no need to make number of columns dynamic...

Shall I do this with UNION?

Cheers

Tom Kyte
March 18, 2004 - 10:08 am UTC

if the query returns either 0, 1 or 2 rows (eg: you run this for a customer), then

select bo_id, max(decode(r,1,cm_id)) cm_id1, max(decode(r,2,cm_id)) cm_id2, ROLE_TYPE_ID
from (
select YY.BO_ID,
YY.CM_ID,
Y.ROLE_TYPE_ID,
YY.CM_TYPE_ID,
rownum r
from (select bo_id,
cm_id,
PROFILE_CM_SEQ,
CM_TYPE_ID
from sysadm.PS_BO_CM_PROFL_DTL
where cm_type_id in (1, 2)
and primary_ind = 'Y'
and effdt < sysdate) YY,
(select BO_ID,
PROFILE_CM_SEQ,
ROLE_TYPE_ID
from sysadm.PS_BO_ROLE_CM
where ROLE_TYPE_ID in (1, 9)) Y
where YY.BO_ID = Y.BO_ID
and YY.PROFILE_CM_SEQ = Y.PROFILE_CM_SEQ
and ((cm_type_id = 1 and role_type_id = 9) OR
(cm_type_id = 2 and role_type_id = 2))
)
group by bo_id, role_type_id
/


will do, else:

....
YY.CM_TYPE_ID,
row_number() over (partition by yy.bo_id order by yy.cm_id) r

from (select bo_id,

.......


will do.

Is this possible with CASE

DXL, May 17, 2004 - 12:16 pm UTC

Tom

I would like to use the results of CASE statement in subsequent CASE statements within the same query without having to write the same CASE statement over and over again.

A very simplified view of what i am trying to achieve is :

create table t1 (c1 number(10), c2 varchar2(50));

insert into t1 values (1, 'aa');
insert into t1 values (2, 'bb');
insert into t1 values (3, 'cc');
insert into t1 values (4, 'dd');
insert into t1 values (5, 'ee');


select case when c1 = 2 then 6 else 0 end AS caseResult1,
case when c1 = 4 then 8 else 0 end AS caseResult2,
case when (caseResult1 + caseResult2 = 6) then 'success' end AS caseResult3
from t1;


case when (caseResult1 + caseResult2 = 6) then 'success' end AS caseResult3
*
ERROR at line 3:
ORA-00904: invalid column name


I get the error above, ie it doesn't recognise the result of the first 2 case statements.

So i have to do:

select case when c1 = 2 then 6 else 0 end AS caseResult1,
case when c1 = 4 then 8 else 0 end AS caseResult2,
case when ((case when c1 = 2 then 6 else 0 end) + (case when c1 = 4 then 8 else 0 end) = 6) then 'success' end AS caseResult3
from t1;

CASERESULT1 CASERESULT2 CASERES
----------- ----------- -------
0 0
6 0 success
0 0
0 8
0 0


Can I only do this by constantly repeating the CASE statements? This is ok for simple CASEs but for fields based on complicated CASE statements reused lots of times it can get very long and complicated.

Also if the case statement is repeated, does ORACLE know to reuse the results or is each CASE statement evaluated by itself? hence re running the same CASE over and over again?? I am concerned about performance here, if the latter is true then how best could i get ORACLE to re use the same CASE statement??


What I have managed to do is establish that within a procedure you are able to test bind variables with CASE as follows:

create table t1 (c1 number(10), c2 varchar2(50));

insert into t1 values (1, 'aa');


declare
v1 varchar2(30):='Y';
v2 varchar2(30):='Y';

v_sql varchar2(1000);

begin

v_Sql := 'select
case when (:v1 = ''Y'') then
case when (c1 = 1 ) then
''TRUE''
end
else ''FALSE'' end,
case when (:v2 = ''N'') then
case when (c1 = 1 ) then
''TRUE''
end
else ''FALSE'' end
from t1
where c1 = 1';

execute immediate v_sql into v1, v2 using v1, v2;

dbms_output.put_line('v1 = '||v1);
dbms_output.put_line('v2 = '||v2);


end;
/


v1 = TRUE
v2 = FALSE

PL/SQL procedure successfully completed.

This is useful but i really want to be able to test the results of previous CASE statements too.
Please can you shed some light on this.

Thanks

(DB version is 8.1.7.4)

Tom Kyte
May 17, 2004 - 4:05 pm UTC

select case when c1 = 2 then 6 else 0 end AS caseResult1,
case when c1 = 4 then 8 else 0 end AS caseResult2,
case when (caseResult1 + caseResult2 = 6) then 'success' end AS caseResult3
from t1;

would be:

select caseresult1, caseresult2,
case when (caseResult1 + caseResult2 = 6) then 'success' end AS caseResult3
from (
select case when c1 = 2 then 6 else 0 end AS caseResult1,
case when c1 = 4 then 8 else 0 end AS caseResult2
from t1
);



Can I use SELECT inside CASE statement?

Nina, July 23, 2004 - 3:07 pm UTC

Tom --

Sorry, the query is kind of long...I am just posting it as an example of the way I use CASE. It compiles fine and understands first 2 conditions. However, the last one never gets executed. So if the nbrbjob_end_date = max(nbrbjob_end-date), Oracle does not understand this condition and selects N/A. Can I even do select inside case like that?

SELECT (CASE WHEN nbrbjob_contract_type='P' AND (nbrbjob_end_date IS NULL OR
nbrbjob_end_date > TO_DATE('31-DEC-02','DD-MON-YY') OR
nbrbjob_end_date = (select max(nbrbjob_end_date)
from nbrbjob where nbrbjob_pidm = z.nbrbjob_pidm and nbrbjob_posn = nbrbjob_posn
AND NBRBJOB_END_DATE < to_date('31-DEC-02','DD-MON-YY'))) THEN
NVL(nbrptot_orgn_code,0)
ELSE 'N/A' END) orgn_code, total_amount

FROM (
SELECT sum(nbrjobs_ann_salary) total_amount , nbrbjob_pidm PIDM
FROM nbrbjob z, nbrjobs X, nbrptot y
WHERE nbrbjob_pidm = '4642073'
AND nbrbjob_posn = 'A03300'
AND nbrbjob_pidm = nbrjobs_pidm
AND nbrbjob_posn = nbrjobs_posn
AND nbrbjob_suff = nbrjobs_suff
AND nbrptot_posn= nbrbjob_posn
AND nbrptot_fisc_code = '2003'
AND nbrptot_effective_date= (
SELECT MAX(nbrptot_effective_date)
FROM nbrptot
WHERE nbrptot_posn=y.nbrptot_posn
AND nbrptot_fisc_code =
y.nbrptot_fisc_code
AND nbrptot_effective_date<=
TO_DATE(sysdate,'DD-MON-YY')
)
AND nbrjobs_effective_date = (
SELECT MAX(nbrjobs_effective_date)
FROM nbrjobs
WHERE nbrjobs_pidm = X.nbrjobs_pidm
AND nbrjobs_posn = X.nbrjobs_posn
AND nbrjobs_suff = X.nbrjobs_suff
AND nbrjobs_effective_date
<= TO_DATE('31-DEC-02','DD-MON-YY')
)
GROUP BY nbrbjob_pidm)A,
nbrbjob z, nbrjobs X, nbrptot y
WHERE nbrbjob_pidm = '4642073'
AND nbrbjob_posn = 'A03300'
AND nbrbjob_pidm = nbrjobs_pidm
AND nbrbjob_posn = nbrjobs_posn
AND nbrbjob_suff = nbrjobs_suff
AND nbrptot_posn = nbrbjob_posn
AND nbrbjob_pidm = A.PIDM
AND nbrptot_fisc_code = '2003'
AND nbrptot_effective_date = (
SELECT MAX(nbrptot_effective_date)
FROM nbrptot
WHERE nbrptot_posn=y.nbrptot_posn
AND nbrptot_fisc_code = y.nbrptot_fisc_code
AND nbrptot_effective_date<=
TO_DATE(sysdate,'DD-MON-YY'))
AND nbrjobs_effective_date = (
SELECT MAX(nbrjobs_effective_date)
FROM nbrjobs
WHERE nbrjobs_pidm = X.nbrjobs_pidm
AND nbrjobs_posn = X.nbrjobs_posn
AND nbrjobs_suff = X.nbrjobs_suff
AND nbrjobs_effective_date <=
TO_DATE('31-DEC-02','DD-MON-YY'))
AND ROWNUM < 2
ORDER BY (CASE when nbrbjob_contract_type='P' AND (nbrbjob_end_date IS NULL OR
nbrbjob_end_date > TO_DATE('31-DEC-02','DD-MON-YY') OR
nbrbjob_end_date = (select max(nbrbjob_end_date) from nbrbjob where nbrbjob_pidm = z.nbrbjob_pidm
and nbrbjob_posn = z.nbrbjob_posn
AND NBRBJOB_END_DATE < to_date('31-DEC-02','DD-MON-YY'))) THEN NVL(nbrptot_orgn_code,0) END);



Tom Kyte
July 23, 2004 - 5:08 pm UTC

how about a simple short example that shows the error you are getting?  

ops$tkyte@ORA9IR2> l
  1  select case when ( 1 = 1 or 1 = 0 and ( select count(*) from dual ) = 1 )
  2          then 'Okey Dokey'
  3             else 'Nope'
  4*            end from dual
ops$tkyte@ORA9IR2> /
 
CASEWHEN(1
----------
Okey Dokey


 

a query of Decode along with INSTR function.

kishore, July 31, 2004 - 3:45 am UTC

Tom,

I am unable to build a query using Decode with INSTR function for the following situation.

There are 4 tables (table1,table2,table3,table4).

table1 has columns (a1,b1,c1,d1)
table2 has columns (a2,b2,c2,d2)
table3 has columns (a3,b3,c3,d3)
table4 has columns (a4,b4,c4,d4)

THe first 2 tables(table1,table2) are joined with a primary key & depending upon the values in the column d2 of the table2, either table3 or either t4 table need to be accessed along with t1 and t2 tables .

FOr example :-

1)If the column d2 of table2 has the
value "Practise started on wednesday", then the join query should even include table3 along with table1 & table2 as the word "started" is present in the column c2 of table2.(the condition is if the word "started" is present the the column,then table3 should be joined along with the table1 and table2.)

2)If the column d2 of table2 has the
value "Practise stopped on saturday", then the join query should even include table4 along with table1 & table2 as the word "stopped" is present in the column c2 of table2.(the condition is if the word "stopped" is present the the column,then table4 should be joined along with the table1 and table2.)
TOm, can u please help me with the above problem ?


can we build a query using DECODE and INSTR functions to arrive at the result ?If so,Please provide me with the query !

IF it is not possible with a query ,then please provide me the solution with a pl/sql program.

Thanks in advance ,Tom



Tom Kyte
July 31, 2004 - 12:08 pm UTC

give me an example to work with -- you know, "create table", "insert into table"


make sure your logic is consistent too please

"If the column d2 of table2 has the
value "Practise started on wednesday"
, then the join query should even include
table3 along with table1 & table2 as the word "started" is present in the column
c2 of table2."


so, which is it? d2 or c2?
is it the phrase or the existence of the word?


and be complete too -- what if the string was "practice started and stopped simultaneously"





a query of Decode along with INSTR function.

kishore, August 02, 2004 - 6:56 am UTC

TOm,

Thank you for your quick reply,TOm!But ,I am really sorry for not being clear earlier.Let me provide you with the scenario .Hence i have changed the complete data and column names to understand clearly!


create table table1(mid varchar2(3),messg_immed varchar2(10),ax varchar2(3),ay varchar2(3),primary key (mid));

insert into table1 values ('101','sshhxx','ax','ay');
insert into table1 values('102','aallmm','ax1','ay1');


create table table2(mid varchar2(3),mref varchar2(3),bxx varchar2(3),byy varchar2(3),primary key (mid,mref),foreign key (mid) references table1(mid));

insert into table2 values('101','1','bx','by');
insert into table2 values('101','2','bx1','by1');
insert into table2 values('102','1','bx2','by2');
insert into table2 values('102','2','bx3','by3');


create table table3(mid varchar2(3),mref varchar2(3),mdomain varchar2(3),cx varchar2(3),cy varchar2(3),map_desc varchar2(20), map_id varchar2(10), primary key (mid,mref,mdomain), foreign key (mid,mref) references table2(mid,mref));

insert into table3 values('101','1','1','cx1','cy1','map southern ic','11251');
insert into table3 values('101','1','2','cx2','cy2',' diacom livein','30642');
insert into table3 values('101','2','1','cx3','cy3','portvic liveinnin','48926');
insert into table3 values('101','2','2','cx4','cy4',' proxum delta mir','23445');
insert into table3 values('102','1','1','cx5','cy5','dwn relian southern ','73912');
insert into table3 values('102','1','2','cx6','cy6','setwin livein prime','349');
insert into table3 values('102','2','1','cx7','cy7','proxium lenmir','1526');
insert into table3 values('102','2','2','cx8','cy8',' southern ic map','22125');

create table table4(map_desc varchar2(20),ccn varchar2(10),cc varchar2(5),maplxi varchar2(5));

insert into table4 values('diacom livein','14432','ld','sem');
insert into table4 values('portvic livein','32604','pli','hex');
insert into table4 values('setwin livein prime','89600','slm','oct');
insert into table4 values('portvic livein','47510','rmt','ano');
insert into table4 values('portvic livein ','76245','kk','penf');
insert into table4 values('diacom livein ','17480','ldi','poly');

create table table5(map_desc varchar2(20),ccnpr varchar2(10),others varchar2(10));

insert into table5 values ('proxium lemir','1526','opt');
insert into table5 values ('dec no mir','497','por');
insert into table5 values ('pmk set mir','84369','max');
insert into table5 values ('proxum delta mir','23445','kkl');


create table table6(map_desc varchar2(20),tns varchar2(10),mis varchar2(10));

insert into table6 values ('southern ic map','22125','oor');
insert into table6 values ('map southern ic','11251','o15');

table1 has one to many relation with table2.
table2 has one to many relation with table3.

THe first 3 tables(table1,table2,table3) are joined with a primary key & depending
upon the values in the column "map_desc" of the table3, either table4 or either table5 or table6
need to be accessed along with table1 ,table2 and table3.


THe Output is the rows where each row is the combination of any of the following 1 of 3 below :

1) ((table1,table2,table3) along with table4 ) :-If the map_desc column in the table3 consists of the word 'live' or 'liveinn' or 'liveinn in' in the value of the column "map_desc" of table3,then the join query should include the table4 along with table1,table2 and table3 .

table3 and table4 is joined when the column values of map_desc and map_id of table3 is compared with the "map_desc" and "ccn" column values of table4.

table4 rows are retrieved upon comparing the column values " map_desc" and "map_id" of table3 with the "map_desc" and "ccn" column values of table4.

2) ((table1,table2,table3) along with table5 ) :-If the map_desc column in the table3 consists of the word 'mir' in the value of the column "map_desc" of table3,then the join query should include the table5 along with table1,table2 and table3 .

table3 and table5 is joined when the column values of map_desc and map_id of table3 is compared with the "map_desc" and "ccnpr" column values of table5.

table5 rows are retrieved upon comparing the column values " map_desc" and "map_id" of table3 with the "map_desc" and "ccnpr" column values of table5.

3) ((table1,table2,table3) along with table6 ):-If the map_desc column in the table3 consists of the word 'southern' in the value of the column "map_desc" of table3,then the join query should include the table6 along with table1,table2 and table3 .

table3 and table6 is joined when the column values of map_desc and map_id of table3 is compared with the "map_desc" and "tns" column values of table5.

table6 rows are retrieved upon comparing the column values " map_desc" and "map_id" of table3 with the "map_desc" and "tns" column values of table6.


TOm,kindly help me out with this situation as to how can we build a query using DECODE and INSTR functions to arrive at the result ?If so,Please provide me with the query !

IF it is not possible with a query ,then please provide me the
solution with a pl/sql program.


Thanks in advance !

Tom Kyte
August 02, 2004 - 8:07 am UTC

is the relation between

(t1,t2,t3) one to one with t4 or t5 or t6

or, is it one to many?

if it is one to one, you can use a scalar subquery with CASE


(select .... from t1,t2,t3 where .....)


that is your join -- call it "Q", you can:


select ..., CASE
when some_column = 'some value' then (select .... from t4 where)
end t4_data,
.....
from (Q);


the when clause can be any sql you like -- decode, instr, substr, like, in, whatever.


if it is 1 to many - then you are looking at a union all query:


select ...
from (Q), t4
where ....
UNION ALL
select ...
from (q), t5
where.....

and so on.


a query of Decode along with INSTR function.

Kishore, August 03, 2004 - 5:33 am UTC

Tom,

      THank you for the quick reply !
I have tried in 3 different ways  but was unable to arrive at the output .This is how  it went:

1)select t1.mid,t1.messg_immed,t1.ax,t1.ay,
t2.mref,t2.bxx,t2.byy,                                    t3.mdomain,t3.cx,t3.cy,t3.map_desc,t3.map_id,
decode(t3.map_desc,(instr(t3.map_desc,'LIVE')>0),(select t4.* from table4 t4 where t3.map_desc =t4.map_desc and t4.ccn=t3.map_id),
decode(t3.map_desc,instr(t3.map_desc,'MIR')>0,(select t5.* from table5 t5 where 
t3.map_desc =t5.map_desc and t5.ccnpr=t3.map_id),
decode(t3.map_desc,(select t6.* from table6 t6 where 
t3.map_desc =t6.map_desc and t6.ccnpr=t3.map_id)))) from table1 t1,table2 t2,table3 t3,table4 t4,table5 t5,table6 t6  where
(t1.mid=t2.mid and t2.mid=t3.mid ) and (t2.mref=t3.mref);

The Output :  "No rows selected"
----------------------------------------------------------

2) select t1.*,t2.*,t3.* ,(CASE
  2  when  instr(t3.map_desc,'LIVE')>0 then
  3  (select t4.cc,t4.maplxi from table4 t4
  4      where t3.map_desc=t4.map_desc and t3.map_id=t4.ccn)
  5  when instr(t3.map_desc,'MIR')>0 then
  6  (select t5.others from table5 t5
  7      where t3.map_desc=t5.map_desc and t3.map_id=t5.ccnpr)
  8  else
  9  (select t6.mis from table6 t6
 10      where t3.map_desc=t6.map_desc and t3.map_id=t6.tns)
 11  END)
 12  from table1 t1,table2 t2,table3 t3,table4 t4,table5 t5,table6 t6
 13  where
 14  (t1.mid=t2.mid and t2.mid=t3.mid ) and (t2.mid=t3.mref);     

output : "No rows selected"

-----------------------------------------------

3)select t1.*,t2.*,t3.*,t4.cc,t4.maplxi from 
        table1 t1,table2 t2,table3 t3,table4 t4
        where 
        (t1.mid=t2.mid and t2.mid=t3.mid) and (t2.mref=t3.mref) and 
    (t3.map_desc=t4.map_desc and t3.map_id=t4.ccn)

union all

select t1.*,t2.*,t3.*,t5.others from 
        table1 t1,table2 t2,table3 t3,table5 t5
        where
        (t1.mid=t2.mid and t2.mid=t3.mid) and (t2.mref=t3.mref) and 
    (t3.map_desc=t5.map_desc and t3.map_id=t5.ccnpr)

union all

select t1.*,t2.*,t3.*,t6.mis from 
        table1 t1,table2 t2,table3 t3,table6 t6
        where 
        (t1.mid=t2.mid and t2.mid=t3.mid) and (t2.mref=t3.mref) and 
    (t3.map_desc=t6.map_desc and t3.map_id=t6.tns);


Output:

SQL> !oerr ora 1789
01789, 00000, "query block has incorrect number of result columns"
// *Cause:
// *Action:     


     Tom,Please help me with a query to arrive at the  output.That would be of great help to me !

      I thank you  for your concern ,Tom!

Kishore.
 

Tom Kyte
August 03, 2004 - 8:53 am UTC

so, just get t1,t2,t3 working first -- that is the root cause of the "no data found"

if t1,t2,t3 don't return data -- well, no need to goto t4 and so on....

you do not JOIN to t4,t5,t6 -- you use scalar subqueries (as stated above)

join t1,t2,t3 only -- like in the example above.

How to group case data into a single row?

ht, August 24, 2004 - 5:56 pm UTC

Tom,
I'm sure you have the solution to this issue but I'm not sure if this is the right place to post this question (I searched for "scott.emp" "group by deptno,job" and found this thread) so I apologize in advance.

How would I get the output of the query below to look like this?

deptno/ has_salesman / has_manager / has_clerk /has_analyst
   10                     Y          Y
   20                     Y          Y          Y
   30        Y            Y          Y
  
SQL> select deptno,
(case job when 'SALESMAN' then 'Y' end) has_salesman,
(case job when 'MANAGER' then 'Y' end) has_manager,
(case job when 'CLERK' then 'Y' end) has_clerk,
(case job when 'ANALYST' then 'Y' end) has_analyst
from scott.emp
group by deptno,job
  2    3    4    5    6    7    8  ;

    DEPTNO H H H H
---------- - - - -
        10     Y
        10   Y
        10
        20     Y
        20       Y
        20   Y
        30     Y
        30   Y
        30 Y

9 rows selected.

tia 

Tom Kyte
August 24, 2004 - 7:44 pm UTC

max( case job when 'SALESMAN' then 'Y' end) has_salesman,
....
group by deptno;


just max them, they are either Y or NULL, Y is the max of Y and NULL so if at least one Y appears/deptno -- it'll pop to the top.

A reader, August 24, 2004 - 7:52 pm UTC


Case Statement

Muhammad Ibrahim, August 24, 2004 - 10:34 pm UTC

Dear Tom,

Some questions regarding case

In oracle9i SQL reference documentation it is given as below

Case -> When -> Comparision Expres -> Then -> Return_Expr -- Simple case

and in the below description it is given as

>>All of the expressions (expr, comparison_expr, and return_expr) must of the same datatype, which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2


i just want to make my understanding better.
so

1)

Select Case When To_Date('25-aug-2004') = Trunc( Sysdate ) Then 4
Else 3 End
From Dual;


"expr, comparison_expr" Is To_Date('24-aug-2004')
"expr, comparison_expr" Is 4

I run the above query and it returns 4. Here my
question is the document says
"expr, comparison_expr, and return_expr) must of the same datatype"
in the above comparison_expr is date and return_expr is number.
So what does this mean? Or i have understood wrongly?

2)

Select Case When True=True Then True
Else False End
From Dual;

Is it poosible to evalute Boolean expression (comparison_expr)
and Boolean/any datatype return_expr ???

Or how we can do conditional checking for Boolean datatype
inside a case statement?

Thanks and Regards,
Ibrahim.

Tom Kyte
August 25, 2004 - 7:27 am UTC

1) both of the case return values are numbers -- forget about the dates, they are the boolean comparision, not relevant to what the CASE returns. "then 4", "else 3" -- the first "then 4" made it so this case returns a number.

2) boolean is not a sql type, there is no true/false in SQL.

Can case help us here?

Susan, August 25, 2004 - 1:46 pm UTC

SELECT rx_dis_code
FROM f263rstop f
WHERE (case when f.event_stop = 'VS05' then
f.event_stop < 'VS05' else when f.event_stop = 'RAND'
then (f.event_stop < 'RAND' or f. event_stope < 'SCRN')
end)

I keep receivning ORA-00905 missing keyword. Thanks.

Tom Kyte
August 25, 2004 - 1:54 pm UTC

where case when f.event_stop = 'VS05' then f.event_stop < 'VS05' ???
^^^^^^^^^^^^^^^^^^^^^

I'm sorry but I cannot even begin to imagine what that case is trying to do?

WHERE
(case when f.event_stop = 'VS05'
then f.event_stop < 'VS05'
else when f.event_stop = 'RAND'
then (f.event_stop < 'RAND' or f. event_stope < 'SCRN')
end)


first that is like saying:

where substr(x,1,5)

thats all -- just substr. you are not saying "where substr(..) = 'xxx'" or anything. You are just saying "where X"

second, the 'then' part is returning a boolean? except SQL has no boolean types to return?


so, please phrase in english what you are trying to do rather than what you attempted....

Sorry...

Susan, August 25, 2004 - 3:20 pm UTC

Here's the full query:

SELECT a.rs_id med_id, a.rx_name med_name, a.rx_freq freq,
a.rx_strt_d start_date, a.rx_source SOURCE
FROM test.f203rnew a, test.formstatus b
WHERE a.rx_stop_d = '01/01/0101'
AND a.formstat_id = b.formstat_id
AND b.master_id = '1210019'
AND a.rs_id NOT IN (SELECT rx_dis_code
FROM test.f263rstop f
WHERE f.event_stop < 'VS05')

The value for f.event_stop is the selected by a user. It's visit type. I'm trying to extract certain data at the time of a particular visit (event_stop in this case). With the exception of SCRN, the values for event_stop are named in sequential order, eg.

ADVR 2
RAND 3
SCRN 1
VS01 4
VS02 5
VS03 6

My query above returns the right data, but not if the event_stop is RAND. I tried using an or statement but that caused wrong data for other event_stops. Thanks for any suggestions.


Tom Kyte
August 25, 2004 - 3:22 pm UTC

wow, thats a horse of a totally different color eh...

create table...
create table....

insert into table....

give me something to play with here.

OK you caught me..

Susan, August 25, 2004 - 4:31 pm UTC

it wasn't the "full" query it's part of a UNION. I tried to come up with short concise example, but, that led to more confusion :( appologies for taking up so much real estate:


CREATE TABLE FORMSTATUS
(
FORMSTAT_ID NUMBER(6) NOT NULL,
MASTER_ID VARCHAR2(12) NOT NULL
);

INSERT INTO FORMSTATUS ( FORMSTAT_ID, MASTER_ID ) VALUES (
5051, '1210019');
INSERT INTO FORMSTATUS ( FORMSTAT_ID, MASTER_ID ) VALUES (
4254, '1210019');


CREATE TABLE F263
(
MASTER_ID VARCHAR2(7),
VISIT CHAR(4),
)

INSERT INTO F263 ( MASTER_ID, VISIT ) VALUES (
'1110018', 'RAND');
INSERT INTO F263 ( MASTER_ID, VISIT ) VALUES (
'1726001', 'RAND');
INSERT INTO F263 ( MASTER_ID, VISIT ) VALUES (
'1290275', 'RAND');
INSERT INTO F263 ( MASTER_ID, VISIT ) VALUES (
'1210019', 'RAND');

CREATE TABLE F203RNEW
(
RX_NAME VARCHAR2(150),
RX_FREQ NUMBER(2),
RX_STRT_D VARCHAR2(10),
RX_STOP_D VARCHAR2(10),
RX_SOURCE NUMBER(2),
RS_ID NUMBER(7),
FORMSTAT_ID NUMBER(6) NOT NULL,
RS_ORDER NUMBER(4)
)

INSERT INTO F203RNEW ( RX_NAME, RX_FREQ, RX_STRT_D, RX_STOP_D, RX_SOURCE, RS_ID, FORMSTAT_ID,
RS_ORDER ) VALUES (
'RX 4', 1, '08/01/2002', '01/01/0101', 1, 1669, 4254, 3);
INSERT INTO F203RNEW ( RX_NAME, RX_FREQ, RX_STRT_D, RX_STOP_D, RX_SOURCE, RS_ID, FORMSTAT_ID,
RS_ORDER ) VALUES (
'RX 2', 1, '08/01/2002', '01/01/0101', 1, 1667, 4254, 1);
INSERT INTO F203RNEW ( RX_NAME, RX_FREQ, RX_STRT_D, RX_STOP_D, RX_SOURCE, RS_ID, FORMSTAT_ID,
RS_ORDER ) VALUES (
'RX1', 1, '08/01/2003', '01/01/0101', 1, 1666, 4254, 0);


CREATE TABLE F263RNEW
(
RX_ADD_NAME VARCHAR2(150),
RX_FREQ NUMBER(2),
RX_STRT_D VARCHAR2(10),
RX_STOP_D VARCHAR2(10),
RX_SOURCE NUMBER(2),
RS_ID NUMBER(7),
FORMSTAT_ID NUMBER(6) NOT NULL,
RS_ORDER NUMBER(4)
)

INSERT INTO F263RNEW ( RX_ADD_NAME, RX_FREQ, RX_STRT_D, RX_STOP_D, RX_SOURCE, RS_ID, FORMSTAT_ID,
RS_ORDER ) VALUES (
'RX 5', 1, '08/01/2003', '01/01/0101', 1, 1692, 5051, 0)

SELECT a.rs_id med_id, a.rx_add_name med_name, a.rx_freq freq,
a.rx_strt_d start_date, a.rx_source SOURCE
FROM uitn.f263rnew a, uitn.formstatus b, uitn.f263 c
WHERE a.rx_stop_d = '01/01/0101'
AND a.formstat_id = b.formstat_id
AND c.visit > 'VS05'
AND a.formstat_id = c.formstat_id
AND b.master_id = '1210019'
AND a.rs_id NOT IN (SELECT rx_dis_code
FROM uitn.f263rstop
WHERE c.visit <= 'VS05')
UNION
SELECT a.rs_id med_id, a.rx_name med_name, a.rx_freq freq,
a.rx_strt_d start_date, a.rx_source SOURCE
FROM uitn.f203rnew a, uitn.formstatus b
WHERE a.rx_stop_d = '01/01/0101'
AND a.formstat_id = b.formstat_id
AND b.master_id = '1210019'
AND a.rs_id NOT IN (SELECT rx_dis_code
FROM uitn.f263rstop f
WHERE f.event_stop < 'VS05')


Tom Kyte
August 26, 2004 - 9:12 am UTC

  5
ops$tkyte@ORA9IR2> SELECT rx_dis_code FROM f263rstop WHERE c.visit <= 'VS05'
  2  /
SELECT rx_dis_code FROM f263rstop WHERE c.visit <= 'VS05'
                        *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 


missing one?

and describe what you mean by "wrong answer" -- what were you expecting and what do you get? 

Function inside the Case Comparision exp

Muhammad Ibrahim, August 25, 2004 - 11:25 pm UTC

Dear Tom,

Thanks for your reply.

So if i want to check some function inside the case statement and return depends on that is it possible?

eg:

1)

Select Case When ( My_Func ) Then 4
Else 3 End
From Dual;
-- My Func returns Boolean either True or False

2)
Select Case When ( My_Func1 = 0 ) Then 4
Else 3 End
From Dual;
-- My Func1 returns Integer 0 or 1

Is these possible inside the Case comparission expression and depends on that return something!

Regards,
Ibrahim.

Tom Kyte
August 26, 2004 - 9:32 am UTC

boolean is not a sql type.

sql does not understand boolean.

your function must return a type sql can understand and use, a datatype you could use as a column type in a create table. that is your litmus test here -- can you create a table that has the same exact type as your function returns....


#1 -- no

#2 -- yes.

oops

Susan, August 26, 2004 - 10:21 am UTC

CREATE TABLE F263RSTOP
(
RX_DIS_CODE NUMBER(4),
RX_DIS_NAME VARCHAR2(40),
RX_DIS_STOP VARCHAR2(10),
RS_ID NUMBER(7),
FORMSTAT_ID NUMBER(6) NOT NULL,
RS_ORDER NUMBER(4),
EVENT_STOP VARCHAR2(4)
)

INSERT INTO F263RSTOP ( RX_DIS_CODE, RS_ID, FORMSTAT_ID, EVENT_STOP ) VALUES (
1669, 1695, 5051, 'RAND');

SELECT a.rs_id med_id, a.rx_add_name med_name, a.rx_freq freq,
a.rx_strt_d start_date, a.rx_source SOURCE
FROM uitn.f263rnew a, uitn.formstatus b, uitn.f263 c
WHERE a.rx_stop_d = '01/01/0101'
AND a.formstat_id = b.formstat_id
AND c.visit > 'VS05'
AND a.formstat_id = c.formstat_id
AND b.master_id = '1210019'
AND a.rs_id NOT IN (SELECT rx_dis_code
FROM uitn.f263rstop
WHERE event_stop <= 'RAND')
UNION
SELECT a.rs_id med_id, a.rx_name med_name, a.rx_freq freq,
a.rx_strt_d start_date, a.rx_source SOURCE
FROM uitn.f203rnew a, uitn.formstatus b
WHERE a.rx_stop_d = '01/01/0101'
AND a.formstat_id = b.formstat_id
AND b.master_id = '1210019'
AND a.rs_id NOT IN (SELECT rx_dis_code
FROM uitn.f263rstop f
WHERE f.event_stop < 'RAND')


MED_ID MED_NAME
1666 RX1
1667 RX 2
1669 RX 4

I don't want 1669 to be returned because it's in the f263stop table. Adding a "or f.event_stop < 'SCRN'", solves the problem for RAND, but, I only want that evaluation (SCRN) for RAND not other visits. Thanks.


Tom Kyte
August 26, 2004 - 10:24 am UTC

sorry -- we are going to have to back waaaayyyyy up here.

vs50 isn't a problem?

can you, in just plain english, explain what you are trying to accomplish. of course using scrn would "solve the problem", but so would using 'Z' (event stop is just a character string here). I'm not sure of the meaning of this "stop" table (or anything really, haven't figured out what the question or the goal is actually.)

confusing I know

Susan, August 26, 2004 - 10:56 am UTC

ok bear with me because it's not my design - F203, F263 are meds tables, F263RSTOP means that one of the meds started has stopped, event_stop and visit are visit types, we're trying to have a report that will show the user what meds the patient was taking (not stopped) as of the last visit of the one entered. With the exception of SCRN, the values for event_stop are named in sequential order, eg. So the character string evaluation works but not for RAND and SCRN because SCRN comes before RAND. All I'm trying to do is to see if there is a way to say for RAND use f.stop_event < 'RAND' or f.stop_event < 'SCRN' but just for RAND not other visits. Thanks for all your help.

ADVR 2
RAND 3
SCRN 1
VS01 4
VS02 5
VS03 6

Tom Kyte
August 26, 2004 - 11:04 am UTC

does this work for you?

f.stop_event < decode( :input_to_query, 'RAND', 'SCRN', :input_to_query )




Yeah!

Susan, August 26, 2004 - 11:21 am UTC

Thanks for your wisdom and patience!

decode

mo, September 01, 2004 - 4:36 pm UTC

Tom:

How can i use decode to do:
if (a>b) then null else 'Process'

select a,b,decode(a>b,true,'PR') from (select 8 a, 7 b from dual)
*
ERROR at line 1:
ORA-00907: missing right parenthesis


Tom Kyte
September 01, 2004 - 8:25 pm UTC

select case when a>b then null else 'Process' end from ...

is the easiest. if you cannot use case and you have a number or date

select decode( sign( a-b ), +1, null, 'process' ) ....

as sign returns +1 for positive (a is bigger than b), zero for zero (a=b), and -1 for negative (a is smaller than b)

Every thing Tom says

Mahesh, September 02, 2004 - 2:10 am UTC

ToThePoint

Decode OR case when...

A reader, September 02, 2004 - 9:37 am UTC

Hi Tom,

Select substr(projet,1,20),substr(code,1,20),proton_id from proton
where code = 'ZC-099_LG' and
Ptype = 'M' and
Projet IN('RQ_TRESOR','TRESOR','SGP','FIEA','CNP','SCP');


SUBSTR(PROJET,1,20) SUBSTR(CODE,1,20) PROTON_ID
-------------------- -------------------- ----------
SGP ZC-099_LG 771983
TRESOR ZC-099_LG 283355


My question is : how to do to have a result like


SUBSTR(PROJET,1,20) SUBSTR(CODE,1,20) PROTON_ID
-------------------- -------------------- ----------
TRESOR ZC-099_LG 283355

distinct and order by the list of Projet IN(.......)


if records match with 'RQ_TRESOR' it should not checj other condition
if 'RQ_TRESOR' not it should go next match of 'TRESOR', if this matched it should not go beyond this.
How to do this

Thanks




Tom Kyte
September 02, 2004 - 10:02 am UTC

be nice to have create table, insert into -- so I can demonstrate

but -- the question isn't very well specified here. (i can sort of overlook the RQ_ all of a sudden appearing from nowhere but...)

are you saying "if in my entire table, there is a record with PROJECT=TRESOR, output just that/those records"

or

are you saying "by CODE, if in my table -- for that code -- there is a record with PROJECT=TRESOR, then output that, else find any of the others and output them.

or

something entirely different from the above two reasonable "maybe you meant that"'s

Decode OR case when..

A reader, September 07, 2004 - 7:57 am UTC

Hi Tom,
Here is a sample


CREATE TABLE t (
PID NUMBER
,Code VARCHAR2(100)
,Ptype VARCHAR2(1)
,Project VARCHAR2(100)
)




INSERT INTO t VALUES(1,'SCP','A','PRA');
INSERT INTO t VALUES(2,'SCP','A','PRB');
INSERT INTO t VALUES(3,'SCP','A','PRC');
INSERT INTO t VALUES(4,'SCP','A','PRD');




SELECT * FROM t WHERE PROJECT IN ('PRD','PRC','PRB',PRA')

Result set should be return in the order as given in IN cluase.
If 'PRD' matches it should return only those matched recored, should not go beyond this.
If 'PRD' not matched, it will search for 'PRC' ,if matches found then it should not go beyond this.
---------------------

SELECT * FROM t WHERE PROJECT IN ('PRD','PRC','PRB',PRA')

Result must be

PID Code Ptype Project
---- ---- ---- -------
4 SCP A PRD


SELECT * FROM t WHERE PROJECT IN ('PRC','PRD','PRB',PRA')


PID Code Ptype Project
---- ---- ---- -------
3 SCP A PRC


Thank you.



Tom Kyte
September 07, 2004 - 9:35 am UTC

in doesn't even remotely work like that - the only thing -- the ONLY THING that orders data is "order by"



select *
from ( select * from t where project in ( 'PRD', 'PRC', 'PRB', 'PRA' )
order by decode( project, 'PRD', 1,
'PRC', 2,
'PRB', 3,
'PRA', 4 )
)
where rownum = 1;


you have to select the rows, order them and then just ask for the first one.


Thank you Tom.

A reader, September 08, 2004 - 4:48 am UTC

Thank you

Hi Tom

reader, November 09, 2004 - 4:18 pm UTC

Is there an ISNull function in sqlplus like VBA.

like below:(using a code snippet of yours with some changes)

IS NULL(x)=decode(y, 5, 'five', 6, 'six', 'zero' ).

instead of having to code:

if x is null then
update t
set x=decode(y, 5, 'five', 6, 'six', 'zero' );
end if;

thanks







Tom Kyte
November 09, 2004 - 7:22 pm UTC

I'm not following you....

if x is null then
update t
set x=decode(y, 5, 'five', 6, 'six', 'zero' );
end if;


that doesn't make sense to me? (how can X be in the IF statement -- isn't X a column in the table T???)


can you just state in english what you are trying to do?

hopefully a better explanation....

reader, November 10, 2004 - 9:14 am UTC

yes...x is a column in a table.

I am adding code to an existing package(not mine)...
the 'y' variable is from another table(in same package)
whose value we want to compare to update into the x column of table t.

example:
if y='Prod' then x='P'
elsif y='Test' then x='T'

not all x columns in table t are null, so we don't
want to change those values.

what I need is:

update t
set x=decode(y, 'Prod', 'P', 'Test', 'T', x )
where x is null;

I was wondering if the null predicate could be included
as part of the 'set' statement so I wouldn't have to
use the 'where' clause since I will be inserting an
additional set statement into an Update statement that already exists for table t in the package.

(this is an example of existing code in package)
update t
set t.col1 = new.col1,
t.col2= new.col2,
t.col3 = y,
t.col4 = new.col3,
t.x=decode(y, 'Prod', 'P', 'Test', 'T', x ) 'my code added here and updated only if x is null'
where
not exists
(select 'x' from ord o
where t.id = o.id)
and t.id = new.id;


hope this clarifies my question,
thanks




Tom Kyte
November 10, 2004 - 12:09 pm UTC

t.x = decode( t.x, null, decode(y, .... ), t.x )


says "if x is null, set to decode(y....), else set to t.x"


Super!!!

reader, November 10, 2004 - 1:08 pm UTC

your solution did the trick!
one last thanks.

Using case in PL/SQL code ORACLE 8I

KAN, January 25, 2005 - 4:45 pm UTC

Hi,Tom,
IF i have in my select in pl/sql
a lot of case columns like :
select sum(case....,
sum(case,sum(case.....
IS it better to use ref cursor or VIEW? And why?
Thank you very much
KAN

Tom Kyte
January 25, 2005 - 7:11 pm UTC

if you can do it in the view (predicates gets pushed just fine, querying the view is as fast as not querying via a view), that would be "best"

static sql rules in plsql. it is easier, faster, cleaner, you get the dependencies set up, you can bulk collect, and so on.

Can CASE/DECODE do this too?

Rex, January 26, 2005 - 4:51 am UTC

Hi Tom, I have a table.

create table my_test
(DevId number(2),Phase varchar2(3));

insert into my_test values (1,'A');
insert into my_test values (2,'AB');
insert into my_test values (3,'C');

What I want is
when I query all phases used, an 'ABC' is returned.

Can CASE/DECODE do this too? if not,
can you suggest a better way?

Thanks.

Tom Kyte
January 26, 2005 - 8:54 am UTC

ops$tkyte@ORA9IR2> select distinct substr(phase,r,1) c
  2    from my_test,(select 1 r from dual union all select 2 from dual union all select 3 from dual)
  3  /
 
C
-
A
B
C
 
 
ops$tkyte@ORA9IR2> select stragg(c)
  2    from (
  3  select distinct substr(phase,r,1) c
  4    from my_test,(select 1 r from dual union all select 2 from dual union all select 3 from dual)
  5         )
  6  /
 
STRAGG(C)
-------------------------------------------------------------------------------
A,B,C


(search this site for stragg)
 

StrAgg with out a ','

Rex, January 26, 2005 - 7:02 pm UTC

Hello Tom,
The follow up was great! I learned something new today, StrAgg.
Thanks!!
The query you provided returned an 'A,B,C' result,
would it be possible to return a result without the comma?
like 'ABC'?

By the way, I made some testing and came up with


SELECT
(
(SELECT DECODE(COUNT(*),0,NULL,'A') FROM MY_TEST WHERE PHASE LIKE '%A%')||
(SELECT DECODE(COUNT(*),0,NULL,'B') FROM MY_TEST WHERE PHASE LIKE '%B%')||
(SELECT DECODE(COUNT(*),0,NULL,'C') FROM MY_TEST WHERE PHASE LIKE '%C%')
) CONNPHASE
FROM DUAL

CON
---
ABC

What do you think?

Tom Kyte
January 27, 2005 - 7:58 am UTC

(hint: you have the code for stragg, think about that......)

;)

you can make it do whatever you desire.


your query above -- what do i think? I think:

select 'ABC' from dual;

would be more efficient and provide the same result -- i mean, are we talking just about A, B and C here - I made the (reasonable) assumption that the characters could be any character at all -- and that you wanted a concatenation of all unique characters to be found in this field.

If it is limited to just A, B and C, there are other infinitely better ways to do this -- but that information was not supplied in the problem description.

Union with message

Shahid, January 28, 2005 - 4:50 am UTC

Hi Tom,
Thank you very much for making us more knowledgeable.
I have a query for you.

SQL> select * from t1;

         X
----------
        10
        20
        30
        40
        50
        60
        70


SQL> select * from t2;

         X
----------
        30
        40
        50
        80
        90

I want to do a union of both the tables and want to dispaly the output somewhat like this.(By seeing the output i can make out from which table the data is fetched)

         X
----------
        10  table t1
        20  table t1
        30  both tables
        40  both tables
        50  both tables
        60  table t1 
        70  table t1
        80  table t2 
        90  table t2

i am using 9.2.0.1.0.
Hopefully, i will hear you soon.

Regards
Shahid.
 

Tom Kyte
January 28, 2005 - 7:36 am UTC

ops$tkyte@ORA9IR2> select * from t1;
 
         X
----------
         1
         1
         2
 
ops$tkyte@ORA9IR2> select * from t2;
 
         X
----------
         2
         3
         3
 
ops$tkyte@ORA9IR2> select x, decode( count(distinct whence), 2, 'both', max(whence) ) what
  2    from (
  3  select 't1' whence, x from t1
  4  union all
  5  select 't2' whence, x from t2
  6         )
  7   group by x
  8  /
 
         X WHAT
---------- ------------------------------
         1 t1
         2 both
         3 t2
 

Remarkable!!!

Rex, January 30, 2005 - 7:07 pm UTC


Union with message

Shahid, January 31, 2005 - 3:34 am UTC

Thanks Tom.
u did it again!!!!!!!
Thanks for the prompt reply.

Regards
Shahid.

Tom Kyte
January 31, 2005 - 8:17 am UTC

who is this "u" guy and why are they getting the credit again?

I keep getting request for "u" to look at things...

I've yet to meet "u", I keep looking -- not even sure of the gender of "u".

But "u" did not participate in this discussion ;)

I did.

Any solution??

Siva, February 03, 2005 - 12:39 pm UTC

Hi Tom,
I don't want to use *where*  condition for this query.Are there other ways to do it?

SQL> select comm from emp
  2  where comm is not null
  3  /

      COMM                                                        

              
----------                                                        

              
       300                                                        

              
       500                                                        

              
      1400                                                        

              
         0   

Tom Kyte
February 03, 2005 - 2:47 pm UTC

if you want to filter data, to remove rows -- well guess what.


where is where it is at.



Until Oracle 101s comes out that is (that is the sentient version of Oracle, it'll know what you want -- sqlplus becomes easy for all, you just have to:

SQL> give it to me;


and it'll know what to print out, the beta is really cool....)


sorry, i just could not resist on this one. 

Oracle 101s

A reader, February 03, 2005 - 11:18 pm UTC

I hear that's the version where they're putting in the fast=true button. If the system is slow, pressing the fast=true button will make it go fast.

when null

A reader, February 14, 2005 - 10:27 am UTC

case x
when 1 then 'one'
when 2 then 'two'
when null then 'null'
end

this doesnt work when x is null

Yes, I can use
case
when x=1 then 'one'
when x=2 then 'two'
when x is null then 'null'
end

but the first form is more compact and probably more efficient.

Can I use the first form of the case and still handle nulls?

Thanks

Tom Kyte
February 14, 2005 - 1:30 pm UTC

decode would work

decode( x, 1, 'one', 2, 'two', null, 'null' )


or

nvl( case x when 1 then 'one' when 2 then 'two' end, 'null' )

else the searched case uses "equals"

CASE WHEN Construct

Maverick, February 17, 2005 - 9:58 am UTC

Tom,
1) I have a query that takes in 2 variables(could be nulls) and produces the output.
Here is what i 'm trying to do:

select * from emp
where case when :p_empno is not null then empno=:p_empno else 1=1 end and
case when :pdeptno is not null then deptno=:pdeptno else 1=1
end

Is this correct syntax for CASE. I'm getting "missing keyword" error at empno=p_empno

2) which one is better using CASE or like this?

select * from emp
where empno=nvl(:p_empno,empno) and
deptno=nvl(:p_deptno,deptno)

** By using above method, it will not use indexes. isn't that true?

Thanks for any help on this one



Tom Kyte
February 17, 2005 - 11:15 am UTC

you have a case attempting to return a "boolean", they cannot do that.  it be a "big" case to cover all of the bases.

NVL or expansion is interesting, the optimizer will take "where x = nvl(:x,x)" and "or expand" it, effectively creating two plans in one.  but the "where x= nvl() and y = nvl()" is going to be suboptimal as it will only do it "once" (two plans -- not 3 or 4 that you would like to have)

Consider:


ops$tkyte@ORA9IR2> create table t
  2  as
  3  select a.*, to_char(object_id) id1, to_char(object_id) id2
  4    from all_objects a
  5  /
 
Table created.
 
ops$tkyte@ORA9IR2> create index t_idx1 on t(id1);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx2 on t(id2);
 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @plan "select * from t where id1 = nvl(:x,id1) and id2 = nvl(:y,id2)"
ops$tkyte@ORA9IR2> delete from plan_table;
 
7 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select * from t where id1 = nvl(:x,id1) and id2 = nvl(:y,id2)
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
-----------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |   208 |    71 |
|   1 |  CONCATENATION                |             |       |       |       |
|*  2 |   FILTER                      |             |       |       |       |
|*  3 |    TABLE ACCESS FULL          | T           |     1 |   104 |     2 |
|*  4 |   FILTER                      |             |       |       |       |
|*  5 |    TABLE ACCESS BY INDEX ROWID| T           |     1 |   104 |     2 |
|*  6 |     INDEX RANGE SCAN          | T_IDX2      |     1 |       |     1 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(:Z IS NULL)
   3 - filter("T"."ID1"=NVL(:Z,"T"."ID1") AND "T"."ID2" IS NOT NULL)
   4 - filter(:Z IS NOT NULL)
   5 - filter("T"."ID1"=NVL(:Z,"T"."ID1"))
   6 - access("T"."ID2"=:Z)
 
Note: cpu costing is off
 
23 rows selected.
 
<b>At runtime, if the bind to be compared to ID2 is NOT NULL, it'll index range scan, else if the bind is NULL, it'll full scan.  reverse the predicate and in this case:</b>

ops$tkyte@ORA9IR2> @plan "select * from t where id2 = nvl(:x,id2) and id1 = nvl(:y,id1)"
ops$tkyte@ORA9IR2> delete from plan_table;
 
7 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select * from t where id2 = nvl(:x,id2) and id1 = nvl(:y,id1)
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
-----------------------------------------------------------------------------
| Id  | Operation                     |  Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |   208 |    71 |
|   1 |  CONCATENATION                |             |       |       |       |
|*  2 |   FILTER                      |             |       |       |       |
|*  3 |    TABLE ACCESS FULL          | T           |     1 |   104 |     2 |
|*  4 |   FILTER                      |             |       |       |       |
|*  5 |    TABLE ACCESS BY INDEX ROWID| T           |     1 |   104 |     2 |
|*  6 |     INDEX RANGE SCAN          | T_IDX1      |     1 |       |     1 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(:Z IS NULL)
   3 - filter("T"."ID2"=NVL(:Z,"T"."ID2") AND "T"."ID1" IS NOT NULL)
   4 - filter(:Z IS NOT NULL)
   5 - filter("T"."ID2"=NVL(:Z,"T"."ID2"))
   6 - access("T"."ID1"=:Z)
 
Note: cpu costing is off
 
23 rows selected.
 
<b>it do id1 instead (they are more or less "equal" here in this example).  

So, the way to get the best plan each of the 4 cases?</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure open_query( p_x in varchar2, p_y in varchar2, p_cur in out sys_refcursor )
  2  as
  3  begin
  4      if ( p_x is not null and p_y is not null )
  5      then
  6          open p_cur for select * from t where id1 = p_x and id2 = p_y;
  7      elsif ( p_x is not null )
  8      then
  9          open p_cur for select * from t where id1 = p_x and id2 is not null;
 10      elsif ( p_y is not null )
 11      then
 12          open p_cur for select * from t where id2 = p_y and id1 is not null;
 13      else
 14          open p_cur for select * from t where id2 is not null and id1 is not null;
 15      end if;
 16  end;
 17  /
 
Procedure created.
 
 

CASE WHEN Construct

Maverick, February 17, 2005 - 3:07 pm UTC

Tom, Thanks for your clear explanation with all plans.
1) Can you explain more on your answer on my first question?
Can you give me correct syntax (assuming we are not
looking at booleans but some values)

2) By using NVL function, i'm covering all four cases ,aren't I? can you explain more.

Thanks for all your help and time..


Tom Kyte
February 17, 2005 - 3:13 pm UTC

1) it would be "big"

case when :x is not null and :y is not null and x = :x and y = :y then 1
when :x is not null and :y is null and x = :x and y is not null then 1
when :x is null and :y is not null and y = :y and x is not null then 1
when :x is null and :y is null and x is not null and y is not null then 1
else 0
end = 1;

and it would full scan

2) you are covering all four cases HOWEVER look at the plans.

We can either

a) use a single index when that bind is supplied
b) full scan

however you want:

a) index on X used when :x is supplied
b) index on Y used when :y is supplied
c) full scan otherwise
and maybe even sometimes
d) index on X and index on Y to be used when :x and :y is supplied.


using NVL() you can get either:

a) index on X used when :x is supplied
b) full scan otherwise

or

b) index on Y used when :y is supplied
b) full scan otherwise


that was my point, you want 3 to 4 plans to be possible, using NVL(), I can get 2, using the stored procedure, I can get 3 or 4 of them.



Mavericks No. 1

Mathias Rogel, February 18, 2005 - 3:09 am UTC

Hallo Maverick,

I would write No.1 as

select * from emp
where (:p_empno is null or empno=:p_empno) and
(:pdeptno is null or deptno=:pdeptno)



Tom Kyte
February 18, 2005 - 8:06 am UTC

why? because you *always* want to full scan and *never* use an index at all?


this discussion has been sort of focused on "how to do this and get performance", all of the talk has been centered around why you want to do it the way described in order to get one of 4 potential plans.

CASE in Where clause

A reader, February 24, 2005 - 5:44 pm UTC

Tom,
It has been explained many times in this thread on how to use CASE in where clause, but i could not get it to use to my particular scenario. Hope you can help me.

I am trying to write a SQL query where in i have to fetch the following
id,name,status_no and status_date.

there would be many status_no's and many status_dates for each id. so, there is a repetetion of id's.

What i need is
if a status_no falls in one of the three (10,12,13)
then
if status_date >=to_date(sysdate)
then get this row
else disregard this one
else
display all.

I thought of using Case in where clause but could not do that successfully..(i have to do this in SQL query)
Can you give me a sample to do that?

Thanks,




Tom Kyte
February 24, 2005 - 5:51 pm UTC

did you read the text on the page you used to submit this?

the text that says....

If your followup requires a response that might include a query, you had better supply very very simple create tables and insert statements. I cannot create a table and populate it for each and every question. The SMALLEST create table possible (no tablespaces, no schema names, just like I do in my examples for you)



Case in Where clause

A reader, February 25, 2005 - 11:44 am UTC

Tom,
Sorry for not includnig all the info. Here are the details

CREATE TABLE T_STATUS
(
ID NUMBER(5),
NAME VARCHAR2(20 BYTE),
STATUS_ID NUMBER(2),
STATUS_DATE DATE
)


INSERT INTO T_STATUS ( ID, NAME, STATUS_ID, STATUS_DATE ) VALUES (
1, 'Mike', 12, TO_Date( '10/20/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO T_STATUS ( ID, NAME, STATUS_ID, STATUS_DATE ) VALUES (
2, 'jake', 10, TO_Date( '10/01/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO T_STATUS ( ID, NAME, STATUS_ID, STATUS_DATE ) VALUES (
3, 'michael', 26, TO_Date( '10/30/2004 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO T_STATUS ( ID, NAME, STATUS_ID, STATUS_DATE ) VALUES (
4, 'poker', 18, TO_Date( '12/10/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO T_STATUS ( ID, NAME, STATUS_ID, STATUS_DATE ) VALUES (
5, 'john', 26, TO_Date( '12/12/2002 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO T_STATUS ( ID, NAME, STATUS_ID, STATUS_DATE ) VALUES (
6, 'july', 28, TO_Date( '11/12/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));

COMMIT;

I want this result set..

Id Name Status Date

1 Mike 12 10/20/2003
2 jake 10 10/01/2001
3 michael 26 10/30/2004
4 poker 18 12/10/2002
6 july 28 11/12/2003

Here Mike & July are selected in the result as their status is in (26,28,30) and date is >=10/01/2003
But john is not selected as his status is 26, but date is <10/01/2003
but others are selected as they do not have status in (26,28,30)


thanks.

Tom Kyte
February 25, 2005 - 6:29 pm UTC

ops$tkyte@ORA10G> select *
  2    from t_status
  3   where case when status_id in (26,28,30) and status_date >= to_date('10/01/2003','mm/dd/yyyy')
  4              then 1
  5                          when status_id NOT in (26,28,30)
  6                          then 1
  7                  end = 1
  8  /
 
        ID NAME                            STATUS_ID STATUS_DA
---------- ------------------------------ ---------- ---------
         1 Mike                                   12 20-OCT-03
         2 jake                                   10 01-OCT-01
         3 michael                                26 30-OCT-04
         4 poker                                  18 10-DEC-02
         6 july                                   28 12-NOV-03
 

Case in Where clause

A reader, February 25, 2005 - 11:48 am UTC

It's me again..Forgot to mention that last night i've said, if status in (10,12,13)then, should check for dates
but , in my reply today i have mentioned 26,28,and 30 as status no's to check for dates.
You can use either of those in your answer.

Thanks

case when and decode

daxu, March 01, 2005 - 4:15 pm UTC

Tom,
I got Ora-01722 invalid number error when I tried this,

create table TBL_A
(
F1 NUMBER,
F2 NUMBER
);
insert into tbl_a values (1, 2);
insert into tbl_a values (2, 2);
create table TBL_B
(
F1 NUMBER,
PK VARCHAR2(10)
);
insert into tbl_b values (1, '1|1');
insert into tbl_b values (1, '1');
insert into tbl_b values (2, '1');

select * from tbl_a a
where a.f1 in (select case when b.f1 = 2 then
to_number(b.pk)
else a.f1
end
from tbl_b b);

Is it a bug and is there anyway I can avoid this kind of error?


Thanks,







Tom Kyte
March 01, 2005 - 4:26 pm UTC

I cannot reproduce that using either of 9ir2 or 10gr1.

can you cut and paste the example from sqlplus like this:

ops$tkyte@ORA10G> create table TBL_A
  2  (
  3    F1 NUMBER,
  4    F2 NUMBER
  5  );
 
Table created.
 
ops$tkyte@ORA10G> insert into tbl_a values (1, 2);
 
1 row created.
 
ops$tkyte@ORA10G> insert into tbl_a values (2, 2);
 
1 row created.
 
ops$tkyte@ORA10G> create table TBL_B
  2  (
  3    F1 NUMBER,
  4    PK VARCHAR2(10)
  5  );
 
Table created.
 
ops$tkyte@ORA10G> insert into tbl_b values (1, '1|1');
 
1 row created.
 
ops$tkyte@ORA10G> insert into tbl_b values (1, '1');
 
1 row created.
 
ops$tkyte@ORA10G> insert into tbl_b values (2, '1');
 
1 row created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from tbl_a a
  2  where a.f1 in (select case when b.f1 = 2 then
  3                             to_number(b.pk)
  4                        else a.f1
  5                        end
  6                 from tbl_b b);
 
        F1         F2
---------- ----------
         1          2
         2          2
 

case when and decode

daxu, March 02, 2005 - 11:12 am UTC

Tom,
The previous example I sent to you might not a good one, I am sorry for wasting your time on that. The followings are the code that similar what I coded in our system. The purpose of that is, if the type value is in tbl_c 1, then I will only get those record in the timestamp range in tbl_c, other wise I will get all the records despite of it. I also tried using decode to replace the case when, and got the same error, too.

Here are the steps I ran though, and the error message I got,

Connected to Oracle9i Release 9.2.0.5.0 
Connected as dcao


SQL> 
SQL> create table TBL_A
  2  (
  3    TABLE_ID    NUMBER(10),
  4    SCHEMA_NAME VARCHAR2(30),
  5    TABLE_NAME  VARCHAR2(30)
  6  )
  7  /

Table created

SQL> insert into tbl_a values (27, 'TEST', 'COMPOSITE_KEY');

1 row inserted

SQL> insert into tbl_a values (31, 'TEST', 'PRIMARY_KEY');

1 row inserted

SQL> 
SQL> create table TBL_B
  2  (
  3    TABLE_ID    NUMBER(10),
  4    ENTRY_TS    DATE,
  5    PRIMARY_KEY VARCHAR2(500)
  6  )
  7  /

Table created

SQL> insert into tbl_b values (27, sysdate - 5, '1|1');

1 row inserted

SQL> insert into tbl_b values (31, sysdate - 4, '61916');

1 row inserted

SQL> insert into tbl_b values (31, sysdate - 30 , '61976');

1 row inserted

SQL> 
SQL> create table TBL_C
  2  (
  3    LAST_START_TS    DATE,
  4    CURRENT_START_TS DATE,
  5    TYPE             NUMBER(1)
  6  )
  7  /

Table created

SQL> insert into tbl_c values (sysdate - 15, sysdate, 1);

1 row inserted

SQL> create table TBL_D
  2  (
  3    TRANSACTION_SEQ NUMBER
  4  )
  5  /

Table created

SQL> insert into tbl_d values (61916);

1 row inserted

SQL> insert into tbl_d values (61976);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from tbl_d d
  2  where d.transaction_seq in
  3        ( select case when
  4                      c.type = 1 then to_number(b.primary_key)
  5                 else
  6                      d.transaction_seq
  7                 end
  8          from   tbl_c c,
  9                 tbl_a a,
 10                 tbl_b b
 11          where  b.entry_ts between c.last_start_ts and c.current_start_ts
 12          and    a.schema_name = 'TEST'
 13          and    a.table_id    = (case when c.type = 1 then b.table_id else a.table_id end)
 14          and    a.table_name  = 'PRIMARY_KEY')
 15  /

select * from tbl_d d
where d.transaction_seq in
      ( select case when
                    c.type = 1 then to_number(b.primary_key)
               else
                    d.transaction_seq
               end
        from   tbl_c c,
               tbl_a a,
               tbl_b b
        where  b.entry_ts between c.last_start_ts and c.current_start_ts
        and    a.schema_name = 'TEST'
        and    a.table_id    = (case when c.type = 1 then b.table_id else a.table_id end)
        and    a.table_name  = 'PRIMARY_KEY')

ORA-01722: invalid number

SQL> 

Thanks again for the help.












 

Tom Kyte
March 02, 2005 - 11:32 am UTC

where d.transaction_seq in
      ( select case when
                    c.type = 1 then to_number(b.primary_key)
               else
                    d.transaction_seq
               end

to run a correlated subquery for each row that is a three table join..... ouch.  


do you realize how so very slow this will be -- the predicate scares me to *death*.  joining on a case, with betweens -- for every row in the outer table.  wow.


looks like it did a wild rewrite on that nasty cartesian product with semi-join conditions:

ops$tkyte@ORA9IR2> create or replace function my_to_number( p_type in number, p_string in varchar2, p_other in number ) return number
  2  is
  3  begin
  4          dbms_output.put_line( p_type || ' "' || p_string || '"' );
  5          if ( p_type = 1 )
  6          then
  7                  return to_number(p_string);
  8          else
  9                  return p_other;
 10          end if;
 11  exception
 12  when others
 13       then return null;
 14  end;
 15  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from tbl_d d
  2  where d.transaction_seq in
  3        ( select my_to_number( c.type,b.primary_key,d.transaction_seq)
  4          from   tbl_c c,
  5                 tbl_a a,
  6                 tbl_b b
  7          where  b.entry_ts between c.last_start_ts and c.current_start_ts
  8          and    a.schema_name = 'TEST'
  9          and    a.table_id    = (case when c.type = 1 then b.table_id else a.table_id end)
 10          and    a.table_name  = 'PRIMARY_KEY')
 11  /
 
TRANSACTION_SEQ
---------------
          61916
 
ops$tkyte@ORA9IR2> exec null
1 "1|1"
1 "61916"
1 "1|1"
1 "61916"
1 "61976"
 
PL/SQL procedure successfully completed.


so, it was selecting before all of the predicates were evaluated.  

my_to_number might come in handy therefore -- but remove the dbms_output 

case when

daxu, March 02, 2005 - 12:51 pm UTC

Tom,
Thank you very much for the quick response as you always be, actually, I am a big fan of you.

I still have questions about this query, why the query complains about the '1|1' value even though in the prediction, the query has "and a.table_name = 'PRIMARY_KEY'" to exclude that row?

Also, when you say "do you realize how so very slow this will be -- the predicate scares me to
*death*. joining on a case, with betweens -- for every row in the outer table.", do you mean for any sub select query, we need to keep it as less tables involved as we can even though the return rows from the subquery is pretty small?

Thanks,





Tom Kyte
March 02, 2005 - 12:54 pm UTC

you have a correlated subquery -- a really complex one. It uses inputs from the "outer query", it is not run once but conceptually once per each row in the outer query -- in your case, I'll say it'll be run once per row)

As I said, the query must be rewritten in such away that the selection is taking place at a different point -- before the entire predicate was evaluated. This sort of stuff happens every time we stuff numbers into strings instead of into numbers.

Thanks

daxu, March 02, 2005 - 1:46 pm UTC


Boolean Datatype and SQL

Yuan, April 06, 2005 - 1:23 pm UTC

I found mention that Boolean is a standard datatype in SQL99. Do you know of any plans to build that into Oracle's RDBMS?

Tom Kyte
April 06, 2005 - 2:17 pm UTC

i am not personally aware of any (which means nothing one way or the other)



Case and Decode and nulls

John Gilmore, June 03, 2005 - 4:34 am UTC

Hi Tom,

In your response to Akhilesh's question back in September 2001 you stated that "everything decode can do, CASE can".

I'm having problems trying to get a simple case expression to match either nulls or empty strings, something that decode seems to have no problem doing. Of course, this can be done with a searched case expression however I would have thought it possible with a simple case expression too as I'd always considered these to be semantically equivalent to a decode.

In the examples below the decode matches on the null or '' but the case does not. Do you have any comments on this?

--
drop table case_decode_test;
--
create table case_decode_test (
col varchar2 (2));
--
insert into case_decode_test values (
'A');
insert into case_decode_test values (
'B');
insert into case_decode_test values (
null);
--
select
col "Raw",
decode (col,
'A', 'It''s A',
'B', 'It''s B',
'', 'It''s nothing',
'It''s an error') "Decoded"
from case_decode_test;
--
select
col "Raw",
decode (col,
'A', 'It''s A',
'B', 'It''s B',
null, 'It''s nothing',
'It''s an error') "Decoded"
from case_decode_test;
--
select
col "Raw",
case col
when 'A' then 'It''s A'
when 'B' then 'It''s B'
when '' then 'It''s nothing'
else 'It''s an error'
end "Cased"
from case_decode_test;
--
select
col "Raw",
case col
when 'A' then 'It''s A'
when 'B' then 'It''s B'
when null then 'It''s nothing'
else 'It''s an error'
end "Cased"
from case_decode_test;
--


Tom Kyte
June 03, 2005 - 7:28 am UTC

a searched case is just equals.

you would have to use a where clause

case when col = 'a'
when col = 'b'
when col is null

Alex, June 03, 2005 - 2:47 pm UTC

Tom,

I have an usual request that I need help with. I have been asked to add a predicate in a query to check for a value for a column, if that's not found check column 2 for a value, if neither are found return all the results for the query as if the predicate was never there. This is part of a stored procedure, so "pID' will represent an incoming parameter:

cursor c1 is
select *
from table1 t1, table2 t2
where t1.id = t2.id
and t2.id = decode(pID, nvl(t1.id, ' '), decode(pID, nvl(t1.id2, ' '), t1.id2);

So if we don't find a match in either of the t1.id, t1.id2 columns, I would like the query to return as if

select *
from table1 t1, table2 t2
where t1.id = t2.id

Suggestions for this?

Tom Kyte
June 03, 2005 - 5:18 pm UTC

you know we have to inspect the entire result set then before you can get the first row back.

that decode isn't "right" is it? I mean, it doesn't seem to do what you said I don't think.

What I'm hearing is:

return me all rows where t1.id = pID
IF that set is empty
then return me all rows where t1.id = pID
IF that set is empty
then return me the ENTIRE SET


is that right?

Alex, June 06, 2005 - 9:24 am UTC

I have revised my code to include everything. I noticed that the decode didn't look quite right also. Your comments on this approach:


cursor c1 is
select *
from table1 t1, table2 t2
where t1.id = t2.id
and t2.id = decode(t1.id, pID, pID, decode(t1.id2, pID, pID, null)

union

select *
from table1 t1, table2 t2
where t1.id = t2.id
and not exists (
select null from table1 t1, table2 t2
where t1.id = t2.id
and t2.id = decode(t1.id, pID, pID, decode(t1.id2, pID, pID, null));

Your pseudo code was correct except you used t1.id instead of t2.id. This seems to be working...I think. I'll be interested to hear your thoughts, thanks a lot.







Tom Kyte
June 06, 2005 - 9:34 am UTC

I'm not understanding the decode.

can you phrase as an if then else the logic YOU need.

then I can respond...

Alex, June 06, 2005 - 10:03 am UTC

It would go like this:

Look for the t1.id to match on first
IF that's not there, look for t1.id2
IF that's not there either, return everything as if the predicate wasn't there.

Tom Kyte
June 06, 2005 - 11:07 am UTC

select t1.object_name, t2.owner
from t1, t2
where t1.id = t2.id
and t1.id = :pid
union all
select t1.object_name, t2.owner
from t1, t2
where t1.id = t2.id
and t1.id2 = :pid
and (select 1 from t1 where id = :pid and rownum = 1) is null
union all
select t1.object_name, t2.owner
from t1, t2
where t1.id = t2.id
and (select 1 from t1 where id = :pid and rownum = 1) is null
and (select 1 from t1 where id2= :pid and rownum = 1) is null
/


Now, if I make t1 and t2 as follows:


create table t1 as select a.*, object_id id, -data_object_id id2 from all_objects a;
create index t1_idx1 on t1(id);
create index t1_idx2 on t1(id2);
exec dbms_stats.gather_table_stats( user, 'T1', cascade=>true );

create table t2 as select a.*, object_id id, -data_object_id id2 from all_objects a;
create index t2_idx on t2(id);
exec dbms_stats.gather_table_stats( user, 'T2', cascade=>true );


and run the query 3 times, once with 42 (first query returns answer), -42 (second query returns answer) and 0 (third part returns data), tkprof shows this:



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 13 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 13 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 295

Rows Row Source Operation
------- ---------------------------------------------------
1 UNION-ALL (cr=13 r=0 w=0 time=231 us)
1 MERGE JOIN CARTESIAN (cr=7 r=0 w=0 time=138 us)
1 TABLE ACCESS BY INDEX ROWID T2 (cr=4 r=0 w=0 time=62 us)
1 INDEX RANGE SCAN T2_IDX (cr=3 r=0 w=0 time=46 us)(object id 39600)
1 BUFFER SORT (cr=3 r=0 w=0 time=51 us)
1 TABLE ACCESS BY INDEX ROWID T1 (cr=3 r=0 w=0 time=19 us)
1 INDEX RANGE SCAN T1_IDX1 (cr=2 r=0 w=0 time=12 us)(object id 39597)
0 FILTER (cr=0 r=0 w=0 time=1 us)
0 TABLE ACCESS BY INDEX ROWID T2
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T1
0 INDEX RANGE SCAN T1_IDX2 (object id 39598)
0 INDEX RANGE SCAN T2_IDX (object id 39600)
1 COUNT STOPKEY (cr=2 r=0 w=0 time=22 us)
1 INDEX RANGE SCAN T1_IDX1 (cr=2 r=0 w=0 time=17 us)(object id 39597)
0 FILTER (cr=0 r=0 w=0 time=1 us)
0 HASH JOIN
0 TABLE ACCESS FULL T2
0 TABLE ACCESS FULL T1
0 COUNT STOPKEY (cr=2 r=0 w=0 time=9 us)
0 INDEX RANGE SCAN T1_IDX2 (cr=2 r=0 w=0 time=8 us)(object id 39598)
1 COUNT STOPKEY (cr=2 r=0 w=0 time=9 us)
1 INDEX RANGE SCAN T1_IDX1 (cr=2 r=0 w=0 time=5 us)(object id 39597)

it effectively use the scalar subquery probes to decide whether to run a query, or not


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 14 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 295

Rows Row Source Operation
------- ---------------------------------------------------
1 UNION-ALL (cr=14 r=0 w=0 time=190 us)
0 MERGE JOIN CARTESIAN (cr=2 r=0 w=0 time=34 us)
0 TABLE ACCESS BY INDEX ROWID T2 (cr=2 r=0 w=0 time=31 us)
0 INDEX RANGE SCAN T2_IDX (cr=2 r=0 w=0 time=28 us)(object id 39600)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID T1
0 INDEX RANGE SCAN T1_IDX1 (object id 39597)
1 FILTER (cr=8 r=0 w=0 time=84 us)
1 TABLE ACCESS BY INDEX ROWID T2 (cr=8 r=0 w=0 time=75 us)
3 NESTED LOOPS (cr=7 r=0 w=0 time=61 us)
1 TABLE ACCESS BY INDEX ROWID T1 (cr=4 r=0 w=0 time=27 us)
1 INDEX RANGE SCAN T1_IDX2 (cr=3 r=0 w=0 time=15 us)(object id 39598)
1 INDEX RANGE SCAN T2_IDX (cr=3 r=0 w=0 time=23 us)(object id 39600)
0 COUNT STOPKEY (cr=2 r=0 w=0 time=9 us)
0 INDEX RANGE SCAN T1_IDX1 (cr=2 r=0 w=0 time=7 us)(object id 39597)
0 FILTER (cr=0 r=0 w=0 time=0 us)
0 HASH JOIN
0 TABLE ACCESS FULL T2
0 TABLE ACCESS FULL T1
1 COUNT STOPKEY (cr=2 r=0 w=0 time=19 us)
1 INDEX RANGE SCAN T1_IDX2 (cr=2 r=0 w=0 time=14 us)(object id 39598)
0 COUNT STOPKEY
0 INDEX RANGE SCAN T1_IDX1 (object id 39597)

and did it again....

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1867 0.45 0.48 0 2660 0 27978
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1869 0.45 0.48 0 2660 0 27978



Rows Row Source Operation
------- ---------------------------------------------------
27978 UNION-ALL (cr=2660 r=0 w=0 time=389252 us)
0 MERGE JOIN CARTESIAN (cr=2 r=0 w=0 time=32 us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(39599) (cr=2 r=0 w=0 time=30 us)
0 INDEX RANGE SCAN OBJ#(39600) (cr=2 r=0 w=0 time=28 us)(object id 39600)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID OBJ#(39596)
0 INDEX RANGE SCAN OBJ#(39597) (object id 39597)
0 FILTER (cr=2 r=0 w=0 time=18 us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(39599) (cr=2 r=0 w=0 time=15 us)
1 NESTED LOOPS (cr=2 r=0 w=0 time=12 us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(39596) (cr=2 r=0 w=0 time=8 us)
0 INDEX RANGE SCAN OBJ#(39598) (cr=2 r=0 w=0 time=7 us)(object id 39598)
0 INDEX RANGE SCAN OBJ#(39600) (object id 39600)
0 COUNT STOPKEY (cr=2 r=0 w=0 time=10 us)
0 INDEX RANGE SCAN OBJ#(39597) (cr=2 r=0 w=0 time=8 us)(object id 39597)
27978 FILTER (cr=2650 r=0 w=0 time=308340 us)
27978 HASH JOIN (cr=2650 r=0 w=0 time=267675 us)
27981 TABLE ACCESS FULL OBJ#(39599) (cr=406 r=0 w=0 time=25084 us)
27978 TABLE ACCESS FULL OBJ#(39596) (cr=2244 r=0 w=0 time=44574 us)
0 COUNT STOPKEY (cr=2 r=0 w=0 time=5 us)
0 INDEX RANGE SCAN OBJ#(39598) (cr=2 r=0 w=0 time=4 us)(object id 39598)
0 COUNT STOPKEY (cr=2 r=0 w=0 time=5 us)
0 INDEX RANGE SCAN OBJ#(39597) (cr=2 r=0 w=0 time=4 us)(object id 39597)

and again...



Alex, June 06, 2005 - 11:50 am UTC

I can't believe you did all that so quickly. I didn't even know you can do

and (query) is null

And those three queries did so little work too, pretty genius It'll be a massive cursor, but who cares if it's fast right? Thanks a ton.

A reader, June 10, 2005 - 12:52 pm UTC

Hi Tom,

Table1 Table2
------ ------
col1 (pri.key) col2(for.key)
col3(for.key)


col2, col3 are refering to Table1.col1.

col2 will always have data, where as col3 is optional.

I have to join these two tables and pull matching data.

if I do an equi join (with col2 and col3) and if col3 is NUll, no records would be fetched.

I wrote the following query, but would like to know if there is a better approach:


select * from
(
select col...
from table1 a, table2 b
where a.col1=b.col2
union all
select col...
from table1 a, table2 b
where a.col1=b.col3
)



Please help!

Tom Kyte
June 10, 2005 - 3:47 pm UTC

select ...
from table1 a, table1 b, table2 c
where c.col2 = a.col1
and c.col3(+) = b.col1;


or, if you just need a column or so from table1


select ...., (select X from table1 where col1 = X.COL3 )
from table1 a, table2 X
where a.col1 = X.col2
/



how can I re-rewrite this using case?

A reader, June 13, 2005 - 12:00 pm UTC

decode ((SELECT ih.in_date
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id > i.container_id
AND ihh.container = i.container)), NULL,
(SELECT ic.in_date
FROM inv_containers ic
WHERE i.container=ic.container )) IN_DATE,

Tom Kyte
June 13, 2005 - 12:14 pm UTC

decode( x, a, b )

is

case when x = a then b end


but if you want to know how to make that "better performing, do less work", what you need to do is state in text "what the goal is"

Quote of the day Everybody Knows:
If you don't understand it, you can't program it.
If you didn't measure it, you didn't do it.

seems you want to find the in_date for the given container such that the container_id is the minimum container_id that is larger then the "next" one.

almost looks like an analytic function (LEAD and LAG -- look forwards a row, look back a row) to me.

the second part of that -- the second subquery implies lots of hidden knowledge (or a bug :)

you are saying

if there isn't a in_date for this container such that the container_id > my container id -- return the SOLE in_date for this container from the inv_containers table.

but if this query was driven from the inv_containers table (again, we have tiny snippet), we might not even need to do that.

you hit it right in the nail!!

A reader, June 13, 2005 - 12:22 pm UTC

What I want to accomplish is what you said. 'seems you want to find the in_date for the given container such that the
container_id is the minimum container_id that is larger then the "next" one'.

How can I accomplish that?

Tom Kyte
June 13, 2005 - 12:35 pm UTC

sigh, tried to hint that "with a snippet, really hard to say"....

I've no clue what tables are there.

look at LEAD() over () the analytic function.

without snippet....

A reader, June 13, 2005 - 2:54 pm UTC

Here is the whole query ;)

SELECT *
FROM (
SELECT ssl_user_code,
ssl_user_code ssl,
i.container,
out_date,
cl.code LENGTH_CODE,
out_trucker_code,
decode ((SELECT ih.in_date
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id > i.container_id
AND ihh.container = i.container)), NULL,
(SELECT ic.in_date
FROM inv_containers ic
WHERE i.container=ic.container )) IN_DATE,
decode ((SELECT ih.in_trucker_code
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id > i.container_id
AND ihh.container = i.container)), NULL,
(SELECT ic.in_trucker_code
FROM inv_containers ic
WHERE ic.container = i.container)) IN_TRUCKER_CODE,
decode ((SELECT ih.chassis
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id > i.container_id
AND ihh.container = i.container)), NULL,
(SELECT ic.chassis
FROM inv_containers ic
WHERE ic.container = i.container)) IN_CHASSIS
FROM HIS_containers i,
container_masters cm,
tml_container_lhts clht,
tml_container_lengths cl
WHERE i.chassis IS NULL AND
i.out_mode = 'T'
AND
cm.container = i.container AND
cm.lht_code = clht.code AND
clht.length_code = cl.code AND
decode ((SELECT ih.container_id
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id > i.container_id
AND ihh.container = i.container)), NULL,
(SELECT ic.container_id
FROM inv_containers ic
WHERE ic.container = i.container)) IS NOT NULL
)
WHERE
SSL_USER_CODE = 'ACL'


Tom Kyte
June 13, 2005 - 8:52 pm UTC

yes, you can use lead

....
from (select ....,
lead(in_date)
over (partition by container order by container_id) next_in_date
his_containers ) i,
......


Using FIRST aggregate function

Jay, June 13, 2005 - 3:02 pm UTC

This query

SELECT ih.in_date
FROM his_containers ih
WHERE ih.container = i.container
AND ih.container_id > i.container_id
AND ih.container_id = (SELECT MIN(ihh.container_id)
FROM his_containers ihh
WHERE ihh.container_id > i.container_id
AND ihh.container = i.container)

can be re-written as

SELECT MIN(ih.in_date) KEEP (DENSE_RANK FIRST ORDER BY ih.container_id)
-- MIN or MAX does not matter if only one row is expected
FROM his_containers ih
WHERE ih.container_id > i.container_id
AND ih.container = i.container

In this re-written query table his_containers is accessed only once.

Tom is there any drawback in using this feature (i.e. <Aggr> KEEP (DENSE_RANK FIRST/LAST ORDER BY .. ) ?

Tom Kyte
June 13, 2005 - 8:56 pm UTC

well, in this case -- I'd want to move it UP a level so we don't have to run the scalar subqueries over and over and over ....

see above.

still not working

A reader, June 14, 2005 - 4:38 pm UTC

Tom,

In_date is coming out bigger when I need it to be
smaller. It seems like lag/lead is not working.
Any ideas?? in_date is when they checkin and
out is when they come back

select ssl_user_code,in_date, out_date, container,in_trucker_code,out_trucker_code,chassis --cl.code
from (
select ssl_user_code,
lag(out_date) over (partition by ssl_user_code order by out_date) lead_container,
lead(in_date) over (partition by ssl_user_code order by in_date) lag_container,
container_id,
out_date,
container,
in_trucker_code,
out_mode,
out_trucker_code,
chassis,
-- cl.code,
lag (out_date) over (partition by ssl_user_code order by out_date)next_record,
lead (in_date) over (partition by ssl_user_code order by in_date) in_date
from his_containers I
)
where
ssl_user_code = 'ACL'



Tom Kyte
June 15, 2005 - 3:11 am UTC

order by DESC if you want "smaller" or use lag when you use lead/vice versa.


it does what you ask it to do, partition by COLUMNS order by OTHER_COLUMNS and then look back or fowards a row

this is how it looks

A reader, June 14, 2005 - 5:10 pm UTC

SSL_USER_CODE IN_DATE OUT_DATE CONTAINER IN_TRUCKER_CODE
ACL 2/26/2005 6:01:06 PM 3/1/2005 8:14:24 AM GCNU462766 C4561
ACL 2/26/2005 6:00:52 PM 3/1/2005 8:17:27 AM GCNU461154 C4561
ACL 2/26/2005 6:00:27 PM 3/1/2005 8:28:45 AM ACLU217790
ACL 2/26/2005 4:54:21 PM 3/1/2005 8:47:05 AM TTNU935542 L0003
ACL 2/26/2005 8:14:28 PM 3/1/2005 8:49:19 AM ACLU962098 C4561


I am trying to make it to look like this

A reader, June 14, 2005 - 5:12 pm UTC

SSL_USER_CODE SSL CONTAINER OUT_DATE LENGTH_CODE OUT_TRUCK IN_DATE
ACL ACL ACLU217348 2/4/2005 4:36:57 PM 4 I1305 3/7/2005 9:47:41 AM I1305
ACL ACL GCNU402638 1/18/2005 12:40:06 PM 4 I1305 3/1/2005 9:11:00 AM I1305
ACL ACL RATT000024 3/31/2005 9:26:20 AM 4 M0995 3/31/2005 9:46:16 AM M0995

OK

Ram, August 09, 2005 - 1:47 pm UTC

Hi Tom,
Can this query be put using Decode??

SQL> select comm,case when comm is null then 0 else comm end as cm from emp
  2  / 

Tom Kyte
August 09, 2005 - 2:39 pm UTC

yes

decode(comm,null,0,comm)



Alex, September 07, 2005 - 11:01 am UTC

Hi Tom,

I have kind of a stupid question, but my curiosity is getting the better of me.  I ran across a decode in one of our stored procedures that looked similar to the one you just suggested to Ram, decode(comm,null,0,comm).
When I first saw this, I thought "that's stupid, that's always going to return 'comm'" (Of course once I saw your reply I knew it was no longer stupid and it was I in fact).

So I ran a quick test:

SQL> SELECT DECODE (NULL, NULL, 'Its null', 'Not null')
  2    FROM DUAL;

DECODE(N
--------
Its null

So decode must do a different comparison than a predicate like

select * from t where dept = null

That will never return anything.  Would you mind enlightening me, thank you.
 

Tom Kyte
September 07, 2005 - 1:45 pm UTC

that is correct, null matches null in decode.

How do you say not null in this expression?

A reader, September 09, 2005 - 2:10 pm UTC

select case null when null then 'hello' when not null then 'goodbye' end from dual;


I know you can write this as:

select case when null is null then 'hello' when null is not null then 'goodbye' end from dual;

What am I missing from the first one?

and why does the syntax seem to change from the first to the second? In that in the first one we say "case bla when null" and in the second one we say "case when bla IS null"?

Thanks !

Tom Kyte
September 09, 2005 - 2:30 pm UTC

the searched case (first example) uses equality, null is not equal to null (nor is it not equal)

the non-searched case has a predicate that is evaluated for each when clause in turn. using the "is null" operator, it did "hit"

more...

A reader, September 09, 2005 - 2:58 pm UTC

But the first command actually fails with a syntax problem.
If you remove the not null portion of it though, it works.

Are you saying you can use null, but not "not null" in the first example then?

Try the first statement both with not null and without it to see what I mean.

Tom Kyte
September 09, 2005 - 3:12 pm UTC

you can say:

case X
when NULL /* because null is a "value" here */
then Y


NOT NULL is more like an operator - and a "searched case" is not expecting operators, it is expecting values.

thanks much

A reader, September 09, 2005 - 3:29 pm UTC


A reader, September 28, 2005 - 5:15 am UTC

Dear Tom,
Could you please help me with this query? It is about using a Case expression in the 'where' clause of an Sql statement.

For eg:
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP1 PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
MDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7698,to_date('17-12-1980','dd-mm-yyyy'), to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,NULL,to_date('18-12-1980','dd-mm-yyyy'),1600,300,10);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('19-12-1980','dd-mm-yyyy'),to_date('19-12-1980','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7698,NULL,to_date('20-12-1980','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('21-12-1980','dd-mm-yyyy'),to_date('21-12-1980','dd-mm-yyyy'),1250,1400,30);


SELECT
JOB, SUM(SAL)
FROM
EMP
WHERE
(CASE
WHEN HIREDATE IS NOT NULL THEN
HIREDATE BETWEEN to_date('17-12-1980','dd-mm-yyyy') AND to_date('19-12-1980','dd-mm-yyyy')
ELSE
(MDATE BETWEEN to_date('17-12-1980','dd-mm-yyyy') AND to_date('19-12-1980','dd-mm-yyyy')) AND DEPTNO = 30
END
)
GROUP BY JOB
/


I would like to sum the salary. The where clause should be if the hiredate is not null use hiredate else use mdate and deptno in the where clause

This gives error ORA-00905: missing keyword

Thanking you in advance
Neelz

Tom Kyte
September 28, 2005 - 9:56 am UTC

(case when hiredate is not null
then case when hiredate between A and B
then 1
end
when mdate between A and B and deptno = 30
then 1
end) = 1


use case to return 1 when "true" and null otherwise....

Von, September 28, 2005 - 10:02 am UTC

Is it possible to have > or < conditions in decode

i.e.,
case when a>5 then 1
else 0

how can i write the above statement using decode?

Tom Kyte
September 28, 2005 - 10:38 am UTC

you can use sign

decode( sign(a-5), 1 /* sign=1, a-5 >0 */, 1, 0 )

ops$tkyte@ORA10G> select rownum, decode( sign(rownum-5), 1, 1, 0 )
  2  from all_users where rownum <10;
 
    ROWNUM DECODE(SIGN(ROWNUM-5),1,1,0)
---------- ----------------------------
         1                            0
         2                            0
         3                            0
         4                            0
         5                            0
         6                            1
         7                            1
         8                            1
         9                            1
 
9 rows selected.
 
 

Pardon me for jumping in ...

Doug Burns, September 28, 2005 - 10:24 am UTC

... but you might find this useful.

</code> http://doug.burns.tripod.com/decode6.html <code>

Tom, just delete this if you'd rather not have the link in here.

Cheers,

Doug

Tom Kyte
September 28, 2005 - 10:40 am UTC

Doug - never a problem, this is an "open" system ;)

Von

A reader, September 28, 2005 - 12:31 pm UTC

Thanks guys

Decode Strange Situations

Mohamed Abd El Mawla, October 03, 2005 - 7:07 am UTC

Hi tom

We have a strange situation when we tried to use the decode function using Oracle 10g Database:

connect scott/tiger
Step(1)

CREATE OR REPLACE PACKAGE C_PKG AS
PROCEDURE SET_V(V_NUMBER NUMBER) ;
FUNCTION GET_V RETURN NUMBER;
END C_PKG;
/

STEP (2)

CREATE OR REPLACE PACKAGE BODY C_PKG AS
V_CONSTANT NUMBER;
PROCEDURE SET_V(V_NUMBER NUMBER) IS
BEGIN
V_CONSTANT := V_NUMBER;
END;
FUNCTION GET_V RETURN NUMBER IS
BEGIN
RETURN V_CONSTANT;
END;
END C_PKG;
/

STEP(3)

BEGIN
C_PKG.SET_V(1);
END;
/

NOW THE PACKAGE VARIABLE IS SUPPOSED TO BE 1

STEP(4)

SELECT *
FROM EMP A
WHERE NOT EXISTS (SELECT 1
FROM DEPT
WHERE DECODE (C_PKG.GET_V,1,0,1) = 1);
WE EXPECT ALL ROWS TO BE RETURNED FROM TABLE EMP
BUT THE ACTUAL RESULT IS NO ROWS RETURNED !!!!!!!!

STEP(5)

SELECT *
FROM EMP A
WHERE NOT EXISTS (SELECT 1
FROM DEPT
WHERE DECODE (1,1,0,1) = 1);
ALL ROWS FROM TABLE EMP WERE RETUREND (THE EXPECTED RESULT)

Notice : when we tried to use the two queries using Oracle 9i , it performs well .

Is it a bug in Oracle 10g or there is any missed information ??

Thanks for your considerations



Tom Kyte
October 03, 2005 - 7:48 am UTC

I got this result in 9206, 10.1.0.4, 10.2.0.1, are you sure you didn't accidently miss the set call? are the plans different or the same? can we see a cut and paste from both with autotrace on?


scott@ORA10GR2> CREATE OR REPLACE PACKAGE C_PKG AS
2 PROCEDURE SET_V(V_NUMBER NUMBER) ;
3 FUNCTION GET_V RETURN NUMBER;
4 END C_PKG;
5 /

Package created.

scott@ORA10GR2>
scott@ORA10GR2> CREATE OR REPLACE PACKAGE BODY C_PKG AS
2 V_CONSTANT NUMBER;
3 PROCEDURE SET_V(V_NUMBER NUMBER) IS
4 BEGIN
5 V_CONSTANT := V_NUMBER;
6 END;
7 FUNCTION GET_V RETURN NUMBER IS
8 BEGIN
9 RETURN V_CONSTANT;
10 END;
11 END C_PKG;
12 /

Package body created.

scott@ORA10GR2>
scott@ORA10GR2> BEGIN
2 C_PKG.SET_V(1);
3 END;
4 /

PL/SQL procedure successfully completed.

scott@ORA10GR2> SELECT empno, ename
2 FROM EMP A
3 WHERE NOT EXISTS (SELECT 1
4 FROM DEPT
5 WHERE DECODE (C_PKG.GET_V,1,0,1) = 1);

EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.



Decode Case

Mohamed Abd El Mawla, October 17, 2005 - 1:03 pm UTC

Hi tom

Sorry for late of this review cause i thought that i'll recieve a confirmation mail as soon as you reply.

Here is the output of the TKRPOF resulted from SQL_TRACE = TRUE:

TKPROF: Release 9.2.0.1.0 - Production on &#1575;&#1604;&#1571;&#1585;&#1576;&#1593;&#1575;&#1569; 10 5 14:26:57 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: D:\oracle\product\10.1.0\admin\iss\udump\iss_ora_1528.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for
update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

ALTER SESSION SET SQL_TRACE = TRUE


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 0 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 1 0.00 0.00 0 0 0
0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 142
********************************************************************************

BEGIN
C_PKG.SET_V(1);
END;

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.46 0.46 3 96 0
0
Execute 1 0.31 0.31 0 51 0
1
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 0.78 0.77 3 147 0
1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 142
********************************************************************************

SELECT *
FROM EMP A
WHERE NOT EXISTS (SELECT 1
FROM DEPT
WHERE DECODE (C_PKG.GET_V,1,0,1) = 1)

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.42 0.40 0 0 0
0
Execute 1 0.00 0.31 0 2 0
0
Fetch 1 0.00 0.54 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 0.42 1.26 0 2 0
0

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 142

Rows Row Source Operation
------- ---------------------------------------------------
0 PX COORDINATOR (cr=2 pr=0 pw=0 time=859722 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)(Orphan Entry)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL EMP (cr=0 pr=0 pw=0 time=0 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX FULL SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us)(object id
43772)

********************************************************************************

SELECT *
FROM EMP A
WHERE NOT EXISTS (SELECT 1
FROM DEPT
WHERE DECODE (1,1,0,1) = 1)

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.03 0.02 0 0 0
0
Execute 1 0.01 0.14 0 2 0
0
Fetch 2 0.00 0.11 0 0 0
15
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.04 0.28 0 2 0
15

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 142

Rows Row Source Operation
------- ---------------------------------------------------
15 PX COORDINATOR (cr=2 pr=0 pw=0 time=150653 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)(Orphan Entry)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL EMP (cr=0 pr=0 pw=0 time=0 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX FULL SCAN PK_DEPT (cr=0 pr=0 pw=0 time=0 us)(object id
43772)

********************************************************************************

select a.spid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid')

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 1.54 6.35 9 478 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 0 0
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 1.54 6.35 9 478 0
1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 142



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 4 2.46 7.25 12 574 0
0
Execute 5 0.32 0.76 0 55 0
1
Fetch 5 0.00 0.66 0 0 0
16
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 14 2.79 8.68 12 629 0
17

Misses in library cache during parse: 4
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 70 0.68 2.58 0 29 0
0
Execute 211 0.53 1.29 0 2 4
2
Fetch 381 0.07 0.33 15 734 2
565
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 662 1.29 4.21 15 765 6
567

Misses in library cache during parse: 16
Misses in library cache during execute: 13

5 user SQL statements in session.
70 internal SQL statements in session.
75 SQL statements in session.
********************************************************************************
Trace file: D:\oracle\product\10.1.0\admin\iss\udump\iss_ora_1528.trc
Trace file compatibility: 9.00.01
Sort options: default

2 sessions in tracefile.
5 user SQL statements in trace file.
70 internal SQL statements in trace file.
75 SQL statements in trace file.
41 unique SQL statements in trace file.
1438 lines in trace file.




Tom Kyte
October 17, 2005 - 1:14 pm UTC

i'll have to refer you to support for this, I'm not reproducing that - it would likely be a function of your non-default optimizer related parameter settings (of which I don't have any...)


I don't email when following up to a review - not unless I do it "by hand" for some reason.

Why does nvl always execute the second expression

putchi, November 03, 2005 - 11:39 am UTC

Hi Tom!

I have a select that I want to look like this

select nvl((select a from small_table),(select a from heavy_table))
      ,b
from another_table

If I get anything from the small_table I am happy with that, if not I have to use the heavy_table (which in fact is more than one table) but the nvl function always execute both expressions, also shown by the example below. So I end up in the following statement where I only have to use the heavy_table when really needed but I have to acess the small_table twice.

select case when (select a from small_table) is not null then
   (select a from small_table)
else
  (select a from heavy_table)
end
      ,b
from another_table

Any suggeestions how to avoid two accesses of the small_table.

SQL> set autotrace traceonly explain statistics
SQL> select nvl(1,(select count(*) from all_objects)) from dual;
Statistik
----------------------------------------------------------
          7  recursive calls
          0  db block gets
     756335  consistent gets
          0  physical reads
          0  redo size
        224  bytes sent via SQL*Net to client
        254  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> l
  1* select nvl(1,(select count(*) from all_objects)) from dual
SQL> c/1/null/
  1* select nvl(null,(select count(*) from all_objects)) from dual
SQL> /
Statistik
----------------------------------------------------------
          7  recursive calls
          0  db block gets
     756335  consistent gets
          0  physical reads
          0  redo size
        230  bytes sent via SQL*Net to client
        254  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

Tom Kyte
November 04, 2005 - 2:43 am UTC

ops$tkyte@ORA10GR2> create or replace function f( x in varchar2 ) return varchar2
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv( 'client_info') + 1 );
  5          return x;
  6  end;
  7  /

Function created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select count(case when mod(user_id,2)=0 then 1 end) even,
  2         count(case when mod(user_id,2)=1 then 1 end) odd
  3    from all_users;

      EVEN        ODD
---------- ----------
        13         16

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select case when c1 is null
  2              then (select 'hello' from dual)
  3                          else c1
  4                  end data
  5    from (select (select f(dummy) from dual where mod(user_id,2) = 0 ) c1,
  6                 username
  7                    from all_users
  8             )
  9  /

DATA
-------------------------------------------------------------------------------
hello
X
hello
X
hello
X
hello
X
hello
X
X
hello
X
hello
X
hello
hello
hello
X
X
X
hello
X
hello
hello
hello
hello
hello
X

29 rows selected.

ops$tkyte@ORA10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
-------------------------------------------------------------------------------
13
 

CASE and Inline View

Su Baba, November 03, 2005 - 4:15 pm UTC

I executed the following two SQL statements. The first one succeeded, but the second SQL failed. The second SQL has an inline view within the CASE statement. Is there any way to pass a column (e.g. p_id) from the outer FROM statement into the inline view?  thanks.


SQL> SELECT CASE WHEN result = 0 THEN (
  2                   SELECT 'x'
  3                   FROM   dual
  4                   WHERE  p_id = 1
  5              )
  6              ELSE 'y'
  7         END
  8  FROM  (SELECT 0 result, 1 p_id FROM dual);

C
-
x

SQL> 
SQL> 
SQL> SELECT CASE WHEN result = 0 THEN (
  2                   SELECT *
  3                   FROM   (SELECT 'x'
  4                           FROM   dual
  5                           WHERE  p_id = 1)
  6              )
  7              ELSE 'y'
  8         END
  9  FROM  (SELECT 0 result, 1 p_id FROM dual);
                         WHERE  p_id = 1)
                                *
ERROR at line 5:
ORA-00904: "P_ID": invalid identifier

 

Tom Kyte
November 04, 2005 - 2:52 am UTC

correlated variables can only be pushed "one layer down", p_id is available only ONE layer down

(p_id - that is a convention for plsql variable names, I would avoid using the p_ for column names like that)

?? Why does nvl always execute the second expression

putchi, November 07, 2005 - 5:35 am UTC

Hi Tom!

I couldn't really follow your answer to my question with title

"Why does nvl always execute the second expression" on Nov 3

Cut-and-Paste error ??


Tom Kyte
November 07, 2005 - 9:00 am UTC

No cut and paste, you asked "Any suggeestions how to avoid two accesses of the small_table"

and I showed how to use scalar subqueries to achieve your goal?

3000 CASE?

A reader, November 28, 2005 - 4:18 am UTC

Hi

I have a customer table with 100000 rows and a Category table with 3000 rows. I need to assign a category to a customer depending on its customer_id. For example (the range is fixed, i.e every 30)

if customer_id between 1 and 30 then
category 1
else if customer_id between 31 and 60 then
category 2
else
category 0

I was thinking to use CASE however looking the data I need like 3300 CASE statements...!!!

Is there any other way to do it?



and so on

Tom Kyte
November 28, 2005 - 7:38 am UTC

update t set category = ceil( customer_id/30 )



Case with update statement

sujit, March 03, 2006 - 5:01 pm UTC

I need to update a column(status) in table A based on year_of_make in another table. See below,

I am getting the error,

case when a.year in ('2004','2005') then 2
*
ERROR at line 5:
ORA-00905: missing keyword

Any help will be greatly appreciated.

update block
set status_fk =
( select
(case when a.year in ('1991', '1992', '1993', '1994', '1995', '1996') then 1
case when a.year in ('2004','2005') then 2
end
)
from block
where block_pk in
(
select a.block_fk ---this select generates multiple records, about 4 million
from manufacturer a, product b
where a.product_fk = b.product_pk
)
)


Tom Kyte
March 03, 2006 - 6:01 pm UTC

well, when I run it I get even worse stuff.

given that I'm not a sql compiler, perhaps a complete working example with tables and such to play with would help me?

Case with update statement (ABOVE)

Sujit, March 03, 2006 - 5:36 pm UTC

new error after fixing CASE statement syntax,

when d.year_of_make in ('2004','2005') then 5
*
ERROR at line 4:
ORA-00904: "D"."MODEL_YEAR": invalid identifier

The table has 14 million rows, I prefer to do both the updates in a single table scan, than separately for different set of make_of_year


Tom Kyte
March 03, 2006 - 8:10 pm UTC

14 million rows or zero rows....

one needs a "test case"


I cannot even get as far as you did for some reason! (i don't have "your tables")

CASE : THEN with pultiple expression

totu, May 16, 2006 - 2:24 am UTC

Dear Tom.

I have table with field of (x int, y int, z int)

Is it possible below with CASE


SELECT

CASE WHEN X IS NULL THEN y * z, y - z
ELSE y + z

Thanks in advance.



Tom Kyte
May 16, 2006 - 7:00 am UTC

no, that doesn't even make sense - how can a function return "two columns once" and 1 column later?


you can

create type mytype as object ( a int, b int )
/

select case when x is null
then mytype(y*z,y-z)
else mytype(y+z,null)
end


But rsultset is not expected one...

A reader, May 16, 2006 - 7:14 am UTC

Tom,
but result is as below for my data:
SQL> select * from t1;

         X          Y          Z
---------- ---------- ----------
         1          2          3
         4          5          6
                    7          8
         9         10         11
                   12         13

select case when x is null
            then mytype(y*z,y-z)
            else mytype(y+z,null)
        end
from t1;

MYTYPE(5, NULL)
MYTYPE(11, NULL)
MYTYPE(56, -1)
MYTYPE(21, NULL)
MYTYPE(156, -1)

But how to "transform" or fetch as below:
5 NULL
11 NULL
56 -1
21 NULL
156 -1

I mean as normal columns.

Thanks in advance. 

Tom Kyte
May 16, 2006 - 8:38 am UTC

use two case statements, that would be the obvious path of least resistance


select case when x is null then ... else ... end,
case when x is null then ... else ... end
from

CASE vc OR in WHERE clause

A reader, June 09, 2006 - 9:20 am UTC

Hi Tom,

I am doing an INSERT SELECT FROM. The records are selected depending on whether they match by name, address 1, address 2, or name, postcode, or name, address 1 etc.
I have coded it like this:
INSERT INTO table_a
(col1
,col2)
,search_type)
SELECT col1
,ea.col2
,CASE
WHEN ea.search_key = adrnam AND
pcd = ea.postcode AND
adrlneone = ea.address_line1 AND
adrlnetwo = ea.address_line2 THEN 1
WHEN adrnam = ea.search_key AND
pcd = ea.postcode THEN 2
WHEN adrnam = ea.search_key AND
adrlneone = ea.address_line1 AND
adrlnetwo = ea.address_line2 THEN 3
ELSE 0
END
FROM tableb ea
,tablec
WHERE 1 = CASE
WHEN ea.search_key = adrnam AND
pcd = ea.postcode AND
adrlneone = ea.address_line1 AND
adrlnetwo = ea.address_line2 THEN 1
WHEN adrnam = ea.search_key AND
pcd = ea.postcode THEN 1
WHEN adrnam = ea.search_key AND
adrlneone = ea.address_line1 AND
adrlnetwo = ea.address_line2 THEN 1
ELSE 0
END
I only want the best match selected, i.e name, address 1 and 2 and postcode is the best one.

Would it be better to use OR rather than CASE? In that case I would have to clean up duplicates afterwards.
It seems to take so long to run with CASE.

Tom Kyte
June 09, 2006 - 12:58 pm UTC

I would try join by search_key (only constant) in all three

select *
from (
select ...,
row_number() over (partition by search_key
order by
case when (this matches) then 1
when (that matches) then 2
when (the other thing matches) then 3
else 4
end ) rn

from tableb ea, tablec c
where ea.search_key = c.adrnam
)
where rn = 1


give it *something* tangible to join on.



CASE vc OR in WHERE clause

A reader, June 09, 2006 - 9:38 am UTC

Hi Tom,

I am doing an INSERT SELECT FROM. The records are selected depending on whether they match by name, address 1, address 2, or name, postcode, or name, address 1 etc.
I have coded it like this:
INSERT INTO table_a
(col1
,col2)
,search_type)
SELECT col1
,ea.col2
,CASE
WHEN ea.search_key = adrnam AND
pcd = ea.postcode AND
adrlneone = ea.address_line1 AND
adrlnetwo = ea.address_line2 THEN 1
WHEN adrnam = ea.search_key AND
pcd = ea.postcode THEN 2
WHEN adrnam = ea.search_key AND
adrlneone = ea.address_line1 AND
adrlnetwo = ea.address_line2 THEN 3
ELSE 0
END
FROM tableb ea
,tablec
WHERE 1 = CASE
WHEN ea.search_key = adrnam AND
pcd = ea.postcode AND
adrlneone = ea.address_line1 AND
adrlnetwo = ea.address_line2 THEN 1
WHEN adrnam = ea.search_key AND
pcd = ea.postcode THEN 1
WHEN adrnam = ea.search_key AND
adrlneone = ea.address_line1 AND
adrlnetwo = ea.address_line2 THEN 1
ELSE 0
END
I only want the best match selected, i.e name, address 1 and 2 and postcode is the best one.

Would it be better to use OR rather than CASE? In that case I would have to clean up duplicates afterwards.
It seems to take so long to run with CASE.

Alexander the ok, July 11, 2006 - 4:06 pm UTC

Hi Tom,

I'm writing a query with a predicate using a case. I would like to use multiple return values in the case but it won't let me, it fails with a too many values error, it looks like this:

select...
from...
where joins
and col1 in (case when col2 = 'condition' then
(select x,y,z from dual)

Do you know of a different approach to do this better? Thanks very much.

Tom Kyte
July 12, 2006 - 3:18 pm UTC

case returns a scalar value - a single "value".  period.

you might be meaning to code:


where ...joins...
  and case when col2 = 'condition' AND (col1,col3) in (select a, b from t)
           then 1
           when col2 = 'othercond' AND (col1,col3) in (select d, e from t)
           then 1
           else 0
       end = 1

or something similar (not really SURE what you are trying to do since you have "col1 in (select x,y,z from dual)" which doesn't make sense....

ops$tkyte@ORA10GR2> create table t ( a int, b int, c int, d int, e int, col1 int, col2 int, col3 int );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select *
  2    from t
  3    where 1=1
  4      and case when col2 = 1 and (col1,col3) in (select a,b from t)
  5                   then 1
  6                           when col2 = 2 and (col1,col3) in (select d, e from t)
  7                           then 1
  8                           else 0
  9                   end = 1
 10  /

no rows selected

      
             

Alexander the ok, July 12, 2006 - 3:55 pm UTC

My problem I am trying to solve is this: I have a field "location" that is a string, that also contains another within it call it po_box, either the first 3,4, or 5 characters, depending on the value on a field called "type".

So if type = car
then po_box = substr(location, 1, 3)
or substr(location, 1, 4)
or substr(location, 1, 5)
if type = truck then po_box = substr(location, 1, 4)

I must validate the po_box value against the table where this field lives (the data is coming in from a file).

So essentially I need a case statement like

and (case when type = 'CAR' then substr(location, 1, 3)
or substr(location, 1, 4)
or substr(location, 1, 5)
when type = 'TRUCK' then substr(location, 1, 4)
end) in (select po_box from x)

I hope that it more clear. Thanks for the time.

create table t (type varchar2(10), location varchar(10));

insert into t values ('CAR', '123ABCD');
insert into t values ('CAR', '1234ABCD');
insert into t values ('CAR', '12345ABCD');
insert into t values ('TRUCK', '1234ABCD');
insert into t values ('TRUCK', '4321ABCD');

create table x (po_box varchar2(5));

insert into x values ('CAR', '123');
insert into x values ('CAR', '1234');;
insert into x values ('TRUCK', '1234');
insert into x values ('TRUCK', '4321');

And no I am not storing numbers as strings, just to represent the number of characters in this example ;)


Tom Kyte
July 12, 2006 - 5:13 pm UTC

case when type = 'CAR'
and exists (select null
from x
where x.po_box in (substr_1, substr_2, substr_3 ) )
then 1
when type = 'TRUCK'
and exists ( select null
from x
where x.po_box = substr_1 )
else 0
end = 1


turn your thinking "inside out" here.

This data is botched, I'd take a serious look at it, it must be broken into two fields upon input and stored as two fields.

The, we could actually use a FOREIGN KEY!!!!!


Odd rows then even

A reader, July 12, 2006 - 5:25 pm UTC

SELECT LEVEL l FROM dual
CONNECT BY LEVEL<=10

How can I sort the above output so that I get all the odd rows first (1,3,5,7,9) and then the even rows (2,4,6,8,10)?

I tried

SELECT LEVEL l FROM dual
CONNECT BY LEVEL<=10
ORDER BY CASE WHEN MOD(l,2)=1 THEN l END

and that gave me (1,3,5,7,9,2,4,8,10,6)

The odd part worked fine but the even one got messed up.

Thanks for any ideas.

Thanks

Tom Kyte
July 12, 2006 - 5:50 pm UTC

ops$tkyte@ORA10GR2> with data
  2  as
  3  (select level l from dual connect by level <= 10)
  4  select *
  5    from data
  6   order by mod(l,2) DESC, l
  7  /

         L
----------
         1
         3
         5
         7
         9
         2
         4
         6
         8
        10

10 rows selected.
 

Alexander the ok, July 13, 2006 - 9:14 am UTC

Tom thanks for the neat statement. Could you tell me what the then 1, else 0, end = 1 stuff means though?

Tom Kyte
July 13, 2006 - 9:26 am UTC

you are doing a check, you want the case statement to return 0 (for failed) and 1 (for success)

you want to keep the "successes" I assume

Alexander the ok, July 13, 2006 - 9:41 am UTC

Sure, makes sense.  I'm having a problem running it though, which is why I asked:

SQL> select *
  2  from t
  3  where 1 = 1
  4  and case when type = 'CAR'
  5         and exists (select null
  6                       from x
  7                      where x.po_box in (substr_1, substr_2, substr_3 ) )
  8       then 1
  9       when type = 'TRUCK'
 10        and exists ( select null
 11                       from x
 12                      where x.po_box = substr_1 )
 13       else 0
 14  end = 1;
     else 0
     *
ERROR at line 13:
ORA-00905: missing keyword

 

Tom Kyte
July 13, 2006 - 9:47 am UTC

we missed the "then 1" on line 12/13



Alexander the ok, July 13, 2006 - 9:49 am UTC

Sorry I rushed, aside from the fact I didn't put the actual columns in the substr's, the 2nd "when" also just needed a "then 1".

Tom Kyte
July 13, 2006 - 12:50 pm UTC

perfectly OK, since I did say "we", I missed it too ;)

To "Alexander the ok" ... it isn't OK

Gabe, July 13, 2006 - 12:30 pm UTC

Well, there is more that has been missed.

In your table definition, X obviously has the column named TYPE too. And the subqueries have to be correlated on the TYPE:

where x.po_box in (substr_1, substr_2, substr_3 )
and x.type = t.type

Despite this the solution is hardly correct [at least based on the supplied test case] since ('CAR', '12345ABCD') is going to be returned although there is no ‘12345’ po_box in your lookup table for ‘CAR’.

Assuming the solution somehow works for you real data I would re-write the query as

gabe@XE> select *
2 from t
3 where exists (select null from x
4 where t.type = x.type
5 and substr(t.location,1,length(x.po_box)) = x.po_box
6 )
7 ;

A join shows you quite easily the problem with that ('CAR', '12345ABCD') false positive.

gabe@XE> select t.*, x.po_box
2 from t, x
3 where t.type = x.type
4 and substr(t.location,1,length(x.po_box)) = x.po_box
5 ;




Odd rows then even

A reader, July 13, 2006 - 1:38 pm UTC

Thanks, I missed that.

Another "brain teaser" for you.

Given the same query

SELECT LEVEL l
FROM dual
CONNECT BY LEVEL<=10

how can I get the output in the following order
1
6
2
7
3
8
4
9
5
10

This corresponds to a 2-column output

1 6
2 7
3 8
4 9
5 10

Thanks!

Tom Kyte
July 13, 2006 - 2:06 pm UTC

ops$tkyte@ORA10GR2> with data
  2  as
  3  (SELECT LEVEL l
  4  FROM dual
  5  CONNECT BY LEVEL<=:x)
  6  select l, row_number() over (partition by case when l <= :x/2 then 1 else 0 end order by l) rn
  7    from data
  8   order by 2, 1
  9  /
 
         L         RN
---------- ----------
         1          1
         6          1
         2          2
         7          2
         3          3
         8          3
         4          4
         9          4
         5          5
        10          5
 
10 rows selected.
 

OK

Kumar, July 13, 2006 - 2:24 pm UTC

Hi Tom,
I would like to have a query which returns 'true'
when we enter a alphabetic character-only and 'false'
when we enter a alphanumeric string??

SQL> select decode(:str,'string to be checked','true','false')
     from dual
/
 

Tom Kyte
July 13, 2006 - 5:17 pm UTC

ops$tkyte@ORA10GR2> select str,
  2         nvl(translate( str, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ', 'x' ), 'x'),
  3         decode( nvl(translate( str, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ', 'x' ), 'x'),
  4                'x', 'true', 'false' )
  5    from t;

STR                            NVL(TRANSLATE(STR,'ABCDEFGHIJK DECOD
------------------------------ ------------------------------ -----
string to be checked           x                              true
a string to be checked         x                              true
a string to be checked 1       x1                             false
                               x                              true
x                              x                              true
1                              1                              false

6 rows selected.

 

On that brainteaser Â… odd numbers ...

Gabe, July 13, 2006 - 3:26 pm UTC

For odd numbers, one would assume the order should be consistent with this arrangement:

1 6 and not 1 5
2 7 2 6
3 8 3 7
4 9 4 8
5 9

and hence your query should be (changed the CASE part only):

gabe@XE> with data
2 as
3 (SELECT LEVEL l
4 FROM dual
5 CONNECT BY LEVEL<=:x)
6 select l, row_number() over (partition by case when l <= (:x+1)/2 then 1 else 0
7 end order by l) rn
8 from data
9 order by 2, 1
10 /

L RN
---------- ----------
1 1
6 1
2 2
7 2
3 3
8 3
4 4
9 4
5 5

9 rows selected.

My own version Â…

gabe@XE> with data
2 as
3 (SELECT LEVEL l
4 FROM dual
5 CONNECT BY LEVEL<=:x)
6 select ceil(l+case when mod(l,2) = 0 then (:x-l)/2 else -(l-1)/2 end) x
7 from data
8 order by l
9 ;

X
----------
1
6
2
7
3
8
4
9
5

9 rows selected.


Odd rows then even

A reader, July 13, 2006 - 3:30 pm UTC

Cool, but it breaks down for odd numbers

SELECT LEVEL d,row_number() over (PARTITION BY CASE WHEN LEVEL <= 11/2 THEN 1 ELSE 0 END ORDER BY LEVEL) rn FROM dual
CONNECT BY LEVEL<=11
ORDER BY rn,LEVEL

gives me

1 1
6 1
2 2
7 2
3 3
8 3
4 4
9 4
5 5
10 5
11 6

i.e. the 11 goes in the first "column", I need it to go to the second column

Any ideas? Thanks

Tom Kyte
July 13, 2006 - 5:22 pm UTC

eh?

what could/should the output be?

did you REALLY want two columns - if so, you asked the wrong question.

Odd and even

A reader, July 13, 2006 - 7:27 pm UTC

See
</code> http://htmldb.oracle.com/pls/otn/f?p=24317:39 <code>

Enter 10 in the textfield and click Submit, your query works fine. 1..10 are rendered in 2 vertical columns.

But when you enter 11, you get 11 in the first "column" because the query returns
1
6
2
7
3
8
4
9
5
10
11

Instead, I would like it to return

1
7
2
8
3
9
4
10
5
11
6

so that the checkboxes render in 2 columns vertically.

Thanks

Tom Kyte
July 13, 2006 - 8:40 pm UTC

but once you "see" the trick - you should be able to tweak it to fit your needs.

trick was - set up two partitions, slice the data into two "sets".  Just tweak the partition:

ops$tkyte@ORA10GR2> variable x number
ops$tkyte@ORA10GR2> exec :x := 10

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> with data
  2  as
  3  (select level l from dual connect by level <= :x)
  4  select l, row_number() over (partition by case when l <= ceil(:x/2) then 1 else 0 end order by l) rn
  5    from data
  6   order by 2, 1
  7  /

         L         RN
---------- ----------
         1          1
         6          1
         2          2
         7          2
         3          3
         8          3
         4          4
         9          4
         5          5
        10          5

10 rows selected.

ops$tkyte@ORA10GR2> exec :x := 11

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> /

         L         RN
---------- ----------
         1          1
         7          1
         2          2
         8          2
         3          3
         9          3
         4          4
        10          4
         5          5
        11          5
         6          6

11 rows selected.

ops$tkyte@ORA10GR2> exec :x := 12

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> /

         L         RN
---------- ----------
         1          1
         7          1
         2          2
         8          2
         3          3
         9          3
         4          4
        10          4
         5          5
        11          5
         6          6
        12          6

12 rows selected.
 

Odd and even

A reader, July 13, 2006 - 9:51 pm UTC

Tom (and Gabe):

Thanks a lot, you guys are geniuses...hats off to you.

Alexander the ok, July 14, 2006 - 10:59 am UTC

I would like to thank Tom and Gabe as well. I am going to break the field into two. It was only until then I realized what Gabe was talking about before, and he was correct so thanks.

CASE WHEN

Su Baba, October 16, 2006 - 5:03 pm UTC

In the following example, I was expecting SQL #1 to have a much high logical I/O than SQL #2. I was surprised to find they're the same!

For SQL #1, doesn't Oracle have to run "CASE WHEN deptno IN (SELECT deptno FROM emp)" once for every record returned in the inner query?


CREATE TABLE dept AS
SELECT rownum + 1 deptno
FROM all_objects
WHERE rownum <= 10000;

CREATE TABLE emp AS
SELECT MOD(rownum, 5000) + 30 deptno, object_name ename
FROM all_objects
WHERE rownum <= 40000;


CREATE INDEX dept_u1 ON dept(deptno);
CREATE INDEX emp_n1 ON emp(deptno);

exec dbms_stats.gather_table_stats( user, 'DEPT', cascade => TRUE );
exec dbms_stats.gather_table_stats( user, 'EMP', cascade => TRUE );

-- ------------------------------------------------------------------
-- SQL #1
-- ------------------------------------------------------------------
SELECT *
FROM (
SELECT a.*, rownum rn
FROM (
SELECT deptno,
CASE WHEN deptno IN (SELECT deptno FROM emp)
THEN 'STAFFED'
ELSE 'EMPTY'
END
FROM dept
) a
WHERE rownum <= 50
)
WHERE rn >= 1;


----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 2100 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 50 | 2100 | 2 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| DEPT | 50 | 150 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">=1)
2 - filter(ROWNUM<=50)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
108 consistent gets
0 physical reads
0 redo size
1543 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed


-- ------------------------------------------------------------------
-- SQL #2
-- ------------------------------------------------------------------
SELECT b.*,
CASE WHEN deptno IN (SELECT deptno FROM emp)
THEN 'STAFFED'
ELSE 'EMPTY'
END
FROM (
SELECT a.*, rownum rn
FROM (
SELECT deptno
FROM dept
) a
WHERE rownum <= 50
) b
WHERE rn >= 1;


------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 1300 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN | EMP_N1 | 2 | 8 | 1 (0)| 00:00:01 |
|* 2 | VIEW | | 50 | 1300 | 2 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | TABLE ACCESS FULL| DEPT | 50 | 150 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("DEPTNO"=:B1)
2 - filter("RN">=1)
3 - filter(ROWNUM<=50)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
108 consistent gets
0 physical reads
0 redo size
1543 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed


Tom Kyte
October 16, 2006 - 5:49 pm UTC

why did you expect that pops into my head immediately, both of them get 50 records

Decode or case

cyno, October 27, 2006 - 1:16 am UTC

Hi Tom,
Could you please help me on this.

i have a table
say table load with columns phase, capacity.

phase - capacity
----- --------
A 23
B 34
C 32
B 14
A 39
ABC 55
ABC 90

i need to get them in the following way
sum of capacity for phase A
Sum of capacity for phase B
sum of capacity for phase C


so from the data above, i can simply add the capacity for each phase
and
if phase is ABC, then i have to split this by dividing by 3.
means load for A is capacity / 3
load for B is capacity / 3


effectively the row

ABC 55

becomes

A 55/3 = 18 (rounded)
B 55/3 = 18 (rounded)
C 55/3 = 18 (rounded)

and for this record ABC - 90 it will be A - 30, B - 30 , C - 30

therefore my output should like

sum(A) sum(B) sum(C)
---------------- ----------- -------
23+39+18(SP)+30(SP) 34+14+18+30 32+18+30

where SP = split record data.

ie

sum(A) sum(B) sum(C)

110 96 80

can this be achieved in a single query?

i tried with decode successfully, but am not able to get them through a single query.
i created intermediate views and from them, could able to get the required output.

Could you please help me out on this.

Thanks
Cyno




Tom Kyte
October 27, 2006 - 7:43 am UTC

no create table
no insert
no lookie (but no promises that if they appear I'll be able to answer)

Decode or case

cyno, October 27, 2006 - 11:55 am UTC

Tom,
My bad, I should have done that before.

here are my create and insert scripts

create table load (phase varchar2(3), capacity number(5));

insert into load values ('A', 23);
insert into load values ('B', 34);
insert into load values ('C', 32);
insert into load values ('B', 14);
insert into load values ('A', 39);
insert into load values ('ABC', 55);
insert into load values ('ABC', 90);

Thanks for sparing your time.


Tom Kyte
October 27, 2006 - 8:00 pm UTC

ops$tkyte%ORA10GR2> select sum( case when phase = 'A' then capacity
  2                   when phase = 'ABC' then round(capacity/3)
  3               end ) sum_a,
  4         sum( case when phase = 'B' then capacity
  5                   when phase = 'ABC' then round(capacity/3)
  6               end ) sum_b,
  7         sum( case when phase = 'C' then capacity
  8                   when phase = 'ABC' then round(capacity/3)
  9               end ) sum_c
 10    from load;

     SUM_A      SUM_B      SUM_C
---------- ---------- ----------
       220        192        160
 

excellent

cyno, October 28, 2006 - 12:16 am UTC

Excellent Tom, thanks a lot.
I was trying with decode and had a tough time defining multiple check conditions in a single block.

I did not know that it is so easily achievable using case statement.

Next time I would better go through the documentation to get the basics right before troubling you.
Thanks a lot, this will cut down my multiple queries.

to: Cyno

Michel Cadot, October 28, 2006 - 1:32 am UTC

It could also be done with decode but it is less readable.
For instance, extending the case with any number of different unitary phases in "phase":

SQL> select 
  2    sum(decode(instr(phase,'A'),0,0,round(capacity/length(phase)))) sum_a,
  3    sum(decode(instr(phase,'B'),0,0,round(capacity/length(phase)))) sum_b,
  4    sum(decode(instr(phase,'C'),0,0,round(capacity/length(phase)))) sum_c
  5  from load
  6  /
SUM_A SUM_B SUM_C
----- ----- -----
  110    96    80

Michel  

Thanks Michel

cyno, October 28, 2006 - 2:24 am UTC

Many thanks MIchel,

this one too works out well, though it took some time for me to understand the logic.

thanks again



Different behaviour of DECODE in different products

abz, November 29, 2006 - 5:36 am UTC

In SQLPLUS 9i connected with 9i database

SQL>  SELECT DECODE(TO_NUMBER(NULL), NULL, ' ', TO_NUMBER('3')) FROM DUAL;

D
-


SQL> 

But in Forms 6i connected with the same 9i database
when I execute this code
DECLARE
    m_temp varchar2(4);
BEGIN
    
SELECT DECODE(TO_NUMBER(NULL), NULL, ' ', TO_NUMBER('3')) 
INTO m_temp
FROM DUAL;
END;

it return to me ORA-01722.

Why there is different behaviour of the same statement
in different products 

Tom Kyte
November 30, 2006 - 9:00 am UTC

it should not - but I don't use forms so I cannot even try it.

case statement in a where clause

A reader, January 18, 2007 - 12:15 pm UTC

Hi,
I am trying to incorporate the case statement in where clause, but failing.
I want to retrieve records from a table based on the date criteria in one of the column named timstamp

1. If there is data in the table with timstamp = sysdate-1 then only pull these records that satisfy this condition

2. If there are no data that match the above criteria, then
pull the last date value (max timstamp) in the table and display all the records for this date.

I tried using the case statement, but it fetches records for both "When" and "Else" clause. Not sure how to approach.
Here is the simple table scripts.
-- Create table statement
CREATE TABLE seodat
( A CHAR(1),
TIMSTAMP DATE);

-- Insert Statement
INSERT INTO SEODAT VALUES ( '1',TO_DATE('1/16/2007','MM/DD/YYYY'));
INSERT INTO SEODAT VALUES ( '2',TO_DATE('1/16/2007','MM/DD/YYYY'));
INSERT INTO SEODAT VALUES ( '3',TO_DATE('1/17/2007','MM/DD/YYYY'));
INSERT INTO SEODAT VALUES ( '4',TO_DATE('1/17/2007','MM/DD/YYYY'));
INSERT INTO SEODAT VALUES ( '6',TO_DATE('1/17/2007','MM/DD/YYYY'));

-- Sysdate is 1/18/2007

--Select query

SELECT * FROM SEODAT WHERE TRUNC(timstamp,'dd') =
CASE WHEN TRUNC(timstamp,'dd') = TRUNC(SYSDATE-1,'dd') THEN TRUNC(SYSDATE-1,'dd') ELSE TRUNC(SYSDATE-2,'dd') END

--O/p
A TIMSTAMP
- ---------
1 16-JAN-07
2 16-JAN-07
3 17-JAN-07
4 17-JAN-07
6 17-JAN-07

--Required O/p (if data for 1/17 is available)
3 17-JAN-07
4 17-JAN-07
6 17-JAN-07
(else since that is the max date present)
1 16-JAN-07
2 16-JAN-07


Regards,

Vishal, January 23, 2007 - 3:58 am UTC

In my recent interview i asked one question ,that how can i use operator like (>,<,&) in my decode statment,the qustion was like there is one table, named t1 with two fields amt and emi,in case of amt > 5000,emi should be updated to the 1.0% and in case of amt < 5000,emi should be updated to the 2.0%.
so how we perform a query where we can manupulate <,> in decode function.

Handling "If Then Else" in where clause

Shrikant, January 23, 2007 - 1:25 pm UTC

Hi Tom,

create table product_tran_test (product_id number(5), category varchar2(20), sellprice number(10, 2)) ;

create table measure_category_test (product_id number(5),

category varchar2(20), low_price number(10, 2), high_price number(10, 2));

insert into product_tran_test values
(10001, 'CellPhones', 6000);

insert into product_tran_test values
(10002, 'DVD', 5500);

insert into product_tran_test values
(10003, 'General', 500);

insert into measure_category_test values (10001, 'CellPhones', 5000, 7000);

insert into measure_category_test values
(10002, 'DVD', 5000, 7000);

insert into measure_category_test values
(-1000, 'General', 1000, 10000);

insert into measure_category_test values (10004, 'General', 1000, 10000);

insert into measure_category_test values
(-1000, 'DVD', 5000, 7000) ;

insert into measure_category_test values
(-1000, 'CellPhones', 5000, 7000) ;

Table explanation -

"product_tran_test" table is a transaction table and will hold information about products measure category and current price.

"measure_category_test" table is a lookup table. Will store price range for all products and categories. Table will have a default row of -1000 will all categories.


Requirement is, if for a product id in "product_tran_test" there is no correspoding mapping category in lookup table, it should pick up price range for "-1000" product id for same "category". In example above, for product 10003 there is no correspoding row in category in measure category table, but table has default row of -1000 for category "General"

Query should give floowing output.

product_id category sellprice low_price high_price
10001 CellPhones 6000 5000 7000
10002 DVD 5500 5000 7000
10003 General 500 1000 10000

--
I tried DECODE and OR conditions, but very confuse on how to code SQL in such a way that use defaukt value only and only if there is no records.

Please help.

Reply : If Then Else in sql

RM, January 23, 2007 - 2:25 pm UTC

Following query will give the result required

select product.product_id, category.category, sellprice, low_price, high_price
from product_tran_test product, measure_category_test category
where ( product.category = category.category )
AND (
(product.product_id = category.product_id )
or (category.product_id = -1000)
) ;

Handling "If Then Else" in where clause

Shrikant Gavas, January 23, 2007 - 2:29 pm UTC

Thanks for the reply.

But if we insert one more row in "measure_category_test"
as below

insert into measure_category_test values (10003, 'General', 1000, 10000);

The above query fails. In this case it should ignore row with -1000, 'General' from lookup table.


Please help.

Total within Financial Year

Arindam Mukherjee, July 16, 2007 - 3:57 am UTC

Sir,

Pleae read my question and kindly help me write a SQL query to get the data.

CREATE TABLE T1
(year        NUMBER(4),
 month       NUMBER(2),
 commission  NUMBER(7,2))
/

INSERT INTO T1 VALUES (2007,3,103)
/
INSERT INTO T1 VALUES (2007,4,104)
/
INSERT INTO T1 VALUES (2007,5,105)
/
INSERT INTO T1 VALUES (2007,6,106)
/
INSERT INTO T1 VALUES (2007,7,107)
/
INSERT INTO T1 VALUES (2007,8,108)
/
INSERT INTO T1 VALUES (2007,9,109)
/
INSERT INTO T1 VALUES (2007,10,110)
/
INSERT INTO T1 VALUES (2007,11,111)
/
INSERT INTO T1 VALUES (2007,12,112)
/
INSERT INTO T1 VALUES (2008,1,201)
/
INSERT INTO T1 VALUES (2008,2,202)
/
INSERT INTO T1 VALUES (2008,3,203)
/


I need the following output with maintaining financial year April-2007 to march 2008.

 Select commission currenct_commission, 
        SUM(commission when month < input and year between 2007 and 2008) total_previous_commission
 from  T1
 WHERE MONTH = INPUT
   AND YEAR = INPUT.


Pleae help me write this SQL.

Tom Kyte
July 17, 2007 - 11:18 am UTC

i hate your schema. having a year column and a month column. Oh, how, I, hate, that.

You have made this SO HARD by doing that - so so so so SO hard.

Why?????

we want to do date arithmetic and you have made it so inefficient to do so. man.

hate it.

ops$tkyte%ORA10GR2> variable y number
ops$tkyte%ORA10GR2> variable m number
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :m := 6; :y := 2007

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sum( case when year = (case when :m between 4 and 12 then :y else :y-1 end) and month >= 4
  2                     OR year = (case when :m between 4 and 12 then :y+1 else :y end) and month <= 3
  3                          then commission
  4                           end ) fy,
  5         sum(case when month = :m and year = :y then commission end) single
  6    from t1
  7   where year in ( (case when :m between 4 and 12 then :y else :y-1 end),
  8                   (case when :m between 4 and 12 then :y+1 else :y end))
  9  /

        FY     SINGLE
---------- ----------
      1578        106


ugh.

Please once again

Arindam Mukherjee, July 18, 2007 - 1:01 am UTC

Sir,

I beg to differ with your result. When you choose month = 6 and year = 2007, sum of previous would be for the month of 4 and 5 as financial year starts from April (4). So the correct result should be 209 as for the month (4) = 104 and month (5) = 105.

If you choose month = 12, then sum of previous is between 4 and 11 in the same year 2007.
If you choose month = 2 and year 2008, then sum of previous is between month (4) and month (12) of year = 2007 and month = 1 for the year = 2008.

In a nutshell, calculation domain is from month = 4 and year 2007 to month = 3 and year = 2008 as it is one financial year.


Tom Kyte
July 18, 2007 - 10:24 am UTC

so, fix it! you see the logic there don't you????? If I got your math wrong (because of your schema) - FIX IT


if you cannot - given what I've already demonstrated - then you should be using this technique at all (you should spend the time fixing your SCHEMA so we can use dates when you have dates!!)

Try this code

Jay, July 18, 2007 - 1:13 pm UTC

-- &year = year prompt
-- &month = month prompt 

select  &year,
        &month,
        sum(commission) as commission
  from

(
  select  month,
          year,
          case
           when month = 4 then 1
           when month = 5 then 2
           when month = 6 then 3
           when month = 7 then 4
           when month = 8 then 5
           when month = 9 then 6
           when month = 10 then 7
           when month = 11 then 8
           when month = 12 then 9
           when month = 1 then 10
           when month = 2 then 11
           when month = 3 then 12
          end as FM,
          
           
          case
           when year = &year and month between 4 and 12
           then &year
           when year = &year + 1 and month between 1 and 3
           then &year 
          end as FY,
          
          commission 
           
      from population 
       )

where FY is not null
  and FM < 
      (case
        when &month between 4 and 12
        then &month - 3
        else &month + 9
       end) 

-- Output = 209 (Entered prompts: &year = 2007, &month = 6) 



Thanks. You should leave dates as dates as Tom mentioned. This is indeed making things way more complicated!!!


Please look at my table Structure for Date columns

Arindam Mukherjee, July 19, 2007 - 6:13 am UTC

I am very much obliged to all of you for responding to my query. On reading response, I could not understand how I can leave Number data type for "Year" and "Month" column for date fields. In my application, commission is calculated for Agents at the end of every month. The number of agents is 100 thousands and every month I have to store information for those agents in a table and that table is partitioned under month . This table contains only one financial year data.
So can you please suggest how I can arrange the table structure for that commission table with date fields and how can I partition that table also? My table structure is as follows.

CREATE TABLE COLLECTION_QUOTA
( 
  AGENT_ID               NUMBER (13)      NOT NULL, 
  BRANCH_ID             NUMBER (3)       NOT NULL,
  REGION_ID              NUMBER (3)       NOT NULL, 
  YEAR                      NUMBER (4)       NOT NULL, 
  MONTH                   NUMBER (2)       NOT NULL,
  COLLECTION_AMT     NUMBER (15,2)   NOT NULL, 
  COLLECTION _QUOTA           NUMBER(15,2)   NOT NULL,    
QUOTA_PERCENTAGE      NUMBER (15,2)    DEFAULT 0 NOT NULL, -- For this month
  CREATE_USER            VARCHAR2 (30)    NOT NULL, 
  CREATE_DT_TM           DATE            NOT NULL
)
/

Tom Kyte
July 19, 2007 - 10:58 am UTC

get rid of the year and month columns
add a column "the_date" of type DATE

not sure what you mean.


and no one can tell you how to partition your tables - except for you. For you have to

a) have a goal in wanting to partition (pick one of performance, administrative ease or availability)

b) understand how you use the data (we don't)

c) understand the physics behind partitioning (what it does and how it does it)

and using the outputs of a, b, c - develop a technical solution that accomplishes A given the constraints of B and C.


A reader, August 14, 2007 - 9:15 am UTC

Attn. Vishal,
<code>In my recent interview i asked one question ,that how can i use operator like (>,<,&) in my decode statment,the qustion was like there is one table, named t1 with two fields amt and emi,in case of amt > 5000,emi should be updated to the 1.0% and in case of amt < 5000,emi should be updated to the 2.0%. 
so how we perform a query where we can manupulate <,> in decode function. 


I know i am very late in this answering this but it could prove useful for somebody. Here is the logic

select decode(sign(amt - 5001),-1,amt + (amt * 0.01), amt + (amt *0.02)) from <table_name></code>

lot of OR conditions

Rajeswari, August 23, 2007 - 11:15 am UTC

Oracle version: 9.2.0.8

WK930,WK910 table - Maximum record count 1.5 million

Below select will fetch maximum of 10000 records

Indexes:
WK930 - appln_status + ver_pass
- appln_batch_no + appln_serial_no

WK910 - appln_batch_no + appln_serial_no


SELECT 
 WK910.APPLN_BATCH_NO,
 WK910.APPLN_SERIAL_NO,
 LPAD(NVL(EMP_CODE,'09'),2,'0'),
 LPAD(NVL(SELF_EMPL,'9'),2,'0'),
 LPAD(NVL(SALARIED,'9'),2,'0'),
 WK910.CARD_TYPE,
 LPAD(NVL(DOC_TYPE,'0'),2,'0'),
 TRUNC(BIRTHDATE),
 CARD_NAME,
 APPLN_STATUS,
 NVL(VER_PASS,'VVV'),
FROM  WK910, WK930
WHERE 
 (
  (APPLN_STATUS  IN (4,5,9) 
         AND NVL(CPV_SEQ_FLAG,'N') ='P' 
         AND NVL(CIB_REQ_FLAG,'N') ='N'
         )         
         OR         
        (APPLN_STATUS = 17  
         AND ( (CPV_SEQ_FLAG ='S' AND NVL(CIB_REQ_FLAG,'R') IN ('R','E','S','N'))
                OR
               (CPV_SEQ_FLAG ='P' AND CIB_REQ_FLAG IN ('E','S','N')) 
             )  
        )
        OR
        (APPLN_STATUS = 17 
         AND CPV_SEQ_FLAG IN ('P','S') 
         AND CIB_REQ_FLAG = 'Y' 
         AND (CIB_OD_FLAG  = 'N'
              OR (CIB_OD_FLAG = 'Y' AND CIB_FIN_FLAG IS NOT NULL)
              )
        )
        
     )

  AND WK930.CARD_TYPE BETWEEN 10 AND 999 
  AND WK930.CARD_TYPE NOT IN (11,13,32,33,34)
  AND WK930.APPLN_BATCH_NO = WK910.APPLN_BATCH_NO
  AND WK930.APPLN_SERIAL_NO = WK910.APPLN_SERIAL_NO
  AND WK910.OTHER_CARD_NO IS NULL
  AND (VER_PASS IS NULL OR VER_PASS LIKE '%O%'
           OR VER_PASS LIKE '%R%' OR VER_PASS LIKE '%H%' OR VER_PASS LIKE '%J%'
         OR VER_PASS LIKE '%P%')
  
  ORDER BY EMP_CODE,SALARIED,SELF_EMPL,DOC_TYPE,TOTAL_SCORE;



Will you please suggest to simplify such type of queries?

It is 10 year old system in Pro*C.
Whenever new changes, new flag column is introduced in work tables to differentiate the scenario and programs are changed.
Eventhough I cann't change design now, I can learn better ways to design.

Tom Kyte
August 23, 2007 - 1:16 pm UTC

what is there to simplify?

you have a question
it requires certain data with certain attributes having certain values

looks okey dokey to me. but I know nothing about your application, your data, your needs.

but I see nothing inherently wrong

Adding flag columns

Rajeswari, August 24, 2007 - 3:48 am UTC

Actually we have program1 which has around 100 policies, in that around 10-20 policies we have to take differently in further process. For this we are adding new columns to differentiate. These new columns will be used by program2 to program6 for other processing. our most of the where criteria is similar to one which I gave in previous post.

I am little bit confused on the approach eventhough we met the requirement. Your reply lead us to discussion and gave us clarity on our approach. Thanks Tom.

Using CASE for a DATE Column in WHERE clause

Maverick, December 07, 2007 - 2:14 pm UTC

Tom,
I have a problem using CASE in WHERE Clause. the column in where clause is a date Datatype.
Please see below:

create Table Test(id integer, test_date date);

insert into test values (1,sysdate);
insert into test values (2,sysdate+1);
insert into test values (3,null);
insert into test values (4,null);

commt;

My Requirement is [I don't want to user Dynamic Query]:

if p_id parameter is 0 then
{p_id is a parameter for a function [p_id integer]}
Select all rows
otherwise
Select all rows where id=p_id.

For that I can write query like this:

select * from test
where id = Case when :p_id=0 then id else :p_id end;

I need to do the same with test_date. But requirement is,
if p_id parameter is 1 then
Select all rows where test_date is null
otherwise [p_id =0]
Select all rows where test_date is not null.

problem comes when I use IS NULL or IS NOT NULL..Operator is changing in this case..

Any suggestions?


Tom Kyte
December 10, 2007 - 10:51 am UTC

no need for dynamic sql

declare
   l_cursor sys_refcursor;
begin
   if ( p_id = 1 ) 
   then
      open l_cursor for select * from t where dt is null;
   else
      open l_cursor for select * from t where dt is not null;
   end if;
end;
/



or, if you want one query and are happy with the ONE PLAN (eg: full scan likely) then

select * from t where (:x = 1 and dt is null) or (:x=0 and dt is not null);



multiple union all

Reene, December 11, 2007 - 8:31 am UTC

Hi Tom

is it possible to simplify the sql below (there are 2 union all ) - i think there is a way to change it as single select statemet -


SELECT tab.acctg_unit_no ACCTG_UNIT_NO , tab.acct_no ACCT_NO ,
SUM ( TAB.IC_OPEN_BAL_SUM ) IC_OPEN_BAL ,
SUM ( tab.IC_DR_SUM ) IC_DR ,
SUM ( tab.IC_CR_SUM ) IC_CR
FROM
( SELECT v.acctg_unit_no , v.acct_no , v.trans_source_code ,
SUM ( NVL ( v.AMOUNT_BASE , 0 ) * NVL ( v.DEBIT_CREDIT_SIGN , 0 ) ) IC_OPEN_BAL_SUM ,
NULL IC_DR_SUM ,
NULL IC_CR_SUM
FROM GE_OPM_FINAL_UPDATE_V v
WHERE v.trans_source_code = 'IC' AND
TO_DATE ( V.GL_TRANS_DATE ) < TO_DATE ( '24-NOV-2007') AND
GROUP BY v.acctg_unit_no , v.acct_no , v.trans_source_code
UNION ALL
SELECT v.acctg_unit_no , v.acct_no , v.trans_source_code ,
NULL IC_OPEN_BAL_SUM , SUM ( NVL ( v.AMOUNT_BASE , 0 ) * NVL ( v.DEBIT_CREDIT_SIGN , 0 ) ) IC_DR_SUM ,
NULL IC_CR_SUM
FROM GE_OPM_FINAL_UPDATE_V v
WHERE v.trans_source_code = 'IC' AND
v.debit_credit_sign = 1 AND
TO_DATE ( V.GL_TRANS_DATE ) BETWEEN TO_DATE ( '24-NOV-2007' ) AND TO_DATE ( '03-DEC-2007' ) AND
GROUP BY v.acctg_unit_no , v.acct_no , v.trans_source_code
UNION ALL
SELECT v.acctg_unit_no , v.acct_no , v.trans_source_code ,
NULL IC_OPEN_BAL_SUM , NULL IC_DR_SUM ,
SUM ( NVL ( v.AMOUNT_BASE , 0 ) * NVL ( v.DEBIT_CREDIT_SIGN , 0 ) ) IC_CR_SUM
FROM GE_OPM_FINAL_UPDATE_V v
WHERE v.trans_source_code = 'IC' AND
v.debit_credit_sign = - 1 AND
TO_DATE ( V.GL_TRANS_DATE ) BETWEEN TO_DATE ( '24-NOV-2007' ) AND TO_DATE ('03-DEC-2007' )
GROUP BY v.acctg_unit_no , v.acct_no , v.trans_source_code
) tab
group by tab.acctg_unit_no ACCTG_UNIT_NO , tab.acct_no ACCT_NO
/

please take a look.

thanks
Tom Kyte
December 11, 2007 - 9:35 am UTC

you have dangling AND's in there. Not going to waste my cycles on that, this sql isn't sql.

but - tell you what - you are right, the union all's CAN be removed probably (well, depends on what the AND's do that dangle right now...)

Did you give it a good old fashioned college try before posting it on this (unrelated) question thread?

yes, i can merge the last 2 union alls

Reene, December 11, 2007 - 9:46 am UTC

Hi Tom

yes I tried before asking,

I can merge the last 2 SQLs of this union all like this -

also all the "and " are same only thing differnt is

debit_credit_sign ...so does it look right or any other better way...also how to merge them in 1..if possible

I am simplifying it for your quick review...you can remove all the and statements except the debit_credit_sign = 1 and -1 respectively and the gl_trans_date criteria..to look at it quickly .
sorry for unrelated query..

select
t.acctg_unit_no ,
t.acct_no ,
t.trans_source_code,
SUM(t.ic_dr_sum),
sum(t.ic_cr_sum)
from
(
SELECT
v.acctg_unit_no ,
v.acct_no ,
v.trans_source_code ,
v.debit_credit_sign,
case when ( v.debit_credit_sign = 1 and v.trans_source_code='IC' ) then
(nvl(v.amount_base,0)*nvl(v.debit_credit_sign,0))
else 0 end ic_dr_sum,
case when ( v.debit_credit_sign = -1 and v.trans_source_code='IC' ) then
(nvl(v.amount_base,0)*nvl(v.debit_credit_sign,0))
else 0 end ic_cr_sum
FROM GE_OPM_FINAL_UPDATE_V v
WHERE v.trans_source_code in ('IC', 'PM','OM','COS','PUR') AND
v.debit_credit_sign in ( 1,-1) AND
TO_DATE ( V.GL_TRANS_DATE ) BETWEEN TO_DATE ( '24-NOV-2007') AND TO_DATE ( '03-DEC-2007') AND
) t
GROUP BY t.acctg_unit_no , t.acct_no , t.trans_source_code
Tom Kyte
December 11, 2007 - 10:52 am UTC

...
v.debit_credit_sign in ( 1,-1) AND
TO_DATE ( V.GL_TRANS_DATE ) BETWEEN TO_DATE ( '24-NOV-2007') AND TO_DATE
( '03-DEC-2007') AND
) t
.....

you have dangling AND's - this is not sql.

and now there is no union all - so we are done... ( i don't read backwards on the page, everything needs to be 100% complete here and clear)

the mail cannot be send more than 32kb

Abdul Mateen, December 11, 2007 - 10:58 am UTC

please hel me the the pdf file is not been open when the file attachment is more than 32 kb.

CASE statement in SQL predicate

Gary Wicke, November 03, 2008 - 4:13 pm UTC

Hi Tom

Environment:

Oracle EE 10.2.0.2 on AIX 5.3

I couldn't find an example of my question via the Search so I was hoping you could address it here.

I am trying to use a CASE statement in a SQL predicate to fill in the values needed in an 'IN' clause.

Here is my test case:

create table tt (model varchar2(10), brand varchar2(10));

insert into tt (model, brand) values ('modela','ajax');
insert into tt (model, brand) values ('modelb','ajax');
insert into tt (model, brand) values ('modelc','acme');
insert into tt (model, brand) values ('modeld','acme');
insert into tt (model, brand) values ('modele','acme');
insert into tt (model, brand) values ('modelf','zeus');
insert into tt (model, brand) values ('modelg','zeus');
insert into tt (model, brand) values ('modelh','mojo');
insert into tt (model, brand) values ('modeli','mojo');
insert into tt (model, brand) values ('modelj','mama');


Here is what I'm attempting to do. For a given starting letter for a brand I would like all the models printed out whose brand starts with the entered letter.

Here is what I started with:
select model, (case '&brandfirstletter'
 when 'a' then '(''ajax'',''acme'')'
 when 'z' then '(''zeus'')'
 when 'm' then '(''mojo'')'
 end) pred
from tt
where brand in
(case '&brandfirstletter'
 when 'a' then '(''ajax'',''acme'')'
 when 'z' then '(''zeus'')'
 when 'm' then '(''mojo'')'
 end);


I wanted to print out the results of my CASE statement just to be sure it was doing what I thought I wanted.

I get 'no rows selected'

When I comment out the WHERE clause to see what the PRED values are I get:

gww@dwt1> /
Enter value for brandfirstletter: a
old   1: select model, (case '&brandfirstletter'
new   1: select model, (case 'a'
Enter value for brandfirstletter: a
old   8: -- (case '&brandfirstletter'
new   8: -- (case 'a'


MODEL      PRED
---------- ---------------
modela     ('ajax','acme')
modelb     ('ajax','acme')
modelc     ('ajax','acme')
modeld     ('ajax','acme')
modele     ('ajax','acme')
modelf     ('ajax','acme')
modelg     ('ajax','acme')
modelf     ('ajax','acme')
modelg     ('ajax','acme')
modelh     ('ajax','acme')


I believe this is what I want the results of the CASE statement to look like but I don't get the output I would like from the table.

What am I missing?

Many thanks for your support of the INOUG and all the hours you spend teaching us. It is truly appreciated.

-gary
Tom Kyte
November 11, 2008 - 11:53 am UTC

where brand in
( decode( :brandfirstletter, 'a', 'ajax' ),
  decode( :brandfirstletter, 'a', 'acme' ),
  decode( :brandfirstletter, 'z', 'zeus' ),
  decode( :brandfirstletter, 'm', 'mojo' )
)


would be the way to do that.


CASE returns a single scalar, not a set.


You could have CASE return a collection, and then "table" it into a set

ops$tkyte%ORA10GR2> select *
  2    from dual
  3   where dummy in
  4   (select *
  5      from TABLE( case when 1=0 then sys.odcivarchar2list( 'a','b','c')
  6                       when 1=1 then sys.odcivarchar2list( 'X', 'y', 'z' )
  7                   end )
  8   )
  9  /

D
-
X




but that is a little obscure.

Oddity with case and NULL...

Dan, November 12, 2008 - 5:15 pm UTC

Hey Tom,

Ran into this oddity with case statements and got it down to a fairly small test case. Basically, it seems that if you put the expression after the case and it evaluates to NULL, case will not recognize it correctly:

Works:
with t as (
select NULL as t_date from dual UNION ALL
select '20080801' as t_date from dual UNION ALL
select '20080801' as t_date from dual UNION ALL
select '20080801' as t_date from dual UNION ALL
select '20080801' as t_date from dual
)
SELECT CASE
         WHEN t_date IS NULL THEN 'test'
         ELSE to_char(to_date(t_date, 'YYYYMMDD'), 'MM/DD/YYYY')
       END
FROM t;

CASEWHENT_DATEISNULLTHEN'TEST'ELSETO_CHAR(TO_DATE(T_DATE,'YYYYMMDD'),'MM/DD/YYYY')END 
------------------------------------------------------------------------------------- 
test                                                                                  
08/01/2008                                                                            
08/01/2008                                                                            
08/01/2008                                                                            
08/01/2008                                                                            

5 rows selected


Does not work:
with t as (
select NULL as t_date from dual UNION ALL
select '20080801' as t_date from dual UNION ALL
select '20080801' as t_date from dual UNION ALL
select '20080801' as t_date from dual UNION ALL
select '20080801' as t_date from dual
)
SELECT CASE t_date
         WHEN NULL THEN 'test'
         ELSE to_char(to_date(t_date, 'YYYYMMDD'), 'MM/DD/YYYY')
       END
FROM t;

CASET_DATEWHENNULLTHEN'TEST'ELSETO_CHAR(TO_DATE(T_DATE,'YYYYMMDD'),'MM/DD/YYYY')END 
----------------------------------------------------------------------------------- 
                                                                                    
08/01/2008                                                                          
08/01/2008                                                                          
08/01/2008                                                                          
08/01/2008                                                                          

5 rows selected


I wasn't able to find this documented, it seems like it should work either way though.
Tom Kyte
November 13, 2008 - 5:03 pm UTC

well, a searched case (the second one) is just shorthand for "="

eg:

select case X
       when y then 
       when z then
       end 

is the same as

select case when x=y then
            when x=z then


now, nothing is equal to null. nothing is also NOT equal to null. it is UNKNOWN.


decode is nifty... decode does let null=null

select decode( x, null, 'test', ... )

would work.

nvl of NULL inline view column

jy, May 19, 2010 - 12:59 pm UTC

Hi Tom;

I have a situation where, in my opinion, the result of an inline view gives inconsistent results for Null valued columns.

for the following sql, the result of the Null 'flg' column is evaluted by the NVL in the outer select and returns 'val':

select nvl(dummy,'jy') flgu , null flg
from dual where dummy = 'X'
Row# FLGU FLG
1 X {null}


select nvl(flg,'val') val, flg
from (
select nvl(dummy,'jy') flgu , null flg
from dual where dummy = 'X'
)
Row# VAL FLG
1 val {null}


However, if I run the following, the 'flg' column returns Null in the outer select even though the column in the in-line view is Null like in the first scenario.

select nvl(dummy,'jy') flgu , null flg
from dual where dummy = '1'
Row# FLGU FLG
1 {null} {null}


select nvl(flg,'val') val, flg
from (
select nvl(dummy,'jy') flgu , null flg
from dual where dummy = '1'
)
Row# VAL FLG
1 {null} {null}



the difference here is the 'dummy' column is now set to '1' vs. 'X'.

Is there a logical expalntion for this behavior.

Thanks for your help.
jy
Tom Kyte
May 24, 2010 - 11:21 am UTC

I tried this in 9ir2, 10gr2, 11gr1, 11gr2 and the results were all the same - after changing '1' to 'X' of course:

ops$tkyte%ORA9IR2> select nvl(flg,'val') val, flg
  2   from (
  3         select nvl(dummy,'jy') flgu , null flg
  4         from dual where dummy = 'X'
  5        )
  6  /

VAL F
--- -
val



how about you cut and paste instead of editing your output - and supply of course a VERSION/platform

Case and sequence

Syed Khurram, August 30, 2010 - 3:07 am UTC

Hi,

I am getting strange result from CASE statment when used with SEQUENCE. In my understanding, CASE statement will work exactly like IF and ELSE statments in any programing language. That is, if condition is matched, the statment of that IF will be executed, else the other statement will be executed. This is not happening in CASE statment. Consider the below example

"select b.a,case
when b.a > 0 then to_char(seq.currval)
else to_char(seq.nextval)
end chunk
from (
select '1' a from dual
union
select '2' a from dual
union
select '3' a from dual
union
select '-2' a from dual
)b"

in this statment seq is a sequence. I expect below result

A CHUNK
-- --------
-2 82
1 81
2 81
3 81

while I am getting
A CHUNK
-- ---------
-2 81
1 82
2 83
3 84

It appears that else part is executed everytime whether the condition is true or not.
Is it a bug, or what??

Thanks
Tom Kyte
September 09, 2010 - 9:09 am UTC

I see no bugs, sequences have rules and they currval/nextval are evaluated before the rowsource needs them - as they rowsource needs them. So, the nextval and currval are both populated AND THEN the rowsource gets them. That would be the only way to reference nextval repeatedly in a sql statement.

pretend that nextval/currval are replaced with literals for each execution of each row flowing through the rowsource - because they are in effect.

CASE

A reader, October 20, 2010 - 4:48 am UTC

Hi Tom,
the below quote is from oracle 11g r2 doc( you were the primary author)

"For a simple CASE expression, the expr and all comparison_expr values must either have the same data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric data type. If all expressions have a numeric data type, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

For both simple and searched CASE expressions, all of the return_exprs must either have the same data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric data type. If all return expressions have a numeric data type, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type."


My question is - when Oracle uses short circuit evaluation - how can it determine the numeric precedence of ALL EXPRESSIONS ? even it does not look at the other expressions when executing .. (or)
is it something that checking the precedence happens at the parsing level itself but evaluation happens at the execution level ??

Tom Kyte
October 25, 2010 - 2:55 pm UTC

As 'parse time' it looks at everything - but evaluates nothing.


datatype determination takes place WAY before execution does.


case when 1=1 then 1 else 1/0 end


you can look at that and you know that

a) 1 is a number, clearly.

b) 1 is a number, 0 is a number, number divide number is a number, hence number

We can ascertain the return type of a function/expression without ever evaluating it.

A reader, October 28, 2010 - 3:47 pm UTC

Hi Tom,

There is CASE in SQL and CASE in pl/sql - Is there any difference between them ? Is the internal implementation different ?

if any function/program construct such as CASE is in both SQL and PL/SQL langauges , would they be coded internally in SQL engine and Pl/SQL engine seperatey ?
Tom Kyte
November 01, 2010 - 4:54 am UTC

documentation reveals

http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e17126/case_statement.htm
http://docs.oracle.com/docs/cd/E11882_01/server.112/e17118/expressions004.htm

they are the same in sql as plsql - there are simple case statements

case something
when somevalue then ...
when someothervalue then ...
else ....
end


there are searched case statements

case
when somecondition then ...
when someotherconditition then ...
else ...
end



would then be coded separately?

they certainly could be - are they? I don't know if they each have their own custom implementations. I would, if asked to guess, say "yes, they probably do, but they probably share a lot of code as well". No reason to switch from plsql to sql for a case statement - it would perform 'better' as a native sort of language feature - something the compiler generated optimal plsql byte code for when compiling plsql and optimal sql byte code for when compiling sql.

feature wise - equivalent.
implementation - not something we need to know really (and I don't feel like tracking it down, just isn't on our radar screens as developers/dbas)


conditional group by

hardik bhalani, November 20, 2010 - 6:04 am UTC

I have such a statements in my query

count(case when (ca.actionflag = 'F' and bc.benchcode = '1' )then 1
END) as CC1 ,
count(case when (ca.actionflag = 'F' and bc.benchcode = '2' )then 1
END) as CC2

which gives individaul result based on column name as if out of two rows
for row one it gives output like name of retrieved field,count1 value fetched above,0
for row two it gives output like name of retrieved field,0,count2 value fetched above

I want to combine these two rows in one row,hence the name of retrieved field is same,so that my output should be like name of retrieved field,count1 value fetched above,count2 value fetched above

Help.....its urgent.....


Tom Kyte
November 20, 2010 - 9:25 am UTC

say the output is:

CC1             CC2 
-----------     ----------
123             456



simply take your query (and call it Q) that produces that output and:


select r, decode( r, 1, cc1, cc2 ) cnt
  from (Q), (select 1 r from dual union all select 2 r from dual)
/



CASE and TimeStamp validation

Rajeshwaran, Jeyabal, June 27, 2011 - 10:14 am UTC

ods@V01> select * from nls_session_parameters;

PARAMETER                                          VALUE
-------------------------------------------------- ---------------------------------
NLS_LANGUAGE                                       AMERICAN
NLS_TERRITORY                                      AMERICA
NLS_CURRENCY                                       $
NLS_ISO_CURRENCY                                   AMERICA
NLS_NUMERIC_CHARACTERS                             .,
NLS_CALENDAR                                       GREGORIAN
NLS_DATE_FORMAT                                    DD-MON-RR
NLS_DATE_LANGUAGE                                  AMERICAN
NLS_SORT                                           BINARY
NLS_TIME_FORMAT                                    HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                               DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                 HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                            DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                  $
NLS_COMP                                           BINARY
NLS_LENGTH_SEMANTICS                               BYTE
NLS_NCHAR_CONV_EXCP                                FALSE

17 rows selected.

Elapsed: 00:00:00.07
ods@V01>


Below query ran from Central Timezone (CST)

ods@V01> select systimestamp ,  exp_dttm,
  2    case when systimestamp > exp_dttm
  3    then 'CLOSED' else 'OPEN' end as "status"
  4  from prty where prty_sk = 1;

SYSTIMESTAMP                             EXP_DTTM                                 status
---------------------------------------- ---------------------------------------- ------
27-JUN-11 09.50.48.986105 AM -05:00      27-JUN-11 05.42.34.328000 PM             OPEN

Elapsed: 00:00:00.04
ods@V01>


Below query ran from Indian Timezone(IST)

ods@V01> select systimestamp ,  exp_dttm,
  2    case when systimestamp > exp_dttm
  3    then 'CLOSED' else 'OPEN' end as "status"
  4  from prty where prty_sk = 1;

SYSTIMESTAMP                             EXP_DTTM                                 status
---------------------------------------- ---------------------------------------- ------
27-JUN-11 09.04.35.924273000 AM       27-JUN-11 05.42.34.328000 PM             CLOSED

Elapsed: 00:00:00.04
ods@V01>


Tom:

Why the query returns two different status when ran from different timezone? though we used systimestamp which is supposed to return the timestamp information of database server. ( We are on 10.2.0.4)
Tom Kyte
June 27, 2011 - 11:39 am UTC

show us the entire setup here, how did you get your sessions to be in different TZ's, what would we need to do to fully reproduce.

CASE and TimeStamp validation

Rajeshwaran, Jeyabal, June 27, 2011 - 11:55 am UTC

Tom:

how did you get your sessions to be in different TZ's

I took one session from client machine and ran the query (which is Central time zone) and asked offshore team to ran the same query (which is Indian Time zone). By this way i got session in different TZ (one in CST and other in IST)

what would we need to do to fully reproduce
I am not sure what you are looking for? you need create table and insert's ?
Tom Kyte
June 27, 2011 - 12:36 pm UTC

the setting of the clients timezone can affect the dates returned, also the manner in which a client connects can affect their returned date/time

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3489876004675#45435777641065


if you both select systimestamp from dual at the same time - by how much do your clocks differ and how are you each connecting?

CASE and TimeStamp validation

Rajeshwaran, Jeyabal, June 27, 2011 - 12:43 pm UTC

if you both select systimestamp from dual at the same time - by how much do your clocks differ and how are you each connecting?

If you see above both session's (IST & CST) returning the same result's

27-JUN-11 09.04.35.924273000 AM

how are you each connecting? - We connect using Oracle SQL Developer & SQL*Plus


Tom Kyte
June 27, 2011 - 12:56 pm UTC

umm, if you see above, you can see from above one is 9:04 and one is 9:50 - they were not run anywhere near the same time as each other.

one has a timezone in the display format, one does not (indicates different client settings)

both of you should run this:

select to_char( systimestamp, 'DD-MON-RR HH.MI.SSXFF AM TZR' ) from dual;


and tell us what you each see via cut and paste. you have different session settings going on here.


are you both connecting over the network, using the EXACT same listener.


CASE and TimeStamp validation

Rajeshwaran, Jeyabal, June 27, 2011 - 1:04 pm UTC

Tom:

I think i am incorrect with my question.

ods@V01> select systimestamp ,  exp_dttm,
  2    case when systimestamp > exp_dttm
  3    then 'CLOSED' else 'OPEN' end as "status"
  4  from prty where prty_sk = 1;

SYSTIMESTAMP                             EXP_DTTM                                 status
---------------------------------------- ---------------------------------------- ------
27-JUN-11 09.04.35.924273000 AM          27-JUN-11 05.42.34.328000 PM             CLOSED

Elapsed: 00:00:00.04
ods@V01>


Why this select statment is returning the status as CLOSED since Systimestamp(09:04 am) is **NOT** greater than exp_dttm (05:42 pm)? It should return status as OPEN right (as per simple math)?


Tom Kyte
June 27, 2011 - 1:13 pm UTC

will you run what I asked you to run? there is a timezone issue going on here.

You stated above that your nls format had the timezone in it, I don't see that in the above output. Please use the format I asked you to use.

This is getting messy, what you've posted so far doesn't add up - the times were not the same, the nls formats where not the same...

CASE and TimeStamp validation

Rajeshwaran, Jeyabal, June 27, 2011 - 11:53 pm UTC

Tom:

As you suggested I added the to_char( systimestamp, 'DD-MON-RR HH.MI.SSXFF AM TZR' ) to the query and ran from two session

From IST Timezone session, i see this

Wrote file afiedt.buf

  1  select systimestamp ,
  2                          exp_dttm,
  3                          to_char( systimestamp, 'DD-MON-RR HH.MI.SSXFF AM TZR' ) as ts,
  4        case when systimestamp > exp_dttm
  5        then 'CLOSED' else 'OPEN' end as "status"
  6*     from prty where prty_sk = 1
ods@V01> /

SYSTIMESTAMP                             EXP_DTTM                                 TS                                    status
---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
27-JUN-11 11.24.10.414867 PM -05:00      28-JUN-11 09.53.21.140000 AM             27-JUN-11 11.24.10.414867 PM -05:00      CLOSED

Elapsed: 00:00:00.15
ods@V01>


from CST Timezone session i see this,

Wrote file afiedt.buf

  1  select systimestamp ,
  2                          exp_dttm,
  3                          to_char( systimestamp, 'DD-MON-RR HH.MI.SSXFF AM TZR' ) as ts,
  4        case when systimestamp > exp_dttm
  5        then 'CLOSED' else 'OPEN' end as "status"
  6*     from prty where prty_sk = 1
ods@V01> /

SYSTIMESTAMP                             EXP_DTTM                                 TS                                    status
---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
27-JUN-11 11.24.10.414867 PM -05:00      28-JUN-11 09.53.21.140000 AM             27-JUN-11 11.24.10.414867 PM -05:00      OPEN

Elapsed: 00:00:00.15
ods@V01>


Question:

1) As you see the session from CST Timezone is working fine since SYSTIMESTAMP is **not** greater than exp_dttm. why the session from IST Timezone is providing wrong result?
Tom Kyte
June 28, 2011 - 11:49 am UTC

would you please do that for ALL DATES, let's see what each and every date really is.

Use explicit formats

DarrenL, June 28, 2011 - 5:46 am UTC

@Rajeshwaran

what is the datatype of EXP_DTTM? if its a TIMESTAMP, then implicit conversion (using TIME_ZONE offset to shift EXP_DTTM back to UTC) will occur and sys_extract_utc() will be applied to both columns during the ">" compare.

eg if you have it as timestamp only, in your case 28-JUN-11 09.53.21.140000 AM will have have to be converted to a TZ..ie 28-JUN-11 09.53.21.140000 AM + 05:30 (for IST) and then jumped back to 4.23 AM to get it to UTC. this would then be compared to 27-JUN-11 11.24.10.414867 PM -05:00 which would jump forward to 4.24 AM UTC. so 4.24AM UTC>4.23AM UTC.

CASE and TimeStamp validation

Rajeshwaran, Jeyabal, June 28, 2011 - 9:07 am UTC

Yes Exp_Dttm column in Timestamp(6).

ods@V01> desc prty;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ----------------------
 EFF_DTTM                                                                NOT NULL TIMESTAMP(6)
 EXP_DTTM                                                                         TIMESTAMP(6)
 ................

Tom Kyte
June 28, 2011 - 12:06 pm UTC

then see above...

Flag for Count of Employees in a department

Umesh Kasturi, April 24, 2012 - 5:20 am UTC

Hi Tom,
Note :Referring to the standard emp,dept tables with 10,20,30,40 departments.

Will it be possible to display the follwing columns

Dept_with_emp_count_1_to_3,dept_with_emp_count_4_to_6,dept_with_Emp_count_7_to_10,dept_with_Emp_count_11_to_55,dept_with_Emp_count_Above_15
10,20,30,0,0
Where ever null values 0 (zero) needs to be displayed


The above columns should display 1 based on the number of employees in the departments
Tom Kyte
April 24, 2012 - 7:50 am UTC

please specify this a tad bit better.


How, in your output, do you differentiate between 1_to_3 and 4_to_6, you have what appears as one line of output:


10,20,30,0,0


How do you know which are 1_to_3, 4_to_6 and so on????

How is best to write this type of query

Yogesh, April 28, 2013 - 11:17 pm UTC

Hi Tom,

I always encounter queries like this where the main I want let's say "student" and then the second query i do not want "student". How can I do it in one pass or possibly avoid the union all together?


select substr(obj_nm, 1,4) as student_ID,a.attrib_id as ATTRIB_ID,oav.attrib_val as ATTRIB_VAL
from main_campus.accom_fea_vals oav, main_campus.accom_fea_obj_attrib oa, main_campus.accom_fea_attribs a, main_campus.accom_fea_objs o
where
oav.lcle = 'university' and
OAV.budget_atributes = OA.budget_atributes
and oa.budget_id = a.budget_id
and oa.budget_status = o.budget_status and o.obj_type_nm = 'STUDENT'
union
select substr(p.obj_nm, 1,4) as student_ID,a.attrib_id as ATTRIB_ID,oav.attrib_val as ATTRIB_VAL
from main_campus.accom_fea_vals oav, main_campus.accom_fea_obj_attrib oa, main_campus.accom_fea_attribs a, main_campus.accom_fea_objs o, main_campus.accom_fea_objs p
where
oav.lcle = 'university' and
OAV.budget_atributes = OA.budget_atributes
and oa.budget_id = a.budget_id
and oa.budget_status = o.budget_status
and o.prnt_obj_id = p.obj_id
and o.obj_type_nm != 'STUDENT'
Tom Kyte
April 30, 2013 - 2:19 pm UTC

no schema, no description of schema, no look, no inserts to play with... no look

it looks however like an entity attribute value model, they are very secure. You can get data in easily, but never get it back out again. very secure...

follow up

A reader, May 01, 2013 - 4:30 am UTC

Tom
Thanks for looking at the query. I just wanted to show how I can deal with query of that nature where I want "the student" and the need to use **union** to join the same query to exclude "student" in the join clause of the second query.......any thoughts ?
Tom Kyte
May 06, 2013 - 1:37 pm UTC

same comment.

in information about the schema from you, no guessing from me.

To Yogesh

David P, May 01, 2013 - 6:27 am UTC

The only difference between the two pieces is that the second one does another join, and returns p.obj_nm instead of o.obj_nm
You could re-do it as an outer join to p when o.obj_type_nm != 'STUDENT' and use a CASE expression to pick the right value in the select list:

select substr(CASE when o.obj_type_nm = 'STUDENT' then o.obj_nm else p.obj_nm END, 1,4) as student_ID
,a.attrib_id as ATTRIB_ID, oav.attrib_val as ATTRIB_VAL
from main_campus.accom_fea_vals oav
join main_campus.accom_fea_obj_attrib oa ON OAV.budget_atributes = OA.budget_atributes
join main_campus.accom_fea_attribs a ON oa.budget_id = a.budget_id
join main_campus.accom_fea_objs o ON oa.budget_status = o.budget_status
LEFT OUTER JOIN main_campus.accom_fea_objs p on o.obj_type_nm != 'STUDENT' AND o.prnt_obj_id = p.obj_id
where
oav.lcle = 'university'
/

This is untested because you didn't give schema or test data.
Tom Kyte
May 06, 2013 - 1:49 pm UTC

or explain the schema.

this is not only untested, it assumes how the schema works... what relations there are and so on...

Thanks David !

A reader, May 01, 2013 - 6:05 pm UTC


cas statement vs case expression

Richard, October 23, 2013 - 12:02 pm UTC

Hi Tom,
What's the difference between case statement and case expression in SQL and PL/SQL? I could not find any difference by looking at the syntax. Thanks for your excellent service to oracle community.



Tom Kyte
November 01, 2013 - 8:45 pm UTC

they are the same.

A reader, October 24, 2013 - 1:37 pm UTC

Hi Tom, When you get a chance, could you please respond to my above queries?

Thanks & Regards,
Richard

diff.

Richard, October 29, 2013 - 2:57 am UTC

Hi Tom,
What's the difference between case statement and case expression in SQL and PL/SQL? I could not
find any difference by looking at the syntax. Thanks for your excellent service to oracle
community. I am eagerly waiting for your response.

differences between case statement vs case expression

Sokrates, November 01, 2013 - 9:06 pm UTC

a.
case expresssion ends with "end",
case statement ends with "end case"

b.
case statement can raise ORA-06592

sokrates@11.2 > select case when 1=0 then '?' end from dual;

C
-


sokrates@11.2 > exec case when 1=0 then dbms_output.put_line('?'); end case;
BEGIN case when 1=0 then dbms_output.put_line('?'); end case; END;

*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 1

Tom Kyte
November 01, 2013 - 9:49 pm UTC

yes, plsql has "statements" whereas sql has "expressions". In plsql you can use a statement as a bit of code or you can use an expression in sql. in sql, you can only use the expression


but for all intents and purposes - they are the same.

differences between case statement vs case expression

Michel cadot, November 18, 2013 - 11:18 am UTC


And to avoid the error you have to add an ELSE part:
SQL> exec case when 1=0 then dbms_output.put_line('?'); else null; end case;

PL/SQL procedure successfully completed.

Regards
Michel

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library