Hi all

I have the following SQL Pass Through (to FireBird backend) working well. I would like to
create the same SQL in FoxPro 2.5 DOS. I am not worried about the stored procedure as it can be
created here using IIF()'s, I guess <g>. I have the memory variables ready so it will be a
matter of switching ?piAcctID to m.AcctID.

I am more concerned how to get back similar number fo rows
from the JOIN and LEFT JOINS converted to FPD25.

Please advise.

SQL Statement

SELECT 'Accounts J.V.            ', t.cBk, t.iNo, t.dDt, ;
      (SELECT bOSDrAmt FROM OSAcctDrAmt(s.bDrAmt, uf.bDr, uf.bCr)) AS bBalDr, ;
      (SELECT bOSCrAmt FROM OSAcctCrAmt(s.bCrAmt, uf.bDr, uf.bCr)) AS bBalCr, ;
      s.mRemarks, t.iID ;
   FROM tAccountsJV t ;
      JOIN sAccountsJV s ;
         ON s.iPID = t.iID ;
            AND s.iAcctID = ?piAcctID ;
            AND t.dDt <= ?pdDt ;
      LEFT JOIN uOSAccounts uf ;
         ON uf.iPID = t.iID ;
            OR uf.iSID = t.iID

Store Procedure OSAcctDrAmt:

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
^

Store Procedure OSAcctCrAmt:

SET TERM ^ ;

CREATE PROCEDURE OSACCTCRAMT (
    BCRAMT DOUBLE PRECISION,
    BDR DOUBLE PRECISION,
    BCR DOUBLE PRECISION)
RETURNS (
    BOSCRAMT DOUBLE PRECISION)
AS
begin
    bOSCrAmt = CASE WHEN (bCrAmt > 0 AND bDr > 0) THEN bCrAmt - bDr
                    WHEN (bCrAmt > 0 AND bCr > 0) THEN bCrAmt - bCr
                    WHEN (bDr IS NULL AND bCr IS NULL) THEN bCrAmt
                    ELSE 0 END ;

  suspend;
end
^