Solved: User defined functions dont works with where

Former Member · ‎11-14-2007

Hi,

in a production application i must, change decodes to user defined functions, and using functions the where clause dont works, to better understand i created a test case.

the DDL is:

CREATE TABLE account (

id VARCHAR(8) NOT NULL,

account_number VARCHAR(9),

name VARCHAR(30),

subaccount_Type VARCHAR(2),

PRIMARY KEY (id)

)

//

CREATE TABLE customer (

id VARCHAR(8) NOT NULL,

name VARCHAR(30),

account_order VARCHAR(2),

PRIMARY KEY (id)

)

//

CREATE TABLE product (

id VARCHAR(8),

name VARCHAR(30),

account_order VARCHAR(2),

PRIMARY KEY (id)

)

//

CREATE TABLE balance (

account_id VARCHAR(8),

subaccount_id VARCHAR(8)

)

//

populate with some data

INSERT INTO account VALUES ('00000001','1','INVENTORY','PD')

//

INSERT INTO account VALUES ('00000002','2','TO RECEIVE','CS')

//

INSERT INTO customer VALUES ('00000003','CUSTOMER 1','01')

//

INSERT INTO customer VALUES ('00000004','CUSTOMER 2','02')

//

INSERT INTO product VALUES ('00000005','PRODUCT 1','01')

//

INSERT INTO product VALUES ('00000006','PRODUCT 2','02')

//

INSERT INTO balance VALUES ('00000001','00000001')

//

INSERT INTO balance VALUES ('00000001','00000005')

//

INSERT INTO balance VALUES ('00000001','00000006')

//

INSERT INTO balance VALUES ('00000002','00000002')

//

INSERT INTO balance VALUES ('00000002','00000003')

//

INSERT INTO balance VALUES ('00000002','00000004')

//

the old SQL with DECODES:

SELECT

a.account_number ||

DECODE(INDEX(b.account_id,b.subaccount_id),1,'',

DECODE(a.subaccount_Type,'CS',c.account_order,'PD',p.account_order)

) account_number,

DECODE(INDEX(b.account_id,b.subaccount_id),1,a.name,

DECODE(a.subaccount_Type,'CS',c.name,'PD',p.name)

) account_name

FROM balance b, customer c, product p, account a

WHERE

a.id = account_id AND

c.id(+) = b.subaccount_id AND

p.id(+) = b.subaccount_id AND

b.account_id = '00000001'

functions to avoid DECODES:

CREATE FUNCTION APP_NAME.getAccountNumber (

ACCOUNT_ID CHAR(8),

SUBACCOUNT_ID CHAR(8)

) RETURNS CHAR(20) AS

VAR accountNumber CHAR(20);

subaccountType CHAR(2);

name CHAR(30);

accountOrder CHAR(2);

BEGIN

TRY

SELECT account_number, subaccount_Type, name FROM APP_NAME.Account WHERE id = :ACCOUNT_ID;

WHILE $rc = 0 DO BEGIN

FETCH INTO :accountNumber, :subaccountType, :name;

END;

CATCH

IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

IF (SUBACCOUNT_ID != ACCOUNT_ID) THEN BEGIN

IF subaccountType = 'CS' THEN BEGIN

TRY

SELECT account_order, name FROM APP_NAME.Customer WHERE id = :SUBACCOUNT_ID;

WHILE $rc = 0 DO BEGIN

FETCH INTO :accountOrder, :name;

SET accountNumber = accountNumber || accountOrder;

END;

CATCH

IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

END;

IF subaccountType = 'PD' THEN BEGIN

TRY

SELECT account_order, name FROM APP_NAME.Product WHERE id = :SUBACCOUNT_ID;

WHILE $rc = 0 DO BEGIN

FETCH INTO :accountOrder, :name;

SET accountNumber = accountNumber || accountOrder;

END;

CATCH

IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

END;

RETURN accountNumber;

END;

//

CREATE FUNCTION APP_NAME.getAccountName (

ACCOUNT_ID CHAR(8),

SUBACCOUNT_ID CHAR(8)

) RETURNS CHAR(30) AS

VAR accountNumber CHAR(20);

subaccountType CHAR(2);

name CHAR(30);

accountOrder CHAR(2);

BEGIN

TRY

SELECT account_number, subaccount_Type, name FROM APP_NAME.Account WHERE id = :ACCOUNT_ID;

WHILE $rc = 0 DO BEGIN

FETCH INTO :accountNumber, :subaccountType, :name;

END;

CATCH

IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

IF (SUBACCOUNT_ID != ACCOUNT_ID) THEN BEGIN

IF subaccountType = 'CS' THEN BEGIN

TRY

SELECT account_order, name FROM APP_NAME.Customer WHERE id = :SUBACCOUNT_ID;

WHILE $rc = 0 DO BEGIN

FETCH INTO :accountOrder, :name;

SET accountNumber = accountNumber || accountOrder;

END;

CATCH

IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

END;

IF subaccountType = 'PD' THEN BEGIN

TRY

SELECT account_order, name FROM APP_NAME.Product WHERE id = :SUBACCOUNT_ID;

WHILE $rc = 0 DO BEGIN

FETCH INTO :accountOrder, :name;

SET accountNumber = accountNumber || accountOrder;

END;

CATCH

IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

END;

RETURN name;

END;

//

new SQL using User Defined FUNCTIONS:

SELECT

APP_NAME.getAccountNumber(b.account_id,b.subaccount_id) account_number,

