碰到一个问题,有一张表
create table aaa(
id number(12),
st_cd number(2),
start_dt date);向这张表插入数据:
insert into aaa values(1,1,sysdate);可以插入一条数据。然后这样插入insert into aaa select id,stcd,start_dt from bbb插aaa表后,字段start_dt为空值,
但是查询select id,stcd,start_dt from bbb时,
start_dt不是空的。请问这个问题怎么解决?
create table aaa(
id number(12),
st_cd number(2),
start_dt date);向这张表插入数据:
insert into aaa values(1,1,sysdate);可以插入一条数据。然后这样插入insert into aaa select id,stcd,start_dt from bbb插aaa表后,字段start_dt为空值,
但是查询select id,stcd,start_dt from bbb时,
start_dt不是空的。请问这个问题怎么解决?
尤其是start_dt 字段,是完全一样的么?
另外,Insert之后提交没有?
insert into aaa(id,st_cd,start_dt)
select id,stcd,start_dt from bbb
看看aaa1中start_dt 是否为空?
SELECT PROD_ID,PROD_STATUS_CD, CASE WHEN PROD_STATUS_CD=1 THEN NULL ELSE START_DT END AS START_DT FROM(
SELECT A.PROD_ID,A.PROD_STATUS_CD, START_DT,ROW_NUMBER() OVER (PARTITION BY PROD_ID
ORDER BY STATUS_SEQ ASC,PROD_STATUS_CD DESC NULLS LAST, START_DT DESC NULLS LAST, END_DT DESC NULLS LAST)
AS ROW_NUM FROM (SELECT PROD_ID,PROD_STATUS_CD, START_DT,END_DT,
CASE
WHEN STATUS_CD = 12 THEN 1
ELSE 2 END AS STATUS_SEQ
FROM INST.OFFER_PROD_STATUS@TO_BSSNJ
WHERE PROD_STATUS_CD IN (1,3,9,10) AND SYSDATE BETWEEN START_DT AND END_DT AND STATUS_CD IN (12,13,20,21)
AND PROD_ID=250000107717
)A
)B
WHERE ROW_NUM=1;改成下面的语句:
INSERT INTO ODS.TEMP_UPDATE_ODS_PD_INST_1
SELECT PROD_ID,PROD_STATUS_CD, START_DT FROM(
SELECT A.PROD_ID,A.PROD_STATUS_CD, START_DT,ROW_NUMBER() OVER (PARTITION BY PROD_ID
ORDER BY STATUS_SEQ ASC,PROD_STATUS_CD DESC NULLS LAST, START_DT DESC NULLS LAST, END_DT DESC NULLS LAST)
AS ROW_NUM FROM (SELECT PROD_ID,PROD_STATUS_CD,CASE WHEN PROD_STATUS_CD=1 THEN NULL ELSE START_DT END AS START_DT,END_DT,
CASE
WHEN STATUS_CD = 12 THEN 1
ELSE 2 END AS STATUS_SEQ
FROM INST.OFFER_PROD_STATUS@TO_BSSNJ
WHERE PROD_STATUS_CD IN (1,3,9,10) AND SYSDATE BETWEEN START_DT AND END_DT AND STATUS_CD IN (12,13,20,21)
AND PROD_ID=250000107717
)A
)B
WHERE ROW_NUM=1
为什么上面的插入为空呢,还是不明白