Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Count with expression

10 posts in DataWindow Last posting was on 2009-05-04 20:27:09.0Z
Andrew K. Posted on 2009-05-03 16:57:32.0Z
Sender: 7ec8.49fdc85e.1804289383@sybase.com
From: Andrew K.
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Count with expression
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49fdccfc.7f11.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 May 2009 09:57:32 -0700
X-Trace: forums-1-dub 1241369852 10.22.241.41 (3 May 2009 09:57:32 -0700)
X-Original-Trace: 3 May 2009 09:57:32 -0700, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89218
Article PK: 418473

This syntax is valid:
Count(company FOR All DISTINCT )

This syntax is not valid:
Count(company FOR All DISTINCT enr_period1 >0 )

What is the correct syntax for using the expression
enr_period1 >0 ?


Jeremy Lakeman Posted on 2009-05-04 00:41:41.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Count with expression
Date: Sun, 3 May 2009 17:41:41 -0700 (PDT)
Organization: http://groups.google.com
Lines: 11
Message-ID: <2467c6f1-3340-4207-8851-c464fffd5e39@21g2000vbk.googlegroups.com>
References: <49fdccfc.7f11.1681692777@sybase.com>
NNTP-Posting-Host: 203.122.242.105
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1241397701 22791 127.0.0.1 (4 May 2009 00:41:41 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 4 May 2009 00:41:41 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 21g2000vbk.googlegroups.com; posting-host=203.122.242.105; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.9) Gecko/2009040821 Firefox/3.0.9 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 fly.essential.com.au:3128 (squid/2.5.STABLE8), 1.0 asp.essential.intranet:3128 (squid/2.5.STABLE8)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!news.glorb.com!postnews.google.com!21g2000vbk.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89219
Article PK: 418471


On May 4, 1:57 am, Andrew K. wrote:
> This syntax is valid:
> Count(company FOR All DISTINCT )
>
> This syntax is not valid:
> Count(company FOR All DISTINCT enr_period1 >0 )
>
> What is the correct syntax for using the expression
> enr_period1 >0 ?

sum( if( enr_period1 >0, 1, 0) DISTINCT company)


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2009-05-04 02:54:00.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <2467c6f1-3340-4207-8851-c464fffd5e39@21g2000vbk.googlegroups.com> <49fe48fb.9b6.1681692777@sybase.com>
Subject: Re: Count with expression
Lines: 30
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49fe58c8$1@forums-1-dub>
Date: 3 May 2009 19:54:00 -0700
X-Trace: forums-1-dub 1241405640 10.22.241.152 (3 May 2009 19:54:00 -0700)
X-Original-Trace: 3 May 2009 19:54:00 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89221
Article PK: 418475

You want to count the number of rows where enr_period1 > 0 ?
What does the result set look like?

--
Report Bugs: http://case-express.sybase.com/cx/welcome.do
Product Enhancement Requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

<Andrew> wrote in message news:49fe48fb.9b6.1681692777@sybase.com...
> On May 4, 1:57 am, Andrew K. wrote:
> > This syntax is valid:
> > Count(company FOR All DISTINCT )
> >
> > This syntax is not valid:
> > Count(company FOR All DISTINCT enr_period1 >0 )
> >
> > What is the correct syntax for using the expression
> > enr_period1 >0 ?
>
> sum( if( enr_period1 >0, 1, 0) DISTINCT company)

Sorry, I get expression is not valid. I think I just need a
working example for:

Count ( column { FOR range { DISTINCT { expres1{, expres2 {,
..}}}}} )

btw, I am using PowerBuilder 9.03. Build 8004


Arthur Hefti Posted on 2009-05-04 11:28:08.0Z
From: "Arthur Hefti" <arthur@catsoft.ch>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <49fe58c8$1@forums-1-dub> <49fecba4.1840.1681692777@sybase.com>
Subject: Re: Count with expression
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49fed148$1@forums-1-dub>
Date: 4 May 2009 04:28:08 -0700
X-Trace: forums-1-dub 1241436488 10.22.241.152 (4 May 2009 04:28:08 -0700)
X-Original-Trace: 4 May 2009 04:28:08 -0700, vip152.sybase.com
X-Authenticated-User: pb12beta
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89223
Article PK: 418476

