June 18, 2010 at 4:40 am
--View1
CREATE VIEW dbo.VW_ReportSOSlipDetail
AS
SELECT dbo.Tbl_DemandIssue.BatchNo, dbo.VW_AllItemMaster.PartNo, dbo.VW_AllItemMaster.PartName, dbo.Tbl_RefItemClassification.ClassificationDesc,
dbo.Tbl_DemandIssueDetail.IssueQty, dbo.Tbl_DemandIssueDetail.Location, dbo.Tbl_RefUOM.UOM, dbo.Tbl_DemandIssueDetail.SerialNo,
dbo.Tbl_DemandIssueDetail.PartId, dbo.Tbl_DemandIssue.DemandNo, dbo.Tbl_DemandIssue.SlipNo
FROM dbo.Tbl_DemandIssue INNER JOIN
dbo.Tbl_RefItemClassification INNER JOIN
dbo.VW_AllItemMaster ON dbo.Tbl_RefItemClassification.ClassificationID = dbo.VW_AllItemMaster.ClassificationID ON
dbo.Tbl_DemandIssue.PartID = dbo.VW_AllItemMaster.PartID INNER JOIN
dbo.Tbl_RefUOM ON dbo.VW_AllItemMaster.UOM = dbo.Tbl_RefUOM.UOMID INNER JOIN
dbo.Tbl_DemandIssueDetail ON dbo.Tbl_DemandIssue.IssueLineNo = dbo.Tbl_DemandIssueDetail.IssueLineNo
--View2
CREATE VIEW dbo.VW_ReportSOSlipWithGroup
AS
SELECT SUM(dbo.Tbl_DemandIssueDetail.IssueQty) AS IssueQty, dbo.Tbl_DemandIssueDetail.Location, dbo.Tbl_DemandIssueDetail.PartId,
dbo.Tbl_ItemMaster.PartNo
FROM dbo.Tbl_DemandIssueDetail INNER JOIN
dbo.Tbl_ItemMaster ON dbo.Tbl_DemandIssueDetail.PartId = dbo.Tbl_ItemMaster.PartID
GROUP BY dbo.Tbl_DemandIssueDetail.Location, dbo.Tbl_DemandIssueDetail.PartId, dbo.Tbl_ItemMaster.PartNo
--Combination of View1 and View2
SELECT DISTINCT
dbo.VW_ReportSOSlipDetail.BatchNo, dbo.VW_ReportSOSlipDetail.PartName, dbo.VW_ReportSOSlipDetail.ClassificationDesc,
dbo.VW_ReportSOSlipDetail.UOM, dbo.VW_ReportSOSlipDetail.IssueQty, dbo.VW_ReportSOSlipWithGroup.Location,
dbo.VW_ReportSOSlipWithGroup.PartNo, dbo.VW_ReportSOSlipWithGroup.PartId, dbo.VW_ReportSOSlipDetail.DemandNo,
dbo.VW_ReportSOSlipDetail.SlipNo
FROM dbo.VW_ReportSOSlipDetail INNER JOIN
dbo.VW_ReportSOSlipWithGroup ON dbo.VW_ReportSOSlipDetail.PartId = dbo.VW_ReportSOSlipWithGroup.PartId
and VW_ReportSOSlipDetail.Location = dbo.VW_ReportSOSlipWithGroup.Location
Need Result,
I need this in Single query(I.e without using View). I was searching for past one week, but i cant able to combine in to single query. i am using sql2000. can any one plz guide me.
June 18, 2010 at 5:05 am
SELECT di.BatchNo,
aim.PartName,
ric.ClassificationDesc,
uom.UOM,
did.IssueQty,
d.Location,
d.PartNo,
d.PartId,
di.DemandNo,
di.SlipNo
FROM dbo.Tbl_DemandIssue di
INNER JOIN dbo.VW_AllItemMaster aim
ON di.PartID = aim.PartID
INNER JOIN dbo.Tbl_RefItemClassification ric
ON ric.ClassificationID = aim.ClassificationID
INNER JOIN dbo.Tbl_RefUOM uom
ON aim.UOM = uom.UOMID
INNER JOIN dbo.Tbl_DemandIssueDetail did
ON di.IssueLineNo = did.IssueLineNo
-- VW_ReportSOSlipWithGroup converted to derived table
INNER JOIN (
SELECT SUM(did.IssueQty) AS IssueQty,
did.Location,
did.PartId,
im.PartNo
FROM dbo.Tbl_DemandIssueDetail did
INNER JOIN dbo.Tbl_ItemMaster im ON did.PartId = im.PartID
GROUP BY did.Location, did.PartId, im.PartNo
) d
ON did = d.PartId
AND did.Location.Location = d.Location
Using table aliases (dbo.Tbl_DemandIssueDetail did) makes the code very much easier to read.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2010 at 5:40 am
when i am run your query, i am getting this error
The column prefix 'did.Location' does not match with a table name or alias name used in the query.
June 18, 2010 at 5:49 am
Unfortunately I don't have the luxury of tables to run the query against, so I can only offer you suggestions.
1. Run the derived table query
2. Comment out the derived table and the output columns and run what remains.
3. Post the code you are running.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2010 at 5:58 am
I have small doubt, Is there any performance issue to joining the query using views.Can you please suggest me.
June 18, 2010 at 6:11 am
KMPSSS (6/18/2010)
I have small doubt, Is there any performance issue to joining the query using views.Can you please suggest me.
You are absolutely right! You have right concerns! There are!
But I should disappoint you a bit. In SQLServer, as well as in many other RDBSes), performance issues are possible even if you don't use any view or table (with joins or without)! You can, if you know SQL well enough and try hard, write a query which will run forever and take huge amount of resources even without mentioning any of SQL objects (view, tables etc.) in it. 😀
June 18, 2010 at 6:15 am
One of the queries already references a view. Chaining views in this manner decreases the chances of an optimal plan. Neither of the views is particularly complex, I'd recommend you adhere to your objective of rewriting the two views.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply