Select Case PL/SQL Procedure 2

[CassidyHunt]

I am getting the impression that two departments that didn't communicate created the select case for SQL side of oracle but not the PL/SQL side in 8i.

i.e.

select table1.field1
  case
  when table1.field2 is null 
  then
      (select table2.field1
       from table2
       where table2.field2 = table1.field1)
  else
      table1.field2
  end
  as field2
from table1

Now in PL/SQL

declare
cursor my_cur is
(select table1.field1
  case
  when table1.field2 is null 
  then
      (select table2.field1
       from table2
       where table2.field2 = table1.field1)
  else
      table1.field2
  end
  as field2
from table1);

begin
    Open my_cur;
    for cur_row in my_cur
    loop
        dbms_output.put_line(cur_row.field1 ||'     '|| cur_row.field2);
    end loop;
end;

Haven't tested this exact code above to make sure it works but I have tested other code that I know works. For some reason you can not use nested selects, case statements, nvl, or decode in PL/SQL.

I guess to make it short, am I correct about this or do I need to adjust my code in PL/SQL to accomplish this? (Hoping for the code adjustment that does not require a view) If I am right that this is not support in PL/SQL then I wonder why would you do that to people.

Thanks

Cassidy

 

[SantaMufasa]

Cassidy,

Let's look at each code feature individually:

1) CASE statements: work in 8i SQL but not in 8i PL/SQL; CASE statements fully functional in both SQL and PL/SQL.

2) Nested SELECTS: should work fine in 8i PL/SQL.

3) NVL() function: should work fine in all versions of PL/SQL.

4) DECODE() function: works only within a SQL statement within PL/SQL on all versions.

Cassidy, if this information differs from your experience, post your code and we can work through it until everything reconciles.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Sorry...I just noticed that I left off "9i and 10g" in the above item 1. It should have read:

1) CASE statements: work in 8i SQL but not in 8i PL/SQL; CASE statements fully functional in both SQL and PL/SQL for 9i and 10g.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[CassidyHunt]

Here is a lump together of everything I tried.

-- Created on 7/18/2005 by CHUNT 
declare 
  -- Local variables here
  jobid varchar2(80) := 'Q05458';
  
  cursor case_statement is
      select c.line_no,
      case
          when c.part_id is not null
          then
               c.part_id
          else
               (select d.supply_base_id
               from demand_supply_link d
               where d.demand_base_id = jobid
               and d.demand_seq_no = c.line_no
               and d.supply_type = 'WO')
          end 
          as part_id
      from  cust_order_line c
      where c.cust_order_id = jobid;
      
  cursor nested_select is
         select distinct i.part_id,
                (select p.description
                from part
                where p.id = i.part_id)
                as description
             from inventory_trans i;
             
  cursor nvl_statement is
       select c.line_no,
              nvl(c.part_id,'No Data') as part_id
       from cust_order_line c
       where c.cust_order_id = jobid;
       
  cursor decode_statement is
         select decode(c.line_no,1,'One',2,'Two',c.line_no) as line_no
         from cust_order_line c
         where c.cust_order_id = jobid;
         
  procedure prt(x varchar2) is
  begin
       dbms_output.put_line(x);
  end;
       
begin
  
  
  
  for cur_row in case_statement
  loop
      prt(cur_row.line_no || '     ' || cur_row.part_id);
  end loop;
  
  
  
  for cur_row in nested_select
  loop
      prt(cur_row.part_id || '    ' || cur_row.description);
  end loop;
  
 
  
  for cur_row in nvl_statement
  loop
      prt(cur_row.line_no || '     ' || cur_row.part_id);
  end loop;
  
 
  
  for cur_row in decode_statement
  loop
      prt(cur_row.line_no);
  end loop;
  
  
end;

I get a PLS-00103 on CASE and SELECT.

Encountered the symbol "Select" or "Case"

I get an invalid number on the decode statement. I was wrong about NVL. I just had a syntax error.

BTW I broke them down individually to and tested them then lumped them up here to shorten the code segment.


Let me know

 

[CassidyHunt]

Just realized that I have to keep the same data type for decode to work. Had to slap my head on that. I guess nested selects is the only one that is a real problem.

why does that not work?

 

[SantaMufasa]

1) CASE statements: work in 8i SQL but not in 8i PL/SQL...

...so your problem is not with nested SELECTs...the problem is that PL/SQL in 8i cannot deal with CASE statements, even when they appear within a SQL statement. Therefore, I recommend your trying the following code (replacing your CASE syntax with DECODE):

cursor case_statement is
      select c.line_no,
      decode(c.part_id
               ,null,(select d.supply_base_id
                      from demand_supply_link d
                      where d.demand_base_id = jobid
                      and d.demand_seq_no = c.line_no
                      and d.supply_type = 'WO')
               ,c.part_id) as part_id
      from  cust_order_line c
      where c.cust_order_id = jobid;

Your other nested SELECT has a slight syntax error...you are missing the alias "p":

  cursor nested_select is
         select distinct i.part_id,
                (select p.description
                from part [b]p[/b]
                where p.id = i.part_id)
                as description
             from inventory_trans i;

Let us know if these suggestions resolve your problems. (I'm leaving in just a few minutes for an appointment, so if you don't hear back from me quickly, it's not because I'm ignoring you.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[CassidyHunt]

Ok I tested the SQL to be sure. But I still get the same error on the PL/SQL side.

Here is the code I am using.

-- Created on 7/18/2005 by CHUNT 
declare 
  -- Local variables here
  jobid varchar2(80) := 'Q05458';
       
  cursor nested_select is
         select distinct i.part_id,
                (select p.description
                from part p
                where p.id = i.part_id)
                as description
             from inventory_trans i;
             
        
  procedure prt(x varchar2) is
  begin
       dbms_output.put_line(x);
  end;
       
begin
     
  for cur_row in nested_select
  loop
      prt(cur_row.part_id || '    ' || cur_row.description);
  end loop;
   
end;

Here is the error I am getting:

 

[SantaMufasa]

Cassidy,

It appears that although the nested SELECTS work in 8i outside of PL/SQL, they don't work inside of PL/SQL. Therefore, you can use the following code that achieves the same results:

-- Created on 7/18/2005 by CHUNT 
declare 
  -- Local variables here
  jobid varchar2(80) := 'Q05458';
       
  cursor nested_select is
         select distinct i.part_id, p.description
                from part p, inventory_trans i
                where p.id(+) = i.part_id;

  procedure prt(x varchar2) is
  begin
       dbms_output.put_line(x);
  end;
       
begin
     
  for cur_row in nested_select
  loop
      prt(cur_row.part_id || '    ' || cur_row.description);
  end loop;
   
end;

Let us know if this is an acceptable resolution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

BTW you may use CASE within DYNAMIC SQL: just declare REF CURSOR variable and open that cursor for string:

...
open refCurVal for 'select CASE when 1=0 then 0 else 1 END from dual';

But as you may see for you specific case there's no need in CASE at all

Regards, Dima

http://seminihin.narod.ru

 

[CassidyHunt]

Thank you. Answered my question. I will just have to use views when I want to use nested selects or case statements. That is interesting you can use a ref cursor to handle SQL that you can't handle directly in PL/SQL.

I'm looking for the light of going 10G for production in January and being able to clean up my objects and views.

Thanks

Cassidy