In my understanding the expression after the keyword DISTINCT will counted
one per value of the expression. This is not a kind of where. You could e.g.
use a filter to achieve this.

Arthur

<Andrew K> wrote in message news:49fecba4.1840.1681692777@sybase.com...
>> You want to count the number of rows where enr_period1 > 0
>> ? What does the result set look like?
>>
>> --
>> Report Bugs: http://case-express.sybase.com/cx/welcome.do
>> Product Enhancement Requests:
>> http://my.isug.com/cgi-bin/1/c/submit_enhancement
>>
>> <Andrew> wrote in message
>> > news:49fe48fb.9b6.1681692777@sybase.com... On May 4,
>> > > 1:57 am, Andrew K. wrote: This syntax is valid:
>> > > Count(company FOR All DISTINCT )
>> > >
>> > > This syntax is not valid:
>> > > Count(company FOR All DISTINCT enr_period1 >0 )
>> > >
>> > > What is the correct syntax for using the expression
>> > > enr_period1 >0 ?
>> >
>> > sum( if( enr_period1 >0, 1, 0) DISTINCT company)
>>
>> Sorry, I get expression is not valid. I think I just need
>> a working example for:
>>
>> Count ( column { FOR range { DISTINCT { expres1{, expres2
>> {, ...}}}}} )
>>
>> btw, I am using PowerBuilder 9.03. Build 8004
>>
>>
>
> I just want the below to work, but I get the error "invalid
> argument to function"
>
> Count(company FOR All DISTINCT enr_period1 >0 )
>
> So, has anybody used count with distinct and an expression?
> If so can I see your computed field?


R. Pods Posted on 2009-05-04 12:40:50.0Z
From: "R. Pods" <r.pods@gmx.net>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Count with expression
References: <49fed148$1@forums-1-dub> <49fed995.19c6.1681692777@sybase.com>
In-Reply-To: <49fed995.19c6.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49fee252$1@forums-1-dub>
Date: 4 May 2009 05:40:50 -0700
X-Trace: forums-1-dub 1241440850 10.22.241.152 (4 May 2009 05:40:50 -0700)
X-Original-Trace: 4 May 2009 05:40:50 -0700, vip152.sybase.com
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89225
Article PK: 418477

Andrew,

could you supply more information about the result set you want to
process with the COUNT function and what you want to achieve? Without
that there is only guessing ...