APP_NAME.getAccountName(b.account_id,b.subaccount_id) account_name

FROM balance b

WHERE

b.account_id = '00000001'

but this SQL dont returns any row, but removing the WHERE clause returns.

using something like:

SELECT

APP_NAME.getAccountNumber(b.account_id,b.subaccount_id) account_number,

APP_NAME.getAccountName(b.account_id,b.subaccount_id) account_name

FROM balance b

this sql returns all rows, and works like the version with DECODE but without using the WHERE clause.

is this a BUG or a limitation of user defined function?

i'm using MaxDB version 'X64/LIX86 7.6.03 Build 007-123-157-515'

thanks for any help

Clóvis

lbreddemann · ‎11-19-2007

Hi Clovis,

I did also check with 7.6.03 Build 9. This also works, but only with a simpler function:

create function foo (test varchar) returns varchar as

return test;

SELECT

foo(b.account_id&b.subaccount_id) account_number,

foo(b.account_id&b.subaccount_id) account_name

FROM balance b

WHERE

b.account_id = '00000001'

ACCOUNT_NUMBER ACCOUNT_NAME

0000000100000001 0000000100000001

0000000100000005 0000000100000005

0000000100000006 0000000100000006

So it's not a problem with user defined functions in general...

KR Lars

BTW:

this is one of the best prepared problem descriptions I ever saw on this forum.

DDL, DML, expected result and actual result.

That's really something one can work with!

Former Member · ‎05-27-2008

Hi Clovis,

sorry for the delay, but I have been on holiday last week.

But today I found time to analyze the problem. I think this is a bug, which has to do with user defined functions indirectly only.

I assume you used SQL Studio to verify the results. SQL Studio internally uses FETCH LAST, which may return no result in some rare cases (for example if user defined functions are involved and if the last row found by the strategy does not fulfill the qualification).

We will fix this bug as soon as possible.

Best Regards,

Thomas

Former Member · ‎05-22-2008

Hi,

I remember some trouble with functions including selects, used in selects, in case no resulttablenames were specified.

Did you check if

declare <some_name> cursor for

select ...

and then

fetch <the same name> into ...

in your functions and having different names for every single select in (at least

with every function having different names for all selects in that one function) ?

There were users who succeeded with similar version of MaxDB after such a change.

Elke

Example:

CREATE FUNCTION KERN.GETACCOUNTNUMBER (

ACCOUNT_ID CHAR(8),

SUBACCOUNT_ID CHAR(8)

) RETURNS CHAR(20) AS

VAR ACCOUNTNUMBER CHAR(20);

SUBACCOUNTTYPE CHAR(2);

NAME CHAR(30);

ACCOUNTORDER CHAR(2);

BEGIN

TRY

DECLARE ANO_1 CURSOR FOR

SELECT ACCOUNT_NUMBER, SUBACCOUNT_TYPE, NAME

FROM KERN.ACCOUNT WHERE ID = :ACCOUNT_ID;

WHILE $RC = 0 DO BEGIN

FETCH ANO_1 INTO :ACCOUNTNUMBER, :SUBACCOUNTTYPE, :NAME;

END;

CATCH

IF $RC <> 100 THEN STOP ($RC, 'unexpected error');

IF (SUBACCOUNT_ID != ACCOUNT_ID) THEN BEGIN

IF SUBACCOUNTTYPE = 'CS' THEN BEGIN

TRY

DECLARE ANO_2 CURSOR FOR

SELECT ACCOUNT_ORDER, NAME

FROM KERN.CUSTOMER WHERE ID = :SUBACCOUNT_ID;

WHILE $RC = 0 DO BEGIN

FETCH ANO_2 INTO :ACCOUNTORDER, :NAME;

SET ACCOUNTNUMBER = ACCOUNTNUMBER || ACCOUNTORDER;

END;

CATCH

IF $RC <> 100 THEN STOP ($RC, 'unexpected error');

END;

IF SUBACCOUNTTYPE = 'PD' THEN BEGIN

TRY

DECLARE ANO_3 CURSOR FOR

SELECT ACCOUNT_ORDER, NAME

FROM KERN.PRODUCT WHERE ID = :SUBACCOUNT_ID;

WHILE $RC = 0 DO BEGIN

FETCH ANO_3 INTO :ACCOUNTORDER, :NAME;

SET ACCOUNTNUMBER = ACCOUNTNUMBER || ACCOUNTORDER;

END;

CATCH

IF $RC <> 100 THEN STOP ($RC, 'unexpected error');

END;

RETURN ACCOUNTNUMBER;

END;

lbreddemann · ‎11-17-2007

Hi Clovis,

I checked this on my Win32 Installation 7.7.02 Build 16 and it worked as it should.

So I think this is likely to be no limitation by design but probably a bug in that version.

Did you check it with a simpler function?

Anyhow there ARE already some known bugs entered into the bugtracking system (http://www.sapdb.org/webpts), also for MaxDB => 7.6.03.

So as long as there is no version with the fix available I'd try to rewrite the statemetns e.g. select the "decoded" data from a view.

KR Lars

User defined functions dont works with where

Accepted Solutions (1)

Accepted Solutions (1)

Answers (3)

Answers (3)

Iterating through JSONModel with multiple nested a...

Re: Connecting SAP CAP with SAP Cloud ALM

How to use CopyProvider on Table created by TypeSc...

How to execute/fire onPost when user press enter o...

Re: Connecting SAP CAP with SAP Cloud ALM