Selecting from DataWindow

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.
0
jwvai316
7/17/2009 12:40:33 AM
sybase.powerbuilder.datawindow 28057 articles. 4 followers. Follow

6 Replies
325 Views

Similar Articles

[PageSpeed] 54

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.

0
Adam
7/17/2009 1:26:42 AM
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.


0
Harry
7/17/2009 1:46:51 AM
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
0
jwvai316
7/17/2009 2:16:12 AM
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.
0
jwvai316
7/17/2009 3:16:25 AM
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.
0
jwvai316
7/17/2009 3:43:50 AM
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. 


0
Paul
7/17/2009 1:05:37 PM
Reply:

Similar Artilces:

DataWindow This, DataWindow That...
Blah blah blah You've heard it enough that you don't even want to comment on how many times... The DataWindow. ....but it can't be enough anymore...can it? This from one of our colleagues (and yes my apologies for taking it out of context)... "...PB covers all you need to do that in ONE Tool. This includes Windowprogramming ( meaning the interface to the user: Windows, Sheets, diallougeboxes, etc.), Database operability AND Reporting! Well, I see als well as many other PB users that some of the implementations and features PB offers are improvable. But thi...

selected row in the datawindow to displayed in the other datawindow
hi iam unable to display the data which is selected data in one datawindow to other datawindow. thnaks deva see IsSelected functions and transfer those rows to the other DW Regards Vasu "Vasu D" <vasu@i-vantage.com> wrote in message news:sDZOLqlpCHA.187@forums.sybase.com... > hi > iam unable to display the data which is selected data in one datawindow to > other datawindow. > thnaks > deva > > > Hello Vasu D, Are u using shared dw's? In this case you'll only have to use the function dw_secundary.scrolltor...

How to share the datawindow to inner datawindow of nested datawindow
How to share the datawindow to inner datawindow of nested datawindow. Ex -------- dw_1 is normal datawindow dw_nes is nested datawindow, both are placed one window dw_nes contain dw_child datawindow Question ---------------- i want to share dw_1 and dw_child. I have tried the below code, getting error dw_1.sharedata( dw_nes.object.dw_child) It's the dot notation. You will need to dw_nes.GetChild("dw_child", ldwc) where ldwc is a _local_ datawindowchild. <kzganesan@gmail.com> wrote in message news:4c64c402-bac5-4ee0-83aa-4ac08ffe43e5@r66g2000hsg.go...

Export datawindow select statement form all datawindows
I have a large application with a lot of DWs. DB is SQL Server 2000. Some DW use " (double quotes) in the select statement (not '), and now, with PB105 and oledb, this statements fails (MS native driver works) I know that " isn't a valid character for delimiting strings in SQL, but this large application was written by many people :) How can I find all " in all select statements in all DW within my application to do a replace. Thanks, Alex B. Hi Alex, I'm not sure wether some tool like PBL Peeper is capable...

datawindows in composite datawindow used in datawindow.NET, are not sliding up
Hi, Problem: datawindows in composite datawindow used in "datawindow.NET", are not sliding up , if one dw is not visible or did not retrieve data. Resulting in unwanted spaces. The height of blank white space is equal to the height of the datawindow art design time. When I looked at the generated JavaScript, I noticed there is "div" for the datawindow with height 0.00in which is supposed to be not visible. Tried different options with html/xhtml/xml same result. But slides up ok when exported to pdf(using saveas) on web and in PB10.5 application. Also s...

DataWindow Within DataWindow
How Can I access/modify object/control from a Datawindow within Datawindow. When i access/modify object/control from a Datawindow i used dw_1.modify("name.text = 'new val'") but within another datawindow or child datawindow it doesnt work. Your description is difficult to understand due to the terminology. First, a datawindow is assigned to a control - either a datawindow control or a datastore. Second, the Modify function can only be called within a script. With that as a basis, can you rephrase the question and describe more precisely what you want to do? ...

DataWindows and datawindow objects
Difference between Datawindow and datawindowobjects? - datawindow is a control on a userobject or a window - datawindowobject (DWObject) is a control within a datawindow, defined in the dataobject of the datawindow. You refer to it as <datawindow-instance>.Object.<datawindow object> HTH, Ben (bede) <Santhoshi> wrote in message news:3f8bee8b.4631.846930886@sybase.com... > Difference between Datawindow and datawindowobjects? Not exactly; DW Control is a "container" that reserves real-estate on the surface of a visual object. DW Obj...

Datawindows with child datawindows
I want to create a datawindow whose only data is three columns that will themselves be drop-down data windows. These child datawindows will be populated in PB script with dw_1.GetChild(). When I create that parent datawindow (New | Datawindow | Freeform), what do I choose in the next dialog box ("Which datasource would you like to use?") External? Thanks. That depends on how you want to populate and use the columns of the main DW. The DDDW is an edit style that assists the user in selecting a value for the column in the main DW. If you want to retrieve the values ...

modifiyin a datawindow in a datawindow
Hi, When I want to modify a datawindow, then it's just dw1.modify() But what do I have to do when I want to modify a datawindow in a datwindow ? I tried : dw1.dw2.modify() but that doesn't work. Anyone can help me out with this ? thanks. Regards, David get the dw via getchild & apply modify In article <4575976f.4dda.1681692777@sybase.com>, David says... > Hi, > When I want to modify a datawindow, then it's just > dw1.modify() > > But what do I have to do when I want to modify a datawindow > in a datwindow ? > I tried : dw...

