need mssql query in oracle - 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)

need mssql query in oracle

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-09 : 04:27:22

hi need it in oracle please.

Update EMRLabResultsDetails
Set LAB_PROFILE_ID = b.LAB_PROFILE_ID
From EMRLabResultsDetails a,
(
SELECT DISTINCT ELRD.LAB_REQUEST_ID,ELRD.LAB_PROFILE_ID,ELRDS.TEST_ID,ELRDS.LAB_RESULT_ID,ELRDS.LAB_RESULT_DETAILS_ID
FROM EMRLabRequest ELR
INNER JOIN EMRLabRequestDetails ELRD ON ELR.LAB_REQUEST_ID=ELRD.LAB_REQUEST_ID AND LAB_PROFILE_ID IS NOT NULL
INNER JOIN EMRLabResults ELRS ON ELRS.LAB_REQUEST_ID=ELR.LAB_REQUEST_ID
INNER JOIN EMRLabResultsDetails ELRDS ON ELRDS.LAB_RESULT_ID=ELRS.LAB_RESULT_ID
INNER JOIN EMRLabTestProfileLkup ELTPL ON ELTPL.LAB_PROFILE_ID=ELRD.LAB_PROFILE_ID
INNER JOIN EMRTestProfiles ETP ON ETP.LAB_PROFILE_ID=ELTPL.LAB_PROFILE_ID
WHERE ELR.STATUS=1 AND ELRD.LAB_PROFILE_ID=ETP.LAB_PROFILE_ID AND ETP.TEST_ID=ELRDS.TEST_ID
) b
Where a.LAB_RESULT_ID = b.LAB_RESULT_ID
GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-09 : 04:42:02

Did you try to execute it as it is?

No, you're never too old to Yak'n'Roll if you're too young to die.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-09 : 04:43:41

yes iam getting Error at Command Line:2 Column:37
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-09 : 04:46:54

Update EMRLabResultsDetails
Set LAB_PROFILE_ID = (SELECT b.LAB_PROFILE_ID
From EMRLabResultsDetails a,
(
SELECT DISTINCT ELRD.LAB_REQUEST_ID,ELRD.LAB_PROFILE_ID,ELRDS.TEST_ID,ELRDS.LAB_RESULT_ID,ELRDS.LAB_RESULT_DETAILS_ID
FROM EMRLabRequest ELR
INNER JOIN EMRLabRequestDetails ELRD ON ELR.LAB_REQUEST_ID=ELRD.LAB_REQUEST_ID AND LAB_PROFILE_ID IS NOT NULL
INNER JOIN EMRLabResults ELRS ON ELRS.LAB_REQUEST_ID=ELR.LAB_REQUEST_ID
INNER JOIN EMRLabResultsDetails ELRDS ON ELRDS.LAB_RESULT_ID=ELRS.LAB_RESULT_ID
INNER JOIN EMRLabTestProfileLkup ELTPL ON ELTPL.LAB_PROFILE_ID=ELRD.LAB_PROFILE_ID
INNER JOIN EMRTestProfiles ETP ON ETP.LAB_PROFILE_ID=ELTPL.LAB_PROFILE_ID
WHERE ELR.STATUS=1 AND ELRD.LAB_PROFILE_ID=ETP.LAB_PROFILE_ID AND ETP.TEST_ID=ELRDS.TEST_ID
) b
Where a.LAB_RESULT_ID = b.LAB_RESULT_ID)
getting

SQL Error: ORA-01427: single-row subquery returns more than one row

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-09 : 05:15:54

got it thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-09 : 06:55:21