A query with lot of inner queries - Database Discussion Boards

Hello All,
I tried using '+' operator for left outer join in MySql Workbench 5.2 CE but it is giving me a syntax error.I want to know whether + operator is supported by MySql Workbench 5.2 CE or not?I have gone through the manual of workbench but I did not find any information on this topic.Kindly help me with it.

I'm pretty sure MySQL isn't supporting outer implicit joins, only inner implicit joins.
You'll have to use an Ansi Join instead.

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

Lots of companies are comparing the SSAS Tabular vs Multi-dimensional Model. Honestly, there are tons and tons of articles already written about the differences between the two (so i don’t plan on doing that). Case in Point: http://www.sqlchick.com/entries/2012/3/4/decisions-powerpivot-ssas-tabular-or-ssas-multidimensional-m.html

I recently worked in an environment where we were looking at using data-mining algorithms for product affinity analysis, linear regression and customer analysis for generating customer mailing lists.

Unfortunately, way back, the decision had been made to go with Tabular model, to take advantage of the the InMemory and speed of development benefits. Now, with tabular in place, requests starts coming for analysis that requires the construction of data-mining structures akin to what is in Multi-dimensional. Unfortunately, that functionality doesn’t exist in Tabular yet, so we can’t do the analysis unless we go Multi-dimensional.

Fortunately, this is not a show-stopper requirement for us but just something to be aware off.

Summary: Currently, tabular model does not support data mining structures required for product affinity analysis, linear regression, customer predictive analysis, e.t.c. I’m not sure if Microsoft plans on building such functionalities into Tabular model going forward, but I just wanted to point it out incase some people are not yet aware of this short coming for Tabular.

I am sick to the back teeth of encoding issues with MySQL. I have Googled the issue and tried every suggestion I can find, and am still plagued with insert errors such as:

ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.18]Incorrect string value: '\xEF\x82\xA7\x09Se...' for column '[column name]' at row 1

Please.. don't bother suggesting ways round this, I give up on it. My question, to anyone else who has experienced this, is simply this: does PostgreSQL suffer this same issue, or at least have a solution that works?

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.