December2000
asked on
Oracle Views - Pl/SQL
Hi Experts,
How to I change rewrite this view to remove the hard coded values of (1623978)?
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
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.
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.
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.
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.
ASKER
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.
ASKER
I dont know how to to remove the hard coded values so that the result set will not be limited to one work order.
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
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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
Accepted answer: 0 points for December2000's comment #37823333
for the following reason:
Awesome! Thank you :)
don't accept your own post.
accept the post (or posts) that helped you
accept the post (or posts) that helped you
ASKER
Awesome, Thank you :0)
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.
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.
ASKER
lol, my mistake! Thank you soo much sdstuber
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'))