Home » SQL & PL/SQL » SQL & PL/SQL » How to return row list? (merged)
How to return row list? (merged) [message #316405] Fri, 25 April 2008 00:39 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I have a query

select name, deptno from dept.

Output is

John Manufacturing

However, I want the output to be

John 420 Manufacturing
John 445 Manufacturing
John 133 Manufacturing
John 764 Manufacturing
John 845 Manufacturing
John 673 Manufacturing

The Numbers are statuses which I shall return by calling a function.

Now, I want the function to return a record list so that the John and manufacturing get duplicated and my final result set has the same number of records that the function returns.

For this, what datatype should I return from the function and could you please provide me with the syntax?

Thanks,

Sharath

Report message to a moderator

Re: Can Function return a list? [message #316409 is a reply to message #316405] Fri, 25 April 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes it is possible, create your own table type but why not returning a ref cursor?

Regards
Michel

Report message to a moderator

Re: Can Function return a list? [message #316439 is a reply to message #316409] Fri, 25 April 2008 02:31 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Michel,

When I use a cursor, the result set that I get is

John <cursor> Manufacturing.

How can I pass this to the code?

Let me make my requirement a bit more clear

This is my query

SELECT
  PARENT,
  CHILD,
  MIN(SAL)
FROM
  PARENTCHILD,
  SAL
WHERE
  PARENTCHILD.NAME = SAL.NAME
GROUP BY
  PARENT
HAVING
  MIN(SAL) > 2500;


Now here, the children have salaries and not the parents, I wish to return the minimum of the childrens salary as the salary of the parent.

You can say that I could write it like this

SELECT
  PARENT,
  MIN(SAL)
FROM
  PARENTCHILD,
  SAL
WHERE
  PARENTCHILD.NAME = SAL.NAME
GROUP BY
  PARENT
HAVING
  MIN(SAL) > 2500;


However, they wish to see the parent, the minimum salary of the children and their children names too.

So, I wish to return the children's names through a separate function which uses this query

select
  child
from 
  parentchild
where parent = parentvalue;


This will return multiple records.

How do I write a function to return multiple records?

The final record set would be like this

Parent Child Min(sal)
JOHN JESSICA 2560
JOHN JASON 2560
JOHN BRIAN 2560
JOHN MOLLY 2560
DAVE SEAN 2600
DAVE MIKE 2600

Here the minimum salary of Johns Children may be that of jessica, however it should as the same for all of his children

Thanks so much for your time.

Sharath

[Updated on: Fri, 25 April 2008 02:50]

Report message to a moderator

How to return row list? [message #316468 is a reply to message #316405] Fri, 25 April 2008 04:23 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

This is my query

SELECT
  PARENT,
  CHILD,
  MIN(SAL)
FROM
  PARENTCHILD,
  SAL
WHERE
  PARENTCHILD.NAME = SAL.NAME
GROUP BY
  PARENT
HAVING
  MIN(SAL) > 2500;


Here, the children have salaries and not the parents, I wish to return the minimum of the childrens salary as the salary of the parent.

You could say that I could write it like this

SELECT
  PARENT,
  MIN(SAL)
FROM
  PARENTCHILD,
  SAL
WHERE
  PARENTCHILD.NAME = SAL.NAME
GROUP BY
  PARENT
HAVING
  MIN(SAL) > 2500;


However, they wish to see the parent, the minimum salary of the children and their children names too.

So, I wish to return the children's names through a separate function which uses this query
select
  child
from 
  parentchild
where parent = parentvalue;


This will return multiple records.

How do I write a function to return multiple records?

The final record set would be like this

Parent Child Min(sal)
JOHN JESSICA 2560
JOHN JASON 2560
JOHN BRIAN 2560
JOHN MOLLY 2560
DAVE SEAN 2600
DAVE MIKE 2600

Here the minimum salary of Johns Children may be that of jessica, however it should as the same for all of his children

Thanks so much for your time.

Sharath

[Updated on: Fri, 25 April 2008 04:25]

Report message to a moderator

Re: How to return row list? [message #316469 is a reply to message #316468] Fri, 25 April 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why do you start a new topic?
Why don't you format your post?

Regards
Michel

Report message to a moderator

Re: How to return row list? [message #316471 is a reply to message #316469] Fri, 25 April 2008 04:30 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Sorry Michel,

I did it by mistake.

Could you please answer my query?

How do I write a function to return multiple records without using a cursor?

Sharath

Report message to a moderator

Re: How to return row list? [message #316478 is a reply to message #316471] Fri, 25 April 2008 05:24 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
By using ANALYTICAL().
select deptno,
        ename,
        sal,
        min(sal) keep (dense_rank first order by sal) over (partition by deptno) min_sal
from emp



regards,

Report message to a moderator

Previous Topic: pad/fill blank spaces (merged 4 topics)
Next Topic: Table Types
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Nov 12 20:51:50 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.