A query with lot of inner queries
-
Hi All, I have the following query that is created by MS Access while creating report. But the query has lots of inner queries and joins. Can anybody please help in making this big query simple and by removing all the possible brackets and make it clean. Any type is very helpful. This query really taking a lot of time to understand it. Any link, tool name or way to solve it any help is very much helpful. Thanks in advance. Here is the query.
SELECT so.EntityId, s.SponsorId, so.OrderId, so.ProgramId, so.ProgramYear, s.SponsorNbr,
s.SponsorNme, s.VendorNbr, s.MailFdpCde, dbo.Reference.RefCde, dbo.Reference.ExtCde, dbo.FDPSponsorApp.CurrentInd,
dbo.DeliveryPeriod.BegDte, dbo.DeliveryPeriod.EndDte, dbo.vwRef_fdpprogram.RefDsc, dbo.vwRef_fdpprogram.RefCde AS ProgramCde, dbo.Product.ProductCde,
dbo.Product.ShortDsc as ProductDsc, sod.QtyInv AS QtyRcv, dbo.Product.NetPackWt,
(CASE WHEN ((dbo.ProductCost.FixedCost IS NULL) OR (dbo.ProductCost.EntDrawInd= 653)) THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0) END) AS UnitCost,
[QtyInv]*(CASE WHEN ((dbo.ProductCost.FixedCost IS NULL) OR (dbo.ProductCost.EntDrawInd= 653)) THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0) END) AS ValueRcvd,
[NetPackWt]*[QtyInv] AS [Total Pounds], dbo.Contact.FirstNme, dbo.Contact.LastNme, dbo.Address.Addr1, dbo.Address.Addr2,
dbo.Address.City, dbo.Address.State, dbo.Address.ZipCde, dbo_Reference_1.RefDsc AS DeliveryType, so.DeliveryTypeId,
dbo.RefYearDefaults.DefValue AS BrownBoxFee, dbo_RefYearDefaults_1.DefValue AS DirectDiversionFee, dbo.Product.NetPackWt, s.VendorNbr,
s.FedEmpIdNbr
FROM dbo.RefYearDefaults AS dbo_RefYearDefaults_1
INNER JOIN (dbo.RefYearDefaults
INNER JOIN ((dbo.Reference
INNER JOIN ((dbo.Contact
INNER JOIN (dbo.Address
INNER JOIN (dbo.ProductCost
INNER JOIN (dbo.Sponsor s
INNER JOIN (dbo.FDPSponsorApp
INNER JOIN ((((dbo.FDPEntity
INNER JOIN dbo.SponsorOrder so ON dbo.FDPEntity.EntityId = so.LocationId)
INNER JOIN dbo.SponsorOrderDetail sod ON so.OrderId = sod.OrderId)
INNER JOIN dbo.Product ON sod.ProductId = dbo.Product.ProductId)
INNER JOIN dbo.DeliveryPeriod ON (so.DeliveryPeriodId = dbo.DeliveryPeriod.DeliveryPeriodId)
AND (so.ProgramYear = dbo.DeliveryPeriod.ProgramYear)) ON (dbo.FDPSponsorApp.EntityId =
so.EntityId) AND (dbo.FDPSponsorApp.ProgramYear = so.ProgramYear)) ON s.SponsorId = dbo.FDPSponsorApp.SponsorId) ON
(dbo.ProductCost.ProgramYear = dbo.FDPSponsorApp.PrWhat a bloody awful construct - I doubt there is any tool to change the structure other than manually coding it. As you will know the data structure you should be able to identify the joins in there and move them to a more supportable format.
INNER JOIN TableName as Alias ON A.Field = B.field
OR Move the entire database to SQL Server where there are tools to help build and tune the query.
Never underestimate the power of human stupidity RAH
-
What a bloody awful construct - I doubt there is any tool to change the structure other than manually coding it. As you will know the data structure you should be able to identify the joins in there and move them to a more supportable format.
INNER JOIN TableName as Alias ON A.Field = B.field
OR Move the entire database to SQL Server where there are tools to help build and tune the query.
Never underestimate the power of human stupidity RAH
Yes we moved everything in to SQL Server. But this query is left, the person who created this query for the report is not there any more in the organization. I am trying my head out to create the same query without lot of brackets. Can you please help me if you have any idea. Thanks in advance.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
-
Yes we moved everything in to SQL Server. But this query is left, the person who created this query for the report is not there any more in the organization. I am trying my head out to create the same query without lot of brackets. Can you please help me if you have any idea. Thanks in advance.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
I would start from scratch using the SQL Server view builder to get the table in and the joins. SSMS may create something similar but it may create the joins in a non nested structure which is what you are looking for to make it a supportable syntax. If nothing else works then winkle out the table hierarchy from the syntax and build it manually by putting the main transaction table into the from and manually adding te rest of the table required. I would also consider creating some views to simplify the structure EG your SponsorOrderDetail could be turned into a view to include the product and sponsor details required for the query.
Never underestimate the power of human stupidity RAH
-
Hi All, I have the following query that is created by MS Access while creating report. But the query has lots of inner queries and joins. Can anybody please help in making this big query simple and by removing all the possible brackets and make it clean. Any type is very helpful. This query really taking a lot of time to understand it. Any link, tool name or way to solve it any help is very much helpful. Thanks in advance. Here is the query.
SELECT so.EntityId, s.SponsorId, so.OrderId, so.ProgramId, so.ProgramYear, s.SponsorNbr,
s.SponsorNme, s.VendorNbr, s.MailFdpCde, dbo.Reference.RefCde, dbo.Reference.ExtCde, dbo.FDPSponsorApp.CurrentInd,
dbo.DeliveryPeriod.BegDte, dbo.DeliveryPeriod.EndDte, dbo.vwRef_fdpprogram.RefDsc, dbo.vwRef_fdpprogram.RefCde AS ProgramCde, dbo.Product.ProductCde,
dbo.Product.ShortDsc as ProductDsc, sod.QtyInv AS QtyRcv, dbo.Product.NetPackWt,
(CASE WHEN ((dbo.ProductCost.FixedCost IS NULL) OR (dbo.ProductCost.EntDrawInd= 653)) THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0) END) AS UnitCost,
[QtyInv]*(CASE WHEN ((dbo.ProductCost.FixedCost IS NULL) OR (dbo.ProductCost.EntDrawInd= 653)) THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0) END) AS ValueRcvd,
[NetPackWt]*[QtyInv] AS [Total Pounds], dbo.Contact.FirstNme, dbo.Contact.LastNme, dbo.Address.Addr1, dbo.Address.Addr2,
dbo.Address.City, dbo.Address.State, dbo.Address.ZipCde, dbo_Reference_1.RefDsc AS DeliveryType, so.DeliveryTypeId,
dbo.RefYearDefaults.DefValue AS BrownBoxFee, dbo_RefYearDefaults_1.DefValue AS DirectDiversionFee, dbo.Product.NetPackWt, s.VendorNbr,
s.FedEmpIdNbr
FROM dbo.RefYearDefaults AS dbo_RefYearDefaults_1
INNER JOIN (dbo.RefYearDefaults
INNER JOIN ((dbo.Reference
INNER JOIN ((dbo.Contact
INNER JOIN (dbo.Address
INNER JOIN (dbo.ProductCost
INNER JOIN (dbo.Sponsor s
INNER JOIN (dbo.FDPSponsorApp
INNER JOIN ((((dbo.FDPEntity
INNER JOIN dbo.SponsorOrder so ON dbo.FDPEntity.EntityId = so.LocationId)
INNER JOIN dbo.SponsorOrderDetail sod ON so.OrderId = sod.OrderId)
INNER JOIN dbo.Product ON sod.ProductId = dbo.Product.ProductId)
INNER JOIN dbo.DeliveryPeriod ON (so.DeliveryPeriodId = dbo.DeliveryPeriod.DeliveryPeriodId)
AND (so.ProgramYear = dbo.DeliveryPeriod.ProgramYear)) ON (dbo.FDPSponsorApp.EntityId =
so.EntityId) AND (dbo.FDPSponsorApp.ProgramYear = so.ProgramYear)) ON s.SponsorId = dbo.FDPSponsorApp.SponsorId) ON
(dbo.ProductCost.ProgramYear = dbo.FDPSponsorApp.PrWell you only need to unwind and indent your parentheses to make it more readable. Then keep in mind that :
SELECT *
FROM TableA
JOIN
(
TableB Join TableC On TableB.ID = TableC.ID
)
ON TableA.ID = TableB.IDIs the same as:
SELECT *
FROM TableB
JOIN TableC
ON TableB.ID = TableC.ID
JOIN TableA
ON TableA.ID = TableB.IDSo your query could quickly be simplified to this:
SELECT
so.EntityId,
s.SponsorId,
so.OrderId,
so.ProgramId,
so.ProgramYear,
s.SponsorNbr,
s.SponsorNme,
s.VendorNbr,
s.MailFdpCde,
dbo.Reference.RefCde,
dbo.Reference.ExtCde,
dbo.FDPSponsorApp.CurrentInd,
dbo.DeliveryPeriod.BegDte,
dbo.DeliveryPeriod.EndDte,
dbo.vwRef_fdpprogram.RefDsc,
dbo.vwRef_fdpprogram.RefCde AS ProgramCde,
dbo.Product.ProductCde,
dbo.Product.ShortDsc as ProductDsc,
sod.QtyInv AS QtyRcv,
dbo.Product.NetPackWt,
CASE
WHEN dbo.ProductCost.FixedCost IS NULL OR dbo.ProductCost.EntDrawInd= 653 THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0)
END AS UnitCost,
CASE
WHEN dbo.ProductCost.FixedCost IS NULL OR dbo.ProductCost.EntDrawInd= 653 THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0)
END * [QtyInv] AS ValueRcvd,
[NetPackWt]*[QtyInv] AS [Total Pounds],
dbo.Contact.FirstNme,
dbo.Contact.LastNme,
dbo.Address.Addr1,
dbo.Address.Addr2,
dbo.Address.City,
dbo.Address.State,
dbo.Address.ZipCde,
dbo_Reference_1.RefDsc AS DeliveryType,
so.DeliveryTypeId,
dbo.RefYearDefaults.DefValue AS BrownBoxFee,
dbo_RefYearDefaults_1.DefValue AS DirectDiversionFee,
dbo.Product.NetPackWt,
s.VendorNbr,
s.FedEmpIdNbr
FROM
bo.FDPEntity
JOIN dbo.SponsorOrder so
ON dbo.FDPEntity.EntityId = so.LocationId
JOIN dbo.SponsorOrderDetail sod
ON so.OrderId = sod.OrderId
JOIN dbo.Product
ON sod.ProductId = dbo.Product.ProductId
JOIN dbo.DeliveryPeriod
ON so.DeliveryPeriodId = dbo.DeliveryPeriod.DeliveryPeriodId
AND so.ProgramYear = dbo.DeliveryPeriod.ProgramYear
JOIN dbo.FDPSponsorApp
ON dbo.FDPSponsorApp.EntityId = so.EntityId -
Well you only need to unwind and indent your parentheses to make it more readable. Then keep in mind that :
SELECT *
FROM TableA
JOIN
(
TableB Join TableC On TableB.ID = TableC.ID
)
ON TableA.ID = TableB.IDIs the same as:
SELECT *
FROM TableB
JOIN TableC
ON TableB.ID = TableC.ID
JOIN TableA
ON TableA.ID = TableB.IDSo your query could quickly be simplified to this:
SELECT
so.EntityId,
s.SponsorId,
so.OrderId,
so.ProgramId,
so.ProgramYear,
s.SponsorNbr,
s.SponsorNme,
s.VendorNbr,
s.MailFdpCde,
dbo.Reference.RefCde,
dbo.Reference.ExtCde,
dbo.FDPSponsorApp.CurrentInd,
dbo.DeliveryPeriod.BegDte,
dbo.DeliveryPeriod.EndDte,
dbo.vwRef_fdpprogram.RefDsc,
dbo.vwRef_fdpprogram.RefCde AS ProgramCde,
dbo.Product.ProductCde,
dbo.Product.ShortDsc as ProductDsc,
sod.QtyInv AS QtyRcv,
dbo.Product.NetPackWt,
CASE
WHEN dbo.ProductCost.FixedCost IS NULL OR dbo.ProductCost.EntDrawInd= 653 THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0)
END AS UnitCost,
CASE
WHEN dbo.ProductCost.FixedCost IS NULL OR dbo.ProductCost.EntDrawInd= 653 THEN ISNULL(dbo.ProductCost.WghtAvgCost, 0)
ELSE ISNULL(dbo.ProductCost.FixedCost,0)
END * [QtyInv] AS ValueRcvd,
[NetPackWt]*[QtyInv] AS [Total Pounds],
dbo.Contact.FirstNme,
dbo.Contact.LastNme,
dbo.Address.Addr1,
dbo.Address.Addr2,
dbo.Address.City,
dbo.Address.State,
dbo.Address.ZipCde,
dbo_Reference_1.RefDsc AS DeliveryType,
so.DeliveryTypeId,
dbo.RefYearDefaults.DefValue AS BrownBoxFee,
dbo_RefYearDefaults_1.DefValue AS DirectDiversionFee,
dbo.Product.NetPackWt,
s.VendorNbr,
s.FedEmpIdNbr
FROM
bo.FDPEntity
JOIN dbo.SponsorOrder so
ON dbo.FDPEntity.EntityId = so.LocationId
JOIN dbo.SponsorOrderDetail sod
ON so.OrderId = sod.OrderId
JOIN dbo.Product
ON sod.ProductId = dbo.Product.ProductId
JOIN dbo.DeliveryPeriod
ON so.DeliveryPeriodId = dbo.DeliveryPeriod.DeliveryPeriodId
AND so.ProgramYear = dbo.DeliveryPeriod.ProgramYear
JOIN dbo.FDPSponsorApp
ON dbo.FDPSponsorApp.EntityId = so.EntityIdDid you use a tool for that or manually code it?
Never underestimate the power of human stupidity RAH
-
Did you use a tool for that or manually code it?
Never underestimate the power of human stupidity RAH
Both, We have a homebrewed tool for formatting, the conversion of the joins I made by hand.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Did you use a tool for that or manually code it?
Never underestimate the power of human stupidity RAH
-
Both, We have a homebrewed tool for formatting, the conversion of the joins I made by hand.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Awesome thanks a lot. I cant forget this help. Thank you thank you thank you very much.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
You're welcome.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Actually this query isn't done by me but the person who did it wrote it with a tool.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
indian143 wrote:
wrote it with a tool.
Oh I knew that, no one I know will write joins like that. I was interested in whether Jorgen hand coded the changes, he did and deserves your up vote.
Never underestimate the power of human stupidity RAH