Formatted Search If Statement

[Former Member]

Hi

I have a formatted search as below for a UDF in outgoing payment screen

Select"CardFName" From OCRD Where "CardCode" = $[OVPM."CardCode"]

The problem is that if user do not select any card code in outgoing payment which means $[OVPM."CardCode"] is null, I would like to just select all foreign name from business partner master data and allow them to select manually from the list. I tried to do the query like below but encountered error "Incorrect syntax near IF". Please kindly advise

IF IFNULL($[OVPM."CardCode"],'')  = ''

BEGIN

SELECT "CardFName" FROM OCRD

ELSE

SELECT "CardFName" FROM OCRD WHERE "CardCode" = $[OVPM."CardCode"]

END

Note: SAP B1 HANA

Regards

Yvaine

[Former Member · Accepted solution]

Hi Yvaine,

Please refer to this webpage:

SAP HANA SQL and System Views Reference - SAP Library

You will find that IF statements are not supported in SAP HANA.

You can use CASE/WHEN as alternative. Look under the topic 'Expressions' in the above web link.

Kind Regards,

Nick Lakasas

[Former Member · Accepted solution]

Thank you so much. I am afraid we can only put case in select statement for eg:

Select CASE $[OVPM."CardCode"] WHEN '1' THEN 'Wrong' ELSE 'Correct' END AS Result

I am not able to put the case outside the query as the first condition to determine which query to execute for the UDF, for eg:

CASE $[OVPM."CardCode"] WHEN '1' THEN

SELECT * FROM OCRD

ELSE

SELECT * FROM OITM

[Former Member · Accepted solution]

Hi Yvaine,

Check this:

declare card NVARCHAR(50);
SELECT COALESCE($[OVPM."CardCode"],'') into card FROM "DUMMY";
IF (:card  = '') THEN
SELECT "CardFName" FROM OCRD;
ELSE
SELECT COALESCE("CardFName",'no contact') FROM OCRD WHERE "CardCode" = $[OVPM."CardCode"];
END IF;

Kind regards,

Radek

[Former Member · Accepted solution]

Thank you so much Radek, work beautifully

[Former Member · Accepted solution]

Looks like I was incorrect stating that "IF statements are not supported".

Best I read the guide I suggested

Apologies if I caused any confusion.

Kind Regards,

Nick Lakasas

[Former Member]

Hi Kong,

try this,

IF ISNULL($[OVPM."CardCode"],'')  = ''

BEGIN

SELECT "CardFName" FROM OCRD

ELSE

SELECT "CardFName" FROM OCRD WHERE "CardCode" = $[OVPM."CardCode"]

END

Thanks,

Harshal

[Former Member]

Hi Harshal

Thank you for your reply. However i still encountered the same error.

Regards

Yvaine

[Johan_H]

Hi Yvaine,

Have you tried without the IFNULL function ? I.e. assume that the variable will automatically return '' in case of null.

Alternatively please test this:

IF $[OVPM."CardCode"] IS NULL OR $[OVPM."CardCode"] = ''

Regards,

Johan

[Former Member]

Hi Johan

Thank you. I think the problem is with the IF statement. I tried to use the if statement without IFNULL eg "IF $[OVPM."CardCode"] = 'ABC'" is still giving same error.

Regards

Yvaine

[Johan_H]

Hi Yvaine,

I am not sure if HANA syntax understands the CASE statement, but I know of cases where CASE will work where IF will not.

Regards,

Johan