sql语法错误,信息: mismatched input '(' expecting ')'(line 4, pos 22) == SQL == with huilv as ( -- 所有年月汇率 select CONCAT(CAST(t2.year AS STRING) as year, LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, t2.exchange_rate as qty ----------------------^^^ FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' where t1.item_no is not null and t2.exchange_rate is not null group by CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')), t2.exchange_rate order by CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) ), huilv_data as ( -- 所有内存条代码汇率 select t0.item_no, '汇率' as data_type, t2.year as cal_year, t2.cal_date, t2.qty FROM dm_sever_label_nct t0 left join huilv t2 where t0.item_no is not null and t2.qty is not null ), gbs as ( -- 所有内存条代码GB单位 select main_ida.text_field_ooil5pe9 as item_no, main_ida.text_field_2lnermdr as qty from ods_ida_t_app0896034115789864961_m1192517514695749632 main_ida where main_ida.id = (select max(ida.id) from ods_ida_t_app0896034115789864961_m1192517514695749632 ida where ida.text_field_ooil5pe9 = main_ida.text_field_ooil5pe9) ), ls_jl_gb as ( -- 历史进料GB select t0.item_no, '历史进料GB' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, t1.acpt_qty*gb.qty as qty FROM dm_sever_label_nct t0 left join gbs gb on t0.item_no = gb.item_no left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' where gb.item_no is not null and t1.item_no is not null and t2.id is not null ), ls_jl_sl as ( -- 历史进料数量 select t0.item_no, '历史进料数量(条)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, t1.acpt_qty as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' ), ls_jl_dj_cny as ( -- 历史进料单价 select t0.item_no, '历史进料单价(CNY/条)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, t1.old_std_price as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' ), ls_jl_dj_usd as ( -- 历史进料单价 select t0.item_no, '历史进料单价(USD/条)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, t1.old_orig_price as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' ), ls_jl_je as ( -- 历史进料金额 select t0.item_no, '历史进料金额(CNY)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, t1.acpt_std_amount as qty FROM dm_sever_label_nct t0 left join ods_srm_pu_acceptance_item t1 ON t1.item_no = t0.item_no and t1.has_red = 0 left JOIN ods_srm_pu_acceptance t2 ON t1.acpt_id = t2.id AND t2.dr = 0 AND t2.latest = 1 and t2.source_type='InStorage' and t2.std_currency = 'CNY' and t2.orig_currency = 'USD' ), ls_jl_dj_cnygb as ( -- 历史进料单价 select t0.item_no, '历史进料单价(CNY/GB)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, t0.qty/t1.qty as qty FROM ls_jl_dj_cny t0 left join gbs t1 ON t1.item_no = t0.item_no and t1.cal_date = t0.cal_date where t1.item_no is not null ), ls_jl_dj_usdgb as ( -- 历史进料单价 select t0.item_no, '历史进料单价(USD/GB)' as data_type, t2.year as cal_year, CONCAT(CAST(t2.year AS STRING),LPAD (CAST(t2.month AS STRING), 2, '0')) as cal_date, t0.qty/t1.qty as qty FROM ls_jl_dj_usd t0 left join gbs t1 ON t1.item_no = t0.item_no and t1.cal_date = t0.cal_date where t1.item_no is not null ), ls_ck_dj_cny as ( select t0.item_no, '历史出库单价(CNY/条)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, t1.item_cost as qty FROM dm_sever_label_nct t0 left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' ), ls_ck_dj_usd as ( select t0.item_no, '历史出库单价(USD/条)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, t1.item_cost/hl.qty as qty FROM dm_sever_label_nct t0 left join huilv_data hl on t0.item_no = hl.item_no left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' and SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) = hl.cal_date where t0.item_no is not null and t1.item_cost is not null ), ls_ck_je as ( select t0.item_no, '历史出库金额(CNY)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, sum(t1.outstock_money) as qty FROM dm_sever_label_nct t0 left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' where t1.inventory_item_code is not null group by t0.item_no, t1.p_date ), ls_ck_dj_cnygb as ( select t0.item_no, '历史出库单价GB(CNY/GB)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, t0.qty/t1.qty as qty FROM ls_ck_dj_cny t0 left join gbs t1 on t0.item_no = t1.item_no and t0.cal_date = t1.cal_date where t1.item_no is not null ), ls_ck_dj_usdgb as ( select t0.item_no, '历史出库单价GB(USD/GB)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, t0.qty/t1.qty as qty FROM ls_ck_dj_usd t0 left join gbs t1 on t0.item_no = t1.item_no and t0.cal_date = t1.cal_date where t1.item_no is not null ), ls_ck_sl as ( select t0.item_no, '历史出库数量(条)' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, sum(t1.outstock_qty) as qty FROM dm_sever_label_nct t0 left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' where t1.inventory_item_code is not null group by t0.item_no, t1.p_date ), ls_ck_gb as ( select t0.item_no, '历史出库GB' as data_type, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 4) as cal_year, SUBSTR(REPLACE(t1.p_date, '-', ''), 1, 6) as cal_date, sum(t1.outstock_qty*gb.qty) as qty FROM dm_sever_label_nct t0 left join gbs gb on t0.item_no = gb.item_no left join edw_sup_mfg_material_transactions t1 on t1.inventory_item_code = t0.item_no and t1.transaction_source_type_name = '任务或计划' and t1.subinventory_code not like '%NX%' where t1.inventory_item_code is not null group by t0.item_no, t1.p_date ), main_data as ( select * from huilv_data --汇率 UNION select * from ls_jl_sl --历史进料条 UNION select * from ls_jl_gb --历史进料GB UNION select * from ls_jl_je --历史进料金额 UNION select * from ls_jl_dj_cnygb --历史进料单价元/GB UNION select * from ls_jl_dj_cny --历史进料单价元/条 UNION select * from ls_jl_dj_usdgb --历史进料单价USD/GB UNION select * from ls_jl_dj_usd --历史进料单价USD/条 UNION select * from ls_ck_sl --历史出库条 UNION select * from ls_ck_gb --历史出库gb UNION select * from ls_ck_je --历史出库金额 UNION select * from ls_ck_dj_cny --历史出库单价元/条 UNION select * from ls_ck_dj_cnygb --历史出库单价元/GB UNION select * from ls_ck_dj_usdgb --历史出库单价usd/GB UNION select * from ls_ck_dj_usd --历史出库单价USD/条 ) INSERT OVERWRITE TABLE dm_server_price_stock_202512 select nct.item_no, '' as item_name, nct.class1, nct.class2, nct.item_bm as bm, '' as jixing, nct.kehu, nct.is_china, nct.is_zte, nct.item_pp, main.data_type, main.cal_year, main.cal_date, main.qty, 0 as num_other1, 0 as num_other2, 0 as num_other3, 0 as num_other4, '' as num_other1, '' as num_other2, '' as num_other3, '' as num_other4, '' as edw_create_date, '' as edw_last_update, '' as edw_data_source, '' as valid_flag from dm_sever_label_nct nct left join qt0 main on main.item_no = nct.item_no
最新发布