query conversion - 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)

query conversion

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-14 : 07:26:50

hi,
can anyone give me this oracle query into sql server.

Update EMRLabTestLkUp lt SET lt.TEST_SPECIMEN = (Select ls.SPECIMEN_ID from EMRLabSpecimenLkUp ls
Where ltrim(Rtrim(lt.TEST_SPECIMEN))=ltrim(Rtrim(ls.SPECIMEN_NAME)) )

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-14 : 07:30:39

Update lt SET lt.TEST_SPECIMEN = ls.SPECIMEN_ID
from EMRLabTestLkUp lt inner join
EMRLabSpecimenLkUp ls
on ltrim(Rtrim(lt.TEST_SPECIMEN))=ltrim(Rtrim(ls.SPECIMEN_NAME)) )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 07:31:27

[code]UPDATE lt
SET lt.TEST_SPECIMEN = ls.SPECIMEN_ID
FROM EMRLabTestLkUp AS lt
LEFT JOIN EMRLabSpecimenLkUp AS ls ON LTRIM(ls.SPECIMEN_NAME) = LTRIM(lt.TEST_SPECIMEN)[/code]

E 12°55'05.63"
N 56°04'39.26"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-14 : 07:38:28

Thanks for that, now i am having one more question like i have to alter column from varchar to numeric.

can i proceed like this

actual query initially i have given is
ALTER TABLE EMRLabTestLkUp alter column TEST_SPECIMEN1 NUMERIC(20,0)
GO

later it did'nt work now i am giving like this.

ALTER TABLE EMRLabTestLkUp ADD TEST_SPECIMEN1 NUMERIC(20,0)
GO
UPDATE EMRLabTestLkUp SET TEST_SPECIMEN1 = TEST_SPECIMEN
GO
ALTER TABLE EMRLabTestLkUp DROP COLUMN TEST_SPECIMEN
GO
EXEC sp_rename 'EMRLabTestLkUp.[TEST_SPECIMEN1]', 'TEST_SPECIMEN', 'COLUMN'
GO

is this the correct way to do so?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 07:41:16

Why didn't it work? Did you get an error message?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 07:42:51

Works for me

CREATE TABLE	#Sample
		(
			vc VARCHAR(20)
		)

INSERT	#Sample
SELECT	'234324' UNION ALL

SELECT	*
FROM	#Sample

ALTER TABLE	#Sample
ALTER COLUMN	vc NUMERIC(20, 0)
GO

SELECT	*
FROM	#Sample

DROP TABLE	#Sample

E 12°55'05.63"
N 56°04'39.26"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-14 : 07:43:21

when i have executed earlier one it given error like

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

thats what i have opted for second one?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 07:47:01

Then you have data that cannot be converted into numeric.
And I am stumped about your UPDATE clause above not fetching the same error.

E 12°55'05.63"
N 56°04'39.26"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-14 : 07:54:39

for the update query too getting exception

Update lt SET lt.TEST_SPECIMEN = ls.SPECIMEN_ID
from EMRLabTestLkUp lt inner join
EMRLabSpecimenLkUp ls
on ltrim(Rtrim(lt.TEST_SPECIMEN))=ltrim(Rtrim(ls.SPECIMEN_NAME)) )
GO

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

IAM GETTING LIKE THIS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 07:55:22

Then try the other suggestion made 04/14/2009 : 07:31:27

E 12°55'05.63"
N 56°04'39.26"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-14 : 08:55:25

hi,

i am having query like this
UPDATE EMRIDS SET EZEMRXID = (SELECT MAX(PARENT_LAB_REQUEST_ID) FROM EMRParentLabRequestMaster) WHERE PROPERTY_NAME = 'PARENT_LAB_REQUEST_ID'
GO

in that EMRParentLabRequestMaster is not having any records so it is throwing exception like
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ezEMRxID', table 'TestData.dbo.EMRIDS'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

now can anyone give me some conditions for the above query without resulting any exceptions eventhough the table is not having any values

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-14 : 09:17:47

Try this...

UPDATE EMRIDS SET EZEMRXID = COALESCE((SELECT MAX(PARENT_LAB_REQUEST_ID) FROM EMRParentLabRequestMaster),0) WHERE PROPERTY_NAME = 'PARENT_LAB_REQUEST_ID'

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-14 : 09:34:23

THANK YOU

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-14 : 09:38:35

np..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources