Skip to Content
0

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

Feb 07, 2017 at 08:23 PM

52

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Feb 08, 2017 at 08:44 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded