在kettle中执行insert into select unqie表 提示未指定的列名Column 'dw_createtime' in field list is ambiguous

Viewed 9
job-scheduler 2.1 storage

DorisV2.1.3&DorisV2.1.6 都有此问题
临时解决方案：
1、将unqie模型先用with as 封装成临时表，sql关联临时表，能正常执行
2、将ods unqie模型更换为agg模型，sql能正常执行

根本原因待查？

5186d6fd67c1a921b2e10eb9a306bd77.png
dc0089528842b516976d7d2802361edc.png

建表语句：
CREATE TABLE ods_iho_ipd_vaj2_rt (
created_time DATETIME(6) NOT NULL COMMENT '创建时间',
vaj01 BIGINT NOT NULL COMMENT 'ID',
dw_createtime DATETIME(6) NOT NULL COMMENT '数仓创建时间',
acf01 BIGINT NULL COMMENT '医疗服务, 不能取0,3值, 关联字段:ACF1.ACF01',
baq24 BIGINT NULL COMMENT '是否gcp费用，2-是，其他为否',
bbx01 BIGINT NULL COMMENT '诊疗项目ID',
bbx06 TEXT NULL COMMENT '检验医嘱样本类型',
bby01 BIGINT NULL COMMENT '收费项目ID, 关联字段:BBY1.BBY01',
bby01a BIGINT NULL COMMENT '组套项目ID',
bce01b BIGINT NULL COMMENT '开单人ID',
bce01c BIGINT NULL COMMENT '执行人ID',
bce01e BIGINT NULL COMMENT '住院医师id, 关联字段:BCE1.BCE01',
bce02b TEXT NULL COMMENT '开单人号, 关联字段:BCE1.BCE02',
bce02c TEXT NULL COMMENT '执行者号，关联字段:BCE1.BCE02',
bce02d TEXT NULL COMMENT '操作员#, 关联字段:BCE1.BCE02',
bce03a TEXT NULL COMMENT '划价人, 关联字段:BCE1.BCE03',
bce03b TEXT NULL COMMENT '开单人, Physician, 一般为医师, 关联字段:BCE1.BCE03',
bce03c TEXT NULL COMMENT '执行者, 关联字段:BCE1.BCE03',
bce03d TEXT NULL COMMENT '操作员, 关联字段:BCE1.BCE03',
bce03e TEXT NULL COMMENT '住院医师, 关联字段:BCE1.BCE03',
bcj02 TEXT NULL COMMENT '发药窗口, 关联字段:BCJ1.BCJ02',
bck01a BIGINT NULL COMMENT '病区ID, 关联字段:BCK1.BCK01',
bck01b BIGINT NULL COMMENT '科室ID, 关联字段:BCK1.BCK01',
bck01c BIGINT NULL COMMENT '开单科室ID, OrderDeptID, 关联字段:BCK1.BCK01',
bck01d BIGINT NULL COMMENT '执行科室ID, 关联字段:BCK1.BCK01',
bck01e BIGINT NULL COMMENT '给药科室ID, 关联字段:BCK1.BCK01',
bck01f BIGINT NULL COMMENT '病人床位对应病区, 关联字段:BCK1.BCK01',
bck01g BIGINT NULL COMMENT '开单人所属行政科室',
bcq04 TEXT NULL COMMENT '病人床号, 关联字段:BCQ1.BCQ04',
bdn01 TEXT NULL COMMENT '类型, 编码, 关联字段:BDN1.BDN01',
beo01 BIGINT NULL COMMENT '自动记账项目关联id',
brn01 BIGINT NULL COMMENT '日间手术项目id',
cbm01 BIGINT NULL COMMENT '医嘱单或手术记录id, 关联字段:CBM1.CBM01',
created_by BIGINT NULL COMMENT '创建人标识',
delete_flag BOOLEAN NULL COMMENT '删除标志，默认否',
dsk01 BIGINT NULL COMMENT '药品批次id DSK_ID，关联字段:DSK1.DSK01',
extend_field_vaj2 TEXT NULL COMMENT '拓展字段',
fab03 TEXT NULL COMMENT '销售单位, 药品门诊或住院单位, 原先为发票号, 关联字段:FAB1.FAB03',
iho_db_status BIGINT NULL COMMENT '数据标识状态 1 在线表 2 离线表 默认为1',
original_id BIGINT NULL COMMENT '原始id',
outvaj01 TEXT NULL COMMENT '三方唯一id',
rownr BIGINT NULL COMMENT '次序',
serialid BIGINT NULL,
source TEXT NULL COMMENT '来源标识 0-门诊医生站/住院医生，1-互联网接诊',
third_vaj01 TEXT NULL COMMENT '第三方明细id',
updated_by BIGINT NULL COMMENT '更新人标识',
updated_time DATETIME(6) NULL COMMENT '更新时间',
vaa01 BIGINT NULL COMMENT '病人ID, 关联字段:VAA1.VAA01',
vaa01a BIGINT NULL COMMENT '新生儿的vaa01 (母婴同床合并计费且是新生儿产生的费用时有值)',
vaa07 BIGINT NULL COMMENT '就诊ID, 主页ID, 关联字段:VAE1.VAE01',
vaa07a BIGINT NULL COMMENT '新生儿的vaa07 (母婴同床合并计费且是新生儿产生的费用时有值)',
vac80 BIGINT NULL COMMENT '付费模式:0:先付费后诊疗1:先诊疗后付费',
vaf01 BIGINT NULL COMMENT '医嘱ID, OrderID, 关联字段:VAF1.VAF01',
vaf01a BIGINT NULL COMMENT '关联父医嘱ID',
vaf42 DATETIME(6) NULL COMMENT '开嘱时间',
vaf59 BIGINT NULL COMMENT '医嘱单组号, 关联字段:CBM1.CBM01',
vai01 BIGINT NULL COMMENT '单据ID, 记帐单ID, 关联字段:VAI1.VAI01',
vai58 BIGINT NULL COMMENT '单据类型 1-医嘱单 2-划价/记账单 3-体检单',
vai59 BIGINT NULL COMMENT '单据退费类型 1-部分退费后重新生成的单据 2-取消结账后重新生成的单据',
vai60 BIGINT NULL COMMENT '是否为自动记账标识：0，否；1，是',
vai72 BIGINT NULL COMMENT '费用转移类型，1-住院费用转门诊,2-门诊费用转住院',
vaj01a BIGINT NULL COMMENT 'vaj01a 发送医嘱关联父医嘱vaf01, 等同vaj2.vaf01a

  • 可废弃原因:等同vaj2.vaf01a',
    vaj01b BIGINT NULL COMMENT '销账时，冲销vaj1(vaj05=4)关联被销账的vaj1(vaj05=3)
    TODO -问题：等同vaj2.vaj09，但是代码有意图设置最原始的被销账vaj1(vaj05=3)',
    vaj01c BIGINT NULL COMMENT '住院取消结账的原始费用vaj01',
    vaj03 TEXT NULL COMMENT '活动ID,活动名称,套餐ID',
    vaj04 BIGINT NULL COMMENT '类型, 1=门诊挂号, 2=门诊划价, 3=门诊记帐, 4=门诊收费, 门诊结帐, 5=医技记帐, 6=住院记帐',
    vaj05 BIGINT NULL COMMENT '记录状态:1:收费划价/记账划价；2：已收费/已记账；3:已退费/已销账 ; 4:退费记录 是根据类型变化 ; 9:作废；10：暂存（vajt专用）',
    vaj09 BIGINT NULL COMMENT '销账时，冲销vaj1(vaj05=4)关联被销账的vaj1(vaj05=3)',
    vaj10 BIGINT NULL COMMENT '销账时，冲销vaj1(vaj05=4)和非冲销vaj1（vaj05=5|1）关联被销账的vaj1(vaj05=3)',
    vaj100 BOOLEAN NULL COMMENT '是否为日间手术收费项目',
    vaj106 TEXT NULL COMMENT '第三方处方/医技明细编码',
    vaj107 TEXT NULL COMMENT '耗材条码',
    vaj108 TEXT NULL COMMENT '手术单id',
    vaj15 BIGINT NULL COMMENT '记帐标志',
    vaj21 BIGINT NULL COMMENT '销账时，冲销vaj1(vaj05=4)和非冲销vaj1（vaj05=5|1）关联最原始被销账的vaj1(vaj05=3)',
    vaj22 BIGINT NULL COMMENT '特殊标志, 附加标志, 不同位置用途不同, 挂号时：存储项目特性(1=挂号, 6=诊金, 7=病历本, 8=就诊卡)',
    vaj23 BIGINT NULL COMMENT '剂数, 中药剂数',
    vaj24 DECIMAL(18, 4) NULL COMMENT '单量',
    vaj25 DECIMAL(18, 4) NULL COMMENT '数量, 数次, 总数量',
    vaj26 BIGINT NULL COMMENT '急诊标志, emergency tag',
    vaj27 BIGINT NULL COMMENT '婴儿费,对应VAP1表中VAP01',
    vaj28 DECIMAL(9, 4) NULL COMMENT '税率, 暂时不用',
    vaj29 DECIMAL(18, 4) NULL COMMENT '税费, 暂时不用',
    vaj30 DECIMAL(18, 4) NULL COMMENT '折扣率分子, discount rate numerator',
    vaj31 DECIMAL(18, 4) NULL COMMENT '折扣率分母, discount rate denominator',
    vaj32 DECIMAL(18, 6) NULL COMMENT '全价',
    vaj33 DECIMAL(18, 6) NULL COMMENT '单价, 标准单价',
    vaj34 DECIMAL(18, 4) NULL COMMENT '包装',
    vaj35 TEXT NULL COMMENT '单位, 计算单位',
    vaj36 DECIMAL(18, 4) NULL COMMENT '全额,原始价格计算得金额',
    vaj37 DECIMAL(18, 4) NULL COMMENT '应收金额, 未临时打折前的金额(可能经过费别打折)',
    vaj38 DECIMAL(18, 4) NULL COMMENT '结帐金额(结账时应付金额)，发票打印以此金额为准',
    vaj39 BIGINT NULL COMMENT '费用标志, 0=正常, 1=自费, 2=免费',
    vaj40 DECIMAL(18, 4) NULL COMMENT '自负金额',
    vaj41 DECIMAL(18, 4) NULL COMMENT '保险金额, 统筹金额',
    vaj46 DATETIME(6) NULL COMMENT '记帐时间, 手工时间',
    vaj47 DATETIME(6) NULL COMMENT '交易时间, 机器时间',
    vaj48 BIGINT NULL COMMENT '自定义互斥规则fak01',
    vaj51 DATETIME(6) NULL COMMENT '执行时间',
    vaj52 DATETIME(6) NULL COMMENT '执行交易时间',
    vaj53 BIGINT NULL COMMENT '执行情况：0:未执行; 1:执行完成; 2:拒绝执行; 3:正在执行;4:过期挂起',
    vaj54 TEXT NULL COMMENT '备注',
    vaj57 TEXT NULL COMMENT '摘要; 收费项目为主从项目时 摘要=主项目名称',
    vaj59 DECIMAL(18, 6) NULL COMMENT '成本价',
    vaj61 DECIMAL(18, 4) NULL COMMENT '核算金额，财务核算时用到',
    vaj62 DATETIME(6) NULL COMMENT '业务时间、默认记账时间，销账时取被销账那条明细的记账时间',
    vaj64 DATETIME(6) NULL COMMENT '发生时间、用于住院长嘱发送时记跨天的费用',
    vaj65 BIGINT NULL COMMENT '住院中途结帐时，为1参与本次结帐，否则不参与',
    vaj67 DECIMAL(18, 6) NULL COMMENT '原价',
    vaj72 BIGINT NULL COMMENT '首次末次标示 0=正常 1=首次 2=末次',
    vaj76 DECIMAL(18, 4) NULL COMMENT '数量(库房)',
    vaj77 TEXT NULL COMMENT '单位 计算单位(库房)',
    vaj78 DECIMAL(18, 4) NULL COMMENT '门诊包装系数(库房)',
    vaj79 DECIMAL(18, 6) NULL COMMENT '全价(库房)',
    vaj80 DECIMAL(18, 6) NULL COMMENT '单价(库房)',
    vaj87 BIGINT NULL COMMENT '系统来源:0,收费系统;1, 住院医生站;2, 住院护士站;3, 入院管理;4, 门诊医生站;5, 药房;6, 物资材料管理;7, 治疗工作站;8, 医技工作站;9,手麻系统;10,血库系统内',
    vaj90 TEXT NULL COMMENT '存储第三方内容字段，按需使用',
    vaj91 BIGINT NULL COMMENT '是否存在附加费，0否，1是',
    vajt01 BIGINT NULL COMMENT '冗余vajt表的vaj01',
    vak01 BIGINT NULL COMMENT '结帐ID, 关联字段:VAK1.VAK01',
    vak01b BIGINT NULL COMMENT '部分退费或取消结账时，关联原有的结账记录id',
    vak01c BIGINT NULL COMMENT '日间手术结账期间实际收费项目结账关联vak1.vak01',
    vbd01 BIGINT NULL COMMENT '医嘱计价唯一ID',
    vbi01 BIGINT NULL COMMENT '医嘱发送唯一id',
    vcm01 BIGINT NULL COMMENT '医嘱执行工作记录唯一id',
    veh55 BIGINT NULL COMMENT '借药单id，对应dtc1.id',
    version BIGINT NULL COMMENT '版本',
    vfa32 TEXT NULL COMMENT '高值耗材透传字段',
    ziw01 BIGINT NULL COMMENT '发药发料列表ID',
    dw_updatetime DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数仓更新时间',
    dw_delete_flag TINYINT NULL DEFAULT "0" COMMENT '记录业务数据库物理删除标识，0：未删除，1：已删除，默认为0',
    dw_org_id TEXT NULL COMMENT '数仓机构id'
    ) ENGINE=OLAP
    UNIQUE KEY(created_time, vaj01, dw_createtime)
    COMMENT '住院病人费用明细'
    AUTO PARTITION BY RANGE (date_trunc(<slot 91>, 'month'))
    (PARTITION p20240401000000 VALUES [('2024-04-01 00:00:00'), ('2024-05-01 00:00:00')),
    PARTITION p20240501000000 VALUES [('2024-05-01 00:00:00'), ('2024-06-01 00:00:00')),
    PARTITION p20240601000000 VALUES [('2024-06-01 00:00:00'), ('2024-07-01 00:00:00')),
    PARTITION p20240701000000 VALUES [('2024-07-01 00:00:00'), ('2024-08-01 00:00:00')),
    PARTITION p20240801000000 VALUES [('2024-08-01 00:00:00'), ('2024-09-01 00:00:00')),
    PARTITION p20240901000000 VALUES [('2024-09-01 00:00:00'), ('2024-10-01 00:00:00')),
    PARTITION p20241001000000 VALUES [('2024-10-01 00:00:00'), ('2024-11-01 00:00:00')),
    PARTITION p20241101000000 VALUES [('2024-11-01 00:00:00'), ('2024-12-01 00:00:00')),
    PARTITION p20241201000000 VALUES [('2024-12-01 00:00:00'), ('2025-01-01 00:00:00')),
    PARTITION p20250101000000 VALUES [('2025-01-01 00:00:00'), ('2025-02-01 00:00:00')),
    PARTITION p20250201000000 VALUES [('2025-02-01 00:00:00'), ('2025-03-01 00:00:00')),
    PARTITION p20250301000000 VALUES [('2025-03-01 00:00:00'), ('2025-04-01 00:00:00')),
    PARTITION p20250401000000 VALUES [('2025-04-01 00:00:00'), ('2025-05-01 00:00:00')),
    PARTITION p20250501000000 VALUES [('2025-05-01 00:00:00'), ('2025-06-01 00:00:00')),
    PARTITION p20250601000000 VALUES [('2025-06-01 00:00:00'), ('2025-07-01 00:00:00')),
    PARTITION p20250701000000 VALUES [('2025-07-01 00:00:00'), ('2025-08-01 00:00:00')),
    PARTITION p20250801000000 VALUES [('2025-08-01 00:00:00'), ('2025-09-01 00:00:00')),
    PARTITION p20250901000000 VALUES [('2025-09-01 00:00:00'), ('2025-10-01 00:00:00')),
    PARTITION p20251001000000 VALUES [('2025-10-01 00:00:00'), ('2025-11-01 00:00:00')),
    PARTITION p20251101000000 VALUES [('2025-11-01 00:00:00'), ('2025-12-01 00:00:00')))
    DISTRIBUTED BY HASH(created_time, vaj01, dw_createtime) BUCKETS AUTO
    PROPERTIES (
    "replication_allocation" = "tag.location.default: 1",
    "min_load_replica_num" = "-1",
    "bloom_filter_columns" = "vaf01, vaa07, vaa01, vaj01, vak01",
    "is_being_synced" = "false",
    "storage_medium" = "hdd",
    "storage_format" = "V2",
    "inverted_index_storage_format" = "V1",
    "enable_unique_key_merge_on_write" = "true",
    "light_schema_change" = "true",
    "disable_auto_compaction" = "false",
    "enable_single_replica_compaction" = "false",
    "group_commit_interval_ms" = "10000",
    "group_commit_data_bytes" = "134217728"

kettle的SQL：
insert into bidata.DWS_HOSP_CLINS_ADVICE_INPATIENT_ADVICE_DETAIL_DI(
MEDICAL_TECH_ID -- 医技唯一ID
,ORG_NAME -- 院区名称
,ORG_CODE -- 院区编码
,DATA_ETL_TIME -- 数据ETL时间
,ENCOUNTER_ID --就诊流水号
,CREATE_TIME -- 明细时间
,DEPARTMENT_NAME -- 科室名称
,DEPARTMENT --科室
,SPECIFIC_MEDICAL_TECHNOLOGY_NO -- 医技编码
,DELETE_FLAG --删除标识
)
--医技人次表（明细） （DWS_HOSP_CLINS_ADVICE_INPATIENT_ADVICE_DETAIL_DI）
select
vaj.vaj01 as MEDICAL_TECH_ID
,'红原县人民医院' as ORG_NAME
,'h74943' as ORG_CODE
,now() DATA_ETL_TIME
,vaj.vaa07 as ENCOUNTER_ID
,date_format(vak13,'%Y-%m-%d %H:%i:00') as CREATE_TIME
,bck.bck02 DEPARTMENT_NAME
,bck.bck03 DEPARTMENT
,bax.bax01 SPECIFIC_MEDICAL_TECHNOLOGY_NO
,CASE WHEN vak.delete_flag=false or vak.dw_delete_flag=false then '0' else '1' end as delete_flag
from ods_pro_1767831969868636161.ods_iho_opd_vak1_rt vak
join ods_pro_1767831969868636161.ods_iho_opd_vaj1_rt_zmy vaj on vaj.vak01=vak.vak01 and vaj.delete_flag=false and vaj.dw_delete_flag=false
left join ods_pro_1767831969868636161.ods_iho_opd_bby1_rt bby on bby.bby01=vaj.bby01 and bby.delete_flag=false and bby.dw_delete_flag=false
left join ods_pro_logical_u00.ods_iho_mds_bax1_rt bax on bax.bax01=bby.bax01 and bax.delete_flag=false and bax.dw_org_id = vak.dw_org_id
left join ods_pro_logical_u00.ods_iho_mds_bck1_rt bck on vaj.bck01d=bck.bck01 and bck.delete_flag=false and bck.dw_org_id = vak.dw_org_id
where bby.bdn01 in ('E','L' )
and to_date(vak.vak13)>=to_date(current_date())
and to_date(vak.vak13)<=to_date(current_date())
and vaj.vaj05<='4'
and vaj.acf01='1'
and vak.vak06<=2

union all

select
vaj.vaj01 as MEDICAL_TECH_ID
,'红原县人民医院' as ORG_NAME
,'h74943' as ORG_CODE
,now() DATA_ETL_TIME
,vaj.vaa07 as ENCOUNTER_ID
,date_format(vak13,'%Y-%m-%d %H:%i:00') as CREATE_TIME
,bck.bck02 DEPARTMENT_NAME
,bck.bck03 DEPARTMENT
,bax.bax01 SPECIFIC_MEDICAL_TECHNOLOGY_NO
,CASE WHEN vak.delete_flag=false or vak.dw_delete_flag=false then '0' else '1' end as delete_flag
from ods_pro_1767831969868636161.ods_iho_ipd_vak1_rt vak
join ods_pro_1767831969868636161.ods_iho_ipd_vaj2_rt_zmy vaj on vaj.vak01=vak.vak01 and vaj.delete_flag=false and vaj.dw_delete_flag=false
left join ods_pro_1767831969868636161.ods_iho_ipd_bby1_rt bby on bby.bby01=vaj.bby01 and bby.delete_flag=false and bby.dw_delete_flag=false
left join ods_pro_logical_u00.ods_iho_mds_bax1_rt bax on bax.bax01=bby.bax01 and bax.delete_flag=false and bax.dw_org_id = vak.dw_org_id
left join ods_pro_logical_u00.ods_iho_mds_bck1_rt bck on vaj.bck01d=bck.bck01 and bck.delete_flag=false and bck.dw_org_id = vak.dw_org_id
where bby.bdn01 in ('E','L' )
and to_date(vak.vak13)>=to_date(current_date())
and to_date(vak.vak13)<=to_date(current_date())
and vaj.vaj05<='4'
and vaj.acf01='2'
and vak.vak06>2

0 Answers
Related Questions
在kettle中执行insert into select unqie表 提示未指定的列名Column 'dw_createtime' in field list is ambiguous