Union in a subquery

[sa0309]

I'm trying to run the query below; however I get the following 2 errors:

1) Number (156) Severity (15) State (68) Server (SYBASE) Incorrect syntax near the keyword 'UNION'.

2) Number (102) Severity (15) State (1) Server (SYBASE) Incorrect syntax near ')'.

The subquery runs successfully by itself.

I'm not seeing the syntax error. Any assistance would be appreciated.


SELECT A.MEME_CK, MIN(A.MEPE_EFF_DT) , MAX(A.MEPE_TERM_DT)
FROM CMC_MEPE_PRCS_ELIG A
WHERE A.MEME_CK IN
(
SELECT B.MEME_CK
FROM HHT_CMS_MSP_DTL_WORK B
where CAST( SUBSTRING(B.MEPE_TERM_DT, 1, 4) + '-' + SUBSTRING(B.MEPE_TERM_DT, 5, 2) + '-' + SUBSTRING(B.MEPE_TERM_DT, 7, 2) AS DATETIME) > '10/31/2010'
AND B.MEPE_TERM_DT != '00000000'
UNION
SELECT C.MEME_CK
FROM HHT_CMS_MSP_DTL_WORK C
where C.MEPE_TERM_DT = '00000000'
)

AND A.CSPD_CAT = 'M'
AND A.MEPE_ELIG_IND = 'Y'
GROUP BY A.MEME_CK

 

[gmmastros]

Try this:

SELECT A.MEME_CK, MIN(A.MEPE_EFF_DT) , MAX(A.MEPE_TERM_DT) 
       FROM CMC_MEPE_PRCS_ELIG A
      INNER JOIN
        ( 
            SELECT B.MEME_CK 
               FROM HHT_CMS_MSP_DTL_WORK B
               where CAST( SUBSTRING(B.MEPE_TERM_DT, 1, 4) + '-' + SUBSTRING(B.MEPE_TERM_DT, 5, 2) + '-' + SUBSTRING(B.MEPE_TERM_DT, 7, 2) AS DATETIME) > '10/31/2010'
                 AND B.MEPE_TERM_DT != '00000000'
                UNION  
              SELECT C.MEME_CK 
                FROM HHT_CMS_MSP_DTL_WORK C
               where C.MEPE_TERM_DT = '00000000' 
        ) As X On A.MEME_CK = X.MEME_CK
        
WHERE A.CSPD_CAT = 'M'
        AND A.MEPE_ELIG_IND = 'Y'
    GROUP BY A.MEME_CK

I know this would work with SQL Server. I'm not sure about SYBASE, but it's worth a try.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Alternatively, you may try

SELECT A.MEME_CK, MIN(A.MEPE_EFF_DT) , MAX(A.MEPE_TERM_DT) 
       FROM CMC_MEPE_PRCS_ELIG A
      WHERE EXISTS (select 1 from 
        ( 
            SELECT B.MEME_CK 
               FROM HHT_CMS_MSP_DTL_WORK B
               where CAST( SUBSTRING(B.MEPE_TERM_DT, 1, 4) + '-' + SUBSTRING(B.MEPE_TERM_DT, 5, 2) + '-' + SUBSTRING(B.MEPE_TERM_DT, 7, 2) AS DATETIME) > '10/31/2010'
                 AND B.MEPE_TERM_DT != '00000000'
                UNION  
              SELECT C.MEME_CK 
                FROM HHT_CMS_MSP_DTL_WORK C
               where C.MEPE_TERM_DT = '00000000' 
        ) As X WHERE A.MEME_CK = X.MEME_CK)
        
AND A.CSPD_CAT = 'M'
        AND A.MEPE_ELIG_IND = 'Y'
    GROUP BY A.MEME_CK

the WHERE EXISTS should return the same result as you originally tried with the IN (..)

INNER JOIN may produce different result if you have multiple

MEME_CK records in the derived table.

PluralSight Learning Library

 

[gmmastros]

INNER JOIN may produce different result if you have multiple MEME_CK records in the derived table.

This isn't possible because it's a union query (not union all), so you are guaranteed to NOT get duplicates.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sa0309]

In this case, it did work in Sybase. I guess Sybase is being particular about its syntax.

Thank your for quick response. I appreciate it.

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, good point. I didn't notice we only select one field. Exists and INNER JOIN should work the same for this case.

PluralSight Learning Library