datawindow
Add direct setting and getting capability for the datawindow where clause. This would have to be a string array in case of unions. Return ONLY the where clause(s) in the main select (in case of sub-selects, ignore the where clause or somehow list it as a subselect in the where or a subselect in the select). It would be great if I could just get and set this as a value and have the datawindow automatically change (or add) the where clause as specified. This is for setting the where clause dynamically at run time. Currently have to parse through the datawindow.table.select looking f...

Can i modify the "Table.Select" syntax for DataWindow in a composite Datawindow ?
Hi everyone, For a normal datawindow (means not composite datawindow) control , it works very well: sVarSaveSyntax = dw_1.Describe("DataWindow.Table.Select") and dw_1.modify( "datawindow.table.select" ) = my_newSelectSyntax but in a composite datawindow, it does not work. So how can i get and modify the syntax of nested datawindow? or it's impossible at all? Thanks in advance! David Wu "David Wu" <milon@nbip.net> wrote in message news:LM6#5XBwBHA.307@forums.sybase.com... > Hi everyone, > > For a normal datawi...

Help! How to modify the sql select statement for a nested datawindow inside a composite datawindow?
Help! How to modify the sql select statement for a nested datawindow inside a composite datawindow? Would the following work? dw_nest.Object.DataWindow.Table.Select="select employee_id from ..." Please help. jungan@aboex.com GetChild () ??? "Jun Gan" <jungan@aboex.com> wrote in message news:40e48313$1@forums-2-dub... > Help! > > How to modify the sql select statement for a nested datawindow inside a > composite datawindow? > > Would the following work? > > dw_nest.Object.DataWindow.Table.Select="select e...

I will print two datawindow in one page,and I use child datawindow.One master datawindow (dw_master)with two child datawindow(dc1 and dc2)
dw_master("dw_dc1",dc1) dw_master("dw_dc2",dc2) dc1 has a argument (arg1) dc2 has a argument (arg2) Arg1 and Arg2 are send by one column of dw_master. When I use retrieve() function,the datawindow can display normal,but when I print preview or use Zoom or printsetup() ,the childdatawindow dc2 can't display but dc1 normal! Why?? Thanks for your help! I'm having trouble understanding your question. Is this a composite or nested datawindow? On Mon, 6 May 2002 22:49:23 +0800, in powersoft.public.powerbuilder.datawindow spark <fenfei79@hot...

Datawindow select
When I tried to look at the SQL behind a datawindow it I get a message saying sp_pb60primarykey not found. Is something missing? We are using PB6.5 with Sybase. Thanks. Did you remember to load the pbsyc.sql stored procedures? These can be found in the /server directory on the install CD. steve [TeamPS] Saravana wrote: > > When I tried to look at the SQL behind a datawindow it I get a message > saying sp_pb60primarykey not found. Is something missing? We are using > PB6.5 with Sybase. > Thanks. ...

Web resources about - Selecting from DataWindow - sybase.powerbuilder.datawindow

PowerBuilder - Wikipedia, the free encyclopedia
PowerBuilder is an integrated development environment owned by Sybase , a division of SAP . It has been in use since 1991, peaking around 1998 ...

Intelligent Versus Surrogate Keys
Should I use business columns as primary key fields for tables in thedatabase, or generate artificial primary key values? When business-related ...

chevy volt
ProEXR File Description =Attributes= channels (chlist) compression (compression): Zip dataWindow (box2i): [0, 0, 5119, 2475] displayWindow (box2i): ...

Enable - A comprehensive solution for the localization of PowerBuilder applications Untitled Document ...
Enable is localization/ translation tool used by PowerBuilder developers to make their applications multilingual.Enable is a powerful tool that ...

comp.lang.clipper.visual-objects - Google Groups
comp.lang.clipper.visual-objects 1-20 von 33006 Themen werden angezeigt Integrity of a file marcosm...@gmail.com 09:35 Combobox value Lu 08:12 ...

Sybase PowerBuilder tool readied for Microsoft's .Net
Developers can choose Win32 or newer style .Net development

PowerToTheBuilder - PowerBuilder UI Controls
HOME - COMPONENTS - PB UI DESIGNER - DOWNLOAD\PURCHASE - SUPPORT - FAQ - ABOUT Take Control of your UI with PB Ultimate Suite PowerToTheBuilder.com ...

Beautiful iPad Mini 3 Concept Reimagines Appleā€™s Tablet With iPhone 6 Design, Gold Color [Images]
The iPhone 6 may be grabbing all of the recent headlines due to its impending announcement, but spare a thought for those who prefer to stay ...

Java Networking - Real's HowTo
Real's HowTo Custom Search Java Language String and Number Applet AWT Swing Environment IO Javascript interaction JDBC Thread Networking JSP ...

Business Intelligence Products - Database Management, Data Warehousing & Mobility Software - Sybase Inc ...
Sybase offers a variety of Business Intelligence (BI) software like products for database management, data warehousing and mining, data integration ...

Resources last updated: 1/11/2016 12:44:12 AM