How to go about this DataWindow problem

[Greener73]

I've got a situation where, within a single window, i need to have two different (although same columns) queries, and provide a total column to the far right.

I'm not exactly sure the best way to approach this. Keeping in mind that although they both will return results with the same columns, they may or may not have all the same rows. And with that said, I need common rows to add up.

So for example, lets say i use a composite datawindow. And within this i have 2 datawindow children. But now i have the problem of totalling their values, when their rows do not necessarily line up...because one has more rows then the other.

Any hints or tips to go about this would be much appreciated.

Thanks,

 

[bernds44]

can you give an example of the sql queries you intend to use?

It is not really clear what you want to do.

what do you mean with 'But now i have the problem of totalling their values' ?

 

[Greener73]

Query 1:
Select account, sum(rate) from
table1 where
locationID = 676
group by account

Query 2:
Select account, sum(rate) from
table1 where
locationID = 767
group by account

Report:

Account LocationID.1 LocationID.2 Total
------------------------------------------------------------
ActName sum(rate).1 sum(rate).2 sum of left 2


This is kinda what i'm going after. Mind you my query statement is a lot more complex. But the idea is i need a select statement to determine the rates for each location.

I don't think this problem is overly complex. I just don't know the best way to do this. Maybe its possible to combine both select statements into one.
The other way I thought would be to load the data into a datastore, and somehow from that move it into a datawindow.

Thanks for the reply bernds44

 

[thekl0wn]

You could also group the report, based on the account, and sum for that group each individual rate.

So your SELECT would look something like: (2 long arguments: an_id1 & an_id2)

SELECT account,
rate,
locationID
FROM TABLE1
WHERE ( locationID = :an_id1 OR locationID = :an_id2 )
ORDER BY account

In the detail portion of the DataWindow create two computed fields, loc_1_rate and loc_2_rate. In each of these, their expressions would be: IF( locationid = an_id1, an_id1, 0 )

In the group header, create two more computed fields to sum the computed fields in the detail for that group. Their expressions: SUM( loc_1_rate FOR group 1 )

And then for the total, just add the two sum computed fields.


I'm sure there's a MUCH more efficient method of doing this, but this was the quick & dirty theory I came up with.

 

[Greener73]

thank the reply guys

After some advice from others, and thinking about this more, I managed to do everything through SQL using UNIONS and GROUP by.

Whether this is the right way to do this in the long run, well time will tell

Again, thanks for the help. Its very hard to find information on powerbuilder online..compared to C++, C# ect ect. Nice to have someplace to ask and get help

Have a good weekend guys