locked
Using parameters in a WHERE clause RRS feed

  • Question

  • User-34860367 posted

    Hi all,

    My WHERE clause depends on parameter P_COMPANY. I tried to write PL/SQL but it displayed the error 

    • Error PL/SQL: ORA-00905: missing keyword 
    create or replace PROCEDURE SP_RET_STOCK (
       P_COMPANY               IN     VARCHAR2,
       RS                      OUT    SYS_REFCURSOR)
    IS
       P_RET_STATUS   VARCHAR2 (5);
    BEGIN
       OPEN RS FOR
           SELECT InvoiceDate,
                  Item,
                  SalePrice,
                  Quantity,
                  Category			  
           FROM SALE_INFO 
           WHERE SalePrice > 100
                 AND (CASE WHEN P_COMPANY = "A" 
                          THEN Item IN ('Apple', 'Orange', 'Mantarine') 
    					
                          WHEN P_COMPANY = "B" 
                          THEN Item IN ('Potato', 'Sweet Potato') 
    					  
                       WHEN P_COMPANY = "C" 
                       THEN Item IN ('Salad', '', 'Pear')
                     END)
                                                              
                 AND Category = "Vegetable 
    		 
          ORDER BY InvoiceDate ASC; 
       END IF;
       
       P_RET_STATUS := SQLCODE;
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          P_RET_STATUS := SQLCODE;
       WHEN OTHERS
       THEN
          P_RET_STATUS := SQLCODE;
    END SP_RET_STOCK;

    For example,

    If P_COMPANY = "A", I want the query as shown:

    SELECT InvoiceDate,
           Item,
           SalePrice,
           Quantity,
           Category			  
    FROM SALE_INFO 
    WHERE SalePrice > 100
          AND Item IN ('Apple', 'Orange', 'Mantarine')                               
          AND Category = "Vegetable 
    		 
    ORDER BY InvoiceDate ASC; 

    Anyone knows what wrong with above syntax, please help. Thanks an advance.

    Tuesday, February 2, 2016 8:54 PM

Answers

  • User198012145 posted

    you can implement this using 2 ways

    1. you can generate query string and then execute.

    2. use 2 conditions so if 1st true then it applies second else it skips i.e.

    WHERE ((P_COMPANY = 'A' AND ITEM IN ('Apple', 'Orange') OR (P_COMPANY = 'B'  AND ITEM IN ('Potato',....))

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 3, 2016 12:50 AM

All replies

  • User198012145 posted

    you can implement this using 2 ways

    1. you can generate query string and then execute.

    2. use 2 conditions so if 1st true then it applies second else it skips i.e.

    WHERE ((P_COMPANY = 'A' AND ITEM IN ('Apple', 'Orange') OR (P_COMPANY = 'B'  AND ITEM IN ('Potato',....))

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 3, 2016 12:50 AM
  • User-34860367 posted

    Hi,

    I tried second way, it worked but I am not sure what you meant in first one. Thank for your query logic. 

    Thursday, February 4, 2016 5:24 PM