How to use 'IN' inside function to query dynamic data

Question

I have a function in which I'm querying data according to the passed on variables as follows:

    CREATE OR REPLACE FUNCTION IR.SRG (
       IR_item       IN VARCHAR2,
       IR_comp          VARCHAR2,
       IR_locn          VARCHAR2,
       IR_Type          VARCHAR2,
       IR_fromdate           DATE,
       IR_tilldate           DATE
    )
       RETURN NUMBER
       DETERMINISTIC
    IS
       IR_qty   NUMBER;
    BEGIN

      IF IR_Type = 'O' 
       THEN
            SELECT   SUM(QTY)
              INTO   IR_qty
              FROM   STOCK_LEDGER 
             WHERE                          
          ITEM_CODE = IR_item       AND   
                     LOCATION_CODE IN
                              DECODE(IR_locn,
                                 'ALL',
                                 '('
                                 || '''D2'', ''D4'', ''D5'', ''D11'''
                                 || ')',
                                 '(' || IR_locn || ')')

                     AND DOCUMENTDATE <= IR_TILLDATE
        AND DOCUMENTDATE >= IR_FROMDATE;
       END IF;

  RETURN (NVL (IR_QTY, 0));
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      RETURN 0;
END;
/

If the user passes D2 for the IR_locn parameter then the query should run for that particular location, if the user passes ALL, then the query should run for the four locations D2, D4, D5,D11 as specified.

I am not able to achieve it by using IN, the data returns no records.

I tried running the query using DUAL and the format for the IN clause looks fine.

Select DECODE ('ALL',
                       'ALL',
                       '('
                       || '''D2'', ''D4'', ''D5'', ''D11'''
                       || ')',
                       '(' || 'D5' || ')'
                    ) FROM DUAL

I am getting the results as follows as I am running it with ALL:

('D2', 'D4', 'D5', 'D11')

score 2 · Accepted Answer · edited May 23 '17 at 11:59

If the problem is still relevant! I just found a workaround to your problem, you create an Oracle defined collection.

CREATE OR REPLACE FUNCTION IR.SRG(
    IR_item IN VARCHAR2,
    IR_comp     VARCHAR2,
    IR_locn     VARCHAR2,
    IR_Type     VARCHAR2,
    IR_fromdate DATE,
    IR_tilldate DATE,
  )
 RETURN NUMBER DETERMINISTIC
IS
  IR_qty NUMBER;
  myLocations sys.odcivarchar2list; --collection
BEGIN
  if IR_TYPE       = 'O' then
    IF IR_locn = 'ALL' THEN
       myLocations := SYS.ODCIVARCHAR2LIST('D2','D4','D5','D11');
    ELSE
       myLocations := SYS.ODCIVARCHAR2LIST('D2');
    END IF;
    SELECT SUM(QTY)
    INTO IR_qty
    FROM STOCK_LEDGER
    WHERE ITEM_CODE    = IR_item
    AND LOCATION_CODE IN
    (SELECT column_value FROM TABLE(myLocations)
  )
  AND DOCUMENTDATE <= IR_TILLDATE
  AND DOCUMENTDATE >= IR_FROMDATE;
END IF;
RETURN (NVL (IR_QTY, 0));
EXCEPTION
  WHEN ZERO_DIVIDE THEN
   RETURN 0;
END;

The solution is taken from this link: Oracle PL/SQL - How to create a simple array variable?

Pay attention to : MyLocations: = SYS.ODCIVARCHAR2LIST ( 'D2'); 'D2' must be replaced by IR_locn. — Mohamad TAGHLOBI, Nov 21 '16 at 17:15

score 0 · Answer 2 · answered Nov 20 '16 at 12:13

0

Does this logic do what you want?

WHERE ITEM_CODE = IR_item AND 
      DOCUMENTDATE <= IR_TILLDATE AND
      DOCUMENTDATE >= IR_FROMDATE AND
      (LOCATION_CODE = IR_locn OR IR_locn = 'ALL')

answered Nov 20 '16 at 12:13

Gordon Linoff

1,242,037
58
646
786

I want to use 'ALL' as an identifier to query multiple locations and at data level there is nothing called 'ALL'. The user will enter either the single location code or 'ALL' while querying the data from the application. – user3625561 Nov 20 '16 at 12:20
@user3625561 . . . That is the logic that this answer implements -- assuming that `IR_locn` is the user input. – Gordon Linoff Nov 21 '16 at 02:09

score 0 · Answer 3 · answered Nov 20 '16 at 12:29

0

You can create a varchar2 variable (All_IR_locn) that contains the string the results of an if statement :

If IR_locn = 'ALL' then

    All_IR_locn := ''D2','D4','D5','D11'';

Else

   All_IR_locn := IR_locn;

End If;

In your query you replace :

DECODE(IR_locn,
                             'ALL',
                             '('
                             || '''D2'', ''D4'', ''D5'', ''D11'''
                             || ')',
                             '(' || IR_locn || ')')

By

(All_IR_locn)

Hope this can help

answered Nov 20 '16 at 12:29

Mohamad TAGHLOBI

581
5
11

Single quotes gave an error, so I used double quotes All_IR_locn := '''D2'', ''D4'',''D5'', ''D11'''; but it returns no data, same like before. – user3625561 Nov 20 '16 at 13:36
Did you execute the query directly from SQL Developper (TOAD or the device you use to run query) to see if there is any result ? SELECT SUM(QTY) INTO IR_qty FROM STOCK_LEDGER WHERE ITEM_CODE = IR_item AND LOCATION_CODE IN ('D2', 'D4', 'D5', 'D11') AND DOCUMENTDATE <= IR_TILLDATE AND DOCUMENTDATE >= IR_FROMDATE; – Mohamad TAGHLOBI Nov 21 '16 at 10:03
if i am directly specifying the codes (as running a static query), the query will run and it will output the correct data but the problem is while executing the query using decode or if functions. – user3625561 Nov 21 '16 at 10:06

How to use 'IN' inside function to query dynamic data

3 Answers3