SQL语句如下:SELECT TOP 100 PERCENT l.nbrLoan AS L_NBRLOAN,
i.nameShort AS I_NAMESHORT,
zc.nameCountry AS C_CDCOUNTRY,
CONVERT(char(10), l.dtPtd, 101) AS L_DTPTD,
CONVERT(char(10), l.dtNote, 101) AS L_DTNOTE,
l.cntTimesLate,
c.nameLast + ', ' + c.nameFirst AS C_NAME,
zlt.txtLoanType AS L_CDLOANTYPE,
l2c.cdPrimaryCIF
FROM dbo.LQS_Investor i
INNER JOIN dbo.LQS_Investor2Portfolio i2p on i.nbrInv = i2p.nbrInv
INNER JOIN dbo.LQS_Loan l ON i2p.nbrPortfolio = l.nbrPortfolio
and i2p.cdPrimary = 'Y'
LEFT OUTER JOIN dbo.CIF c
INNER JOIN dbo.LQS_Loan2CIF l2c ON c.nbrCIF = l2c.nbrCIF ON
l.nbrLoan = l2c.nbrLoan
LEFT OUTER JOIN dbo.z_LoanType zlt ON l.cdLoanType = zlt.cdLoanType
LEFT OUTER JOIN dbo.z_Country zc ON c.cdCountry = zc.cdCountry
WHERE DATEADD(month, 1, l.dtPtd) + DATEADD(day, l.cntGraceDays, l.dtPtd) -
l.dtPtd < @L_DTCURRENT
AND DATEADD(month, 3, l.dtPtd) > @L_DTCURRENT
AND l2c.cdPrimaryCIF = 'Y'
AND l.cdActive = 'Y'
ORDER BY CONVERT(char(10), l.dtPtd, 101), l.nbrLoan
现要把这段SQL语句,转换为oracle语法的sql语句.一些简单的函数变换我知道,例如:CONVERT(char(10), l.dtNote, 101)可以转换为oracle的 to_char(l.dtNote,'mm/dd/yyyy'), DATEADD(month, 3, l.dtPtd) 可以转换为oracle的
add_month(l.dtPtd,3)等等.关键是where子句这段,我不会转换,还请高手帮忙,谢谢!
另外,几个datetime类型的数据是不可以直接用 + 号进行计算的,但是单独执行(MS-SQlServer下)
Select DATEADD(month, 1, l.dtPtd) + DATEADD(day, l.cntGraceDays, l.dtPtd) -
l.dtPtd < '2007-01-04' 却是有结果的, 这是为什么?
i.nameShort AS I_NAMESHORT,
zc.nameCountry AS C_CDCOUNTRY,
CONVERT(char(10), l.dtPtd, 101) AS L_DTPTD,
CONVERT(char(10), l.dtNote, 101) AS L_DTNOTE,
l.cntTimesLate,
c.nameLast + ', ' + c.nameFirst AS C_NAME,
zlt.txtLoanType AS L_CDLOANTYPE,
l2c.cdPrimaryCIF
FROM dbo.LQS_Investor i
INNER JOIN dbo.LQS_Investor2Portfolio i2p on i.nbrInv = i2p.nbrInv
INNER JOIN dbo.LQS_Loan l ON i2p.nbrPortfolio = l.nbrPortfolio
and i2p.cdPrimary = 'Y'
LEFT OUTER JOIN dbo.CIF c
INNER JOIN dbo.LQS_Loan2CIF l2c ON c.nbrCIF = l2c.nbrCIF ON
l.nbrLoan = l2c.nbrLoan
LEFT OUTER JOIN dbo.z_LoanType zlt ON l.cdLoanType = zlt.cdLoanType
LEFT OUTER JOIN dbo.z_Country zc ON c.cdCountry = zc.cdCountry
WHERE DATEADD(month, 1, l.dtPtd) + DATEADD(day, l.cntGraceDays, l.dtPtd) -
l.dtPtd < @L_DTCURRENT
AND DATEADD(month, 3, l.dtPtd) > @L_DTCURRENT
AND l2c.cdPrimaryCIF = 'Y'
AND l.cdActive = 'Y'
ORDER BY CONVERT(char(10), l.dtPtd, 101), l.nbrLoan
现要把这段SQL语句,转换为oracle语法的sql语句.一些简单的函数变换我知道,例如:CONVERT(char(10), l.dtNote, 101)可以转换为oracle的 to_char(l.dtNote,'mm/dd/yyyy'), DATEADD(month, 3, l.dtPtd) 可以转换为oracle的
add_month(l.dtPtd,3)等等.关键是where子句这段,我不会转换,还请高手帮忙,谢谢!
另外,几个datetime类型的数据是不可以直接用 + 号进行计算的,但是单独执行(MS-SQlServer下)
Select DATEADD(month, 1, l.dtPtd) + DATEADD(day, l.cntGraceDays, l.dtPtd) -
l.dtPtd < '2007-01-04' 却是有结果的, 这是为什么?
SELECT TOP 100 PERCENT l.nbrLoan AS L_NBRLOAN,
i.nameShort AS I_NAMESHORT,
zc.nameCountry AS C_CDCOUNTRY,
CONVERT(char(10), l.dtPtd, 101) AS L_DTPTD,
CONVERT(char(10), l.dtNote, 101) AS L_DTNOTE,
l.cntTimesLate,
c.nameLast + ', ' + c.nameFirst AS C_NAME,
zlt.txtLoanType AS L_CDLOANTYPE,
l2c.cdPrimaryCIF
FROM dbo.LQS_Investor i
INNER JOIN dbo.LQS_Investor2Portfolio i2p on i.nbrInv = i2p.nbrInv
INNER JOIN dbo.LQS_Loan l ON i2p.nbrPortfolio = l.nbrPortfolio
and i2p.cdPrimary = 'Y'
LEFT OUTER JOIN dbo.CIF c
INNER JOIN dbo.LQS_Loan2CIF l2c ON c.nbrCIF = l2c.nbrCIF ON
l.nbrLoan = l2c.nbrLoan
LEFT OUTER JOIN dbo.z_LoanType zlt ON l.cdLoanType = zlt.cdLoanType
LEFT OUTER JOIN dbo.z_Country zc ON c.cdCountry = zc.cdCountry
WHERE DATEADD(month, 1, l.dtPtd) + DATEADD(day, l.cntGraceDays, l.dtPtd) -
l.dtPtd < @L_DTCURRENT
AND DATEADD(month, 3, l.dtPtd) > @L_DTCURRENT
AND l2c.cdPrimaryCIF = 'Y'
AND l.cdActive = 'Y'
ORDER BY CONVERT(char(10), l.dtPtd, 101), l.nbrLoan
TOP 100 去掉,最后加roenum <= 100其他转化你也会,还有就是 @L_DTCURRENT 是什么啊?