Help Center/ Data Warehouse Service/ Standard Data Warehouse (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-05-29 GMT+08:00
View PDF

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

The statements before and after rewriting are not equivalent because the original SQL statement is incorrect. The correct format is "coalesce(d.plan_unit_code, 'snull') AS plan_unit_code".

Modification comparison

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