oracle to mssql required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

oracle to mssql required

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-11-10 : 07:55:16

Hi,
This is oracle query
SELECT ((SELECT password_expiry FROM EMRLOGINCONFIGINFO WHERE group_id = PGroupid) - ROUND((to_date(PCurrentDate, 'yyyy-mm-dd HH24:MI:SS') - password_created_on),0))
INTO PCountDays
FROM (SELECT max(password_created_on) as password_created_on FROM emrpasswordhistory WHERE Ltrim(Rtrim(Upper(USER_LOGIN))) = Ltrim(Rtrim(Upper(PLoginName))) ) x ;

i have written it in mssql like this
SELECT @PCountDays=((SELECT password_expiry FROM EMRLOGINCONFIGINFO WHERE group_id = @PGroupid) -ROUND((Convert(Varchar(35),@PCurrentDate,120) - password_created_on),0)))
FROM (SELECT max(password_created_on) as password_created_on FROM emrpasswordhistory WHERE Ltrim(Rtrim(Upper(USER_LOGIN))) = Ltrim(Rtrim(Upper(@PLoginName))) x ;

getting exception as
Msg 102, Level 15, State 1, Procedure EZChkLogin, Line 114
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure EZChkLogin, Line 115
Incorrect syntax near 'x'.

please help me in solving this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 08:03:33

Try

SELECT @PCountDays=
(SELECT password_expiry FROM EMRLOGINCONFIGINFO WHERE group_id = @PGroupid) -ROUND((Convert(Varchar(35),@PCurrentDate,120) -
(SELECT max(password_created_on) as password_created_on FROM emrpasswordhistory WHERE Ltrim(Rtrim(Upper(USER_LOGIN))) = Ltrim(Rtrim(Upper(@PLoginName))))
),0)

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-10 : 08:04:42

having the table schemas would REALLY help.

and some sample data, and desired output. Follow the "How to ask" link and it will show you what we need to really help.
[EDIT] or madhi-bot will answer right away.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 05:11:41

quote:
Originally posted by DonAtWork

having the table schemas would REALLY help.

and some sample data, and desired output. Follow the "How to ask" link and it will show you what we need to really help.
[EDIT] or madhi-bot will answer right away.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

But the OP dont have an habit of replying back

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources