function return many values

sorry
the out put should be like this

item_no , 1st_unit_no, 1st_unit_name , 2nd_unit_no , 2nd_unit_name  , 3rd_unit_no , 3rd_unit_name
12000        1               box                      12                    packet              .907     kg

Select allOpen in new window

I'm guessing a little here that as the list changes so does the number of expected columns in the output?

You have to know the number of columns up front.

I would look at returning a collection, cursor or XML and let the calling program process the data.

we can use max function to know the maximum number of units
but if it should be fixed numbers of output ,  i accept first 3 returned units

>>the out put should be like this

I was trying to set up a test case and the data doesn't make sense with that expected output.

For example the data shows 'box' with a unit of 6.  kg has unit of 1.

The expected output has 1 for box unit and .907 for kg.

I don't see how you get from the sample data to the expected results.

For example the data shows 'box' with a unit of 6.  kg has unit of 1.

every unit has code , the 6 is unit code for box , and 1 is code of KG and so on

The expected output has 1 for box unit and .907 for kg.

no , look again

unit   unit_name   un_pack
6        box                10,8864
1       kg                     1
2       packet              0.907
3      gm                    0.001

Select allOpen in new window

each un_pack for any item is the equivalent measure of the unit which equal to 1
the major unit here is KG because its un_pack = 1
then , each un_pack for other units is how much to KG
1 box = 10.8864 KG
1 packet = 0.907  KG
1 gm = 0.001 KG
and then
1 box = 12 packet because 10.886 div 0.907 = 12
the expected final result here is :
1 box = 12 packet , 1 packet = 0.907 kg

hope it is clear now

OK, I think I understand the math.

I still don't see how you plan on taking the 4 rows from the table and produce the results you posted in http:#a39849805

if oracle provide cross_tab query mode it will be easy , but i don't think
so ,my expected solution is to use function with multi out put
the input parameter will be the item_code
then will determine 6 outputs
1st_unit_code :  " always 1"
1st_unit_name : is the name of the biggest unit  "box"
2nd_unit_code : is the pack of the 1st unit div the pack of 2nd one  "10.886 / 0.907=12 "
2nd_unit_name : is the name of the 2nd unit  "packet"
3rd_unit_code : is the pack of the 2nd unit div the pack of 3rd one  "0.907/1=0.907 "
3rd_unit_name : is the name of the 3rd unit  "kg"
note :
we will consider the unit which = 1 as the last one always , so when choosing the 2nd unit , we will exlude the kg , but we will include it as the 3rd unit always
in some items , it may have one unit only = 1 , so we will output only the 1st unit

>>is the pack of the biggest unit " 1"

I know that YOU know 1 is the 'biggest'.  How does the computer know it is the biggest?

>>oracle provide cross_tab query mode it will be easy

Oracle now has a PIVOT sql function:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#SQLRF55389

There are also ways to mimic them.  I know this is what you want but I don't understand the actual 'logic' that defines what goes in 1st_unit_code versus what goes in 2nd_unit_code.

Well, I understand your description but I don't know how to tell Oracle what goes where just based on the data you provided.

For example:
Say I tell you I have the following data:
1
2
5
6

I want it in the order:
1
6
2
5

What I don't tell you is how that order is achieved.

I agree with you , let me do somthing tomorrow , but just for now plz but an example if multi output function

>> but just for now plz but an example if multi output function

I also don't know what you want your unction to return.

I can easily provide a dummy manual PIVOT from dummy data but I don't see how that will help.

Can I guess that you want to pass in an id to the function and have the function return a 'et' of results?

Again, pretty easy using any method I posted in http:#a39849996

What object you return is driven by the requirements and calling program.

I need to know how the results will be used by the calling program before I can suggest what method to use.

We need more detailed requirements.

i inspect the number of units per items , i found it is maximum 4
let say we will design cursor to loop between units of one item
it will ascend the records by packing value by descending order
then
while fetching , 1st record should be the highest unit ,
here we will output  a value of 1
then will concatenate the unit name
then concatenate its pack value/pack value of next record ,
concatenate the unit name of the next to it
concatenate its packing value/pack value of the next
 , concatenate the next unit ,, etc up to the last record in the cursor

>> 1st record should be the highest unit , here we will output  a value of 1

There might be a language issue so I need to clarify:  To me 'highest unit'  would be 6 using your sample data.

unit   unit_name   un_pack
6        box                10,8864
1       kg                     1
2       packet              0.907
3      gm                    0.001

Select allOpen in new window

Are you saying that you want the output ordered by the unit column?

So the output is:
kg   1  packet  0.907   gm  0.001   box 10.8864

To me 'highest unit'  would be 6

THE HIGEST unit is box because its pack values is 10.886 ,which is the higest value in that array, then kg because = 1 , then packet = 0.907 , last is gm


1 box =10,886/1 kg
1box= 10,886/0.907 packet
1box = 10.886/0.001 gm
1 packet = 0.907/1 kg
1 packet = 0.907/0.001 gm
1 kg = 1/0.001 gm

but we will delay the kg because its value = 1 , to the third always
so , the output will be

1 box  (10.886 / 0.907 = 12) packet (0.907/1 = 0.907 ) kg (1/0.001 = 0.001 ) gm

Select allOpen in new window

