Pass parameters to oracle plsql function

Question

I have written two queries.

query 1:

select stock_id from_id, 
       nvl(lead(stock_id, 1, null) over (order by stock_date),stock_id) to_id
  from STOCK
 where stock_time not like 'NEW_STOCK';

The above query returns result like below:

FROM_ID TO_ID
331     341
341     351
351     361
361     371
371     391
391     401
401     421
421     441
441     451
451     461
461     321
321     323
323     491
491     501
501     501

My second query is

 select max(m.material_thikness) || 
        ' mm' || ' | ' || 
        m.material_guage || 
        ' g' guage,    
        sum(util.find_usage_from_stock(MATERIAL_THIKNESS,P_FROM_ID,P_TO_ID)) -
        sum(util.find_sheets_sold(MATERIAL_THIKNESS,P_FROM_ID,P_TO_ID)) diff
  from material m
 where m.active like 'Y'
 group by m.material_guage
 order by m.material_guage;

The above query returns results like below:

GUAGE           DIFF
6 mm | 11 g     0
4 mm | 12 g     -0.32
3 mm | 13 g     0.51
2 mm | 14 g     0.85
1.5 mm | 16 g   -0.41
1.2 mm | 18 g   0.35
1 mm | 19 g     1.67
.8 mm | 20 g    0

I need to add FROM_ID and TO_ID return from my first query into P_FROM_ID and P_TO_ID in my second query and I need to modify my second query to get results like below.

RANGE     DIFF
331-341   0
341-351   0.35
351-361   0.45
.......
441-451   1.25
451-461   -0.75
461-321   1.67

How could I do that ?

does all MATERIAL_THIKNESS should be run with all pairs of <from_id, to_id> ? — A.B.Cade
– A.B.Cade, Commented Jan 31, 2012 at 11:39

A.B.Cade · Accepted Answer · 2012-01-31 12:55:32Z

1

If you want to get values for every material_thikness for all the p_id intervals then:

select  s.from_id || '-' ||s.to_id range,
        sum(util.find_usage_from_stock(MATERIAL_THIKNESS,s.from_id,s.to_id)) -
        sum(util.find_sheets_sold(MATERIAL_THIKNESS,s.from_id,s.to_id)) diff
  from material m, (select stock_id from_id, 
                                         lead(stock_id, 1, stock_id) 
                                             over (order by stock_date) to_id
                                    from STOCK
                                   where stock_time not like 'NEW_STOCK') s
 where m.active like 'Y'
 group by s.from_id,s.to_id
 order by s.from_id;

answered Jan 31, 2012 at 12:55

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark J. Bobak · Accepted Answer · 2012-01-31 12:26:44Z

Are you looking for something like this:

 select max(m.material_thikness) || 
        ' mm' || ' | ' || 
        m.material_guage || 
        ' g' guage,    
        sum(util.find_usage_from_stock(MATERIAL_THIKNESS,P_FROM_ID,P_TO_ID)) -
        sum(util.find_sheets_sold(MATERIAL_THIKNESS,P_FROM_ID,P_TO_ID)) diff
  from material m
 where m.active like 'Y'
   and (m.p_from_id,m.p_to_id) in(select stock_id from_id, 
                                         nvl(lead(stock_id, 1, null) 
                                             over (order by stock_date),stock_id) to_id
                                    from STOCK
                                   where stock_time not like 'NEW_STOCK')
 group by m.material_guage
 order by m.material_guage;

Will that do it?

Collectives™ on Stack Overflow

Pass parameters to oracle plsql function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related