Re: Syntax for Count(*)

[Tony DeLia <tdelia@EROLS.COM>]

The syntax:

count(*)   &b.count

is correct... I use this frequently...

You should have: group by b.erncd
                 order by 2 desc, 1

The GROUP BY comes before the ORDER BY...

I don't know why you're moving the COUNT into the Heading... You should
be sorting by the count(*) for each erncd then move the ERNCD to the
ERNmtx.ERNttl (#ERNctr) array element... the COUNT itself is irrelevant
for headings...

All you need is...

let #ERNctr                = #ERNctr + 1
let ERNmtx.ERNttl(#ERNctr) = &b.erncd
if  #ERNctr >= 10
    exit-select
end-if

...within the select loop...

                      -Tony DeLia

PS - Another e-mail address????

Sam Spritzer wrote:
>
> you need to replace the * with something at least for starts.
>
> Sam
>
> <<< Bryan Richey <b_richey@YAHOO.COM>  4/ 1  3:07p >>>
> Hello all!
>
> I am trying to populate the dynamic headings that follow ....I was
> wondering if someone has any insight to the correct syntax for the
> count(*)...I am currently getting a mess of SQL errors...
>
> Thanks,
>
> Bryan
>
> !******************************************************
> begin-procedure Determine-Sequence
> !******************************************************
> create-array name=ERNmtx size=11 field=ERNttl:char
>
> do erncd-detail
>
> end-procedure
>
> !******************************************************begin-procedure
> erncd-detail
> !******************************************************
> let #ERNctr  = 0
>
> begin-select
>
> b.erncd
> count(*) &b.count
>
>   let $E.ERNCD               = &b.erncd
>   let #ERNctr                = #ERNctr   1
>   let ERNmtx.ERNttl(#ERNctr) = &b.count
>   if  #ERNctr >= 10
>       exit-select
>   end-if
>
>  from ps_dhm_emp_detail b
> where b.DEPTID          = $V_DEPTID
>   and b.paygroup        = $V_PayGroup
>   and b.pay_end_dt      = $V_Pay_End_dt
>   and b.DHM_WEEK_NBR    = #V_Week_Nbr
> order by 2 desc, 1
> !group by $E.ERNCD
> !group by b.erncd
>
> end-select
>
> let $H01  = ERNmtx.ERNttl(1)
> let $H02  = ERNmtx.ERNttl(2)
> let $H03  = ERNmtx.ERNttl(3)
> let $H04  = ERNmtx.ERNttl(4)
> let $H05  = ERNmtx.ERNttl(5)
> let $H06  = ERNmtx.ERNttl(6)
> let $H07  = ERNmtx.ERNttl(7)
> let $H08  = ERNmtx.ERNttl(8)
> let $H09  = ERNmtx.ERNttl(9)
> let $H10  = ERNmtx.ERNttl(10)
>
> display $h01
>
> end-procedure
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com