Skip to Content
avatar image
Former Member

SAP HANA . Include WHERE caluse after IF or CASE Statement.

Hi All,

I'm trying to include WHERE clause in my query.

I'd like to do that if the parameter is passed to my procedure and it's not null.

Here is my script which gives me syntax error.

CREATE PROCEDURE WORKSHOP_01.SALES_PROCEDURE(in catId integer) as
BEGIN


SELECT 	WORKSHOP_01.CATEGORY.NAME AS CATEGORY_NAME,
		WORKSHOP_01.CATEGORY.ID AS CATEGORY_ID,
		SUM(WORKSHOP_01.PRODUCT.PRICE*WORKSHOP_01.SALES.QTY) AS SALES
FROM WORKSHOP_01.PRODUCT 
INNER JOIN WORKSHOP_01.SALES ON WORKSHOP_01.PRODUCT.ID = WORKSHOP_01.SALES.PRODUCTID
INNER JOIN WORKSHOP_01.CATEGORY ON WORKSHOP_01.CATEGORY.ID = WORKSHOP_01.PRODUCT.CATEGORYID


CASE :catId
	WHERE WORKSHOP_01.CATEGORY.ID == :catId
END


GROUP BY WORKSHOP_01.CATEGORY.ID, WORKSHOP_01.CATEGORY.NAME;


END;

Regards,

Arman

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 08, 2017 at 08:44 AM

    Such a kind of statement is not supported.

    But you can use the "like" predicate in combination with the "coalesce" function.

    ...
    
    DECLARE cat_id_where_value NVARCHAR(100) := COALESCE(TO_NVARCHAR(:catId),'%');
    
    SELECT ...
    FROM ...
    WHERE workshop_01.category.id LIKE :cat_id_where_value
    GROUP BY ...;
    
    ...
    

    The "coalesce" function cannot be used directly in the where clause, because you will get an error that '%' is not a valid number string (in case your parameter is null). Therefore a nvarchar variable is declared (you have to adjust the length to your needs) which gets either the value of your parameter (if not null) or the value '%'). So in case your parameter is null your condition will be ... workshop_01.category.id LIKE '%' ... which determines all values. If your parameter has a valid integer value your result will be restricted to that value.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded