Home » SQL & PL/SQL » SQL & PL/SQL » need to write user defined function on group by clause (oracle 10g)
need to write user defined function on group by clause [message #399195] Tue, 21 April 2009 01:43 Go to next message
mmtr
Messages: 4
Registered: April 2009
Location: DELHI
Junior Member
Hi all,
I have to write an user defined function eval_flag() that returns value on this logic

if any of the flag in group is 'y' then return 'Y'
else return 'N'.

Then it is called in the following select statement
select heirarchy.acct_no,
ar_new.ar_dt,
sum(ar_new.amt60_plus) sites_amt60_plus,
sum(ar_new.amt120_plus) sites_amt120_plus,
eval_flag(AR_NEW.PMT_PERF)
from ar_new join heirarchy on ar_new.cust_no = heirarchy.cust_no
GROUP BY acct_no,ar_dt


Regards
mmtr
Re: need to write user defined function on group by clause [message #399201 is a reply to message #399195] Tue, 21 April 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use MAX in its analytic form instead of a custom function.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: need to write user defined function on group by clause [message #399223 is a reply to message #399201] Tue, 21 April 2009 03:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think you'd even need to use it as an analytic - there's a group by on the whole query, so you can just do a MAX(CASE .....)

In fact, given that Y is greater than N in a string comparison, if the flags values are 'y' and 'n' you might be able to get away with MAX(upper(flag_column))
Re: need to write user defined function on group by clause [message #399233 is a reply to message #399223] Tue, 21 April 2009 03:54 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Tue, 21 April 2009 10:15
I don't think you'd even need to use it as an analytic - there's a group by on the whole query, so you can just do a MAX(CASE .....)

In fact, given that Y is greater than N in a string comparison, if the flags values are 'y' and 'n' you might be able to get away with MAX(upper(flag_column))

Yes you are right. With this NOT-formatted code I didn't see that all other returned fields where aggregate ones.

Regards
Michel
Previous Topic: regarding SQL query + unix Script
Next Topic: External Table
Goto Forum:
  


Current Time: Sat Dec 10 10:55:49 CST 2016

Total time taken to generate the page: 0.12172 seconds