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.

Selecting from DataWindow

7 posts in DataWindow Last posting was on 2009-07-17 13:05:37.0Z
jwvai316 Posted on 2009-07-17 00:40:33.0Z
From: jwvai316 <jwvai316@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Selecting from DataWindow
Date: Thu, 16 Jul 2009 17:40:33 -0700 (PDT)
Organization: http://groups.google.com
Lines: 31
Message-ID: <311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com>
NNTP-Posting-Host: 218.185.3.130
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1247791233 11531 127.0.0.1 (17 Jul 2009 00:40:33 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 17 Jul 2009 00:40:33 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: a39g2000pre.googlegroups.com; posting-host=218.185.3.130; posting-account=lQAhBgoAAAApu4UadqDj7x8uChkEGvel
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.33 Safari/530.5,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 WSUS
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!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!a39g2000pre.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89652
Article PK: 418840

Hi,

I am wondering if we can do kind of sql select command in DataWindow.

Suppose I have a data window with 3 columns

studentID | SubjectID | score
----------------+-------------------+------------
123 | MT01 | 80
123 | GO02 | 70
456 | MT01 | 70
456 | FS01 | 90

This is the result I am expecting
StudentID | maxScore |
----------------+----------------------------|
123 | 80 |
456 | 90 |

In SQL it will be like this
select studentID, max(Score)
from Student
group by studentID

How do I do it if it (student table) was a datawindow?
I need it to be displayed on other datawindow control and exported to
excel after viewing it?

Can anyone help me with this?

Thank you very much.


Adam Simmonds Posted on 2009-07-17 01:26:42.0Z
From: Adam Simmonds <adam.simmonds@[remove]exemail.com.au>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Selecting from DataWindow
References: <311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com>
In-Reply-To: <311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5fd352$2@forums-3-dub.sybase.com>
Date: 16 Jul 2009 18:26:42 -0700
X-Trace: forums-3-dub.sybase.com 1247794002 10.22.241.152 (16 Jul 2009 18:26:42 -0700)
X-Original-Trace: 16 Jul 2009 18:26:42 -0700, vip152.sybase.com
Lines: 48
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89653
Article PK: 418841


jwvai316 wrote:
> Hi,
>
> I am wondering if we can do kind of sql select command in DataWindow.
>
> Suppose I have a data window with 3 columns
>
> studentID | SubjectID | score
> ----------------+-------------------+------------
> 123 | MT01 | 80
> 123 | GO02 | 70
> 456 | MT01 | 70
> 456 | FS01 | 90
>
> This is the result I am expecting
> StudentID | maxScore |
> ----------------+----------------------------|
> 123 | 80 |
> 456 | 90 |
>
> In SQL it will be like this
> select studentID, max(Score)
> from Student
> group by studentID
>
> How do I do it if it (student table) was a datawindow?

I dont understand what you are trying to do here.
Why cant you do the sql select to get the max results as per your query?
That sql you wrote will work fine as the SQL for a datawindow and will
give you a column name maxscore if you alias it, for example

select studentID, max(Score) maxscore
from Student
group by studentID

Maybe explain in a little more detail what you are trying to do.

a.


> I need it to be displayed on other datawindow control and exported to
> excel after viewing it?
>
> Can anyone help me with this?
>
> Thank you very much.


jwvai316 Posted on 2009-07-17 02:16:12.0Z
From: jwvai316 <jwvai316@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Selecting from DataWindow
Date: Thu, 16 Jul 2009 19:16:12 -0700 (PDT)
Organization: http://groups.google.com
Lines: 22
Message-ID: <14eee28f-9e5b-4c41-bdc4-f8a41c055118@l5g2000pra.googlegroups.com>
References: <311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com> <4a5fd352$2@forums-3-dub.sybase.com>
NNTP-Posting-Host: 218.185.3.130
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1247796973 29670 127.0.0.1 (17 Jul 2009 02:16:13 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 17 Jul 2009 02:16:13 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: l5g2000pra.googlegroups.com; posting-host=218.185.3.130; posting-account=lQAhBgoAAAApu4UadqDj7x8uChkEGvel
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.33 Safari/530.5,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 WSUS
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!l5g2000pra.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89655
Article PK: 418843

Hi Adam,

Thanks for your reply.

I can't sql query because it is not a database table it is a
datawindow. the content of that datawindow is calculated and then
inserted into the datawindow.

I was just thinking if we can do

insert into table2
select studentID, max(Score) maxscore
from Student
group by studentID

why can't we do exactly the same in powerbuilder (transferring grouped
data to other table)

Hope that explain more about my problem.

Thanks,
Jeremiah


Harry Posted on 2009-07-17 01:46:51.0Z
From: "Harry" <nospam.harrylhy@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com>
Subject: Re: Selecting from DataWindow
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5fd80b$1@forums-3-dub.sybase.com>
Date: 16 Jul 2009 18:46:51 -0700
X-Trace: forums-3-dub.sybase.com 1247795211 10.22.241.152 (16 Jul 2009 18:46:51 -0700)
X-Original-Trace: 16 Jul 2009 18:46:51 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89654
Article PK: 418842

If only view on other datawindow (e,g.:dw_2), create a group in dw_2, define the column in definition tab, make a compute field with
the expression with "max(score for group 1)", then share the data to dw_2.

But if view and need to export the grouped data to excel, I think write a script in PB is well. Sort the datawindow (or copy data to
datastore first), loop this dw/ds, find the max score, and fill to dw_2, then you can view and export freely. I' not sure the
compute field data can export the data directly, but you can try it also.

HTH

"jwvai316" <jwvai316@gmail.com> ??? news:311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com ???...

> Hi,
>
> I am wondering if we can do kind of sql select command in DataWindow.
>
> Suppose I have a data window with 3 columns
>
> studentID | SubjectID | score
> ----------------+-------------------+------------
> 123 | MT01 | 80
> 123 | GO02 | 70
> 456 | MT01 | 70
> 456 | FS01 | 90
>
> This is the result I am expecting
> StudentID | maxScore |
> ----------------+----------------------------|
> 123 | 80 |
> 456 | 90 |
>
> In SQL it will be like this
> select studentID, max(Score)
> from Student
> group by studentID
>
> How do I do it if it (student table) was a datawindow?
> I need it to be displayed on other datawindow control and exported to
> excel after viewing it?
>
> Can anyone help me with this?
>
> Thank you very much.


jwvai316 Posted on 2009-07-17 03:16:25.0Z
From: jwvai316 <jwvai316@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Selecting from DataWindow
Date: Thu, 16 Jul 2009 20:16:25 -0700 (PDT)
Organization: http://groups.google.com
Lines: 21
Message-ID: <b72fea5c-a537-40c0-8a47-9c922cd3902c@u38g2000pro.googlegroups.com>
References: <311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com> <4a5fd80b$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: 218.185.3.130
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1247800586 31208 127.0.0.1 (17 Jul 2009 03:16:26 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 17 Jul 2009 03:16:26 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: u38g2000pro.googlegroups.com; posting-host=218.185.3.130; posting-account=lQAhBgoAAAApu4UadqDj7x8uChkEGvel
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.33 Safari/530.5,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 WSUS
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!u38g2000pro.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89656
Article PK: 418844

Hi Harry,

Thanks for your reply.

Your 1st way is what I tried before. the problem is when I am printing
it, it prints the original datawindow.

and then I tried to copy the datawindow to dw_2 (after setting a group
on dw_2) instead of sharing it (which in result make it faster for the
second run).
but it is still the same when I print it.

It will print the "detail" not the "summary" only.

I haven't tried the 2nd way yet but what happen if there are 2 maximum
value. as in, student 123 has 3 subject in which he scored 90. it will
have 3 90 then.
is there any built in command to find the max score group by ID? or
else I have to create 1 then.

Thank you very much.


"Paul Horan[Sybase]" <phoran_remove Posted on 2009-07-17 13:05:37.0Z
From: "Paul Horan[Sybase]" <phoran_remove@remove_sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com> <4a5fd80b$1@forums-3-dub.sybase.com> <b72fea5c-a537-40c0-8a47-9c922cd3902c@u38g2000pro.googlegroups.com>
Subject: Re: Selecting from DataWindow
Lines: 36
Organization: Sybase
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: <4a607721$2@forums-3-dub.sybase.com>
Date: 17 Jul 2009 06:05:37 -0700
X-Trace: forums-3-dub.sybase.com 1247835937 10.22.241.152 (17 Jul 2009 06:05:37 -0700)
X-Original-Trace: 17 Jul 2009 06:05:37 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89658
Article PK: 418846

You can add a group on student_id, and place the max( score ) expression
into it.

When you want to see the detail, use Modify() to set the detail band height
to 72 and the trailer band height to 0.
When you want to see the summary, do the opposite.

--
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/

"jwvai316" <jwvai316@gmail.com> wrote in message
news:b72fea5c-a537-40c0-8a47-9c922cd3902c@u38g2000pro.googlegroups.com...
> Hi Harry,
>
> Thanks for your reply.
>
> Your 1st way is what I tried before. the problem is when I am printing
> it, it prints the original datawindow.
>
> and then I tried to copy the datawindow to dw_2 (after setting a group
> on dw_2) instead of sharing it (which in result make it faster for the
> second run).
> but it is still the same when I print it.
>
> It will print the "detail" not the "summary" only.
>
> I haven't tried the 2nd way yet but what happen if there are 2 maximum
> value. as in, student 123 has 3 subject in which he scored 90. it will
> have 3 90 then.
> is there any built in command to find the max score group by ID? or
> else I have to create 1 then.
>
> Thank you very much.


jwvai316 Posted on 2009-07-17 03:43:50.0Z
From: jwvai316 <jwvai316@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Selecting from DataWindow
Date: Thu, 16 Jul 2009 20:43:50 -0700 (PDT)
Organization: http://groups.google.com
Lines: 25
Message-ID: <8d4300cb-b650-487c-a589-5e8e11f2a039@a39g2000pre.googlegroups.com>
References: <311cf804-5214-4803-a598-eaf4cbba81a0@a39g2000pre.googlegroups.com> <4a5fd80b$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: 218.185.3.130
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1247802231 3947 127.0.0.1 (17 Jul 2009 03:43:51 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 17 Jul 2009 03:43:51 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: a39g2000pre.googlegroups.com; posting-host=218.185.3.130; posting-account=lQAhBgoAAAApu4UadqDj7x8uChkEGvel
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.33 Safari/530.5,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 WSUS
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!a39g2000pre.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89657
Article PK: 418845

Hi Harry,

Thanks for your reply.

the 1st way was the one I tried before. however the I was not able to
export dw_2. When I export it will show everything including the
"details" part and not only the "summary" parts.

I haven't tried the 2nd way 1st but I that is for last resort. do I
have to loop all rows? or there is still a build in function to get
the max score of each distinct ID.

but if there is no other way I will just do:

- sort descending dw_1 by the ID column and score column (I hope
datawindow handle sorting 2 column)
- insert the 1st row into dw_2 and variables
- keep going until the ID values is different, if it is different
insert it into dw_2.

a bit hard to explain it here.

my dw has 100k rows. I hope this work.

Thanks.