AnsweredAssumed Answered

Duh! DISTINCT from UNION - ExecuteSQL

Question asked by JeffMillard on Aug 11, 2013
Latest reply on Aug 12, 2013 by JeffMillard

Title

Duh! DISTINCT from UNION - ExecuteSQL

Post

     OOPS- REPOSTING IN EMBARASSMENT (but you all should be embarassed too).  UNIONS ARE BY DEFAULT DISTINCT UNLESS YOU DO A UNION ALL!!!!   My test data had a SPACE in one of the 'duplicate values'  it so it legitimately repeated it.

     -----

     Having trouble combining DISTINCT with a UNION-- can do on the individual SELECTS but not across the result of the union. The following statement gets me the rows I want (with duplicates)...how do I do a DISTINCT on this:

     ExecuteSQL ("SELECT  a.\"ARRIVAL_TERMINAL\" from \"QUOTE_LEGS\" a where a.\"_QUOTEIDfk\" = ?  
                             UNION
                              SELECT  b.\"DEP_TERMINAL\"  from \"QUOTE_LEGS\" b where b.\"_QUOTEIDfk\" = ?"
                             ;"";"";QUOTE_CONF::_QUOTEIDpk;QUOTE_CONF::_QUOTEIDpk) 

     I tried a valid 'real SQL' construct "SELECT DISTINCT TERM FROM (SELECT A FROM TABLE T1 UNION SELECT B FROM TABLE T2) AS TERM" but get the dreaded ? 

Outcomes