Syntax error on CASE Statement join in subquery

Question

Syntax error on CASE Statement join in subquery

Nick 1

I have the below query and subquery where I am getting a SUM value, and I want to join PS_VOUCHER_LINE LineSub conditionally (on Line.LINE_NBR = LineSub.LINE_NBR) on PS_VOUCHER Line only when a VOUCHER_ID has more than 1 LINE_NBR, otherwise I don't want this last join condition to execute. I am getting a Syntax error on the statement (Incorrect syntax near '='.) . How can I get this conditional join to work properly?

SELECT  
    CONCAT(Header.BUSINESS_UNIT, Header.VOUCHER_ID) AS INVOICE_ID 
    ,   
        ( 

            SELECT SUM(LineSub.MERCHANDISE_AMT) 
            FROM PS_VOUCHER_LINE LineSub
            WHERE Line.BUSINESS_UNIT = LineSub.BUSINESS_UNIT 
            AND Line.VOUCHER_ID = LineSub.VOUCHER_ID 
            AND 
                CASE 
                    WHEN COUNT(Line.LINE_NBR) > 1 THEN Line.LINE_NBR = LineSub.LINE_NBR
                END
            GROUP BY LineSub.VOUCHER_ID 
        ) + Header.FREIGHT_AMT + Header.SALETX_AMT AS GROSS_AMT_LINE_FREIGHT_TAX
FROM 
    PS_VOUCHER Header
    INNER JOIN PS_VOUCHER_LINE Line ON Line.BUSINESS_UNIT = Header.BUSINESS_UNIT 
        AND Line.VOUCHER_ID = Header.VOUCHER_ID
WHERE 
    Header.VOUCHER_ID = '00241107'

Jeffrey Williams 1,896 Reputation points

2021-07-22T20:17:26.227+00:00

If the LINE_NBR count is not > 1 - what should be returned? The problem here is that a CASE expression returns a scalar value and you are trying to use it to form the comparison. Since it isn't really clear what you are trying to do I can only guess, but you could try removing the CASE - using: AND Line.LINE_NBR = LineSub.LINE_NBR and include after the GROUP BY the having clause: HAVING COUNT(Line.Line_NBR) > 1. With that said - you are then attempting to add values using '+' which will fail because you cannot add numeric values to a string.
Nick 1 Reputation point

2021-07-22T20:24:14.683+00:00