If you need the total count of companies with enr_period1 >0 this should
work (Jeremy's proposal modified):

sum( if( enr_period1 > 0, 1, 0) FOR ALL DISTINCT company)

HTH
Reimer

Andrew K wrote:

> This works in Microsoft Transact-SQL:
> select @submitting_companies1 = count(distinct company)
> from @temp
> where enr_period1 > 0
>
> This does not work in Powerbuilder 9:
>
> Count(company FOR All DISTINCT enr_period1 >0 ) because I
> get
> the error "invalid argument to function".
>
> Can I please an example of any valid computed field using
> count,distinct, and an expression?

>> In my understanding the expression after the keyword
>> DISTINCT will counted one per value of the expression.
>> This is not a kind of where. You could e.g. use a filter
>> to achieve this.
>>
>> Arthur
>>
>> <Andrew K> wrote in message
>> news:49fecba4.1840.1681692777@sybase.com... >> You want to
>> count the number of rows where enr_period1 > 0 >> ? What
>> does the result set look like? >>
>>>> --
>>>> Report Bugs:
>> http://case-express.sybase.com/cx/welcome.do >> Product
>> Enhancement Requests: >>
>> http://my.isug.com/cgi-bin/1/c/submit_enhancement >>
>>>> <Andrew> wrote in message
>>>>> news:49fe48fb.9b6.1681692777@sybase.com... On May 4,
>>>>>> 1:57 am, Andrew K. wrote: This syntax is valid:
>>>>>> Count(company FOR All DISTINCT )
>>>>>>
>>>>>> This syntax is not valid:
>>>>>> Count(company FOR All DISTINCT enr_period1 >0 )
>>>>>>
>>>>>> What is the correct syntax for using the expression
>>>>>> enr_period1 >0 ?
>>>>> sum( if( enr_period1 >0, 1, 0) DISTINCT company)
>>>> Sorry, I get expression is not valid. I think I just
>> need >> a working example for:
>>>> Count ( column { FOR range { DISTINCT { expres1{,
>> expres2 >> {, ...}}}}} )
>>>> btw, I am using PowerBuilder 9.03. Build 8004
>>>>
>>>>
>>> I just want the below to work, but I get the error
>>> "invalid argument to function"
>>>
>>> Count(company FOR All DISTINCT enr_period1 >0 )
>>>
>>> So, has anybody used count with distinct and an
>>> expression? If so can I see your computed field?
>>
>


Arthur Hefti Posted on 2009-05-04 13:10:36.0Z
From: "Arthur Hefti" <arthur@catsoft.ch>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <49fed148$1@forums-1-dub> <49fed995.19c6.1681692777@sybase.com>
Subject: Re: Count with expression
Lines: 72
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49fee94c$1@forums-1-dub>
Date: 4 May 2009 06:10:36 -0700
X-Trace: forums-1-dub 1241442636 10.22.241.152 (4 May 2009 06:10:36 -0700)
X-Original-Trace: 4 May 2009 06:10:36 -0700, vip152.sybase.com
X-Authenticated-User: pb12beta
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89226
Article PK: 418479

The Count expression in the datawindow painter is not SQL but simply a
datawindow expression evaluated in the datawindow. However you could change
your query that it gets the companies with enr_period1 > 0.

Arthur

<Andrew K> wrote in message news:49fed995.19c6.1681692777@sybase.com...
>> In my understanding the expression after the keyword
>> DISTINCT will counted one per value of the expression.
>> This is not a kind of where. You could e.g. use a filter
>> to achieve this.
>>
>> Arthur
>>
>> <Andrew K> wrote in message
>> news:49fecba4.1840.1681692777@sybase.com... >> You want to
>> count the number of rows where enr_period1 > 0 >> ? What
>> does the result set look like? >>
>> >> --
>> >> Report Bugs:
>> http://case-express.sybase.com/cx/welcome.do >> Product
>> Enhancement Requests: >>
>> http://my.isug.com/cgi-bin/1/c/submit_enhancement >>
>> >> <Andrew> wrote in message
>> >> > news:49fe48fb.9b6.1681692777@sybase.com... On May 4,
>> >> > > 1:57 am, Andrew K. wrote: This syntax is valid:
>> >> > > Count(company FOR All DISTINCT )
>> >> > >
>> >> > > This syntax is not valid:
>> >> > > Count(company FOR All DISTINCT enr_period1 >0 )
>> >> > >
>> >> > > What is the correct syntax for using the expression
>> >> > > enr_period1 >0 ?
>> >> >
>> >> > sum( if( enr_period1 >0, 1, 0) DISTINCT company)
>> >>
>> >> Sorry, I get expression is not valid. I think I just
>> need >> a working example for:
>> >>
>> >> Count ( column { FOR range { DISTINCT { expres1{,
>> expres2 >> {, ...}}}}} )
>> >>
>> >> btw, I am using PowerBuilder 9.03. Build 8004
>> >>
>> >>
>> >
>> > I just want the below to work, but I get the error
>> > "invalid argument to function"
>> >
>> > Count(company FOR All DISTINCT enr_period1 >0 )
>> >
>> > So, has anybody used count with distinct and an
>> > expression? If so can I see your computed field?
>>
>>
>
> This works in Microsoft Transact-SQL:
> select @submitting_companies1 = count(distinct company)
> from @temp
> where enr_period1 > 0
>
> This does not work in Powerbuilder 9:
>
> Count(company FOR All DISTINCT enr_period1 >0 ) because I
> get
> the error "invalid argument to function".
>
> Can I please an example of any valid computed field using
> count,distinct, and an expression?


Andrew K Posted on 2009-05-04 20:27:09.0Z
Sender: 16c6.49fec8a4.1804289383@sybase.com
From: Andrew K
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Count with expression
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49ff4f9d.25d6.1681692777@sybase.com>
References: <49fee94c$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 May 2009 13:27:09 -0700
X-Trace: forums-1-dub 1241468829 10.22.241.41 (4 May 2009 13:27:09 -0700)
X-Original-Trace: 4 May 2009 13:27:09 -0700, 10.22.241.41
Lines: 84
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89230
Article PK: 418484


> The Count expression in the datawindow painter is not SQL
> but simply a datawindow expression evaluated in the
> datawindow. However you could change your query that it
> gets the companies with enr_period1 > 0.
>
> Arthur
>
>
> <Andrew K> wrote in message
> news:49fed995.19c6.1681692777@sybase.com... >> In my
> understanding the expression after the keyword >> DISTINCT
> will counted one per value of the expression. >> This is
> not a kind of where. You could e.g. use a filter >> to
> achieve this. >>
> >> Arthur
> >>
> >> <Andrew K> wrote in message
> >> news:49fecba4.1840.1681692777@sybase.com... >> You want
> to >> count the number of rows where enr_period1 > 0 >> ?
> What >> does the result set look like? >>
> >> >> --
> >> >> Report Bugs:
> >> http://case-express.sybase.com/cx/welcome.do >> Product
> >> Enhancement Requests: >>
> >> http://my.isug.com/cgi-bin/1/c/submit_enhancement >>
> >> >> <Andrew> wrote in message
> >> >> > news:49fe48fb.9b6.1681692777@sybase.com... On May
> 4, >> >> > > 1:57 am, Andrew K. wrote: This syntax is
> valid: >> >> > > Count(company FOR All DISTINCT )
> >> >> > >
> >> >> > > This syntax is not valid:
> >> >> > > Count(company FOR All DISTINCT enr_period1 >0 )
> >> >> > >
> >> >> > > What is the correct syntax for using the
> expression >> >> > > enr_period1 >0 ?
> >> >> >
> >> >> > sum( if( enr_period1 >0, 1, 0) DISTINCT company)
> >> >>
> >> >> Sorry, I get expression is not valid. I think I just
> >> need >> a working example for:
> >> >>
> >> >> Count ( column { FOR range { DISTINCT { expres1{,
> >> expres2 >> {, ...}}}}} )
> >> >>
> >> >> btw, I am using PowerBuilder 9.03. Build 8004
> >> >>
> >> >>
> >> >
> >> > I just want the below to work, but I get the error
> >> > "invalid argument to function"
> >> >
> >> > Count(company FOR All DISTINCT enr_period1 >0 )
> >> >
> >> > So, has anybody used count with distinct and an
> >> > expression? If so can I see your computed field?
> >>
> >>
> >
> > This works in Microsoft Transact-SQL:
> > select @submitting_companies1 = count(distinct company)
> > from @temp
> > where enr_period1 > 0
> >
> > This does not work in Powerbuilder 9:
> >
> > Count(company FOR All DISTINCT enr_period1 >0 ) because
> > I get
> > the error "invalid argument to function".
> >
> > Can I please an example of any valid computed field
> > using count,distinct, and an expression?
>
>

Thanks everybody for your examples and help. I got my
computed field to work by adding the expression to the
distinct list. Now I don't have to write a stored procedure
and can use a regular datawindow.

works - sum( if( enr_period1 > 0, 1, 0) FOR ALL DISTINCT
company, if( enr_period1 > 0, 1, 0) )
doesn't work - sum( if( enr_period1 > 0, 1, 0) FOR ALL
DISTINCT company )