2 Replies Latest reply on May 2, 2012 2:15 PM by Elizabeth Coleman

    SQL Union All queries

    Elizabeth Coleman

      I need help with the attached UNION All query.  I'm trying to combine three Excel worksheets by using the following Custom SQL, please look at it and give me some input as to why it's not working. I am getting a Database error 0x80040E10: No value given for one or more required parameters.  Thanks for any help you can provide.

       

      SELECT

        [FieldPipeline$].[BU] AS [BU],

        [FieldPipeline$].[PipeLineFiscal Month] AS [Fiscal Month],

        [FieldPipeline$].[ITL] AS [Proj Mgr],

        [FieldPipeline$].[Net] AS [Equip $],

        [FieldPipeline$].[OMS] AS [OMS],

        [FieldPipeline$].[Ops Mgr] AS [Ops Mgr],

        [FieldPipeline$].[Region] AS [Region],

        [FieldPipeline$].[Sales Order] AS [Sales Order],

        [FieldPipeline$].[Work Center] AS [CCT],

        [FieldPipeline$].[OMR CT] AS [OMR CT],

       

      "I" as [Type]

       

      FROM [FieldPipeline$]

       

       

      UNION ALL

       

      SELECT

      [NextMonthForecast$].[BU] AS [BU],

      [NEXTMONTHFORECAST$].[Forecast NextMONTH] AS [FISCAL MONTH],

      [NEXTMONTHFORECAST$].[PROJ MGR] AS [PROJ MGR],

      [NEXTMONTHFORECAST$].[EQUIP $] AS [EQUIP $],

      [NEXTMONTHFORECAST$].[OMS] AS [OMS],

      [NEXTMONTHFORECAST$].[OPS MGR] AS [OPS MGR],

      [NEXTMONTHFORECAST$].[REGION] AS [REGION],

      [NEXTMONTHFORECAST$].[SALES ORDER] AS [SALES ORDER],

      [NEXTMONTHFORECAST$].[CCT] AS [CCT],

      [NEXTMONTHFORECAST$].[OMR CT] AS [OMR CT],

       

       

      "N" AS [Type]

       

      From [NextMonthForecast$]

      UNION ALL

      SELECT

        

      [CYCLETIME$].[BU] AS [BU],

      [CYCLETIME$].[FISCAL MONTH] AS [FISCAL MONTH],

      [CYCLETIME$].[PROJ MGR] AS [PROJ MGR],

      [CYCLETIME$].[EQUIP $] AS [EQUIP $],

      [CYCLETIME$].[OMS] AS [OMS],

      [CYCLETIME$].[OPS MGR] AS [OPS MGR],

      [CYCLETIME$].[REGION] AS [REGION],

      [CYCLETIME$].[SALES ORDER] AS [SALES ORDER],

      [CYCLETIME$].[WORK CENTER] AS [CCT],

      [CYCLETIME$].[OMT CT] AS [OMR CT],

       

       

      "C" AS [Type]

       

      From [CYCLETIME$]

        • 1. Re: SQL Union All queries
          Richard Leeke

          It can be really hard to track these down - the error messages from MS Jet aren't very helpful. Here's how I'd go about trying to find the issue.

           

          1) Copy and paste each of the three SQL statements EXACTLY as they are in the union query and make sure that they work as individual custom SQL connections. That would rule out a simple typo in one of the clauses.

           

          2) Carefully compare the names and data types of all the fields created in the three connections. I'm not sure, but I suspect JET may complain if if thinks it has detected different data types for the same field. (I'm not sure what to do if that's the case, though - I can't think how to control that with a schema.ini file - maybe that's not what's happening. Report back if that does look the problem and I'll think again.)

           

          The only thing I noticed on a quick read of the SQL was that you have this field in the third clause:

           

          [CYCLETIME$].[OMT CT] AS [OMR CT],

           

          Is that supposed to be [OMT CT] or should it be [OMR CT]?


          • 2. Re: SQL Union All queries
            Elizabeth Coleman

            Thank you Richard your suggestion was very helpful in tracking down which query wasn't working.