OraFAQ Forum: Forms » Record Groups using a max function

Home » Developer & Programmer » Forms » Record Groups using a max function

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Record Groups using a max function [message #146145 is a reply to message #146118]

Tue, 08 November 2005 21:05

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Is the record group working for you?

How about extracting the data a different way?

Boy, oh boy, I just looked at the PAY_TO_ID field. You REALLY need three fields.

Anyway, if the record group is unhappy then try a sub-select with a 'group by' to get the PAY_TO_ID and then select the data without the 'group by' at the higher level.

David

Report message to a moderator

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

Report message to a moderator

Previous Topic:	how to connect different db reports to one menu
Next Topic:	Forms 9i: Runtime Java applet not loading

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 12:48:44 CDT 2024