• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

Oracle Views - Pl/SQL

Hi Experts,

How to I change rewrite this view to remove the hard coded values of (1623978)?

create or replace view vw_rpt_testinvoiceheader as
select s.status, s.workorderserviceid, s.servicenumber, s.servicedescription, s.taxable, s.overridestandardprice, 
case when wod.billingtypeid=4 then -1 else s.billseparate end as billseparate, s.baseprice, s.discountpercent,
 case when (wod.billingtypeid=4 or s.billseparate <> 0) then s.netprice else null end as net_price, 
 case when (wod.billingtypeid=4 or s.billseparate <> 0) then null else netperalloc end as alloc_price, s.pricingunit, 
 (select uomshortname from unitofmeasure where unitofmeasureid = s.serviceuomid) as uom, s.totalnetprice, s.createddate, s.createdby, s.lastupdated, 
 s.lastupdatedby,s.servicedescription2 
 from workorderservice s, workorderdetail wod where (s.workorderdetailid = 1623978) and s.workorderdetailid=wod.workorderdetailid 
union 

select null as status, 0 as workorderserviceid, null as servicenumber, b.billingtype as servicedescription, null as taxable, 
null as overridestandardprice, -.5 as billseparate, wod.unitbidamount as baseprice, null as discountpercent, wod.unitbidamount as net_price,
 null as alloc_price, 
 case when uom.uomtypeid = 2 then wo_priced_weight(1623978) when uom.uomtypeid = 3 then wo_priced_length(wod.workorderdetailid) 
 else wo_priced_qty(wod.workorderdetailid) end as pricingunit, 
 (select uomshortname from unitofmeasure where unitofmeasureid = b.unitofmeasureid) as uom, 
 
 GOLD_ws_workorder.wo_extendednet(wod.workorderdetailid) as totalnetprice, null as createddate, null as createdby, null as lastupdated, null as lastupdatedby,
 null as servicedescription2 from workorderdetail wod, billingtype b, unitofmeasure uom 
 where (wod.workorderdetailid = 1623978) and wod.billingtypeid <> 4 and wod.billingtypeid = b.billingtypeid and b.unitofmeasureid = uom.unitofmeasureid(+) 
 
 union 
 
 select null as status, 0 as workorderserviceid, null as servicenumber, 'total net' as servicedescription, null as taxable, null as overridestandardprice, -1.5 as billseparate,
  null as baseprice, null as discountpercent, null as net_price, null as alloc_price, null as pricingunit, null as uom, 
  GOLD_ws_workorder.wo_totalnet(wod.workorderdetailid) as totalnetprice, 
  null as createddate, null as createdby, null as lastupdated, null as lastupdatedby,null as servicedescription2 
  from workorderdetail wod, billingtype b, unitofmeasure uom where (wod.workorderdetailid = 1623978) and wod.billingtypeid = b.billingtypeid 
  and b.unitofmeasureid = uom.unitofmeasureid(+) order by billseparate desc, servicenumber,workorderserviceid

Open in new window

0
December2000
Asked:
December2000
  • 6
  • 6
1 Solution
 
sdstuberCommented:
you could pull the value from a table

 WHERE     (wod.workorderdetailid = (select value from some_table where key = 'Work Order Lookup'))

you could pull the value from a function

 WHERE     (wod.workorderdetailid = your_function)

or package function


 WHERE     (wod.workorderdetailid = your_package.your_function)

or from a system context

 WHERE     (wod.workorderdetailid = sys_context('Work Order','Detail ID'))
0
 
slightwv (䄆 Netminder) Commented:
Replace them with what?

Where will the values come from?

If you are thinking a parameterized view, Oracle doesn't really support them.  There is a solution to them but it is messy:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1448404423206

Better to just create the view without trying to limit the rows in the view and limit the results in the query that uses the view.
0
 
sdstuberCommented:
in all 4 options, you will need to determine how to get the value into whatever you use.

is the table a temp table or a normal table?  is the value relatively static (load once for all to use) or does it change all the time (each user can select a new one)  if the latter you'll probably want a temp table and you'll need to add a step prior to querying the view so that user can populate the table.


If you use a function (package or stand alone)  where does it get the value?  if a table, then you have the same choices above.
if it's a constant, you'll need to define it in code somewhere.
if it's variable, then you'll need to define the variable somewhere and then, like the table, add a step where you populate the variable.


for a system context, you'll need to add a step where you populate the context as well as creating the code to do so.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
December2000Author Commented:
I am a newbie to Oracle.  But yes, I want to rewrite the view without limiting the query results. I  was going parameterize in SSRS.
0
 
December2000Author Commented:
I dont know how to to remove the hard coded values so that the result set will not be limited to one work order.
0
 
