Home » SQL & PL/SQL » SQL & PL/SQL » Conversion of SQL to function (10g)
Conversion of SQL to function [message #378188] Mon, 29 December 2008 05:06 Go to next message
zinger
Messages: 3
Registered: December 2008
Junior Member
We are migrating a number of applications from SQL Server to Oracle, so my exposure/experience in Oracle is very limited.

I had previously created a working user defined function in SQL Server, which processes data in this format.

Name, CC_Code
Alan, 3720
Alan, 3730
Alan, 3740
Anna, 5045
Barry, 5004
Barry, 7518

and produces this

Name, CC_Code
Alan, 3720, 3730, 3740
Anna, 5045
Barry, 5004, 7518

I was able to cobble together the select statement below, which returns the correct output, but would like to create this as a function.

SELECT name,
LTRIM(MAX(SYS_CONNECT_BY_PATH(CC_Code,', '))
KEEP (DENSE_RANK LAST ORDER BY curr),',')
FROM (SELECT name,
CC_Code,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY
CC_Code) AS curr,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY
CC_Code) -1 AS prev
FROM (select * from mnt_staff, mnt_cost_centre_staff
where MNT_STAFF.employee_number = MNT_COST_CENTRE_STAFF.employee_number
and mnt_cost_centre_staff.association_flag = 'S'))
GROUP BY name
CONNECT BY prev = PRIOR curr AND name = PRIOR name
START WITH curr = 1;

Any suggestions gratefully received.
Re: Conversion of SQL to function [message #378190 is a reply to message #378188] Mon, 29 December 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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).

Quote:
but would like to create this as a function.

Why?

Regards
Michel
Re: Conversion of SQL to function [message #378192 is a reply to message #378190] Mon, 29 December 2008 05:25 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

CREATE OR REPLACE FUNCTION myfunc
   RETURN sys_refcursor
IS
   p_ref   sys_refcursor;
BEGIN
   OPEN p_ref FOR
      SELECT <your query>;
    RETURN p_ref;
END;
Re: Conversion of SQL to function [message #378203 is a reply to message #378190] Mon, 29 December 2008 05:49 Go to previous messageGo to next message
zinger
Messages: 3
Registered: December 2008
Junior Member
Thanks for the prompt response.

Sorry about then formatting, will read the guidelines for future posts.

The resultant CC_Code string (3720, 3730, 3740) etc. is used in a report bursting process which sends out one email to each user, attaching a PDF of a report which contains all CC_Code for that user. As Alan is responsible for Cost Centres 3720, 3730, 3740 then he will get one report containing those three Cost Centres, Anna will only get a report for Cost Centre 5045 and Barry only for 5004, 7518.

Regards
Michael
Re: Conversion of SQL to function [message #378204 is a reply to message #378192] Mon, 29 December 2008 05:50 Go to previous message
zinger
Messages: 3
Registered: December 2008
Junior Member
Again, thanks for the very prompt response.
Will try out your suggestion once I get back on deck in eight hours.
Regards
Michael
Previous Topic: Can anyone guide me...
Next Topic: stored procedure inside a stored procedure dynamically
Goto Forum:
  


Current Time: Sat Dec 03 05:44:45 CST 2016

Total time taken to generate the page: 0.14050 seconds