Solved

# function return many values

Posted on 2014-02-11
327 Views
i have table uom , contain data of packing unit per item , like this
item_no  number
unit        number
unit_name  varchar2
un_pack  number

if i select data for one item , it looks like this
``````unit   unit_name   un_pack
6        box                10,8864
1       kg                     1
2       packet              0.907
3      gm                    0.001
``````

as table designed ,  the UOM for each item is the one its un_pack value = 1
for the above example, KG
then the values of the other units is pre_defind as how much it equal to the main UOM
for our example : 1 box = 10.8864 kg
one packet = 0.907 kg ,, etc
i want now to make cross_tab query to out put the above data as shown below
1 box = 12 packet (10.8864/0.907)
1 packet = 0.907 kg
note : the other items may has more or less than 4 units
0
Question by:NiceMan331
• 14
• 12

Author Comment

ID: 39849805
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
``````
0

LVL 76

Expert Comment

ID: 39849996
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.
0

Author Comment

ID: 39850571
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
0

LVL 76

Expert Comment

ID: 39850635
>>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.
0

Author Comment

ID: 39851413
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
``````

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
0

LVL 76

Expert Comment

ID: 39851425
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
0

Author Comment

ID: 39851523
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
0

LVL 76

Expert Comment

ID: 39851535
>>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.
0

Author Comment

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

LVL 76

Expert Comment

ID: 39851767
>> 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.
0

Author Comment

ID: 39853125
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
0

LVL 76

Expert Comment

ID: 39853137
>> 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
``````

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
0

Author Comment

ID: 39854041
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
``````
0

LVL 76

Expert Comment

ID: 39854512
>>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

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
)
/
``````
0

Author Comment

ID: 39855405
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
``````

and so on
0

LVL 76

Expert Comment

ID: 39856770
>>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.
0

Author Comment

ID: 39859211
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
0

LVL 76

Expert Comment

ID: 39859224
>> 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.
0

Author Comment

ID: 39859296
i already did it here :
http://www.experts-exchange.com/Database/Oracle/Q_28361780.html#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
0

LVL 76

Expert Comment

ID: 39859307

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
)
``````
0

Author Comment

ID: 39859388
ok
i will try it tomorrow from my office
0

Author Comment

ID: 39864668
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
``````

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
``````

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

LVL 76

Expert Comment

ID: 39866082
Please post what query you have now.
0

Author Comment

ID: 39866560
``````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)
)
``````
0

LVL 76

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 39867344
Try this:

select
1 || max("1st_unit_name") || ' = ' || (max("1st_unit_no")/(max("2nd_unit_no")) || max("2nd_unit_name")
from
(
...
0

Author Comment

ID: 39869490
great
it is ok now
i will repair it as per my original query
0

## Featured Post

### Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.