|
|
Re: group column in data block [message #356217 is a reply to message #355983] |
Thu, 30 October 2008 00:27   |
xpact83
Messages: 225 Registered: October 2008 Location: philippines
|
Senior Member |
|
|
from an insurance table... which has let say
assured_name / premium_payments / and date_payment are the columns
ex. sample data....
edward 400 01-02-07
edward 300 01-02-08
john 100 01-02-08
mark 200 01-02-08
then the user will input 2 dates which will serve as the data range.... so if the user will input 01-02-07 and 01-10-08
will display all records between the two dates but the premium_payments must be sum up and assured name must be grouped so output will be
edward 700
john 100
mark 200
this is easy in report but in forms how can i do this....
i already than this in report but the client also want to have preview of data in form....
|
|
|
Re: group column in data block [message #356221 is a reply to message #355983] |
Thu, 30 October 2008 00:30   |
xpact83
Messages: 225 Registered: October 2008 Location: philippines
|
Senior Member |
|
|
i have done this in report already...
the sql is
select assured_name , sum(premium_payments) as premium_payments from table where date_payment between :date1_param and :date2_param group by assured_name order by assured_name
and this worked properly but the client wants it to be previewed first in the form....
|
|
|
|
|
|
Re: group column in data block [message #356247 is a reply to message #355983] |
Thu, 30 October 2008 01:33   |
xpact83
Messages: 225 Registered: October 2008 Location: philippines
|
Senior Member |
|
|
so examine the table above ..... then how will grouped that in a view where ...
edward 400 01-02-07
edward 300 01-02-08
so if you group this in a view
this will be the output
edward 700
the problem is what if the user only input this date range
01-02-07 and 12-12-07 so the output must only be
edward 400
that will be the problem if you will create a view because you dont know yet the input of the user....
|
|
|
|
Re: group column in data block [message #356266 is a reply to message #356249] |
Thu, 30 October 2008 01:56   |
|
What your problem really is? It has been said that you can define your restriction on block's where clause. Have you tried using view?
Also, your block data can be from query should you don't want to create a view.
|
|
|
Re: group column in data block [message #356268 is a reply to message #355983] |
Thu, 30 October 2008 02:11   |
xpact83
Messages: 225 Registered: October 2008 Location: philippines
|
Senior Member |
|
|
ok lets start with creating a view .... if you will group the records above in a view using assured name and sum(premium payments) you must not include the date payment ... because if you include the date it will not be grouped properly ...
so how in this earth are you going to search for date range...
so if your code is this.. in view
select assured_name , sum(premium_payments) as premium_payments from table group by assured_name
then the output will be...
edward 700
john 100
mark 200
so there is no way now that the user will be able to enter the date range.. if we will not include the date
.. but if you include date in our view the output will not be grouped properly it will be like this.
edward 400 01-02-07
edward 300 01-02-08
john 100 01-02-08
mark 200 01-02-08
just the same as the table ...
|
|
|
|
Re: group column in data block [message #356271 is a reply to message #355983] |
Thu, 30 October 2008 02:16   |
xpact83
Messages: 225 Registered: October 2008 Location: philippines
|
Senior Member |
|
|
if im going to use the where clause property for the date... this will be the output.....
let say date 1 and date 2 are..... 01-02-06 to 01-02-09
edward 400 01-02-07
edward 300 01-02-08
john 100 01-02-08
mark 200 01-02-08
what the client wants is..... the output should be grouped... like this...
edward 700
john 100
mark 200
but remember if the date ranges are..... 01-01-08 to 01-02-09 it must be like this....
edward 300
john 100
mark 200
why? because the first record is not covered by the date range gets?
|
|
|
Re: group column in data block [message #356274 is a reply to message #355983] |
Thu, 30 October 2008 02:29   |
xpact83
Messages: 225 Registered: October 2008 Location: philippines
|
Senior Member |
|
|
actually i already found the solution for this just an hour ago... by creating a temporary table... when the user clicks the search button the temp table will be truncated first and populated by data based on the date ranges.......
i just want to know if there are other possible ways to solve this...
and i guess this will be not possible in using view...that is in my opinion but if you have ways to do this using view i want to know...
[Updated on: Thu, 30 October 2008 02:30] Report message to a moderator
|
|
|
|
Re: group column in data block [message #356277 is a reply to message #356276] |
Thu, 30 October 2008 02:54   |
|
In view? Search for materialized view, but I think it still won't do. For that problem where you have to channel your data, the best thing is to have a temporary table which you have did.
Another thing to do is to create a cursor, and populate your block with that data from cursor. I think your block here is not a DB block.
|
|
|
|
Re: group column in data block [message #356528 is a reply to message #356492] |
Fri, 31 October 2008 00:29  |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I have no idea what a "pagie in pex" might be; however, would you (xpact83) please discuss it with Wency via private messaging system, or in Community hangout forum, or elsewhere? Now it's been the third time you insist on the same question (obviously, no answer so far).
|
|
|