Solved

SQL Server Enterprise manager changing my query.

Posted on 2009-05-07
9
209 Views
Last Modified: 2012-05-06
Good day

I have the following query that works perfectly in ASP and give me results:

SELECT COUNT(Tree.ChildUserID) AS Level1, Users_1.[Transaction Status] AS Status1, COUNT(Tree_1.ChildUserID) AS Level2, Users_2.[Transaction Status] AS Status2, COUNT(Tree_2.ChildUserID) AS Level3, Users_3.[Transaction Status] AS Status3, COUNT(Tree_3.ChildUserID) AS Level4, Users_4.[Transaction Status] AS Status4, COUNT(Tree_4.ChildUserID) AS Level5, Users_5.[Transaction Status] AS Status5, COUNT(Tree_5.ChildUserID) AS Level6, Users_6.[Transaction Status] AS Status6, COUNT(Tree_6.ChildUserID) AS Level7, Users_7.[Transaction Status] AS Status7 FROM Tree Tree_6 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_7 ON Tree_6.ChildUserID = ProductSubscriptions_7.UserID LEFT OUTER JOIN Users Users_7 ON Tree_6.ChildUserID = Users_7.ID RIGHT OUTER JOIN Users Users_6 ON Tree_6.ParentUserID = Users_6.ID RIGHT OUTER JOIN Tree Tree_5 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_6 ON Tree_5.ChildUserID = ProductSubscriptions_6.UserID ON Users_6.ID = Tree_5.ChildUserID RIGHT OUTER JOIN Users Users_5 ON Tree_5.ParentUserID = Users_5.ID RIGHT OUTER JOIN Tree Tree_4 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_5 ON Tree_4.ChildUserID = ProductSubscriptions_5.UserID ON Users_5.ID = Tree_4.ChildUserID RIGHT OUTER JOIN Users Users_4 ON Tree_4.ParentUserID = Users_4.ID RIGHT OUTER JOIN Tree Tree_3 LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_4 ON Tree_3.ChildUserID = ProductSubscriptions_4.UserID ON Users_4.ID = Tree_3.ChildUserID RIGHT OUTER JOIN Users Users_3 ON Tree_3.ParentUserID = Users_3.ID RIGHT OUTER JOIN Tree Tree_2 RIGHT OUTER JOIN Users Users_2 ON Tree_2.ParentUserID = Users_2.ID LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_3 ON Tree_2.ChildUserID = ProductSubscriptions_3.UserID ON Users_3.ID = Tree_2.ChildUserID RIGHT OUTER JOIN Tree Tree_1 RIGHT OUTER JOIN Users Users_1 ON Tree_1.ParentUserID = Users_1.ID LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_2 ON Tree_1.ChildUserID = ProductSubscriptions_2.UserID ON Users_2.ID = Tree_1.ChildUserID RIGHT OUTER JOIN ProductSubscriptions INNER JOIN Users ON ProductSubscriptions.UserID = Users.ID INNER JOIN Tree ON Users.ID = Tree.ParentUserID LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_1 ON Tree.ChildUserID = ProductSubscriptions_1.UserID ON Users_1.ID = Tree.ChildUserID WHERE (ProductSubscriptions.ProductID = 1) AND (Users.Active = 1) AND (Users.ID = 47) AND (ProductSubscriptions_1.ProductID = 1 OR ProductSubscriptions_1.ProductID IS NULL) AND (Users_1.Active = 1) AND (ProductSubscriptions_2.ProductID = 1 OR ProductSubscriptions_2.ProductID IS NULL) AND (Users_2.Active = 1 OR Users_2.Active IS NULL) AND (ProductSubscriptions_3.ProductID = 1 OR ProductSubscriptions_3.ProductID IS NULL) AND (Users_3.Active = 1 OR Users_3.Active IS NULL) AND (ProductSubscriptions_4.ProductID = 1 OR ProductSubscriptions_4.ProductID IS NULL) AND (Users_4.Active = 1 OR Users_4.Active IS NULL) AND (ProductSubscriptions_5.ProductID = 1 OR ProductSubscriptions_5.ProductID IS NULL) AND (Users_5.Active = 1 OR Users_5.Active IS NULL) AND (ProductSubscriptions_6.ProductID = 1 OR ProductSubscriptions_6.ProductID IS NULL) AND (Users_6.Active = 1 OR Users_6.Active IS NULL) AND (ProductSubscriptions_7.ProductID = 1 OR ProductSubscriptions_7.ProductID IS NULL) AND (Users_7.Active = 1 OR Users_7.Active IS NULL) GROUP BY Users_1.[Transaction Status], Users_2.[Transaction Status], Users_3.[Transaction Status], Users_4.[Transaction Status], Users_5.[Transaction Status], Users_6.[Transaction Status], Users_7.[Transaction Status] HAVING (Users_1.[Transaction Status] <= 1 OR Users_1.[Transaction Status] IS NULL) AND (Users_2.[Transaction Status] <= 1 OR Users_2.[Transaction Status] IS NULL) AND (Users_3.[Transaction Status] <= 1 OR Users_3.[Transaction Status] IS NULL) AND (Users_4.[Transaction Status] <= 1 OR Users_4.[Transaction Status] IS NULL) AND (Users_5.[Transaction Status] <= 1 OR Users_5.[Transaction Status] IS NULL) AND (Users_6.[Transaction Status] <= 1 OR Users_6.[Transaction Status] IS NULL) AND (Users_7.[Transaction Status] <= 1 OR Users_7.[Transaction Status] IS NULL);


