Record Groups using a max function [message #146118] |
Tue, 08 November 2005 14:31 |
eziegler03
Messages: 7 Registered: September 2005 Location: Wisconsin
|
Junior Member |
|
|
Hello,
I am using 9i forms builder and trying to create an LOV
I am trying to create a record group using only one table. I am doing this is the record group properties. The problem that I am encountering is that I have to use the max function to retrieve only one row for each section. The query that I am using is:
select distinct substr(pay_to_id,1,3)||substr(pay_to_id,7,3) as pay_to_cd, max(pay_to_name) as pay_to_name
from payment_codes
GROUP BY substr(pay_to_id,1,3)||substr(pay_to_id,7,3)
order by substr(pay_to_id,1,3)||substr(pay_to_id,7,3)
The data is as such:
PAY_TO_ID PAY_TO_NAME
--------- -------
ABC123DEF , TEST NAME
ABC124DEF , TEST STATE
ABC125DEF , TEST USE
I am expecting the LOV to then return -- ABCDEF , TEST USE.
My question -- Can you use the max function in the record group or do I need to create a dynamic sql. If that is the case I am also struggling with that.
Thanks for any help or advice.
Have a great day.
EZiegler
|
|
|
|
Re: Record Groups using a max function [message #146219 is a reply to message #146145] |
Wed, 09 November 2005 07:19 |
eziegler03
Messages: 7 Registered: September 2005 Location: Wisconsin
|
Junior Member |
|
|
We actually figured it out after a while. We did it this way.
select distinct substr(pay_to_id,1,3)||substr(pay_to_id,7,3) pay_to_cd, max(NVL(pay_to_name,' ')) pay_to_name
from payment_codes
group by substr(pay_to_id,1,3)||substr(pay_to_id,7,3)
order by substr(pay_to_id,1,3)||substr(pay_to_id,7,3)
Our next set was to try the sub-select.
Thanks for replying.
Have a great day.
EZiegler
|
|
|