Solved

TSQL Select aggregate values from sub query

Posted on 2013-05-22
17
487 Views
Last Modified: 2013-05-28
Hi Guys,

I have a query that works fine with other TSQL compliant databases e.g. VistaDB but which fails with SQL Server:

      
SELECT Coalesce(SUM(
    CASE WHEN TransactionTypeId = @PURCHASEORDERTRANSACTIONTYPEID THEN 
        Coalesce(Quantity, 0) - QuantityAllocated 
    ELSE 
         0 
    END), 0) AS QuantityOnPurchaseOrder,
Coalesce(SUM(
    CASE WHEN TransactionTypeId = @SALESORDERTRANSACTIONTYPEID THEN 
        Coalesce(Quantity, 0) - QuantityAllocated 
    ELSE 
         0 
    END), 0) AS QuantityOnSalesOrder
FROM 
(
    SELECT gj.[TransactionTypeId], gj.[Quantity], gj.[QuantityAllocated ]
    FROM GeneralJournal gj
    WHERE (gj.[TransactionTypeId] = @PURCHASEORDERTRANSACTIONTYPEID 
    OR gj.[TransactionTypeId] = @SALESORDERTRANSACTIONTYPEID)
    AND gj.[ItemId] = @ITEMID AND gj.[Inactive] = 0 
    AND gj.[Quantity] - gj.[QuantityAllocated] > 0
);

Open in new window


I know that SQL Server is a bit more picky and requires an alias, but adding one just changes the error message to a more generic complaint about duff syntax.

All the examples I have been able to find for this query are structured much the same but none use aggregate functions which I suspect may be my problem. Anyone suggest how to get the syntax right for SQL Server?
0
Comment
Question by:chrisbray
  • 9
  • 5
  • 3
17 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39189093
I don't really see a problem.

But may this is causing an issue:

    SELECT gj.[TransactionTypeId], gj.[Quantity], gj.[QuantityAllocated ]

Change it to this:

    SELECT gj.[TransactionTypeId], gj.[Quantity], gj.[QuantityAllocated]


I think:
gj.[QuantityAllocated ] and
gj.[QuantityAllocated]
may be two different column names in SQL Server?!


Edit: Nope, testing revealed that SQL matches the other names up.
0
 
LVL 3

Author Comment

by:chrisbray
ID: 39189287
No, that is not the issue.  It is the same column in the same table, but the space is of course a typo and doesn't exist n the real code... Sorry about that!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39189314
So what error do you get?  

I don't see a coding problem there for SQL: looks like it should run fine to me.

SQL allows aggregations against derived tables, although as you noted an alias for the derived table is required in SQL:


FROM
(
    SELECT gj.[TransactionTypeId], gj.[Quantity], gj.[QuantityAllocated]
    ...
) AS derived;
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39189592
I have a query that works fine with other TSQL compliant databases
For the record there is only one T-SQL "compliant" database and that is SQL Server.  In other words, if it does not compile in SQL Server than there is a T-SQL syntax error in the code.
0
 
LVL 3

Author Comment

by:chrisbray
ID: 39190137
ScottPletcher:

My apologies, I thought that I had included the error messages.  The query fails to parse in SSMS, and returns the message that it is "expecting ')', AS, EXCEPT or UNION".  If I add an alias, that changes the message to the less explanatory "syntax error near ';'"

acperkins:

Not sure why you bothered to post your comment, since the whole point of asking the question is that I KNOW there is a syntax error!! Unfortunately I have not been able to determine the exact nature of the error, so if you are able to suggest where or what I have done wrong I would greatly appreciate it.
0
 
LVL 3

Accepted Solution

by:
chrisbray earned 0 total points
ID: 39190165
Hi Guys,

I think the problem may have been a non-printing character in SSMS query window from when I pasted the original query across. I cleared the query, retyped it from scratch adding declarations and the alias and this time it worked.  I cannot see any difference in the two pieces of code, but in case anyone else can spot a stupidity or it helps anyone else here is my final TSQL that works with both databases:

DECLARE @PURCHASEORDERTRANSACTIONTYPEID INT = 21;
DECLARE @SALESORDERTRANSACTIONTYPEID INT = 15;
DECLARE @ITEMID BIGINT = 15;

SELECT Coalesce(SUM(
    CASE WHEN TransactionTypeId = @PURCHASEORDERTRANSACTIONTYPEID THEN 
        Coalesce(Quantity, 0) - QuantityAllocated 
    ELSE 
         0 
    END), 0) AS QuantityOnPurchaseOrder,
Coalesce(SUM(
    CASE WHEN TransactionTypeId = @SALESORDERTRANSACTIONTYPEID THEN 
        Coalesce(Quantity, 0) - QuantityAllocated 
    ELSE 
         0 
    END), 0) AS QuantityOnSalesOrder
FROM 
(
    SELECT gj.[TransactionTypeId], gj.[Quantity], gj.[QuantityAllocated]
    FROM GeneralJournal gj
    WHERE (gj.[TransactionTypeId] = @PURCHASEORDERTRANSACTIONTYPEID 
    OR gj.[TransactionTypeId] = @SALESORDERTRANSACTIONTYPEID)
    AND gj.[ItemId] = @ITEMID AND gj.[Inactive] = 0 
    AND gj.[Quantity] - gj.[QuantityAllocated] > 0
) as TransactionData;

Open in new window

Thanks for trying to help...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39190600
Not sure why you bothered to post your comment,
It was not clear from your original question whether you were using SQL Server or not.