sdstuberCommented:
add the workorderdetailid  to the list of columns returned by the view.

then you can simply


select * from vw_rpt_testinvoiceheader where workorderdetailid = 1623978

but,  if your view is used by other code that isn't expecting that column modifying it could cause problems, in which case you'll have to use one of the embedded filtering methods above
0
 
December2000Author Commented:
It is referencing tables, since it is 3 unions and the workorderdetailid is hard coded in each section of the union, where would I add the column?
0
 
sdstuberCommented:
in all 3 queries of the unions and you wouldn't put the filter in the view at all


CREATE OR REPLACE VIEW vw_rpt_testinvoiceheader
AS
    SELECT wod.workorderdetailid,
           s.status,
           s.workorderserviceid,
           s.servicenumber,
           s.servicedescription,
           s.taxable,
           s.overridestandardprice,
           CASE WHEN wod.billingtypeid = 4 THEN -1 ELSE s.billseparate END AS billseparate,
           s.baseprice,
           s.discountpercent,
           CASE WHEN (wod.billingtypeid = 4 OR s.billseparate <> 0) THEN s.netprice ELSE NULL END
               AS net_price,
           CASE WHEN (wod.billingtypeid = 4 OR s.billseparate <> 0) THEN NULL ELSE netperalloc END
               AS alloc_price,
           s.pricingunit,
           (SELECT uomshortname
              FROM unitofmeasure
             WHERE unitofmeasureid = s.serviceuomid)
               AS uom,
           s.totalnetprice,
           s.createddate,
           s.createdby,
           s.lastupdated,
           s.lastupdatedby,
           s.servicedescription2
      FROM workorderservice s, workorderdetail wod
     WHERE s.workorderdetailid = wod.workorderdetailid
    UNION
    SELECT wod.workorderdetailid,
           NULL AS status,
           0 AS workorderserviceid,
           NULL AS servicenumber,
           b.billingtype AS servicedescription,
           NULL AS taxable,
           NULL AS overridestandardprice,
           -.5 AS billseparate,
           wod.unitbidamount AS baseprice,
           NULL AS discountpercent,
           wod.unitbidamount AS net_price,
           NULL AS alloc_price,
           CASE
               WHEN uom.uomtypeid = 2 THEN wo_priced_weight(1623978)
               WHEN uom.uomtypeid = 3 THEN wo_priced_length(wod.workorderdetailid)
               ELSE wo_priced_qty(wod.workorderdetailid)
           END
               AS pricingunit,
           (SELECT uomshortname
              FROM unitofmeasure
             WHERE unitofmeasureid = b.unitofmeasureid)
               AS uom,
           gold_ws_workorder.wo_extendednet(wod.workorderdetailid) AS totalnetprice,
           NULL AS createddate,
           NULL AS createdby,
           NULL AS lastupdated,
           NULL AS lastupdatedby,
           NULL AS servicedescription2
      FROM workorderdetail wod, billingtype b, unitofmeasure uom
     WHERE     wod.billingtypeid <> 4
           AND wod.billingtypeid = b.billingtypeid
           AND b.unitofmeasureid = uom.unitofmeasureid(+)
    UNION
    SELECT wod.workorderdetailid,
           NULL AS status,
           0 AS workorderserviceid,
           NULL AS servicenumber,
           'total net' AS servicedescription,
           NULL AS taxable,
           NULL AS overridestandardprice,
           -1.5 AS billseparate,
           NULL AS baseprice,
           NULL AS discountpercent,
           NULL AS net_price,
           NULL AS alloc_price,
           NULL AS pricingunit,
           NULL AS uom,
           gold_ws_workorder.wo_totalnet(wod.workorderdetailid) AS totalnetprice,
           NULL AS createddate,
           NULL AS createdby,
           NULL AS lastupdated,
           NULL AS lastupdatedby,
           NULL AS servicedescription2
      FROM workorderdetail wod, billingtype b, unitofmeasure uom
     WHERE wod.billingtypeid = b.billingtypeid AND b.unitofmeasureid = uom.unitofmeasureid(+)
    ORDER BY billseparate DESC, servicenumber, workorderserviceid
0
 
December2000Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for December2000's comment #37823333

for the following reason:

Awesome! Thank you :)
0
 
sdstuberCommented:
don't accept your own post.

accept the post (or posts) that helped you
0
 
December2000Author Commented:
Awesome, Thank you :0)
0
 
sdstuberCommented:
nevermind, you figured it out.  :)

glad to help  - remember, modifying your view to produce different results "could" break other code that uses "select * " since you're adding a new column that wasn't expected.
0
 
December2000Author Commented:
lol, my mistake! Thank you soo much sdstuber
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now