Altering a view within a stored procedure?

[AccessUser22]

Is it possible to alter a view within a stored procedure?

 

[AlexCuse]

I imagine it is, but I don't think it is a very good idea. Why do you need to do this?

Why not just have a separate view that looks just like what the stored procedure wants?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AccessUser22]

Actually it's not possible. Just found it on the msdn.

The problem is I have a stack of views that run off of one another and in the middle view, a date range and CompanyID are supplied. It's from this date range and ID that the data is truncated and then sums are run and appropriate rates are tied to these values in my top queries. I'll have to figure out a way to supply my date range at the top, but it is not going to be easy.

If I was able to alter the middle view within the stored proc and supplied it with the new dates and Company ID, it would be a whole lot easier. I already have it set up this way, but don't want to have to keep going into the middle view to alter the date range and ID each time I need to run the top view.

 

[gmmastros]

Views, in many respects, act like tables. So... If you remove the date range and other where clauses from the view, all of the data would be returned unless you specified filter conditions when you use the view. Ex:

Create View View_Show_All
AS
Select Col1, col2
from   table

Select * 
From   View_Show_All
Where  Col1 = 'Hello'
       And Col2 = 'World'

It is better to do things this way so that you don't need to alter the view, you just apply the filter conditions when you are using the view.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AccessUser22]

Yes, that makes sense, but I don't think that will work or help my situation.

Basically I'm running invoices that based on the number of items sold per period, the company gets a specific rate.
So my first three queries gather my data and put it into one view with the specified date range for the period. The next query sums up the total items sold in the period, and then the top query ties the 4th query back to the third queries data so that all the details can be shown and the appropriate rate determined by the 4th query can be applied. If I moved the date input to the top level query, I would not get an accurate sum in the 4th query as it would sum all of the data for all time.

I also can't move the sum to the top level query as it wouldn't work since I'm trying to show the supporting details as well. I would only get the sum per detail in that case.

 

[AlexCuse]

I think you are confusing views with temp tables. Views should be a way to display commonly accessed tables/groups of tables. If you are changing your parameters each time, then this is NOT commonly accessed.

Maybe you should modify the view as george suggested, then create a temp table by selecting from this view, and work with the data in there. Make sense?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AccessUser22]

So instead make the third level query a temp table and then run the top views off of that? So that when the stored proc runs, it populates the temp table and then runs the top queries?

I guess that could work. The only other thing I could think of was to create an invoice period table that holds the dates and then tie each record to it's appropriate period through the query. Then at the top I could just filter for the appropriate period number. That would be more restrictive though and an invoice period would have to be chosen rather than allowing me the flexibility of putting in a date range.

I'll give the temp table way a try and see how it goes. Thanks.

 

[AlexCuse]

Can you post your procedure?

What I think you should do is create a temp table and use that in place of your view, so you can collect the rows you need more easily. you shouldn't create a view off of a temp table I don't thik.

