no, you can't select stored procedure like that, try this :
SELECT (select BOSDRAMT from OSAcctDrAmt(s.bDrAmt, uf.bDr, uf.bCr)) AS bBalDr
FROM tAccountsJV t
JOIN sAccountsJV s
ON s.iPID = t.iID
AND s.iAcctID = 485
LEFT JOIN uOSAccounts uf
ON uf.iPID = t.iID
OR uf.iSID = t.iID ;
:), i am not testing this procedure before, but i do with my own procedur and it's work fine.

with regards,
agung

----- Original Message -----
From: Namit Nathwani
To: firebird-support@yahoogroups.com
Sent: Wednesday, December 08, 2004 2:31 PM
Subject: [firebird-support] "Function Unknown" How to utilize Stored Procedures return value in a SELECT statement?


Hi all

I have a stored procedure as below. The return value of the stored
procedure I want to use in the SELECT statement of mine. Currently I have
tried to utilize it this way and I receive an error stating that the
"Function Unknown OSACCTDRAMT". Upper Lower case of the function name does
not seem to be a problem.

SELECT OSAcctDrAmt(s.bDrAmt, uf.bDr, uf.bCr) AS bBalDr
FROM tAccountsJV t
JOIN sAccountsJV s
ON s.iPID = t.iID
AND s.iAcctID = 485
LEFT JOIN uOSAccounts uf
ON uf.iPID = t.iID
OR uf.iSID = t.iID ;

The Stored Procedure is as follows;

SET TERM ^ ;

CREATE PROCEDURE OSACCTDRAMT (
BDRAMT DOUBLE PRECISION,
BDR DOUBLE PRECISION,
BCR DOUBLE PRECISION)
RETURNS (
BOSDRAMT DOUBLE PRECISION)
AS
begin
bOSDrAmt = CASE WHEN (bDrAmt > 0 AND bCr > 0) THEN bDrAmt - bCr
WHEN (bDrAmt > 0 AND bDr > 0) THEN bDrAmt - bDr
WHEN (bDr IS NULL AND bCr IS NULL) THEN bDrAmt
ELSE 0 END ;

suspend;
end
^

SET TERM ; ^

GRANT EXECUTE ON PROCEDURE OSACCTDRAMT TO SYSDBA;




Regards
Bhavbhuti
___________________________________________
Softwares for Indian Businesses at:
http://ahmedabad.sancharnet.in/vso_ad1/
namitbn@...
___________________________________________


----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.807 / Virus Database: 549 - Release Date: 07/12/2004


[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor

Get unlimited calls to

U.S./Canada




------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




[Non-text portions of this message have been removed]