But when I copy and paste it into SQL Enterprise manager it changed something in the query and there are suddenly no records to return
This is the changed query:

SELECT     COUNT(Tree_6.ChildUserID) AS Level1, Users_1.[Transaction Status] AS Status1, COUNT(Tree_1.ChildUserID) AS Level2,
                      Users_2.[Transaction Status] AS Status2, COUNT(Tree_2.ChildUserID) AS Level3, Users_3.[Transaction Status] AS Status3, COUNT(Tree_3.ChildUserID)
                       AS Level4, Users_4.[Transaction Status] AS Status4, COUNT(Tree_4.ChildUserID) AS Level5, Users_5.[Transaction Status] AS Status5,
                      COUNT(Tree_5.ChildUserID) AS Level6, Users_6.[Transaction Status] AS Status6, COUNT(Tree_6.ChildUserID) AS Level7,
                      Users_7.[Transaction Status] AS Status7
FROM         Tree Tree_6 LEFT OUTER JOIN
                      ProductSubscriptions ProductSubscriptions_7 ON Tree_6.ChildUserID = ProductSubscriptions_7.UserID LEFT OUTER JOIN
                      Users Users_7 ON Tree_6.ChildUserID = Users_7.ID RIGHT OUTER JOIN
                      Users Users_6 ON Tree_6.ParentUserID = Users_6.ID RIGHT OUTER JOIN
                      Tree Tree_5 LEFT OUTER JOIN
                      ProductSubscriptions ProductSubscriptions_6 ON Tree_5.ChildUserID = ProductSubscriptions_6.UserID ON
                      Users_6.ID = Tree_5.ChildUserID RIGHT OUTER JOIN
                      Users Users_5 ON Tree_5.ParentUserID = Users_5.ID RIGHT OUTER JOIN
                      Tree Tree_4 LEFT OUTER JOIN
                      ProductSubscriptions ProductSubscriptions_5 ON Tree_4.ChildUserID = ProductSubscriptions_5.UserID ON
                      Users_5.ID = Tree_4.ChildUserID RIGHT OUTER JOIN
                      Users Users_4 ON Tree_4.ParentUserID = Users_4.ID RIGHT OUTER JOIN
                      Tree Tree_3 LEFT OUTER JOIN
                      ProductSubscriptions ProductSubscriptions_4 ON Tree_3.ChildUserID = ProductSubscriptions_4.UserID ON
                      Users_4.ID = Tree_3.ChildUserID RIGHT OUTER JOIN
                      Users Users_3 ON Tree_3.ParentUserID = Users_3.ID RIGHT OUTER JOIN
                      Tree Tree_2 RIGHT OUTER JOIN
                      Users Users_2 ON Tree_2.ParentUserID = Users_2.ID LEFT OUTER JOIN
                      ProductSubscriptions ProductSubscriptions_3 ON Tree_2.ChildUserID = ProductSubscriptions_3.UserID ON
                      Users_3.ID = Tree_2.ChildUserID RIGHT OUTER JOIN
                      Tree Tree_1 RIGHT OUTER JOIN
                      Users Users_1 ON Tree_1.ParentUserID = Users_1.ID LEFT OUTER JOIN
                      ProductSubscriptions ProductSubscriptions_2 ON Tree_1.ChildUserID = ProductSubscriptions_2.UserID ON
                      Users_2.ID = Tree_1.ChildUserID RIGHT OUTER JOIN
                      ProductSubscriptions ProductSubscriptions_8 INNER JOIN
                      Users Users_8 ON ProductSubscriptions_8.UserID = Users_8.ID INNER JOIN
                      Tree Tree_7 ON Users_8.ID = Tree_7.ParentUserID LEFT OUTER JOIN
                      ProductSubscriptions ProductSubscriptions_1 ON Tree_7.ChildUserID = ProductSubscriptions_1.UserID ON Users_1.ID = Tree_7.ChildUserID
