Re: Syntax for Count(*) - DOTS!!!

[Tony DeLia <tdelia@EROLS.COM>]

WITHOUT ANY DOTS??? You CAN and SHOULD take advantage of "dots" whenever
possible...

The statement:
"The ampersand variable should be one word without any dots"

SHOULD BE changed to:
"The ampersand variable should be one word AND CAN have a dot"

"Ampersand variable" referring to the column synonym... whether implicit
or explicit... You may have noticed all "implied" column synonyms have
"dots" when the select list has "dots"...

I use this syntax frequently:

begin-select

decode(a.flag,'X',1,2)      &a.flag
sum(a.amount)               &a.amount
sum(b.amount)               &b.amount
count(*)                    &b.count
a.something                 &a.something_with_dot
etc...

Combining the table alias with the column synonym is sensible, improves
readability and remains consistent with any other columns in the select
list... In the example above the source table is always identified using
the alias... It doesn't get much simpler...

           -Tony DeLia

PS - Each database has their own individual rules. The original request
was for Oracle and this pertains to Oracle (and similar databases). One
more thing:

let ERNmtx.ERNttl(#ERNctr) = &b.erncd

DO NOT move the "count" to the Earnings Code Heading!!!!

Parijat Sahai wrote:
>
> Remove the dot from &b.count, i.e., you should have the following
> syntax:
>
> begin-select
>
> b.erncd
> count(b.*) &b_count
>
>   let $E_ERNCD               = &b.erncd
> ...
>   let ERNmtx.ERNttl(#ERNctr) = &b_count
> .....
> end-select
>
> The ampersand variable should be one word without any dots. I guess same
> holds for $ and # variables in SQR. You can have "-" and "_" as
> separators to make variable names more readable if you like.
>
> Parijat.
>
> >From: Bryan Richey <b_richey@YAHOO.COM>
> >Reply-To: SQR-USERS@list.iex.net
> >To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
> >Subject: Syntax for Count(*)
> >Date: Thu, 1 Apr 1999 12:07:48 -0800
> >
> >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
>
> Get Your Private, Free Email at http://www.hotmail.com

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