Prolay Sarkar
Sub-query Problem January 18, 2009 04:59PM |
Arie
Re: Sub-query Problem January 19, 2009 11:03AM |
Peter Holemans
Re: Sub-query Problem January 19, 2009 11:04AM |
SELECT F0411_MAIN.RPKCO AS MainCompany, F0411_MAIN.RPMCU AS MainBusinessUnit, F0006.MCDL01 AS MainBusinessUnitDesc, F0411_MAIN.RPAN8 AS MainSupplierNr, F0101.ABALPH AS MainSupplierName, F0411_MAIN.RPDOC AS MainDocNr, F0411_MAIN.RPDCT AS MainDocType, F0411_MAIN.RPVINV AS MainVendorDocNr, F0411_MAIN.RPDIVJ AS MainInvoiceDate, F0411_MAIN.RPDGJ AS MainGLDate, F0411_MAIN.RPDDJ AS MainDueDate, -- If it has been voided before the requested AS-IF date or the GL date is passed the requested AS-if date then result 0 else sum total open amount for voucher SUM(CASE WHEN VoidDate <= {pAsIfDateJulian} OR F0411_MAIN.RPDGJ > {pAsIfDateJulian} OR F0411_MAIN.RPDCTA = 'PE' THEN 0 ELSE ISNULL(VoidAmtGross*-1,F0411_MAIN.RPAAP)-ISNULL(TotalAdjAmtAfterAsOf,0)END) AS MainAmtOpen FROM F0411 AS F0411_MAIN LEFT JOIN (SELECT F0411_FLTR.RPKCO AS FltrCompany, F0411_FLTR.RPMCU AS FltrBusinessUnit, F0411_FLTR.RPAN8 AS FltrSupplierNr, F0411_FLTR.RPDOC AS FltrDocNr, F0411_FLTR.RPDCT AS FltrDocType, F0411_FLTR.RPSFX AS FltrDocSuffix, F0411_FLTR.RPPST AS FltrDocPayStatus, F0411_VOID.RPAG AS VoidAmtGross, F0411_VOID.RPACR AS VoidAmtGrossForeign, F0411_VOID.RPDGJ AS VoidDate FROM F0411 AS F0411_FLTR INNER JOIN F0411 AS F0411_VOID ON F0411_FLTR.RPDOC = F0411_VOID.RPDOC AND F0411_FLTR.RPKCO = F0411_VOID.RPKCO AND F0411_FLTR.RPDCT = F0411_VOID.RPDCT AND F0411_FLTR.RPSFX = F0411_VOID.RPSFX WHERE F0411_VOID.RPDCTA = 'PE' AND F0411_FLTR.RPKCO = {pKCOCompany} AND F0411_FLTR.RPMCU = {pMCUBusinessUnit} AND F0411_FLTR.RPAN8 = {pAN8SupplierNr} AND F0411_FLTR.RPPST IN ({pPSTPayStatusList}) ) AS F0411_SBVD ON F0411_MAIN.RPDOC = F0411_SBVD.FltrDocNr AND F0411_MAIN.RPDCT = F0411_SBVD.FltrDocType AND F0411_MAIN.RPKCO = F0411_SBVD.FltrCompany AND F0411_MAIN.RPSFX = F0411_SBVD.FltrDocSuffix LEFT JOIN ( SELECT F0411_FLTR.RPKCO AS SubRPKCO, F0411_FLTR.RPMCU AS SubRPMCU, F0411_FLTR.RPAN8 AS SubRPAN8, F0411_FLTR.RPDOC AS SubRPDOC, F0411_FLTR.RPDCT AS SubRPDCT, F0411_FLTR.RPSFX AS SubRPSFX, F0411_FLTR.RPSFXE AS SubRPSFXE, SUM(F0414.RNPAAP+F0414.RNADSA) AS TotalAdjAmtAfterAsOf FROM F0411 AS F0411_FLTR INNER JOIN F0414 INNER JOIN F0413 ON F0414.RNPYID = F0413.RMPYID ON F0411_FLTR.RPKCO = F0414.RNKCO AND F0411_FLTR.RPDOC = F0414.RNDOC AND F0411_FLTR.RPDCT = F0414.RNDCT AND F0411_FLTR.RPSFX = F0414.RNSFX AND F0411_FLTR.RPSFXE = F0414.RNSFXE WHERE ((F0414.RNDCTM = 'PO' AND F0413.RMVDGJ > {pAsIfDateJulian}) OR F0413.RMDMTJ > {pAsIfDateJulian}) AND F0414.RNDCTM <> 'PG' AND F0411_FLTR.RPKCO = {pKCOCompany} AND F0411_FLTR.RPMCU = {pMCUBusinessUnit} AND F0411_FLTR.RPAN8 = {pAN8SupplierNr} AND F0411_FLTR.RPPST IN ({pPSTPayStatusList}) GROUP BY F0411_FLTR.RPKCO, F0411_FLTR.RPMCU, F0411_FLTR.RPAN8, F0411_FLTR.RPDOC, F0411_FLTR.RPDCT, F0411_FLTR.RPSFX, F0411_FLTR.RPSFXE ) AS SUBSELECT ON F0411_MAIN.RPKCO = SubRPKCO AND F0411_MAIN.RPDOC = SubRPDOC AND F0411_MAIN.RPDCT = SubRPDCT AND F0411_MAIN.RPSFX = SubRPSFX AND F0411_MAIN.RPSFXE = SubRPSFXE INNER JOIN F0006 ON F0411_MAIN.RPMCU = F0006.MCMCU INNER JOIN F0101 ON F0411_MAIN.RPAN8 = F0101.ABAN8 GROUP BY F0411_MAIN.RPKCO, F0411_MAIN.RPMCU, F0006.MCDL01, F0411_MAIN.RPAN8, F0101.ABALPH, F0411_MAIN.RPDOC, F0411_MAIN.RPDCT ,F0411_MAIN.RPVINV ,F0411_MAIN.RPDIVJ ,F0411_MAIN.RPDGJ ,F0411_MAIN.RPDDJ HAVING SUM(CASE WHEN VoidDate <= {pAsIfDateJulian} OR F0411_MAIN.RPDGJ > {pAsIfDateJulian} OR F0411_MAIN.RPDCTA = 'PE' THEN 0 ELSE ISNULL(VoidAmtGross*-1,F0411_MAIN.RPAAP)-ISNULL(TotalAdjAmtAfterAsOf,0)END) <> 0 ORDER BY F0411_MAIN.RPKCO ASC, F0411_MAIN.RPAN8 ASC, F0411_MAIN.RPDCT ASC, F0411_MAIN.RPDDJ ASC
Prolay Sarkar
Re: Sub-query Problem January 19, 2009 01:23PM |