it helps anyone else here is my final TSQL that works with both databases:
Isn't it obvious?  When using T-SQL, all derived tables require an alias, regardless of whether you refer to them or not.  So your first query did not have the alias (TransactionData) the second does.  It does not get any simpler than that.
0
 
LVL 3

Author Comment

by:chrisbray
ID: 39190839
Hi acperkins,

I am grateful for your efforts to help, but felt I had to respond to your most recent comments.

It was not clear from your original question whether you were using SQL Server or not.

Did you actually read the question?  It says (additional emphasis being mine):

I have a query that works fine with other TSQL compliant databases e.g. VistaDB but which fails with SQL Server:

How can it fail with SQL Server if I am not using SQL Server?  How could that be any clearer?  Your statement obviously does not stand up to even the most cursory review, and it should be clear to anyone that SQL Server was indeed being used - the question would not have arisen were I not using it.

Then to your next comment:

When using T-SQL, all derived tables require an alias, regardless of whether you refer to them or not.

Again, a quick look at the original question shows this:

I know that SQL Server is a bit more picky and requires an alias,

So, clearly I knew that was required.  Further on in the same original sentence I clarified the point:

but adding one just changes the error message to a more generic complaint about duff syntax.

So, not only did I know that the alias was required but had already tested the query with an alias and was still getting the error.


ALL THIS WAS IN THE ORIGINAL QUESTION!


Now to your final comment:

So your first query did not have the alias (TransactionData) the second does.  It does not get any simpler than that.

The code sample I posted was the one that worked with other databases but not SQL Server, which was again stated in the question.  That was working code that I was asking for help in converting to SQL Server syntax and as stated in the original question the missing alias from that code had nothing to do with the actual problem!!  

I  guess you did not read the final answer I provided either, since in it I stated that I was able to determine that the problem had nothing to do with the visible parts of the query but was down to my apparent accidental pasting of an invisible character into SSMS which could not have been determined from the code shown in my original question.  I admitted to this stupidity in public in case anyone else had a similar problem and could check for it and eliminate it from their possibilities.

I really do appreciate your efforts to help, but reading the question and responses properly before responding is recommended.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39192869
You are right, I should have quoted the right part.  My question "Isn't it obvious?" was in reference to: "I cannot see any difference in the two pieces of code" (when the differences were pretty obvious).  Also your previous reference to "non-printing character" which had nothing to do with the problem and could confuse future readers who may think that could be the cause.

Glad you figured it out.
0
 
LVL 3

Author Comment

by:chrisbray
ID: 39193757
Hi acperkins,

The 'non-printing character' had EVERYTHING to do with the problem - in fact it was the ONLY problem!

I was not aware that SSMS could be thrown by something that cannot be seen, and it would seem that you weren't either.  I suspect therefore that other people may not be aware as well.  

The information that it is possible and that clearing and then manually typing in the query text instead of pasting it resolved the problem would have been immensely valuable to me had it been available at the start of this issue, saving me several days of testing and head scratching before I found it out for myself, hence my posting the information for the benefit of others.

Again, I don't want to sound ungrateful and your efforts to assist are always appreciated.  It is great to have you and the other EE experts to turn to when I cannot work things out for myself!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39195929
The 'non-printing character' had EVERYTHING to do with the problem - in fact it was the ONLY problem!
Nope, sorry but there is no problem in SSMS with non-printing characters. They are simply ignored.

Again, I don't want to sound ungrateful
Not at all.  I just want to make sure that future readers are not confused with a simple T-SQL syntax problem.
0
 
LVL 3

Author Comment

by:chrisbray
ID: 39196366
Hi acperkins,

Then we are just going to have to disagree.  I have proven to my satisfaction (the error was repeatable) that by pasting the original code in SSMS and adding the alias it has a problem and will not even parse correctly.  By typing it in manually there is NO problem.  Whilst I have not been able to determine what the problem character was I have even been able to determine where in the query it was (in the latter part of the sub select) by pasting the original query and then cutting parts of the code away until it worked.

Typing the query in manually or pasting it from a different source to the original  does not display the problem, suggesting that this was something somehow caught in the paste buffer from the original source.

To repeat: pasting in the code and adding the alias fails.  Typing in the same code to a new query window from scratch works perfectly.  Cutting out and retyping parts of the code selectively also allows SSMS to run the query without error.  I can think of no other cause that fits these symptoms other than something being caught in the paste buffer that we cannot see.

I don't want future readers to think that this cannot happen when demonstrably it can.  I guess we leave both of our points up and let them chosse for themselves...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39196617
Whilst I have not been able to determine what the problem character was I have even been able to determine where in the query it was
Why don't you upload it here as a file? Then it becomes a simple exercise of looking at it with a Hex editor and we can confirm you are right and more importantly determine what is that mysterious non-printing character that kills queries.  That way future reader can avoid that specific one and at the same time continue to use all the other useful ones.
0
 
LVL 3

Author Comment

by:chrisbray
ID: 39197601
Because having resolved the issue I deleted the original code to avoid the possibility of any accidental repetition!  Both databases now use the same query quite happily, and cutting and pasting the current query to restore it does not display the error.
0
 
LVL 3

Author Closing Comment

by:chrisbray
ID: 39198812
I found the solution for myself, and posted the result in case it helps anyone else.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39200307
I still believe my comments were on point and might have helped you, since I clearly stated repeatedly that the original code posted was correct syntax and, barring some other, unusual problem, should have run fine.

Scott
0
 
LVL 3

Author Comment

by:chrisbray
ID: 39200991
Hi Scott,

Hmmm.  It didn't actually provide any help in finding the solution... but there was another unusual problem as you pointed out.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now