2 Replies Latest reply on Aug 12, 2013 1:27 PM by JeffMillard

    Duh! DISTINCT from UNION - ExecuteSQL

    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 ?