Using parameters in a WHERE clause

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.

Answer 1

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',....))

Answer 2

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.