Solved

function return many values

Posted on 2014-02-11
26
327 Views
Last Modified: 2014-02-18
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

Open in new window


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
Comment
Question by:NiceMan331
  • 14
  • 12
26 Comments
 

Author Comment

by:NiceMan331
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

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
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

by:NiceMan331
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

by:slightwv (䄆 Netminder)
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

by:NiceMan331
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

Open 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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
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

by:NiceMan331
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

by:slightwv (䄆 Netminder)
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

by:NiceMan331
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

by:slightwv (䄆 Netminder)
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

by:NiceMan331
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

by:slightwv (䄆 Netminder)
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

Open 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
0
 

Author Comment

by:NiceMan331
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

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
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

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

Open in new window

0
 

Author Comment

by:NiceMan331
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

Open in new window


and so on
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
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

by:NiceMan331
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

by:slightwv (䄆 Netminder)
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

by:NiceMan331
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

by:slightwv (䄆 Netminder)
ID: 39859307
>>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
)

Open in new window

0
 

Author Comment

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

Author Comment

by:NiceMan331
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

Open 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

Open in new window


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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39866082
Please post what query you have now.
0
 

Author Comment

by:NiceMan331
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)
)

Open in new window

0
 
LVL 76

Accepted Solution

by:
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

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now