Click here to Skip to main content
15,179,830 members
Home / Discussions / Database
   

Database

 
AnswerRe: Ready to give up on MySQL - is PostgreSQL better? Pin
Jörgen Andersson1-Jun-14 23:37
professionalJörgen Andersson1-Jun-14 23:37 
GeneralRe: Ready to give up on MySQL - is PostgreSQL better? Pin
Wombaticus1-Jun-14 23:43
MemberWombaticus1-Jun-14 23:43 
GeneralRe: Ready to give up on MySQL - is PostgreSQL better? Pin
Jörgen Andersson2-Jun-14 0:08
professionalJörgen Andersson2-Jun-14 0:08 
QuestionA query with lot of inner queries Pin
indian14329-May-14 13:41
Memberindian14329-May-14 13:41 
AnswerRe: A query with lot of inner queries Pin
Mycroft Holmes29-May-14 14:02
professionalMycroft Holmes29-May-14 14:02 
GeneralRe: A query with lot of inner queries Pin
indian14329-May-14 14:09
Memberindian14329-May-14 14:09 
GeneralRe: A query with lot of inner queries Pin
Mycroft Holmes29-May-14 15:08
professionalMycroft Holmes29-May-14 15:08 
AnswerRe: A query with lot of inner queries Pin
Jörgen Andersson29-May-14 23:20
professionalJörgen Andersson29-May-14 23:20 
Well you only need to unwind and indent your parentheses to make it more readable.
Then keep in mind that :
SQL
SELECT  *
FROM    TableA
JOIN   
    (
    TableB  Join   TableC On TableB.ID = TableC.ID
    )
    ON  TableA.ID = TableB.ID
Is the same as:
SQL
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:
SQL
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
    AND dbo.FDPSponsorApp.ProgramYear = so.ProgramYear
JOIN    dbo.Sponsor s 
    ON  s.SponsorId = dbo.FDPSponsorApp.SponsorId
JOIN    dbo.ProductCost
    ON  dbo.ProductCost.ProgramYear = dbo.FDPSponsorApp.ProgramYear
    AND dbo.ProductCost.ProductId = dbo.Product.ProductId
JOIN    dbo.Address
    ON  dbo.Address.AddrId = dbo.FDPSponsorApp.MailAddrId
JOIN    dbo.Contact
    ON  dbo.Contact.ContactId = dbo.FDPSponsorApp.FdpCtcId
JOIN    dbo.vwRef_fdpprogram
    ON  so.ProgramId = dbo.vwRef_fdpprogram.RefId
JOIN    dbo.Reference
    ON  dbo.Reference.RefId = s.SFMSObjectTypeId
JOIN    dbo.Reference AS dbo_Reference_1
    ON  so.DeliveryTypeId = dbo_Reference_1.RefId
JOIN    dbo.RefYearDefaults
    ON  dbo.RefYearDefaults.ProgramYear = so.ProgramYear
JOIN    dbo.RefYearDefaults AS dbo_RefYearDefaults_1 
    ON  dbo_RefYearDefaults_1.ProgramYear = so.ProgramYear 
WHERE   so.ProgramYear= 2014
    AND dbo.FDPSponsorApp.CurrentInd=1
    AND sod.QtyInv > 0
    AND dbo.RefYearDefaults.DefNme='BrownBoxFee'
    AND dbo_RefYearDefaults_1.DefNme='DirectDiversionFee'
ORDER BY so.EntityId, s.SponsorNme, so.OrderId, Product.ShortDsc
Note that since the parentheses are gone the optimizer isn't forced to do the joins in a certain order anymore and might therefore be much faster.
Wrong is evil and must be defeated. - Jeff Ello[^]

GeneralRe: A query with lot of inner queries Pin
Mycroft Holmes30-May-14 2:50
professionalMycroft Holmes30-May-14 2:50 
GeneralRe: A query with lot of inner queries Pin
Jörgen Andersson30-May-14 4:15
professionalJörgen Andersson30-May-14 4:15 
GeneralRe: A query with lot of inner queries Pin
indian14330-May-14 10:08
Memberindian14330-May-14 10:08 
GeneralRe: A query with lot of inner queries Pin
Jörgen Andersson30-May-14 10:24
professionalJörgen Andersson30-May-14 10:24 
GeneralRe: A query with lot of inner queries Pin
indian14330-May-14 10:05
Memberindian14330-May-14 10:05 
GeneralRe: A query with lot of inner queries Pin
Mycroft Holmes30-May-14 13:46
professionalMycroft Holmes30-May-14 13:46 
QuestionProblem with tempdb - MSSQL Pin
Milan K SK28-May-14 5:32
MemberMilan K SK28-May-14 5:32 
AnswerRe: Problem with tempdb - MSSQL Pin
Kornfeld Eliyahu Peter28-May-14 7:39
professionalKornfeld Eliyahu Peter28-May-14 7:39 
QuestionSQL Server 2012 Agent Job & stored procedure issue URGENT Pin
Seuss27-May-14 12:10
MemberSeuss27-May-14 12:10 
AnswerRe: SQL Server 2012 Agent Job & stored procedure issue URGENT Pin
Mycroft Holmes27-May-14 13:45
professionalMycroft Holmes27-May-14 13:45 
GeneralRe: SQL Server 2012 Agent Job & stored procedure issue URGENT Pin
Seuss27-May-14 21:10
MemberSeuss27-May-14 21:10 
GeneralRe: SQL Server 2012 Agent Job & stored procedure issue URGENT Pin
Mycroft Holmes27-May-14 21:22
professionalMycroft Holmes27-May-14 21:22 
GeneralRe: SQL Server 2012 Agent Job & stored procedure issue URGENT Pin
Seuss27-May-14 23:14
MemberSeuss27-May-14 23:14 
GeneralRe: SQL Server 2012 Agent Job & stored procedure issue URGENT Pin
NitinDhapte16-Jun-14 2:55
MemberNitinDhapte16-Jun-14 2:55 
GeneralRe: SQL Server 2012 Agent Job & stored procedure issue URGENT Pin
Seuss27-May-14 23:28
MemberSeuss27-May-14 23:28 
GeneralRe: SQL Server 2012 Agent Job & stored procedure issue URGENT Pin
Mycroft Holmes27-May-14 23:55
professionalMycroft Holmes27-May-14 23:55 
GeneralRe: SQL Server 2012 Agent Job & stored procedure issue URGENT Pin
Seuss28-May-14 0:00
MemberSeuss28-May-14 0:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.