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