No data found exception: no data found error HANA SAP Business One

[former_member400045]

Dear Experts,

We are using SAP Business One HANA 9.2 pl 07 and we have the following question. We want to implement a control when entering a double LicTradNum on the Business Partner master data

We are using the following query

DECLARE doubleafm NVARCHAR(32);

DECLARE a NVARCHAR(32);

DECLARE CardType NVARCHAR (2);

SELECT $[OCRD."LicTradNum".0] INTO doubleafm FROM DUMMY; SELECT $[OCRD."CardType".0] into CardType from DUMMY;

SELECT "CardCode" into a FROM OCRD WHERE "LicTradNum" = $[OCRD."LicTradNum".0] AND "CardType" = $[OCRD."CardType".0] ;

IF IFNULL(:a, '') = '' THEN (SELECT 'OK' FROM DUMMY); ELSE (SELECT 'Please Check' FROM DUMMY); END IF;

If the LicTradNum exists, the query works fine. If it doesn't we get the following error

1). [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;1299 no data found: "DB"."tmp_qry_sp_63a41413-9273-4932-a57a-ae4b1ff54726": line 10 col 1 'Ερωτήματα' (OUQR) (at pos 258): [1299] (range 3) no data found exception: no data found

Could you please advise?

Kind Regards

Gerasimos

[former_member400045 · Accepted solution]

Hello Johan,

Thanks for your answer. However, that is not the problem. The problem derives from the fact that the variable :a is null because the query (SELECT "CardCode" into a FROM OCRD WHERE "LicTradNum" = $[OCRD."LicTradNum".0] AND "CardType" = $[OCRD."CardType".0]) doesn't return a value. So basically, the variable doesn't have a value.

Gerasimos

[Johan_H · Accepted solution]

Hi Gerasimos,

Do you mean that IFNULL(:a,'')='' does not catch this?

How about encapsulating it in a subquery?

SELECT IFNULL((SELECT "CardCode"
               FROM OCRD 
               WHERE "LicTradNum" = $[OCRD."LicTradNum".0]
                 AND "CardType" = $[OCRD."CardType".0]),'') into a;

Regards,

Johan

[former_member400045 · Accepted solution]

Hello,

It worked! Thanks for the reply!

[Johan_H]

Hi Gerasimos,

You are not checking for an empty LicTradNum. Just add an IF clause, somthing like this:

IF IFNULL(:doubleafm, '') = '' THEN
    (SELECT 'Please enter LicTradNum' FROM DUMMY); 
ELSE
    IF IFNULL(:a, '') = '' THEN
        (SELECT 'OK' FROM DUMMY);
    ELSE
        (SELECT 'Please Check' FROM DUMMY);
    END IF;
END IF;

Regards,

Johan