Keyword UNION not expected

[itops@mdrt.org]

I am trying to get the following union query to process on my coldfusion site:

<cfquery name="committee_record"

         datasource="AS400a CMTLIB"

         maxrows=100

         dbtype="ODBC">

SELECT MBRESMPF.MBMID,

    MBRESMPF.MBCYR,

    MBRESMPF.MBCCD,

    MBRESMPF.MBDCD,

    UCase(COMITEPF.CMNAM) AS COM,

    MBRESMPF.MBMRC,

    UCase(RANKSPF.RKDSC) AS Rank,

    MBRESMPF.MBRPT,

    MBRESMPF.MBESC,

    MBRESMPF.MBERP,

    UCase(DIVISNPF.DVNAM) AS DIV

FROM MBRESMPF INNER JOIN RANKSPF ON MBRESMPF.MBMRC = RANKSPF.RKCDE LEFT JOIN COMITEPF ON MBRESMPF.MBCYR = COMITEPF.CMCYR AND MBRESMPF.MBCCD = COMITEPF.CMCCD AND MBRESMPF.MBDCD = COMITEPF.CMDCD LEFT JOIN DIVISNPF ON MBRESMPF.MBDYR = DIVISNPF.DVDYR AND MBRESMPF.MBDCD = DIVISNPF.DVDCD

WHERE MBRESMPF.MBMRC <> 'ORCDVPRN4' AND MBRESMPF.MBMRC <> 'ORCIPDRNS' AND MBMID = #id_number#

ORDER BY MBRESMPF.MBMID, MBRESMPF.MBCYR DESC

UNION SELECT MBRESMPF.MBMID,

    MBRESMPF.MBCYR,

    MBRESMPF.MBDCD,

    MBRESMPF.MBDCD,

    UCase(DIVISNPF.DVNAM) AS COM,

    MBRESMPF.MBMRC,

    UCase(RANKSPF.RKDSC) AS Rank,

    MBRESMPF.MBRPT,

    MBRESMPF.MBESC,

    MBRESMPF.MBERP,

    UCase(DIVISNPF.DVNAM) AS DIV

FROM MBRESMPF INNER JOIN RANKSPF ON MBRESMPF.MBMRC = RANKSPF.RKCDE LEFT JOIN DIVISNPF ON MBRESMPF.MBDYR = DIVISNPF.DVDYR AND MBRESMPF.MBDCD = DIVISNPF.DVDCD

WHERE MBRESMPF.MBMRC = 'ORCDVPRN4' OR MBRESMPF.MBMRC = 'ORCIPDRNS' AND MBMID = #id_number#

ORDER BY MBRESMPF.MBMID, MBRESMPF.MBCYR DESC

</cfquery>

But I receive the following error message:

Error Diagnostic Information

ODBC Error Code = 37000 (Syntax error or access violation)

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword UNION not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH OPTIMIZE.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (19:1) to (22:23).

This query works for me no problem in Microsoft Access.  Anyone have any suggestions?

Thank you!

[Adam Cameron. · Accepted solution]

I'm surprised it works in Access, but in my experience a UNION query usually expects a single ORDER BY at the end (so the one recordset that is the result of the union is ordered, not each recordset that contributes to the union)?  I'm not in a position to test this right now though, sorry.

--

Adam

[itops@mdrt.org]

Hi Adam,

Thanks for your response.  I tried removing the second Order by and am still receiving the same message.  It seems to be bombing out as soon as it reads the UNION SELECT part.  Almost like it can't interpret what UNION means and is expecting another value as it lists OR USE SKIP WAIT WITH FETCH and OPTIMIZE.

[Reed_Powell-ttnmOb]

It's the first ORDER BY that might be the problem, not the second one.  As Adam pointed out, in MS SQL you can only have one ORDER BY and it has to be after any UNIONs.  What database are you using?  I might make some deductions from the name of your datasource, but ...

[Adam Cameron.]

It's the FIRST one that could be the problem, not the second one.

--

Adam

[itops@mdrt.org]

I removed the first one and now I receieve a different error:

ODBC Error Code = S1000 (General error)

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0197 - Column MBMID cannot be qualified.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (19:1) to (22:23).

Any ideas?  Thank you both so much for the help you have given thus far!

[Dan_Bracuk]

My idea is to start small and grow slowly.  Start again by selecting a field from a single table.  Once you have that working, make it a union query.  Once you have that working, add fields one by one until you are done.  Any errors will be due to the most recently added field.

[itops@mdrt.org]

Thank you all for your input.  I have it working now:

<cfquery name="committee_record"

         datasource="AS400a CMTLIB"

         maxrows=100

         dbtype="ODBC">

SELECT MBRESMPF.MBMID,

    MBRESMPF.MBCYR,

    MBRESMPF.MBCCD,

    MBRESMPF.MBDCD,

    UCase(COMITEPF.CMNAM) AS COM,

    MBRESMPF.MBMRC,

    UCase(RANKSPF.RKDSC) AS Rank,

    MBRESMPF.MBRPT,

    MBRESMPF.MBESC,

    MBRESMPF.MBERP,

    UCase(DIVISNPF.DVNAM) AS DIV

FROM ((MBRESMPF INNER JOIN RANKSPF ON MBRESMPF.MBMRC = RANKSPF.RKCDE) LEFT JOIN COMITEPF ON (MBRESMPF.MBCYR = COMITEPF.CMCYR) AND (MBRESMPF.MBCCD = COMITEPF.CMCCD) AND (MBRESMPF.MBDCD = COMITEPF.CMDCD)) LEFT JOIN DIVISNPF ON (MBRESMPF.MBDYR = DIVISNPF.DVDYR) AND (MBRESMPF.MBDCD = DIVISNPF.DVDCD)

WHERE MBRESMPF.MBMRC <> 'ORCDVPRN4' AND MBRESMPF.MBMRC <> 'ORCIPDRNS' AND MBRESMPF.MBMID = #id_number#

UNION SELECT MBRESMPF.MBMID,

    MBRESMPF.MBCYR,

    MBRESMPF.MBCCD,

    MBRESMPF.MBDCD,

    UCase(DIVISNPF.DVNAM) AS COM,

    MBRESMPF.MBMRC,

    UCase(RANKSPF.RKDSC) AS Rank,

    MBRESMPF.MBRPT,

    MBRESMPF.MBESC,

    MBRESMPF.MBERP,

    UCase(DIVISNPF.DVNAM) AS DIV

FROM ((MBRESMPF INNER JOIN RANKSPF ON MBRESMPF.MBMRC = RANKSPF.RKCDE) LEFT JOIN DIVISNPF ON (MBRESMPF.MBDYR = DIVISNPF.DVDYR) AND (MBRESMPF.MBDCD = DIVISNPF.DVDCD))

WHERE (MBRESMPF.MBMRC = 'ORCDVPRN4' OR MBRESMPF.MBMRC = 'ORCIPDRNS') AND MBRESMPF.MBMID = #id_number#

ORDER BY MBCYR DESC

</cfquery>

I ended up removing the first ORDER BY portion as was suggested here.  I also had to add a few () in certain places.