RE: Error when running SQL table function

Hi Birgitta

Thank you for that, it worked perfectly. I have changed the parameter to a VARCHAR(8) to save having to cast it each time and it runs like a charm.

All the best

Jonathan


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of midrange-l-request@xxxxxxxxxxxx
Sent: 26 July 2012 14:55
To: midrange-l@xxxxxxxxxxxx
subject: AW: Error when running SQL table function

Same problem as always.
SQL routines - Stored Procedures, User Defined Functions and User Defined Table Functions can be overloaded, that means routines with the same name but different parameter definitions can coexist within the same schema.
When passing character expressions they are interpreted as being VarChar, but your UDTF expects a character parameter.
Cast the parameter into CHAR(8) and the UDTF should be found.

select * from table(SCANLEDGER(Cast('5046839W' as Char(8))) X

Mit freundlichen Gr??en / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"

-----Urspr?ngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jonathan Mason
Gesendet: Thursday, 26.7 2012 13:14
An: midrange-l@xxxxxxxxxxxx
Betreff: Error when running SQL table function

Hi All

I have written the following UDF with the intention of returning a table of data consisting of records selected from a live and an archive file:
create function SCANLEDGER(iRefNo char(8))
returns table (File char(10), RefNo char(8), Account char(8),
Status dec(1,0), GLDate dec(8,0), GLSeq dec(7,0),
TranType char(2), GrossAmt dec(13,2), TaxAmt dec(13,2),
TranStat char(2))
language SQL
begin
return
select 'LEDGER', REFNO, digits(AccNo) || AccSfx,
STATUS, GLDATE, GLSEQ, TRNTYP, GROSS, TAX, STATUS2
from LEDGER
where REFNO = iRefNo
union
select 'ARCHIVE', REFNO, digits(AccNo) || AccSfx,
STATUS, GLDATE, GLSEQ, TRNTYP, GROSS, TAX, STATUS2
from ARCHIVE
where REFNO = iRefNo
end
The select statement has been tested on its own and returns the expected results. I have created the function in library QGPL and it shows as a *SRVPGM, however when I try to call the function I get the following:

select * from table(SCANLEDGER('5046839W')) X SCANLEDGER in *LIBL type *N not found.
I have checked and QGPL is in the library list. If I qualify the function with QGPL I get the same error. The help text says to look at the joblog, but that doesn't show anything else even with debug switched on.

The syntax of the function was taken from an example in the IBM Info Centre and modified to fit.

Am I missing something obvious here?

Thanks

Jonathan


Proud partner of The Ageas Bowl and the Ageas Salisbury International Arts Festival.

Registered Address: Ageas House Tollgate Eastleigh Hampshire SO53 3YA Registered Number: 354568 England
Authorised and regulated by the Financial Services Authority

This e-mail together with any attachments are intended for the addressee only and may be private and confidential. If you are not the intended recipient, or the person responsible for delivering it to the intended recipient, you must not open any attachments, or copy, disclose, distribute, retain or use this e-mail, including any attachments, in any way whatsoever; please return it to us immediately using the reply facility on e-mail.

Consider the environment and think before you print this email.