7 Replies Latest reply on Dec 4, 2012 9:41 AM by itops@mdrt.org

    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!

        • 1. Re: Keyword UNION not expected
          Adam Cameron. Level 5

          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

          • 2. Re: Keyword UNION not expected
            itops@mdrt.org Level 1

            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.

            • 3. Re: Keyword UNION not expected
              Reed Powell Level 3

              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 ...

              • 4. Re: Keyword UNION not expected
                Adam Cameron. Level 5

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

                 

                --

                Adam

                • 5. Re: Keyword UNION not expected
                  itops@mdrt.org Level 1

                  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!

                  • 6. Re: Keyword UNION not expected
                    Dan Bracuk Level 5

                    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.

                    • 7. Re: Keyword UNION not expected
                      itops@mdrt.org Level 1

                      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.