Solved

Oracle Views - Pl/SQL

Posted on 2012-04-09
13
476 Views
Last Modified: 2012-04-09
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
Comment
Question by:December2000
  • 6
  • 6
13 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 37823270
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
 
LVL 76

Expert Comment

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

Expert Comment

by:sdstuber
ID: 37823281
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
 

Author Comment

by:December2000
ID: 37823290
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
 

Author Comment

by:December2000
ID: 37823292
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37823300
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:December2000
ID: 37823333
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37823353
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
 

Author Comment

by:December2000
ID: 37823445
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37823446
don't accept your own post.

accept the post (or posts) that helped you
0
 

Author Closing Comment

by:December2000
ID: 37823447
Awesome, Thank you :0)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37823451
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
 

Author Comment

by:December2000
ID: 37823454
lol, my mistake! Thank you soo much sdstuber
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to increase the row limit in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 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

16 Experts available now in Live!

Get 1:1 Help Now