Click here to Skip to main content
15,180,027 members
Home / Discussions / Database
   

Database

 
Question+ operaotor for outer joins in mysql. Pin
aghori3-Jun-14 20:55
Memberaghori3-Jun-14 20:55 
AnswerRe: + operaotor for outer joins in mysql. Pin
Jörgen Andersson3-Jun-14 22:16
professionalJörgen Andersson3-Jun-14 22:16 
SuggestionData Mining Structure not Available in SSAS Tabular Model Pin
FLouis382-Jun-14 7:14
professionalFLouis382-Jun-14 7:14 
QuestionReady to give up on MySQL - is PostgreSQL better? Pin
Wombaticus1-Jun-14 23:09
MemberWombaticus1-Jun-14 23:09 
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 
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.ProgramYear) 	AND (dbo.ProductCost.ProductId = dbo.Product.ProductId)) ON dbo.Address.AddrId = 
dbo.FDPSponsorApp.MailAddrId) ON dbo.Contact.ContactId = dbo.FDPSponsorApp.FdpCtcId)  INNER JOIN dbo.vwRef_fdpprogram ON so.ProgramId = 
dbo.vwRef_fdpprogram.RefId) ON dbo.Reference.RefId = s.SFMSObjectTypeId)  INNER JOIN dbo.Reference AS dbo_Reference_1 ON so.DeliveryTypeId = 
dbo_Reference_1.RefId) ON dbo.RefYearDefaults.ProgramYear = so.ProgramYear) ON dbo_RefYearDefaults_1.ProgramYear = so.ProgramYear 
WHERE (((so.ProgramYear)= 2014) 	AND --so.EntityId  = @SponsorEntityId	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

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

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

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.