Re: dynamic aggregate column

[Ray Ontko <rayo@ONTKO.COM>]

Al,

What error are you getting?  This should work:

   let $SELECT_AMT = 'SUM(D.MONETARY_AMT_BSE)'

Ray

> Environment
> OS: Win NT
> RDBMS:  MS SQL Server 7.0
> SQR Ver:  4.3.2.2
>
> Hello All,
>
> I'm stumped on the syntax below.  Wondering how do you use the dynamic
> variable $SELECT_AMT with an aggregate column:
>
> using the [$SELECT_AMT] dynamic variable
>
> I've tried unsuccessfully using the following syntax:
>
> let $SELECT_AMT = 'D.MONETARY_AMT_BSE'       !using SUM([$SELECT_AMT])
> let $SELECT_AMT = 'SUM(D.MONETARY_AMT_BSE)'  !using [$SELECT_AMT]
> let $SELECT_AMT = 'D.MONETARY_AMT_BSE'       !using SUM( || [$SELECT_AMT] ||
> )
>
>
> variables defined as:
>         let $BFR_FROM = 'PS_CC_VCHRHDRD_TAO A, PS_CC_DSTLNBK_TAO D'
>         let $AFTR_FROM = 'PS_CC_VCHRHDRD_TAO H, PS_DIST_LINE_BOOK G'
>         let $SELECT_AMT = 'SUM(D.MONETARY_AMT_BSE)'
>
> procedure
> begin-procedure Get-Before-Data
>
> begin-select
> D.BUSINESS_UNIT         &BFR_BU (+1,{c1}) ON-BREAK skiplines = 1
> A.RECON_FLG             &BFR_CATEGORY (,{c2})
> COUNT(*)                &BFR_COUNT  (,{c3})
> D.BASE_CURRENCY         &BFR_BASE_CUR (,{c4})
> D.LEDGER                &BFR_LEDGER (,{c5})
> D.PRIMARY_LEDGER        &BFR_PRIM_LEDGER  (,{c6})
> [$SELECT_AMT]           &BFR_SELECT_AMT=NUMBER (,{c7}) edit 999999.99
>
> FROM [$BFR_FROM]
> WHERE A.BUSINESS_UNIT = D.BUSINESS_UNIT
> AND A.VOUCHER_ID = D.VOUCHER_ID
> AND A.RECON_FLG = [$CATEGORY]
> GROUP BY D.BUSINESS_UNIT, A.RECON_FLG, D.BASE_CURRENCY,
>          D.LEDGER, D.PRIMARY_LEDGER
>
> end-select
> end-procedure Get-Before-Data
>
>
> Thanks in advance, Al
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>

----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/