插入表格时的小问题？在线等待！

Question

INSERT INTO SO_Forecast (CCN,MAS_LOC,ITEM,Forecast )
 SELECT FORECAST.CCN, 
        FORECAST.MAS_LOC,FORECAST.ITEM,
        to_date(to_char(FORECAST.FORECAST_DATE,'YYYY') || '/' 
        ||to_char(FORECAST.FORECAST_DATE,'MM') || '/' 
        || '01','YYYY/MM/DD') FORECAST.QTY 
 FROM FORECAST 
 WHERE FORECAST.FORECAST_DATE>=to_date('2003/03/27','YYYY/MM/DD')
------------你的01少了两个单引号-----还多一个逗号--------
也可以这么写
INSERT INTO SO_Forecast (CCN,MAS_LOC,ITEM,Forecast )
 SELECT FORECAST.CCN, 
        FORECAST.MAS_LOC,
        FORECAST.ITEM,
        to_date(to_char(FORECAST.FORECAST_DATE,'YYYY/MM')
        || '/01','YYYY/MM/DD') FORECAST.QTY 
 FROM FORECAST 
 WHERE FORECAST.FORECAST_DATE>=to_date('2003/03/27','YYYY/MM/DD')

Answer 1

不好意思 是COPY错了 应该是这样子也出错误
INSERT INTO SO_Forecast (CCN,MAS_LOC,ITEM,Year_Month,Forecast ) SELECT FORECAST.CCN, FORECAST.MAS_LOC,FORECAST.ITEM,to_date(to_char(FORECAST.FORECAST_DATE,'YYYY') || '/' || to_char(FORECAST.FORECAST_DATE,'MM') || '/' || 01,'YYYY/MM/DD'),FORECAST.QTY FROM FORECAST WHERE FORECAST.FORECAST_DATE>=to_date('2003/03/27','YYYY/MM/DD')

Answer 2

INSERT INTO SO_Forecast (CCN,MAS_LOC,ITEM,Forecast ) 
SELECT FORECAST.CCN, FORECAST.MAS_LOC,FORECAST.ITEM,
trunc(FORECAST.FORECAST_DATE,'MM'),
FORECAST.QTY FROM FORECAST WHERE FORECAST.FORECAST_DATE>=to_date('2003/03/27','YYYY/MM/DD')

Answer 3

INSERT INTO SO_Forecast (CCN,MAS_LOC,ITEM,Forecast ) 
SELECT FORECAST.CCN, FORECAST.MAS_LOC,FORECAST.ITEM,
trunc(FORECAST.FORECAST_DATE,'mm'),
FORECAST.QTY FROM FORECAST WHERE FORECAST.FORECAST_DATE>=to_date('2003/03/27','YYYY/MM/DD')

Answer 4

SELECT FORECAST.CCN, 
        FORECAST.MAS_LOC,
        FORECAST.ITEM,
        trunc(FORECAST.FORECAST_DATE,'MM') FORECAST.QTY

Answer 5

按你的操作，没问题呀?SQL> create table temp(a date);表已创建。SQL> insert into temp values( to_date(to_char(sysdate,'YYYY') ||
  2      '/' || to_char(sysdate,'MM') || '/' || 01,'YYYY/MM/DD') );已创建 1 行。SQL> select to_char(a,'yyyy-mm-dd') from temp;TO_CHAR(A,'YYYY-MM-DD')
------------------------------------------------
2003-03-01

Answer 6

这样子是可以插入 但我还想问一下INSERT 是否不支持 这样子用譬如说 INSERT INTO SO_Forecast (CCN,MAS_LOC,ITEM,Forecast ) 
SELECT FORECAST.CCN, FORECAST.MAS_LOC,FORECAST.ITEM,
trunc(FORECAST.FORECAST_DATE,'mm'),
SUM(FORECAST.QTY) FROM FORECAST 
加上了SUM可以吗 不加SUM插入就是成功的 可以加GROUP BY吗?

Answer 7

sum是一个聚集函数，配合group by使用，当然在查询语句后有group by没问题。例如:
SELECT FORECAST.CCN, FORECAST.MAS_LOC,max(FORECAST.ITEM),
max(trunc(FORECAST.FORECAST_DATE,'mm')),
SUM(FORECAST.QTY) FROM FORECAST group by CCN,MAS_LOC