>>THE HIGEST unit is box because its pack values is 10.886 ,which is the higest value in that array, then kg because = 1 , then packet = 0.907 , last is gm

OK, it looks like you want to order by un_pack descending.

That is until you look at your desired results.

Ordering by un_pack produces the order:
box, kg, packet, gm

then your desired results are:
box, packet, kg, gm

I still don't see the correct order in the output.


Here is the SQL that produces the first step, the pivot.  Hopefully this will help you see my issue.

Once you figure the ordering out it should be a simple matter to manipulate the data.

drop table tab1 purge;
create table tab1(unit number, unit_name varchar2(6), un_pack number);

insert into tab1 values (6,'box',10.8864);
insert into tab1 values (1,'kg',1);
insert into tab1 values (2,'packet',0.907);
insert into tab1 values (3,'gm',0.001);
commit;
                                            
select
	case when myrn=1 then un_pack end "1st_unit_no",
	case when myrn=1 then unit_name end "1st_unit_name",
	case when myrn=2 then un_pack end "2nd_unit_no",
	case when myrn=2 then unit_name end "2nd_unit_name",
	case when myrn=3 then un_pack end "3rd_unit_no",
	case when myrn=3 then unit_name end "3rd_unit_name",
	case when myrn=4 then unit_name end "4th_unit_name"
from
(
	select unit_name, un_pack, row_number() over(order by un_pack desc) myrn
	from tab1
)
/

Select allOpen in new window

as 1st step it is ok

now , we can out put like this :

1 || 1st_unit_name || ' = ' || (1st_unit_no/2nd_unit_no) || 2nd_unit_name 
1 || 1st_unit_name || ' = ' || (1st_unit_no/3rd_unit_no) || 3rd_unit_name
1 || 1st_unit_name || ' = ' || (1st_unit_no/4th_unit_no) || 4th_unit_name

1 || 2nd_unit_name || ' = ' || (2nd_unit_no/3rd_unit_no) || 3rd_unit_name

Select allOpen in new window

and so on

>>now , we can out put like this :

That was my plan.  I just need to understand your ordering.

If you need additional assistance with that, I need more information since your explanation of how it works and the data you are showing as desired results don't seem to match.

for sure i need more assitance , but what additional explanation you ask from me ?
in simple word , i need to set every unit to the other, like this :

1 box =10,886/1 kg
1box= 10,886/0.907 packet
1box = 10.886/0.001 gm
1 packet = 0.907/1 kg
1 packet = 0.907/0.001 gm
1 kg = 1/0.001 gm

>> i need to set every unit to the other, like this :

I need to know how you take the raw data from the table and get it into that order.

This is the same thing I've been asking for a while.

To get it into that order, you need an order by on the select.

If you can give me the basic select that produces the data in the order you want, just plug it into my example and you should have what you need.

i already did it here :
https://www.experts-exchange.com/questions/28361780/function-return-many-values.html?anchorAnswerId=39855405#a39855405

the order is by the value of un_pack , but still not so important , we can set all to all
i mean if you success to set every unit to other all , it will be great
thanx

>>i already did it here

If you already have the order and posted it, what piece are you missing?

You can take what I posted and wrap another select statement around it, like what you posted, and you have it.


Note the double quotes around the column alias is important since Oracle doesn't like names that start with a number.

If you change the alias names, you don't need the double quotes.

Taking the first line from what you posted above, something like this?  You can add whatever additional information you want.

select
1 || "1st_unit_name" || ' = ' || ("1st_unit_no"/"2nd_unit_no") || "2nd_unit_name"
from 
(
select
	case when myrn=1 then un_pack end "1st_unit_no",
	case when myrn=1 then unit_name end "1st_unit_name",
	case when myrn=2 then un_pack end "2nd_unit_no",
...--the rest of my select
)

Select allOpen in new window

ok
i will try it tomorrow from my office

the output of the 1st select is :

10.8864	box	(null)	(null)	(null)	(null)	(null)
(null)	(null)	1	kg	(null)	(null)	(null)
(null)	(null)	(null)	(null)	0.907	packet	(null)
(null)	(null)	(null)	(null)	(null)	(null)	gm

Select allOpen in new window

in the 2nd select it becomes like this

1||"1ST_UNIT_NAME"||'='||("1ST_UNIT_NO"/"2ND_UNIT_NO")||"2ND_UNIT_NAME" 
----------------------------------------------------------------------- 
1box =                                                                  
1 = kg                                                                  
1 =                                                                     
1 =                                                                     

4 rows selected

Select allOpen in new window

is needs something like group by to return the correct output ?

Please post what query you have now.

select
1 || "1st_unit_name" || ' = ' || ("1st_unit_no"/"2nd_unit_no") || "2nd_unit_name"
from 
(
select
	case when myrn=1 then un_pack end "1st_unit_no",
	case when myrn=1 then unit_name end "1st_unit_name",
	case when myrn=2 then un_pack end "2nd_unit_no",
	case when myrn=2 then unit_name end "2nd_unit_name",
	case when myrn=3 then un_pack end "3rd_unit_no",
	case when myrn=3 then unit_name end "3rd_unit_name",
	case when myrn=4 then unit_name end "4th_unit_name"
from
(
	select unit_name, un_pack, row_number() over(order by un_pack desc) myrn
	from tab1)
)

Select allOpen in new window

great
it is ok now
i will repair it as per my original query