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' 却是有结果的, 这是为什么?
解决方案 »
- (100分)求一份好用的oracle 技术文档(注:不是错误文档)
- 关于ORACLE的性能问题
- 求建立表约束关系图的工具或方法
- pl/sql怎样得到当前的“年”和“月”?(date/varchar2哪个类型的都可以)在线等待中
- 初次接触ORACLE,问一个初级的问题.请各位高手帮忙!
- SCO UNIX下的Export
- toad中如何设置成自动提交语句呢?如何设置rollback区域呢
- 不懂就要问:想为用户显示公告是否已阅,数据库应如何设计?
- oracle11g 客户端安装
- 求教,我是萌新,老师给了一个程序包的模板,但是有的地方我没懂
- 关于oracle DB LINKS的问题(高手帮看一下)
- group by的一个问题
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 是什么啊?