Problem with procedure compiling

General

Problem with procedure compiling

Any one please help me,

   INSERT INTO Q_SCM_VW.supplier_performance_work

     (

     date_key,

     supplier_key,

     plant_key,

     buyer_key,

     part_key,

     active_status,

     shipments,

     value_shipped,

     quantity_shipped,

     scheduled_shipments,

     delivery_failures,

     quality_failures,

     administrative_failures,

     quantity_defective,

     amount_spent,

     value_defective,

     notes_supplier_id,

     notes_facility_id,

     the_date,

     part_number,

     SYSTEM

     )

     SELECT  0 AS date_key,

      0 AS supplier_key,

      0 AS plant_key,

      0 AS buyer_key,

      0 AS part_key,

      active_status,

      SUM(shipments) AS shipments,

      SUM(value_shipped) AS value_shipped,

      SUM(quantity_shipped) AS quantity_shipped,

      SUM(scheduled_shipments) AS scheduled_shipments,

      SUM(delivery_failures) AS delivery_failures,

      SUM(quality_failures) AS quality_failures,

      SUM(administrative_failures) AS administrative_failures,

      SUM(quantity_defective) AS quantity_defective,

      SUM(amount_spent) AS amount_spent,

      SUM(value_defective) AS value_defective,

      notes_supplier_id,

      notes_facility_id,

      the_date,

      part_number,

      SYSTEM

     FROM

     (SELECT notes_supplier_id,

      notes_facility_id,

      ADD_MONTHS(datetime_received- (EXTRACT(DAY FROM datetime_received) -1 ), 1) -1 AS the_date,

      part_number,

      COALESCE(active_status,'Active') AS active_status,

      CAST(1 AS DECIMAL(20,6)) AS shipments,

      value_received AS value_shipped,

      quantity_received AS quantity_shipped,

      CAST(1 AS DECIMAL(20,6)) AS scheduled_shipments,

      CAST(0 AS DECIMAL(20,6)) AS delivery_failures,

      CAST(0 AS DECIMAL(20,6)) AS quality_failures,

      CAST(0 AS DECIMAL(20,6)) AS administrative_failures,

      CAST(0 AS DECIMAL(20,6)) AS quantity_defective,

      CAST(0 AS DECIMAL(20,6)) AS amount_spent,

      CAST(0 AS DECIMAL(20,6)) AS value_defective,

      (SELECT system_name

       FROM Q_SCM_vw.SOURCE_SYSTEM_XREF xref

       WHERE RECEIPT.SOURCE = xref.SOURCE) AS SYSTEM

      FROM Q_SCM_vw.RECEIPT

      WHERE datetime_received >= CAST(SUBSTR(CAST(ADD_MONTHS(CURRENT_DATE, - 26) AS DATE FORMAT 'yyyy/mm/dd'), 1, 8)||'01' AS DATE) 

             AND datetime_received <CURRENT_DATE

     UNION ALL

     SELECT  notes_supplier_id,

      CASE WHEN plant_id = 'n/a'

       THEN '70'

           ELSE notes_facility_id

      END AS notes_facility_id,

      ADD_MONTHS(CAST((spend_month || '01' || spend_year) AS DATE FORMAT 'mmddyyyy'),1) -1  AS the_date,

      'Not Assigned' AS part_number,

      'Active' AS active_status,

      0 AS shipments,

      0 AS value_shipped,

      0 AS quantity_shipped,

      0 AS scheduled_shipments,

      0 AS delivery_failures,

      0 AS quality_failures,

      0 AS administrative_failures,

      0 AS quantity_defective,

      amount_spent AS amount_spent,

      0 AS value_defective,

      'SAP' AS SYSTEM

      FROM Q_SCM_vw.SAP_SPEND

      WHERE spend_DATE > CAST(SUBSTR(CAST(ADD_MONTHS(CURRENT_DATE, - 26) AS DATE FORMAT 'yyyy/mm/dd'), 1, 8)||'01' AS DATE)

     UNION ALL

     SELECT notes_supplier_id,

      notes_facility_id,

      ADD_MONTHS(datetime_received- (EXTRACT(DAY FROM datetime_received) -1 ), 1) -1 AS the_date,

      part_number,

      COALESCE(active_status,'Active') AS active_status,

      1 AS shipments,

      value_received AS value_shipped,

      quantity_received AS quantity_shipped,

      1 AS scheduled_shipments,

      0 AS delivery_failures,

      0 AS quality_failures,

      0 AS administrative_failures,

      0 AS quantity_defective,

      0 AS amount_spent,

      0 AS value_defective,

      (SELECT system_name

       FROM Q_SCM_vw.SOURCE_SYSTEM_XREF xref

       WHERE MSA_RECEIPT.SOURCE = xref.SOURCE) AS SYSTEM

     FROM Q_SCM_vw.MSA_RECEIPT

      WHERE datetime_received >= CAST(SUBSTR(CAST(ADD_MONTHS(CURRENT_DATE, - 26) AS DATE FORMAT 'yyyy/mm/dd'), 1, 8)||'01' AS DATE) 

          AND datetime_received < CURRENT_DATE

     UNION ALL

     SELECT  notes_supplier_id,

       notes_facility_id,

       ADD_MONTHS(CAST(date_due AS DATE)- (EXTRACT(DAY FROM CAST(date_due AS DATE)) -1 ), 1) -1 AS the_date,

       part_number,

       COALESCE(active_status,'Active') AS active_status,

       0 AS shipments,

       0 AS value_shipped,

       0 AS quantity_shipped,

       0 AS scheduled_shipments,

       0 AS delivery_failures,

       0 AS quality_failures,

       0 AS administrative_failures,

       0 AS quantity_defective,

       0 AS amount_spent,

       0 AS value_defective,

       (SELECT system_name

        FROM Q_SCM_vw.SOURCE_SYSTEM_XREF xref

        WHERE SCHEDULED_DELIVERY.SOURCE = xref.SOURCE) AS SYSTEM

      FROM Q_SCM_vw.SCHEDULED_DELIVERY

      WHERE  CAST(date_due  AS DATE)  >= CAST(SUBSTR(CAST(ADD_MONTHS(CURRENT_DATE, - 26) AS DATE FORMAT 'yyyy/mm/dd'), 1, 8)||'01' AS DATE) 

                           AND date_due <  CURRENT_DATE

     UNION ALL

     SELECT  mrr.parent_no AS notes_supplier_id,

       mrr.ta_plant_parent_id AS notes_facility_id,

       ADD_MONTHS(mrr.mrr_date- (EXTRACT(DAY FROM mrr.mrr_date) -1 ), 1) -1 AS the_date,

       COALESCE( TRIM(mrr.part_no_rev),'Not Assigned') AS part_number,

       COALESCE(active_status,'Active') AS active_status,

       0 AS shipments,

       0 AS value_shipped,

       0 AS quantity_shipped,

       0 AS scheduled_shipments,

       CASE WHEN cat.category = '3' THEN 1 ELSE 0 END  AS delivery_failures,

       CASE WHEN cat.category = '2' THEN 1 ELSE 0 END  AS quality_failures,

       CASE WHEN cat.category = '1' THEN 1 ELSE 0 END  AS administrative_failures,

        CASE WHEN (cat.category = '2' and cat.code_type = 'NON-CONFORMANCE' ) THEN

      COALESCE(COALESCE(mrr.adj_rejd,mrr.est_def),0)

        ELSE CASE WHEN  (cat.category = '2' and cat.code_type = 'DEF' ) THEN mrr.ACTUAL_DEFECTIVE

        Else 0

               END END AS quantity_defective,

       /*CASE WHEN cat.category = '2' THEN 

        COALESCE(COALESCE(mrr.adj_rejd,mrr.est_def),0)

        ELSE 0

       END AS quantity_defective,*/

       0 AS amount_spent,

       CASE WHEN cat.category = '2' THEN COALESCE(total_amount,0) ELSE 0 END AS value_defective,

       CASE WHEN id_type = 'SAP Canada' THEN 'SAP' ELSE id_type END AS SYSTEM

      FROM Q_SCM_vw.NOTES_MRR mrr,Q_SCM_vw.NOTES_CATEGORY cat

      WHERE mrr.nc_code = cat.code_no AND

         (cat.code_type = 'NON-CONFORMANCE' or cat.code_type ='DEF') AND

         mrr.status <> 'Canceled' AND

              mrr.mrr_date  >= CAST(SUBSTR(CAST(ADD_MONTHS(CURRENT_DATE, - 26) AS DATE FORMAT 'yyyy/mm/dd'), 1, 8)||'01' AS DATE) 

         AND mrr.mrr_date < CURRENT_DATE

       ) a

     GROUP BY  notes_supplier_id,

        notes_facility_id,

        the_date,

        part_number,

        SYSTEM,

        active_status

        ;

In above code I made the below changes in bottom union all

      COALESCE(COALESCE(mrr.adj_rejd,mrr.est_def),0)

        ELSE CASE WHEN  (cat.category = '2' and cat.code_type = 'DEF' ) THEN mrr.ACTUAL_DEFECTIVE

        Else 0

               END END AS quantity_defective,

I am able to execute using below code

       /*CASE WHEN cat.category = '2' THEN 

        COALESCE(COALESCE(mrr.adj_rejd,mrr.est_def),0)

        ELSE 0

       END AS quantity_defective,*/

When I am trying to execute above query, Iam getting error like

Insert Failed.[3654] Corresponding select-list expressions are incompatible

But I am able to execute only last select statement with changes.

Can some one please help me. Thanks in advance...

1 REPLY
Enthusiast

Re: Problem with procedure compiling

Well, You are using union all, and for union all (Set operators)  we have to meet the following conditions for union compatibility:

  1. Number of column should be equal in all select lists
  2. Data type of corresponding columns must match in all select lists

So in order to avoid this incompatibaility you need first make the number of columns equal in all the select statements used in Union all, and secondly check their data types and and cast if any column data type is mismatch

Khurram