CODEWITHSUNDEEP
oracle-databasefunctionoracle10goracle-apex
Bishan
Bishan

Reputation: 15702

Pass parameters to oracle plsql function

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 ?

Upvotes: 0

Views: 328

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

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;

Upvotes: 1

Mark J. Bobak
Mark J. Bobak

Reputation: 14385

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?

Upvotes: 0

Related Questions