有段oracle代碼，想轉成mssql，熟練的大俠幫幫手
--Insert 資料
Insert Into W_DAY_D
Select TO_NUMBER(TO_CHAR(TAB.A2, 'yyyymmdd')) ROW_WID,
TO_NUMBER(SUBSTR(TO_CHAR(TAB.A2, 'yyyymmdd'), 1, 6)) MONTH_WID,
TO_NUMBER(EXTRACT(Year From TAB.A2)) || TO_NUMBER(TO_CHAR(TAB.A2, 'Q')) QUARTER_WID,
TO_NUMBER(EXTRACT(Year From TAB.A2)) YEAR_WID,
TAB.A2 CALENDAR_DATE,
(Select Case
When TO_CHAR(TAB.A2, 'mm') < 7 Then
1
Else
2
End
From DUAL) CAL_HALF,
TO_NUMBER(EXTRACT(Month From TAB.A2)) CAL_MONTH,
TO_NUMBER(TO_CHAR(TAB.A2, 'Q')) CAL_QTR,
TO_NUMBER(TO_CHAR(TAB.A2, 'WW')) CAL_WEEK,
TO_NUMBER(EXTRACT(Year From TAB.A2)) CAL_YEAR,
TAB.A2 - 1 DAY_AGO_DT,
TO_NUMBER(TO_CHAR((TAB.A2 - 1), 'yyyymmdd')) DAY_AGO_WID,
--to_char(TAB.A2) DAY_NAME,
TO_CHAR(TAB.A2, 'DY') DAY_NAME,
TO_NUMBER(TO_CHAR(TAB.A2, 'dd')) DAY_OF_MONTH,
TO_NUMBER(TO_CHAR(TAB.A2, 'D')) DAY_OF_WEEK,
TO_NUMBER(TO_CHAR(TAB.A2, 'ddd')) DAY_OF_YEAR,
ADD_MONTHS(TAB.A2, -1) MONTH_AGO_DT,
TO_NUMBER(TO_CHAR(ADD_MONTHS(TAB.A2, -1), 'yyyymmdd')) MONTH_AGO_WID,
TO_CHAR(EXTRACT(Month From TAB.A2)) MONTH_NAME,
ADD_MONTHS(TAB.A2, -3) QUARTER_AGO_DT,
TO_NUMBER(TO_CHAR(ADD_MONTHS(TAB.A2, -3), 'yyyymmdd')) QUARTER_AGO_WID,
TAB.A2 - 7 WEEK_AGO_DT,
TO_NUMBER(TO_CHAR(TAB.A2 - 7, 'yyyymmdd')) WEEK_AGO_WID,
ADD_MONTHS(TAB.A2, -12) YEAR_AGO_DT,
TO_NUMBER(TO_CHAR(ADD_MONTHS(TAB.A2, -12), 'yyyymmdd')) YEAR_AGO_WID,
(Select Case
When TO_CHAR(TAB.A2, 'mm') < 7 Then
'上半年'
Else
'下半年'
End
From DUAL) PER_NAME_HALF,
EXTRACT(Month From TAB.A2) || '月' PER_NAME_MONTH,
'第' || TO_CHAR(TAB.A2, 'Q') || '季度' PER_NAME_QTR,
--to_char(TAB.A2,'DY') PER_NAME_WEEK,
'第' || TO_CHAR(TAB.A2, 'WW') || '周' PER_NAME_WEEK,
EXTRACT(Year From TAB.A2) || '年' PER_NAME_YEAR,
Sysdate W_INSERT_DT,
TO_NUMBER(TO_CHAR(TAB.A2, 'yyyymmdd')) INTEGRATION_ID,
Sysdate W_UPDATE_DT
From (Select ROWNUM As A1,
TO_DATE('20071231', 'yyyy-mm-dd,hh24:mi:ss') + ROWNUM As A2
From DUAL
Connect By ROWNUM <= 8036) TAB;
uj5u.com熱心網友回復：
建議樓主把涉及到的表結構也一并貼上來；還有就是，你是哪里遇到問題了，可以具體到哪一句；
uj5u.com熱心網友回復：
你好，首要的問題是代碼TO_DATE('20071231', 'yyyy-mm-dd,hh24:mi:ss') + ROWNUM As A2中的A2在mssql里如何實作？oracle中有dual表，可以直接把rownum與其他字符相加，在mssql中嘗試用IDENTITY解決，但必須結合select 。。。into 。。。，結果導致其他問題，不知道如何能達到A2的結果，或者不用IDENTITY還有什么辦法？下面是表結構
CREATE TABLE [dbo].[W_DAY_D](
[ROW_WID] [decimal](10, 0) NOT NULL,
[MONTH_WID] [decimal](10, 0) NULL,
[QUARTER_WID] [decimal](10, 0) NULL,
[YEAR_WID] [decimal](10, 0) NULL,
[CALENDAR_DATE] [date] NULL,
[CAL_HALF] [decimal](2, 0) NULL,
[CAL_MONTH] [decimal](2, 0) NULL,
[CAL_QTR] [decimal](1, 0) NULL,
[CAL_WEEK] [decimal](2, 0) NULL,
[CAL_YEAR] [decimal](4, 0) NULL,
[DAY_AGO_DT] [date] NULL,
[DAY_AGO_WID] [decimal](10, 0) NULL,
[DAY_NAME] [varchar](30) NULL,
[DAY_OF_MONTH] [decimal](2, 0) NULL,
[DAY_OF_WEEK] [decimal](1, 0) NULL,
[DAY_OF_YEAR] [decimal](3, 0) NULL,
[MONTH_AGO_DT] [date] NULL,
[MONTH_AGO_WID] [decimal](10, 0) NULL,
[MONTH_NAME] [varchar](30) NULL,
[QUARTER_AGO_DT] [date] NULL,
[QUARTER_AGO_WID] [decimal](10, 0) NULL,
[WEEK_AGO_DT] [date] NULL,
[WEEK_AGO_WID] [decimal](10, 0) NULL,
[YEAR_AGO_DT] [date] NULL,
[YEAR_AGO_WID] [decimal](10, 0) NULL,
[PER_NAME_HALF] [varchar](50) NULL,
[PER_NAME_MONTH] [varchar](50) NULL,
[PER_NAME_QTR] [varchar](50) NULL,
[PER_NAME_WEEK] [varchar](50) NULL,
[PER_NAME_YEAR] [varchar](50) NULL,
[INSERT_DT] [datetime] NULL,
[INTEGRATION_ID] [varchar](30) NULL,
[UPDATE_DT] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ROW_WID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103461.html
標籤：基礎和管理
上一篇：64位server2012上能否裝oracle11gR1
下一篇：java.sql.SQLException: ORA-00600: internal error code, arguments: [kkpamRFGet0]