My results (VOUCHER_ID's) don't always have a more than one LINE_NBR though, so I need to be able to capture rows with count of 1 LINE_NBR and <1 LINE_NBR as well, and only join on LINE_NBR if the count is greater than 1.
Jeffrey Williams 1,896 Reputation points

2021-07-22T21:05:44.127+00:00

Sorry - but that doesn't really make any sense. The outer query has a join to PS_VOUCER_LINE already. Why do you need to join to that table in a sub-query? What are you trying to return here? If the voucher does not have a line - the INNER JOIN in the outer query will not return that voucher because there is no row in PS_VOUCHER_LINE that matches. To be able to help further - I would need to see some sample data and expected results. I suspect you don't want to join to PS_VOUCHER_LINE in the outer query - and really just want a sum from the voucher line if it exists, but that is just a guess.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-07-22T21:31:01.81+00:00
I suspect that you need to go back to the drawing-board and figure out what you are really looking for.

Now you get the syntax error on the =, but if you would replace Line.LINE_NBR = LineSub.LINE_NBR with a scalar value, you would next get an error on the COUNT which can't appear in a WHERE. And what you mean with COUNT(Line.LINE_NBR) is a little unclear. If you say:

SELECT COUNT(Line_nbr) FROM tbl

You will get back as many rows in tbl where Line_nbr is non-NULL.

It may help if you post CREATE TABLE statements for your tables together with INSERT statements with test data and the expected results from the test data, so we can get idea of what you are looking for.

Trying to understand what you want from an SQL statement with several errors in it is kind of difficult.
Ryan Abbey 1,186 Reputation points

2021-07-23T02:15:51.117+00:00

From what I could garner, you don't want the sum when Line.LINE_NBR = 1..

AND Line.LINE_NBR > 1 and Line.LINE_NBR = LineSub.LINE_NBR

Probably need an ISNULL though since you are adding the result to other fields....

1 answer

Your answer

Jeffrey Williams 1,896 Reputation points

2021-07-22T20:17:26.227+00:00

If the LINE_NBR count is not > 1 - what should be returned? The problem here is that a CASE expression returns a scalar value and you are trying to use it to form the comparison. Since it isn't really clear what you are trying to do I can only guess, but you could try removing the CASE - using: AND Line.LINE_NBR = LineSub.LINE_NBR and include after the GROUP BY the having clause: HAVING COUNT(Line.Line_NBR) > 1. With that said - you are then attempting to add values using '+' which will fail because you cannot add numeric values to a string.
Nick 1 Reputation point

2021-07-22T20:24:14.683+00:00

My results (VOUCHER_ID's) don't always have a more than one LINE_NBR though, so I need to be able to capture rows with count of 1 LINE_NBR and <1 LINE_NBR as well, and only join on LINE_NBR if the count is greater than 1.
Jeffrey Williams 1,896 Reputation points

2021-07-22T21:05:44.127+00:00

Sorry - but that doesn't really make any sense. The outer query has a join to PS_VOUCER_LINE already. Why do you need to join to that table in a sub-query? What are you trying to return here? If the voucher does not have a line - the INNER JOIN in the outer query will not return that voucher because there is no row in PS_VOUCHER_LINE that matches. To be able to help further - I would need to see some sample data and expected results. I suspect you don't want to join to PS_VOUCHER_LINE in the outer query - and really just want a sum from the voucher line if it exists, but that is just a guess.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-07-22T21:31:01.81+00:00

I suspect that you need to go back to the drawing-board and figure out what you are really looking for.

Now you get the syntax error on the =, but if you would replace Line.LINE_NBR = LineSub.LINE_NBR with a scalar value, you would next get an error on the COUNT which can't appear in a WHERE. And what you mean with COUNT(Line.LINE_NBR) is a little unclear. If you say:

SELECT COUNT(Line_nbr) FROM tbl

You will get back as many rows in tbl where Line_nbr is non-NULL.

It may help if you post CREATE TABLE statements for your tables together with INSERT statements with test data and the expected results from the test data, so we can get idea of what you are looking for.

Trying to understand what you want from an SQL statement with several errors in it is kind of difficult.
Ryan Abbey 1,186 Reputation points

2021-07-23T02:15:51.117+00:00

From what I could garner, you don't want the sum when Line.LINE_NBR = 1..

AND Line.LINE_NBR > 1 and Line.LINE_NBR = LineSub.LINE_NBR

Probably need an ISNULL though since you are adding the result to other fields....

Answer 1

Hi @Nick ，

Welcome to the microsoft TSQL Q&A forum!

Sorry,I'm not quite sure about your needs, the following is my guessed solution. If this does not solve your problem, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result? So that we’ll get a right direction and make some test.

Please try:

 SELECT    
     CONCAT(Header.BUSINESS_UNIT, Header.VOUCHER_ID) AS INVOICE_ID   
     ,     
         (   
              
             SELECT SUM(LineSub.MERCHANDISE_AMT)   
             FROM PS_VOUCHER_LINE LineSub  
             WHERE Line.BUSINESS_UNIT = LineSub.BUSINESS_UNIT   
             AND Line.VOUCHER_ID = LineSub.VOUCHER_ID   
             AND Line.LINE_NBR =  
                 (CASE   
                     WHEN COUNT(Line.LINE_NBR) > 1 THEN  LineSub.LINE_NBR  
                 END)  
             GROUP BY LineSub.VOUCHER_ID   
         ) + Header.FREIGHT_AMT + Header.SALETX_AMT AS GROSS_AMT_LINE_FREIGHT_TAX  
 FROM   
     PS_VOUCHER Header  
     INNER JOIN PS_VOUCHER_LINE Line ON Line.BUSINESS_UNIT = Header.BUSINESS_UNIT   
         AND Line.VOUCHER_ID = Header.VOUCHER_ID  
 WHERE   
     Header.VOUCHER_ID = '00241107'

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Nick 1 Reputation point

2021-07-23T11:20:54.19+00:00

I can not do an aggregate function (COUNT) in a WHERE clause - it is causing the below error: "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
Jeffrey Williams 1,896 Reputation points

2021-07-23T13:39:43.63+00:00

That is correct - if you can provide the information that has been requested I am sure someone will be able to resolve your issues.
EchoLiu-MSFT 14,621 Reputation points

2021-07-26T06:46:24.85+00:00

The above query is just my guess, without detailed tables and data, it is difficult for us to help you. Can you share a minimal example that can be tested, and the example output you expect.

Share via

Syntax error on CASE Statement join in subquery

1 answer

Your answer