Re: A query with lot of inner queries - Database Discussion Boards

It will have different issues.
Your best bet is to make sure you install it to use Unicode. But watch out, PostGreSQL uses UTF8 while dotnet uses UTF16 internally. So you might still encounter odd issues.
MySQL supports UTF16 from version 5.5 IIRC.

Why do you use odbc instead of Connector/NET?

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

It was installed with utf8_geberal_ci as the default. With the later versions of MySQL supporting utf8mb4_general_ci I've tried that too - but to no avail.

Why ODBC? Habit, I guess. Would Connector/NET make a difference?

Wombaticus wrote:
Would Connector/NET make a difference?

Mostly performance, and a lot of it.

Have you read this[^]?

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

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

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.

SQL

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

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

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

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

Audience on my site is approximately 2-10 users online, database size 170MB, request 5-20/s. Does anyone advise me please, why the tempdb nonstop read / write to HDD for about 2-4MB/sec. Why, or what you need to set or to my tempdb. Thank you.

The usage of tempdb is highly depend on the queries you run - if you use, for instance, a lot of temporary tables (even local one) it will all go to tempdb...
Please read here to understand the usage of tempdb: http://msdn.microsoft.com/en-us/library/ms190768.aspx[^]

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Hi all,

I am in desperate need of help please.

I have created a stored procedure with a while loop using a hard coded date 31-12-2014 to compare against current date produced in a while loop. I need to make the hard coded date reflect every two years i.e. 31-12-2016, 31-12-2018 etc.
How can the date be automatic instead of hard coded please?
i.e While (currdate <= '31-12-2014')

Second issue, how do I make the stored procedure to run every two years as well?

SQL Agent Job doesn't have a yearly frequency at all.

Can someone please offer an example coding and suggestions to those two issues which I will be grateful please.
Confused | :confused:

Thank You!

Use mod on the year component to determine even years, check that the year component is the same in curredate.

or
Create table to hold the event dates

A 2 year event cycle is very ambitious, a lot can happen in that cycle.

Never underestimate the power of human stupidity
RAH

Could you kindly explain please, I am not sure about the mod with year component? I am rather saying I don't quite understand please.

I still don't know how to get the second issue to call the stored procs every two years as SQL Agent Job doesn't seem to offer the solution.

A simple search for TSQL Mod would have given you the information. However this may help, this year return 0, next year 1.

SQL

SELECT DATEPART(YEAR,GETDATE()) % 2

SELECT DATEPART(YEAR,DATEADD(YEAR,1,GETDATE())) % 2

As for the job not offering year - you are building a test method so a proc will run under only defined circumstances, so run the job every month, if it does not meet your criteria then let it wait for next month.

Never underestimate the power of human stupidity
RAH

Does this look efficient and OK?

DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime

SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1)

SET @GetHour = datepart(hour, GETDATE()) -- returns hour

--Get Today date to compare to the last day of December in current year
IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT(@LastDayOfYear, 'yyyy-MM-dd')) AND @GetHour >= 20
BEGIN
--If Equal - then increase the next 2 years
SET @NextNewDate = FORMAT(DATEADD(YEAR, 2, @LastDayOfYear), 'yyyy-MM-dd HH:mm:ss')
END
ELSE
SET @NextNewDate = FORMAT(@LastDayOfYear, 'yyyy-MM-dd HH:mm:ss')

PRINT @NextNewDate

WHILE (CONVERT(DATE,@currDate, 101) <= @NextNewDate)
BEGIN

Second Issue - "so run the job every month, if it does not meet your criteria then let it wait for next month."

How do you make it meet your criteria i.e. if it falls on 31st December of each year?

modified 28-May-14 4:49am.

If you want to excecute your query on every 31st December add a below condition

SQL

IF (MONTH(GETDATE()) = 12 AND DAY(GETDATE()) = 31)
	BEGIN
		/*	
			This will excecute on every 31'st December	
			Write down your query					
		*/
	END

Hi Again

"so run the job every month, if it does not meet your criteria then let it wait for next month."

Would it mean I have to somehow implement a code within the Job Step List in the SQL Agent Job?

Many Thanks

Seuss wrote:
implement a code within the Job Step List in the SQL Agent Job

No your job is calling a procedure. In the procedure I would first check that the current date matches the next process date and if so has the process been run for this date.

If it fails either test then exit without processing.

Never underestimate the power of human stupidity
RAH

Ah I think I understand what you are saying, have the SQL Job Agent set up and each month - when it calls the stored procedure - if the dates don't match up - it needs to exit out of the SQL Job Agent?

I have also been googling and there were some examples where you could implement a job step code to do it? Not sure if this is feasible or a good idea?

Huge thanks to you for your patience. I know I am a bit persistent on this matter Smile | :)