CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7532

Adding a second UNION causing error

I have a query I am working on that is returning correctly. 

DECLARE @theItewId VARCHAR(100) = (SELECT item_id 
   FROM   ndc_attr 
   WHERE  attr_val = '00310072010'); 

SELECT nr.item_id, 
       nr.field_id, 
       nr.attr_val, 
       nr.upd_dtt, 
       nr.upd_usr, 
       nr.item_id, 
       1 AS counting, 
       nr.field_id 
FROM   ndc_attr nr 
WHERE  item_id = @theItewId 
       AND field_id IN ( 69, 144, 150, 148, 170 ) 
UNION ALL 
SELECT TOP 1 @theItewId          AS item_id, 
             418                 AS field_id, 
             nr.contract_num_val AS attr_val, 
             CURRENT_TIMESTAMP   AS upd_dtt, 
             '-1'                AS upd_usr, 
             '-1'                AS item_id, 
             '-1'                AS counting, 
             '-1'                AS field_id 
FROM   contract_ndc_brg nr 
WHERE  ndc_id = '00310072010' 
ORDER  BY upd_dtt DESC

But when I try to add another UNION ALL I am getting an error and don't understand why. 

DECLARE @theItewId VARCHAR(100) = (SELECT item_id 
   FROM   ndc_attr 
   WHERE  attr_val = '00310072010'); 

SELECT nr.item_id, 
       nr.field_id, 
       nr.attr_val, 
       nr.upd_dtt, 
       nr.upd_usr, 
       nr.item_id, 
       1 AS counting, 
       nr.field_id 
FROM   ndc_attr nr 
WHERE  item_id = @theItewId 
       AND field_id IN ( 69, 144, 150, 148, 170 ) 
UNION ALL 
SELECT TOP 1 @theItewId          AS item_id, 
             418                 AS field_id, 
             nr.contract_num_val AS attr_val, 
             CURRENT_TIMESTAMP   AS upd_dtt, 
             '-1'                AS upd_usr, 
             '-1'                AS item_id, 
             '-1'                AS counting, 
             '-1'                AS field_id 
FROM   contract_ndc_brg nr 
WHERE  ndc_id = '00310072010' 
ORDER  BY upd_dtt DESC 
UNION ALL 
SELECT TOP 1 @theItewId        AS item_id, 
             981               AS field_id, 
             nr.quote_price        AS attr_val, 
             CURRENT_TIMESTAMP AS upd_dtt, 
             '-1'              AS upd_usr, 
             '-1'              AS item_id, 
             '-1'              AS counting, 
             '-1'              AS field_id 
FROM   contract_ndc_brg nr 
WHERE  ndc_id = '00310072010' 
ORDER  BY upd_dtt DESC 

What am I doing wrong and how do I fix it?

Edit:

Msg 156, Level 15, State 1, Procedure USP_ENTY_ATTR_GET_BY_ATTR_VALS, Line 151
Incorrect syntax near the keyword 'UNION'.

Upvotes: 0

Views: 39

Answers (2)

JNevill
JNevill

Reputation: 50019

I believe the problem is that you can't do an ORDER BY in one of your UNIONS. You can do this, but you need to encapsulate the statement in a subquery:

SELECT * FROM
(SELECT nr.item_id, 
       nr.field_id, 
       nr.attr_val, 
       nr.upd_dtt, 
       nr.upd_usr, 
       nr.item_id, 
       1 AS counting, 
       nr.field_id 
FROM   ndc_attr nr 
WHERE  item_id = @theItewId 
       AND field_id IN ( 69, 144, 150, 148, 170 ) ) as t1
UNION ALL 
SELECT * FROM
(SELECT TOP 1 @theItewId          AS item_id, 
             418                 AS field_id, 
             nr.contract_num_val AS attr_val, 
             CURRENT_TIMESTAMP   AS upd_dtt, 
             '-1'                AS upd_usr, 
             '-1'                AS item_id, 
             '-1'                AS counting, 
             '-1'                AS field_id 
FROM   contract_ndc_brg nr 
WHERE  ndc_id = '00310072010' 
ORDER  BY upd_dtt DESC ) as t2
UNION ALL 
SELECT * FROM
(SELECT TOP 1 @theItewId        AS item_id, 
             981               AS field_id, 
             nr.quote_price        AS attr_val, 
             CURRENT_TIMESTAMP AS upd_dtt, 
             '-1'              AS upd_usr, 
             '-1'              AS item_id, 
             '-1'              AS counting, 
             '-1'              AS field_id 
FROM   contract_ndc_brg nr 
WHERE  ndc_id = '00310072010' 
ORDER  BY upd_dtt DESC ) as t3

Upvotes: 3

Santiago San Martin
Santiago San Martin

Reputation: 94

ORDER BY must go only once and at the bottom of your query. You have repeated this line:

ORDER BY upd_dtt DESC

which is causing the error

Upvotes: 2

Related Questions