You may be able to fairly easily apply filters to your view when calculating the sums, etc... as George suggests above (i'm about 98% sure this is the case). Without seeing the proc (and view) its hard to say though.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AccessUser22]

Below are my views. I tried to clean them up a bit, but you should be able to get the general idea from this.

3rd level view:
SELECT WebProcessDate, OrderDate, OrderID, ContractNo, ItemCategory, Receipt, Item No, COUNT(ItemNo) AS ItemCount, OrderingCompanyID
FROM dbo.vw_Sub2
WHERE (WebProcessDate >= '2/24/2007 12:00 AM') AND (WebProcessDate < '3/31/2007 12:00 AM') AND (OrderingCompanyID = 1994)
GROUP BY WebProcessDate, OrderDate, OrderID, ContractNo, ItemCategory, Receipt, ItemNo, OrderingCompanyID

4th level view:
SELECT OrderingCompanyID, ItemCategory, SUM(ItemCount) AS SumItems
FROM dbo.vw_Sub3
GROUP BY OrderingCompanyID, ItemCategory

5th Top level view:

SELECT dbo.vw_Sub3.WebProcessDate, dbo.vw_Sub3.OrderDate,
dbo.vw_Sub3.OrderingCompanyID,
dbo.vw_Sub3.OrderID,
dbo.vw_Sub3.ContractNo, dbo.vw_Sub3.ItemCategory,
dbo.vw_Sub3.ItemNo,
dbo.vw_Sub3.Receipt,
dbo.vw__Sub3.ItemCount,
CASE WHEN vw_Sub3.[ItemCategory] = 'Steel' THEN CASE WHEN [SumItems] >= 100 THEN [SteelCost100Above]ELSE [SteelCostUnder100] END ELSE CASE WHEN [SumItems] >= 100 THEN [OtherCost100Above] ELSE [OtherCostUnder100] END END AS UnitCost,
CASE WHEN vw_Sub3.[ItemCategory] = 'Steel' THEN CASE WHEN [SumItems] >= 100 THEN ([SteelCost100Above]
* [ItemCount]) ELSE ([SteelCostUnder100] * [ItemCount])
END ELSE CASE WHEN [SumItems] >= 100 THEN [OtherCost100Above] * [ItemCount] ELSE [OtherCostUnder100] * [ItemCount] END END AS TotalCost, dbo.tblInvoicingVariables.RateStartDate, dbo.tblInvoicingVariables.RateEndDate, dbo.vw_Sub4Sum.SumItems
FROM dbo.vw_Sub3 INNER JOIN dbo.tblInvoicingVariables ON dbo.vw_Sub3.OrderingCompanyID = dbo.tblInvoicingVariables.CompanyID AND dbo.vw_Sub3GroupBy.OrderDate >= dbo.vw_Sub3.OrderDate <= dbo.tblInvoicingVariables.RateEndDate INNER JOIN
dbo.vw_Sub4Sum ON
dbo.vw_Sub3.OrderingCompanyID = dbo.vw_Sub4Sum.OrderingCompanyID
AND dbo.vw_Sub3.ItemCategory = dbo.vw_Sub4Sum.ItemCategory
ORDER BY dbo.vw_Sub3.ItemCategory, dbo.vw_Sub3.WebProcessDate,
dbo.vw_Sub3.OrderID

Currently the proc just pulls everything from the last stored procedure. Please let me know if you have any suggestions. Thanks.

 

[AlexCuse]

Alright I don't have a ton of time but here's what I came up with.

I don't know what you are trying to do here:

AND   #temp.OrderDate >=  #temp.OrderDate <= dbo.tblInvoicingVariables.RateEndDate

but whatever it is its' not working. I guessed by column names that what you meant was

and #temp.OrderDate <= dbo.tblInvoicingVariables.RateEndDate

Once you fix whatever that is, I think this will work for you. I replaced your two intermediate views with temp tables

[COLOR=green]---declare and set start/end date params
[/color][COLOR=blue]declare[/color] @startDate [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]declare[/color] @FinishDate [COLOR=#FF00FF]datetime[/color]

[COLOR=blue]set[/color] @startDate = [COLOR=red]'20070224'[/color]
[COLOR=blue]set[/color] @FinishDate = [COLOR=red]'20070331'[/color]

[COLOR=green]---create temp table simulating 'third level view'
[/color][COLOR=blue]select[/color] * [COLOR=blue]into[/color] #temp 
[COLOR=blue]from[/color] dbo.vw_Sub2
[COLOR=blue]where[/color] WebProcessDate >= @startDate
and WebProcessDate < @FinishDate
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] WebProcessDate, 
OrderDate, 
OrderID, 
ContractNo,  
ItemCategory, 
Receipt, 
ItemNo, 
OrderingCompanyID

[COLOR=green]---create table simulating '4th level view'
[/color][COLOR=blue]select[/color] OrderingCompanyID, 
ItemCategory,
sum(ItemCount) [COLOR=blue]as[/color] SumItems
[COLOR=blue]into[/color] #temp2
[COLOR=blue]from[/color] #temp
[COLOR=blue]group[/color] [COLOR=blue]by[/color] OrderingCompanyID,
ItemCategory

[COLOR=green]---query bringing it all together ('5th level view')
[/color][COLOR=blue]SELECT[/color] #temp.WebProcessDate,                    
#temp.OrderDate, 
#temp.OrderingCompanyID, 
#temp.OrderID, 
#temp.ContractNo,              
#temp.ItemCategory, 
#temp.ItemNo, 
#temp.Receipt, 
#temp.ItemCount, 
[COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] #TEMP.[ItemCategory] = [COLOR=red]'Steel'[/color] [COLOR=blue]THEN[/color] 
	[COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [SumItems] >= 100 [COLOR=blue]THEN[/color] 
		[SteelCost100Above][COLOR=blue]ELSE[/color] [SteelCostUnder100] [COLOR=blue]END[/color] 
[COLOR=blue]ELSE[/color] [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [SumItems] >= 100 [COLOR=blue]THEN[/color] 
		[OtherCost100Above] [COLOR=blue]ELSE[/color] [OtherCostUnder100] [COLOR=blue]END[/color] 
[COLOR=blue]END[/color] [COLOR=blue]AS[/color] UnitCost, 
[COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] vw_Sub3.[ItemCategory] = [COLOR=red]'Steel'[/color] [COLOR=blue]THEN[/color] 
	[COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [SumItems] >= 100 [COLOR=blue]THEN[/color] 
	([SteelCost100Above]* [ItemCount]) 
	[COLOR=blue]ELSE[/color] ([SteelCostUnder100] * [ItemCount]) [COLOR=blue]END[/color] 
[COLOR=blue]ELSE[/color] [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [SumItems] >= 100 [COLOR=blue]THEN[/color] 
	[OtherCost100Above] * [ItemCount] 
	[COLOR=blue]ELSE[/color] [OtherCostUnder100] * [ItemCount] [COLOR=blue]END[/color] 
[COLOR=blue]END[/color] [COLOR=blue]AS[/color] TotalCost, 
dbo.tblInvoicingVariables.RateStartDate, 
dbo.tblInvoicingVariables.RateEndDate, 
dbo.#Temp2.SumItems
[COLOR=blue]FROM[/color] #temp
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] dbo.tblInvoicingVariables 
[COLOR=blue]ON[/color]  #temp.OrderingCompanyID = dbo.tblInvoicingVariables.CompanyID 
AND #temp.OrderDate <= dbo.tblInvoicingVariables.RateEndDate 
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
#temp2 [COLOR=blue]ON[/color] 
#temp.OrderingCompanyID = #temp2.OrderingCompanyID
 AND #temp.ItemCategory = #temp2.ItemCategory 
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] #temp.ItemCategory, #temp.WebProcessDate, 
#temp.OrderID

[COLOR=green]---clean up temp tables
[/color][COLOR=blue]drop[/color] [COLOR=blue]table[/color] #temp
[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #temp2

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[AccessUser22]

Thanks for your time. I now see how that works by viewing what you did.

I also got it to work by creating a table the sets the invoice periods. This doesn't allow for the flexibility of being able to input in dates like your example would though.

Thanks for all your help.

 

[AlexCuse]

Glad it worked for you

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

AccessUser,
I'll pass on some information to you that someone passed on to me awhile ago. When someone helps you out like Alex has the nice thing to do is to thank him with a little purple star.

Nice job Alex.

- Paul
- If at first you don't succeed, find out if the loser gets anything.