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

[Former Member]

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

[pfefferf]

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