A query with lot of inner queries

indian143

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

Mycroft Holmes

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

indian143

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

Mycroft Holmes

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

Jorgen Andersson

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

Is the same as:

SELECT *
FROM TableB
JOIN TableC
ON TableB.ID = TableC.ID
JOIN TableA
ON TableA.ID = TableB.ID

So 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

Mycroft Holmes

Did you use a tool for that or manually code it?

Never underestimate the power of human stupidity RAH

Jorgen Andersson

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[^]

indian143

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

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

Jorgen Andersson

You're welcome.

Wrong is evil and must be defeated. - Jeff Ello[^]

Mycroft Holmes

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