Help Center/ Data Warehouse Service / Developer Guide/ Developer Guide (9.1.0.x)/ DWS Performance Tuning/ SQL Tuning Examples/ Cases: Modifying Statements That Cannot Be Pushed Down/ Case: Type of Record in TargetList Can Not Be Shipped
Updated on 2026-01-04 GMT+08:00
View PDF
Share

Case: Type of Record in TargetList Can Not Be Shipped

Possible Cause

The output column contains the record type, which is not displayed in the outermost output column. This type of error is reported in the following scenarios:

  1. The SQL writing logic is incorrect. As a result, the output column contains (...).
  2. The SQL service logic is correct. First, grasp what the service does. Then, change the record field to text format and adjust it as needed.

Case 1: Using the Output Column in the (…) Format

Original statement

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
SELECT
    d.id,
    coalesce(d.period, 'snull') AS period,
    (d.plan_unit_code, 'snull') AS plan_unit_code,
    coalesce(d.product_type_model, 'snull') AS product_type_model,
    coalesce(d.revision, 'snull') AS revision,
    d.start_date
FROM (SELECT *
    FROM cdcscm.cdc_mp_d_forecast_t_6120 t
    WHERE t.cdc_timestamp > to_date('2023-07-06 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 7 
    AND t.cdc_timestamp < to_date('2023-08-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
) t1,
sdiscm.mp_d_forecast_t_6120 d
WHERE (t1.audit_op_type = 'delete' AND t1.audit_op_option = 'before')
AND d.id = t1.id

Rewritten statement

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
SELECT
    d.id,
    coalesce(d.period, 'snull') AS period,
    coalesce(d.plan_unit_code, 'snull') AS plan_unit_code,
    coalesce(d.product_type_model, 'snull') AS product_type_model,
    coalesce(d.revision, 'snull') AS revision,
    d.start_date
FROM (SELECT *
    FROM cdcscm.cdc_mp_d_forecast_t_6120 t
    WHERE t.cdc_timestamp > to_date('2023-07-06 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 7 
    AND t.cdc_timestamp < to_date('2023-08-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
) t1,
sdiscm.mp_d_forecast_t_6120 d
WHERE (t1.audit_op_type = 'delete' AND t1.audit_op_option = 'before')
AND d.id = t1.id

Before and after changes differ because the original SQL has errors. Write it correctly as: coalesce(d.plan_unit_code, 'snull') AS plan_unit_code.

Modification comparison

Parent Topic: Cases: Modifying Statements That Cannot Be Pushed Down