Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

User defined functions dont works with where

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;

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;

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

Former Member
replied

Hi Clovis,

you're right - as soon as it looks cheaper to use the table scan this will be done.

To be on the save side you can try to add the

/+KEYACCESS/

hint to the select.

That way the query will always be processed via the KEY.

I will try to find out more about this error and forward it to the development.

KR Lars

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question