Its Challengeble Task Combining View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Its Challengeble Task Combining View

Author

Topic

urzsuresh
Starting Member

30 Posts

Posted - 2010-06-18 : 06:37:10

--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.

Suri

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 07:04:29

[code]
SELECT DISTINCT
VW_ReportSOSlipDetail.BatchNo, VW_ReportSOSlipDetail.PartName, VW_ReportSOSlipDetail.ClassificationDesc,
VW_ReportSOSlipDetail.UOM, VW_ReportSOSlipDetail.IssueQty, VW_ReportSOSlipWithGroup.Location,
VW_ReportSOSlipWithGroup.PartNo, VW_ReportSOSlipWithGroup.PartId, VW_ReportSOSlipDetail.DemandNo,
VW_ReportSOSlipDetail.SlipNo
FROM
(
SELECT Tbl_DemandIssue.BatchNo, VW_AllItemMaster.PartNo, VW_AllItemMaster.PartName, Tbl_RefItemClassification.ClassificationDesc,
Tbl_DemandIssueDetail.IssueQty, Tbl_DemandIssueDetail.Location, Tbl_RefUOM.UOM, Tbl_DemandIssueDetail.SerialNo,
Tbl_DemandIssueDetail.PartId, Tbl_DemandIssue.DemandNo, Tbl_DemandIssue.SlipNo
FROM Tbl_DemandIssue INNER JOIN
Tbl_RefItemClassification INNER JOIN
VW_AllItemMaster ON Tbl_RefItemClassification.ClassificationID = VW_AllItemMaster.ClassificationID ON
Tbl_DemandIssue.PartID = VW_AllItemMaster.PartID INNER JOIN
Tbl_RefUOM ON VW_AllItemMaster.UOM = Tbl_RefUOM.UOMID INNER JOIN
Tbl_DemandIssueDetail ON Tbl_DemandIssue.IssueLineNo = Tbl_DemandIssueDetail.IssueLineNo
) VW_ReportSOSlipDetail INNER JOIN
(
SELECT SUM(Tbl_DemandIssueDetail.IssueQty) AS IssueQty, Tbl_DemandIssueDetail.Location, Tbl_DemandIssueDetail.PartId,
Tbl_ItemMaster.PartNo
FROM Tbl_DemandIssueDetail INNER JOIN
Tbl_ItemMaster ON Tbl_DemandIssueDetail.PartId = Tbl_ItemMaster.PartID
GROUP BY Tbl_DemandIssueDetail.Location, Tbl_DemandIssueDetail.PartId, Tbl_ItemMaster.PartNo
)
VW_ReportSOSlipWithGroup ON VW_ReportSOSlipDetail.PartId = VW_ReportSOSlipWithGroup.PartId
and VW_ReportSOSlipDetail.Location = VW_ReportSOSlipWithGroup.Location
[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources