alternative to view question.

[jcisco2]

i have a crystal report that calls a view on my sql server. the query runs just fine.. but the table is getting a wee bit to big so its starting to hang my report. what i would like to be able to do is pass in a where statement into my select statement, example WHERE F.OrderNumber = '0050517' this way my crystal will not bomb out. can a view take in a value? or is there some other way of goign about this problem. all i can think of is creating a SPROC that my application fires and it would copy the needed data into a temp table. and have that view look off the temp table that the SPROC filled. or does someone else have another way they accomplish this task?

cheers.

SELECT     '708971' + B.ItemNumber AS UPC, B.ItemNumber, D.ItemDescription, SUM(CAST(B.Quantity AS int)) AS Qty, SUM(CAST(D.Weight AS float)) AS totalWT, 
                      B.OrderNumber, D.Weight, SUM(CAST(B.Quantity AS int)) / E.PercentUnitLoadMult AS Unit, A.ShippedDate, A.ShipTo_Name, A.ShipTo_Address, 
                      A.ShipTo_Address2, A.ShipTo_City, A.ShipTo_State, A.ShipTo_Zip, A.OrderNumber AS OrderNumber , A.CarrierName, C.LoadNumber, C.StopNumber, 
                      A.PONumber, A.ShippedDate AS ShippedDate , F.PlannedShipDate, F.PromiseDate
FROM         dbo.ConfirmationHeader A INNER JOIN
                      dbo.ConfirmationDetail B ON A.OrderNumber = B.OrderNumber INNER JOIN
                      TranWorking.dbo.tblOrderMasterfile C ON C.OrderNumber = B.OrderNumber INNER JOIN
                      QueryMaster.dbo.IM1_InventoryMasterfile D ON D.ItemNumber = B.ItemNumber INNER JOIN
                      TranMaster.dbo.tblInventoryMasterfile E ON E.ItemNumber = B.ItemNumber INNER JOIN
                      TranWorking.dbo.tblOrderMasterfile F ON F.OrderNumber = A.OrderNumber
GROUP BY B.OrderNumber, B.ItemNumber, A.ShippedDate, A.ShipTo_Name, A.ShipTo_Address, A.ShipTo_Address2, A.ShipTo_City, A.ShipTo_State, 
                      A.ShipTo_Zip, A.OrderNumber, A.CarrierName, C.LoadNumber, C.StopNumber, D.ItemDescription, D.Weight, A.PONumber, F.PlannedShipDate, 
                      F.PromiseDate, E.PercentUnitLoadMult

 

[jcisco2]

nevermind i'm just having a day all ready.
select and query my view. which crystal was already doing. i just wasn't passing in all my parms to my crystal report. it was my error.

sorry.

SELECT
v_OutsideBOL.UPC, v_OutsideBOL.ItemNumber, v_OutsideBOL.ItemDescription, v_OutsideBOL.Qty, v_OutsideBOL.OrderNumber, v_OutsideBOL.Weight, v_OutsideBOL.Unit, v_OutsideBOL.ShippedDate, v_OutsideBOL.ShipTo_Name, v_OutsideBOL.ShipTo_Address, v_OutsideBOL.ShipTo_Address2, v_OutsideBOL.ShipTo_State, v_OutsideBOL.ShipTo_Zip, v_OutsideBOL.CarrierName, v_OutsideBOL.LoadNumber, v_OutsideBOL.StopNumber, v_OutsideBOL.PONumber, v_OutsideBOL.PlannedShipDate, v_OutsideBOL.PromiseDate
FROM
PWMS.dbo.v_OutsideBOL v_OutsideBOL
WHERE
v_OutsideBOL.OrderNumber = '0050101'

 

[mrdenny]

If your table is going to continue to grow, you may want to look into using a stored procedure instead of a view.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jcisco2]

is a sproc faster than querying the view?

 

[SQLSister]

Often yes it is faster because you can put parameters on it. Indexed views may have better performance so you will need to test. But a view creates the whole data set and then the parmeters of the query calling the view are applied. Ina a stored porcedure you can pull only the records you need by how you write the t-SQl using the iunput parameters.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief