question

Nick-9864 avatar image
0 Votes"
Nick-9864 asked EchoLiu-msft commented

Syntax error on CASE Statement join in subquery

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'
sql-server-generalsql-server-transact-sql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·
Nick-9864 avatar image Nick-9864 JeffreyWilliams-3310 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·

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....

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

Hi @Nick-9864

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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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."

0 Votes 0 ·

That is correct - if you can provide the information that has been requested I am sure someone will be able to resolve your issues.

0 Votes 0 ·

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.

0 Votes 0 ·