title: UNION ALL FROM SELECT STATEMENT NOT WORKING
body: <p>I have following 5 tables:</p>
  
  <pre><code>TABLE OLD_A ( CID, CNO, CNAME )
  TABLE OLD_B ( DID, DNO, DNAME )
  TABLE OLD_MAP ( CID, DID, TERRITORY )
  TABLE NEW_A ( NCID, NCNO, NCNAME )
  TABLE NEW_MAP ( NCNO, DNO, TERRITORY  )
  </code></pre>
  
  <p>I want to get <code>CNO</code> and <code>CNAME</code> from both new and old table so I made following query</p>
  
  <pre><code>DECLARE@TERRITORY varchar(16), @DNO varchar(16)
  
  SELECT RESULT.`enter code here`CNO as CNO, RESULT.CNAME as CNAME, RESULT.Is_New_Or_Old as Is_New_Or_Old
  FROM
  (
    (SELECT C.CNO as CNO, C.CNAME as CNAME, 'OLD' AS Is_New_Or_Old [OLD_A] AS C LEFT JOIN [OLD_MAP] AS DCL ON C.ID = DCL.CID JOIN [OLD_B] AS D ON DCL.DID = D.ID WHERE C.TERRITORY = @TERRITORY AND D.DNO = @DNO )
    UNION ALL 
    (SELECT NC.NCNO as CNO, NC.CNAME as CNAME, 'NEW' AS Is_New_Or_Old  FROM [NEW_A] AS NC LEFT JOIN [NEW_MAP] AS CDTL ON NC.NCNO = CDTL.NCNO WHERE CDTL.DNO = @DNO AND CDTL.Territory_Id = @TERRITORY)
  ) RESULT 
  ORDER BY CNO
  </code></pre>
  
  <p>But it is giving me error</p>
  
  <pre><code>Incorrect syntax near ')'.
  </code></pre>
  
  <p>AT LAST )</p>