时间戳

首页 > 解决方案 > 使用 SQL Oracle 中的 where 语句从列中复制奇怪的值

sql - 使用 SQL Oracle 中的 where 语句从列中复制奇怪的值

问题描述

您好，我有一条在列中有 SQL 的记录。我想复制这些值并将其更新为显示该列为空的类似记录。我不断收到缺少逗号的错误。

我正在尝试像 WHSE 2 一样为 WHSE 1 设置 HDR_LBL_SQL。有什么想法吗？谢谢

表：Label_Cnfg 字段：WHSE、TYPE、HDR_LBL_SQL

WHSE 类型 HDR_LBL_SQL
1 票 无效的
2 票 (*见下面的 SQL)

WHSE 1 的 HDR_LBL_SQL

Select
ch.case_nbr,
ch.case_nbr case_brcd,
ch.RCVD_SHPMT_NBR,
iwm.PUTWY_TYPE ,
ch.PO_NBR, 
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN to_char(ch.MFG_DATE,'MM-DD-YYYY') ELSE 'MIXED' END) MFG_DATE,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN to_char(ch.XPIRE_DATE,'MM-DD-YYYY') ELSE 'MIXED' END)XPIRE_DATE,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN to_char(ch.CONS_PRTY_DATE,'MM-DD-YYYY') ELSE 'MIXED' END) CONS_PRTY_DATE,   
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN trim(im.dsp_sku) ELSE 'MIXED' END) SKU,

(CASE WHEN CASECNT.SKUCOUNT = '1' THEN trim(im.dsp_sku) ELSE 'MIXED' END) SKU_BRCD,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN im.sku_desc ELSE 'MIXED' END) SKU_DESC,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN CD.INVN_TYPE ELSE 'MIXED' END) INVN_TYPE,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN CD.INVN_TYPE ELSE 'MIXED' END) BRCD_INVN_TYPE,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.PROD_STAT ELSE 'MIXED' END) PROD_STAT,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.PROD_STAT ELSE 'MIXED' END) BRCD_PROD_STAT,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.batch_nbr ELSE 'MIXED' END) batch_nbr,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.batch_nbr ELSE 'MIXED' END) BRCD_batch_nbr,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.CNTRY_OF_ORGN ELSE 'MIXED' END) CNTRY_OF_ORGN,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.CNTRY_OF_ORGN ELSE 'MIXED' END) BRCD_CNTRY_OF_ORGN,
SUM(cd.actl_qty),' ',' ',' ',' ',' '
from
case_hdr ch
inner join case_dtl cd on ch.case_nbr = cd.case_nbr
inner join item_master im on im.sku_id = cd.sku_id
inner join item_whse_master iwm on im.sku_id = iwm.sku_id 
inner join (select count(distinct(SKU_ID||INVN_TYPE||PROD_STAT||BATCH_NBR)) SKUCOUNT,case_nbr from case_dtl cd where case_nbr = :g_case_nbr group by case_nbr)CASECNT
on CASECNT.case_nbr = cd.case_nbr
where
ch.case_nbr = :g_case_nbr 
and cd.case_seq_nbr = :g_case_seq_nbr
and ch.stat_code in ( '10', '30', '90', '96' )
group by
ch.case_nbr,
ch.case_nbr ,
ch.RCVD_SHPMT_NBR,
ch.PO_NBR,
iwm.PUTWY_TYPE , 
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN to_char(ch.MFG_DATE,'MM-DD-YYYY') ELSE 'MIXED' END) ,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN to_char(ch.XPIRE_DATE,'MM-DD-YYYY') ELSE 'MIXED' END),
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN to_char(ch.CONS_PRTY_DATE,'MM-DD-YYYY') ELSE 'MIXED' END) ,   
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN trim(im.dsp_sku) ELSE 'MIXED' END) ,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN trim(im.dsp_sku) ELSE 'MIXED' END) ,
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN im.sku_desc ELSE 'MIXED' END),
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN CD.INVN_TYPE ELSE 'MIXED' END),
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.PROD_STAT ELSE 'MIXED' END),
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.batch_nbr ELSE 'MIXED' END),
(CASE WHEN CASECNT.SKUCOUNT = '1' THEN cd.CNTRY_OF_ORGN ELSE 'MIXED' END)

标签： sqloraclesetsql-update

解决方案

您可以执行以下操作：

update Label_Cnfg set 
HDR_LBL_SQL=(select HDR_LBL_SQL from Label_Cnfg where WHSE=2 and TYPE='TICKET')
where WHSE=1 and TYPE='TICKET';

推荐阅读