WHERE     (ProductSubscriptions_7.ProductID = 1) AND (Users_7.Active = 1) AND (Users_7.ID = 47) AND (ProductSubscriptions_1.ProductID = 1 OR
                      ProductSubscriptions_1.ProductID IS NULL) AND (Users_1.Active = 1) AND (ProductSubscriptions_2.ProductID = 1 OR
                      ProductSubscriptions_2.ProductID IS NULL) AND (Users_2.Active = 1 OR
                      Users_2.Active IS NULL) AND (ProductSubscriptions_3.ProductID = 1 OR
                      ProductSubscriptions_3.ProductID IS NULL) AND (Users_3.Active = 1 OR
                      Users_3.Active IS NULL) AND (ProductSubscriptions_4.ProductID = 1 OR
                      ProductSubscriptions_4.ProductID IS NULL) AND (Users_4.Active = 1 OR
                      Users_4.Active IS NULL) AND (ProductSubscriptions_5.ProductID = 1 OR
                      ProductSubscriptions_5.ProductID IS NULL) AND (Users_5.Active = 1 OR
                      Users_5.Active IS NULL) AND (ProductSubscriptions_6.ProductID = 1 OR
                      ProductSubscriptions_6.ProductID IS NULL) AND (Users_6.Active = 1 OR
                      Users_6.Active IS NULL) AND (ProductSubscriptions_7.ProductID = 1 OR
                      ProductSubscriptions_7.ProductID IS NULL) AND (Users_7.Active = 1 OR
                      Users_7.Active IS NULL)
GROUP BY Users_1.[Transaction Status], Users_2.[Transaction Status], Users_3.[Transaction Status], Users_4.[Transaction Status],
                      Users_5.[Transaction Status], Users_6.[Transaction Status], Users_7.[Transaction Status]
HAVING      (Users_1.[Transaction Status] <= 1 OR
                      Users_1.[Transaction Status] IS NULL) AND (Users_2.[Transaction Status] <= 1 OR
                      Users_2.[Transaction Status] IS NULL) AND (Users_3.[Transaction Status] <= 1 OR
                      Users_3.[Transaction Status] IS NULL) AND (Users_4.[Transaction Status] <= 1 OR
                      Users_4.[Transaction Status] IS NULL) AND (Users_5.[Transaction Status] <= 1 OR
                      Users_5.[Transaction Status] IS NULL) AND (Users_6.[Transaction Status] <= 1 OR
                      Users_6.[Transaction Status] IS NULL) AND (Users_7.[Transaction Status] <= 1 OR
                      Users_7.[Transaction Status] IS NULL)

Any idea why this is happening?



0
Comment
Question by:koossa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 24323040
does the new code work in Query Analyser?

As far as I know EM may add junk to your queries.

Personally i try to only use QA

it article may help http://databases.aspfaq.com/database/which-tool-should-i-use-enterprise-manager-or-query-analyzer.html
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 24323064
can you compare the two version and indicate which changes were made?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24323088
well, EM may add some additional information in your T-SQL but not add something which make your code erroneous. You have to make sure that your query is working fine in Query Analyzer and not working in EM. than let us know both the query.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 7

Expert Comment

by:thiyaguk
ID: 24323099
Please run this query in Query analyser. EM might change the Query slightly....
0
 

Author Comment

by:koossa
ID: 24323102
Yes, the top one is working in QA, but when I copy it to EM it change something.
When I copied the changed code to QA, it does not work either.

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24323110
can you post both version (EM and QA) of query here?
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 24323115
they are in question RiteshShah

0
 

Author Comment

by:koossa
ID: 24323147
The query at the top is the QA one and the 2nd one is the EM one.

I have figure out what the problem is:
I have used some of the tables more than once where a underscore and number is put after the table. (table_1)
But my original table did not have an alias.
After changing the original table to table_0 it's working.

Thank you

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24323166
If you will observe part of your query

RIGHT OUTER JOIN Tree Tree_5

LEFT OUTER JOIN ProductSubscriptions ProductSubscriptions_6

ON Tree_5.ChildUserID = ProductSubscriptions_6.UserID

ON Users_6.ID = Tree_5.ChildUserID

there is no ON between RIght OUTER and Left OUTER join, instead, both ON comes after Right and Left outer. this kind of junk is happening which is haulting your query from run.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question