Retrieve String While Using IN Clause

[getjbb]

I have a parameter coming into my package that has a value pertaining to a code I need to plug it into a where clause in a cusor.

According to one or all codes that is selected from a drop down list I need to use it in a where clause such as: 'where item_code in (code)'

I set up a code table for the drop down as follows.


If selection from dropdown is: The parameter coming into package is:

'ALL' 'AD', 'NB', 'VD', 'RF'
'AD' 'AD'
'NB' 'NB'
'VD' 'VD'
'RF' 'RF'

Below is part of the code:

CREATE OR REPLACE PACKAGE BODY UNRESLVD2 IS
PROCEDURE Create_AutoData ( a_code IN VARCHAR2 DEFAULT 'ALL') IS

CURSOR get_equal_recs_cur is
SELECT ACCOUNT,
TRANS_NUM,
DEPOSIT_DATE,
ITEM_CODE
FROM vw_trans_display,
WHERE vw_trans_display.ITEM_CODE IN < WHATEVER IS SELECTED FROM THE DROPDOWN VIA VARIABLE, a_code >
BEGIN
...
....
END

If 'ALL' is selected from the dropdown how can I send the string 'AD', 'NB', 'VD', 'RF' through the IN clause?

Should a decode be used here?

I tried:

where ( (DECODE(a_code,'ALL',(vw_trans_display.ITEM_CODE IN ('AD', 'NB', 'VD', 'RF' )), a_code))

but it did not work.

Thanks in advance for your help.

getjbb

 

[jaxtell]

I think this'll do what you're looking for.

SELECT ACCOUNT, 
                        TRANS_NUM,
                        DEPOSIT_DATE,
                        ITEM_CODE
      FROM  vw_trans_display, 
      WHERE  vw_trans_display.ITEM_CODE = a_code
union all
SELECT ACCOUNT, 
                        TRANS_NUM,
                        DEPOSIT_DATE,
                        ITEM_CODE
      FROM  vw_trans_display, 
      WHERE  vw_trans_display.ITEM_CODE in ('AD', 'NB', 'VD', 'RF' ) and 'ALL' = a_code

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[getjbb]

Hi Jaxtell,

Thanks, but I cannot use the union all because it returns duplicates and the snippet of code:

CURSOR get_equal_recs_cur is
SELECT ACCOUNT,
TRANS_NUM,
DEPOSIT_DATE,
ITEM_CODE
FROM vw_trans_display,
WHERE vw_trans_display.ITEM_CODE IN < WHATEVER IS SELECTED FROM THE DROPDOWN VIA VARIABLE, a_code >
BEGIN
...
....
END

acutally already has a union attached to it with over 10 fields being selected.

Actully it look like:

CURSOR get_equal_recs_cur is
SELECT ACCOUNT,
TRANS_NUM,
DEPOSIT_DATE,
ITEM_CODE
...
...
FROM vw_trans_display,
WHERE vw_trans_display.ITEM_CODE IN < WHATEVER IS
SELECTED FROM THE DROPDOWN VIA VARIABLE, a_code >
AND ...
AND ...
UNION
SELECT ACCOUNT,
TRANS_NUM,
DEPOSIT_DATE,
ITEM_CODE
...
...
FROM vw_trans_display,
WHERE vw_trans_display.ITEM_CODE IN < WHATEVER IS
SELECTED FROM THE DROPDOWN VIA VARIABLE, a_code >
AND ...
AND ...;
BEGIN
...
....
END

Is there any way I can avoid using a union?

Thanks

getjbb

 

[carp]

How about something like:

CREATE OR REPLACE PACKAGE BODY UNRESLVD2 IS          
PROCEDURE Create_AutoData  ( a_code       IN VARCHAR2 DEFAULT 'ALL') IS
          
CURSOR get_equal_recs_cur is
      SELECT ACCOUNT, 
                        TRANS_NUM,
                        DEPOSIT_DATE,
                        ITEM_CODE
      FROM  vw_trans_display, 
      WHERE  vw_trans_display.ITEM_CODE IN CASE
                                             WHEN a_code = 'ALL' THEN 'AD', 'NB', 'VD', 'RF' 
                                             ELSE a_code
                                           END
...
....
END

I haven't tested this, so you may need to tweak it a bit (e.g., embed the character string in parentheses, etc).

 

[getjbb]

carp,

I tried your example and I am getting an the following error:

PL/SQL: ORA-00905: missing keyword

Thanks

getjbb

 

[jaxtell]

If you'd prefer less unions, you could probably do

...
AND ((vw_trans_display.ITEM_CODE = a_code and a_code <> 'ALL') OR (w_trans_display.ITEM_CODE in ('AD', 'NB', 'VD', 'RF' ) and 'ALL' = a_code))
...

but it might have a negative impact on the cost.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[carp]

That's what I get for just doing a cut/paste of your code without really looking closely enough! You have a comma after your table name and I missed it. Also, as mentioned, it does take a little bit of playing around with the code to get it right.
I'll play around with this if I get a chance today and see what I can come up with. Right now, this compiles and returns values for a single code, but the 'ALL' still isn't working right:

create or replace procedure test_case(a_code IN VARCHAR2) as
cursor my_cursor is
      SELECT ACCOUNT, 
             TRANS_NUM,
             DEPOSIT_DATE,
             ITEM_CODE
       FROM  vw_trans_display 
      WHERE  ITEM_CODE IN (CASE
                              WHEN a_code = 'ALL' THEN '''AD'', ''NB'',''VD'',''RF''' 
                              ELSE a_code
                           END);
begin
   for i in my_cursor loop
      dbms_output.put_line(i.trans_num||' '|| i.item_code);
   end loop;
end;

I'm sure there's something simple I'm overlooking, and as soon as the caffeine (or SantaMufasa) kicks in, all will become clear!

 

[carp]

OK, here's a quick and dirty bit of code that works and demonstrates using either a ref cursor or a BULK COLLECT (which, depending on what else you are doing, may be faster):

CREATE OR REPLACE procedure APPS.test_case(a_code IN VARCHAR2) as
   TYPE t_cursor IS ref cursor;
   TYPE t_tab_type IS TABLE OF vw_trans_display%ROWTYPE INDEX BY PLS_INTEGER;
   my_cursor t_cursor;
   l_cmd VARCHAR2(4000) := 'SELECT *
                              FROM  vw_trans_display 
                             WHERE  ITEM_CODE IN ('||CASE
                                                        WHEN a_code = 'ALL' THEN '''AD'',''NB'',''VD'',''RF''' 
                                                        ELSE a_code
                                                     END||')';
   l_rec vw_trans_display%ROWTYPE;
   l_tab t_tab_type;
begin
   -- CURSOR METHOD 
   OPEN my_cursor FOR l_cmd;
   LOOP
      FETCH my_cursor INTO l_rec;
      EXIT WHEN my_cursor%NOTFOUND;
      dbms_output.put_line(l_rec.trans_num||' '|| l_rec.item_code);
   END LOOP;
   CLOSE my_cursor;
   -- TABLE METHOD 
   EXECUTE IMMEDIATE l_cmd BULK COLLECT INTO l_tab;
   FOR i IN l_tab.FIRST..l_tab.LAST LOOP
      dbms_output.put_line(l_tab(i).trans_num||' '|| l_tab(i).item_code);
   END LOOP;
END;

This should take care of your 'ALL' or a_code situation.

Since you are still omitting parts of your code, your second post makes it look like you are UNIONing the same queries over and over again. I assume you have different conditions in your AND clauses - in which case you might be able to use ORs to cover the different conditions and thus avoid the UNIONs. The comma after the table name also hints that there are other tables involved. If there are different sets of tables in each of the queries, then I think you are probably going to be stuck with the UNIONs.

 

[getjbb]

Thanks carp, I will use some of your suggestions in my code and I will let you know how it turn out.

getjbb

 

[getjbb]

Carp,

I changed a couple of my Unioning to Ors and it made a pick difference in runtime. Also I used the code to select the code string.

Again thanks.

getjbb

 

[SantaMufasa]

I changed a couple of my Unioning to Ors and it made a pick difference in runtime.

I presume you meant a big difference in runtime, and if so, was the big difference an improvement or a degradation in runtime?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[getjbb]

Hi SantaMufa,

I was typing faster then I was thinking therefore the wrong choice of words.

The change knocked about 5 minutes from receiving my results.

Every minute counts at my place of employment.

getjbb