Oracle SQL query to MS-SQL
-
Hi I have to migrate a database from Oracle to MS-SQL. The database migration went well, but i have a query that gives me trouble. I have tried to convert it using this on-line tool - http://www.sqlines.com/online, but it is not working since the Oracle alias seems to be a problem for MS-SQL. Is there any way to use a similar syntax in MS-SQL? The original Oracle SQL:
UPDATE folk.individ c
SET (c.stat,
c.statusdto,
c.pnrgaeld,
c.koen,
c.fornvnmrk,
c.mellemnvnmrk,
c.efternvnmrk,
c.nvndto,
c.fornvn_solo,
c.fornvn,
c.mellemnvn,
c.efternvn,
c.adr_fornvn,
c.adr_efternvn,
stilling,
z_stilling,
c.z_fornvn,
c.z_mellemnvn,
c.z_efternvn,
c.z_adr_fornvn,
c.z_adr_efternvn,
c.z_fornvn_solo,
c.haenstart_umrk_navne,
c.stillingdto,
c.mynkod_ctnavn,
c.myntxt_ctnavn,
c.indrap,
c.adrnvndto,
c.soegnvn,
c.soegnvndto
) =
(SELECT l.status,
TO_DATE(DECODE(SUBSTR(l.statushaenstart,7,2),'00',NULL,
SUBSTR(l.statushaenstart,1,8)),'YYYYMMDD'),
l.pnrgaeld,
l.koen,
a.fornvn_mrk,
a.melnvn_mrk,
a.efternvn_mrk,
TO_DATE(DECODE(SUBSTR(a.nvnhaenstart,7,2),'00',NULL,
SUBSTR(a.nvnhaenstart,1,8)),'YYYYMMDD'),
Upper(a.fornvn), -- c.fornvn_solo
substr(decode(a.melnvn, null, upper(a.fornvn), upper(a.fornvn)|| ' ' || upper(a.melnvn) ),1,50), -- skal være som før: fornavn indh. også mlnavne
upper(a.melnvn),
upper(a.efternvn),
upper(LTRIM(SUBSTR(a.adrnvn, INSTR(a.adrnvn,',')+1))), --adr_fornvn
upper(SUBSTR(a.adrnvn,1,INSTR(a.adrnvn,',') - 1)), --adr_efternavn
upper(l.stilling),
l.stilling,
substr(decode(a.melnvn, null, a.fornvn, a.fornvn || ' ' || a.melnvn),1,50), -- z_fornvn, fornavn indh. også mlnavne
a.melnvn, -- z_mellemnvn
a.efternvn,
LTRIM(SUBSTR(a.adrnvn, INSTR(a.adrnvn,',')+1)), --z_adr_fornvn
SUBSTR(a.adrnvn,1,INSTR(a.adrnvn,',') - 1), -- z_adr_eftermnvn
a.fornvn, -- z_fornvn_solo
a.haenstart_umrk_navne, -- anvendelse ukendt
null, --stillingdto udgået
null, --mynkod_ctnavn udgået
null, --myntxt_ctnavn udgået
null, --indrap udgået
null, -- adrnvndto udgået
null, --soegnvn udgået
null --soegnvndto udgået
FROM folk.lperson2010 l, folk.laktnvnopl2010 a
WHERE l.pnr = c.pnr
AND l.pnr = a.pnr
AND l.lseq BETWEEN 1 AND 6000 )
where c.pnr in (select l.pnr FROM folk.lperson2010 l
where l.lseq BETWEEN 1 AND 6 -
Hi I have to migrate a database from Oracle to MS-SQL. The database migration went well, but i have a query that gives me trouble. I have tried to convert it using this on-line tool - http://www.sqlines.com/online, but it is not working since the Oracle alias seems to be a problem for MS-SQL. Is there any way to use a similar syntax in MS-SQL? The original Oracle SQL:
UPDATE folk.individ c
SET (c.stat,
c.statusdto,
c.pnrgaeld,
c.koen,
c.fornvnmrk,
c.mellemnvnmrk,
c.efternvnmrk,
c.nvndto,
c.fornvn_solo,
c.fornvn,
c.mellemnvn,
c.efternvn,
c.adr_fornvn,
c.adr_efternvn,
stilling,
z_stilling,
c.z_fornvn,
c.z_mellemnvn,
c.z_efternvn,
c.z_adr_fornvn,
c.z_adr_efternvn,
c.z_fornvn_solo,
c.haenstart_umrk_navne,
c.stillingdto,
c.mynkod_ctnavn,
c.myntxt_ctnavn,
c.indrap,
c.adrnvndto,
c.soegnvn,
c.soegnvndto
) =
(SELECT l.status,
TO_DATE(DECODE(SUBSTR(l.statushaenstart,7,2),'00',NULL,
SUBSTR(l.statushaenstart,1,8)),'YYYYMMDD'),
l.pnrgaeld,
l.koen,
a.fornvn_mrk,
a.melnvn_mrk,
a.efternvn_mrk,
TO_DATE(DECODE(SUBSTR(a.nvnhaenstart,7,2),'00',NULL,
SUBSTR(a.nvnhaenstart,1,8)),'YYYYMMDD'),
Upper(a.fornvn), -- c.fornvn_solo
substr(decode(a.melnvn, null, upper(a.fornvn), upper(a.fornvn)|| ' ' || upper(a.melnvn) ),1,50), -- skal være som før: fornavn indh. også mlnavne
upper(a.melnvn),
upper(a.efternvn),
upper(LTRIM(SUBSTR(a.adrnvn, INSTR(a.adrnvn,',')+1))), --adr_fornvn
upper(SUBSTR(a.adrnvn,1,INSTR(a.adrnvn,',') - 1)), --adr_efternavn
upper(l.stilling),
l.stilling,
substr(decode(a.melnvn, null, a.fornvn, a.fornvn || ' ' || a.melnvn),1,50), -- z_fornvn, fornavn indh. også mlnavne
a.melnvn, -- z_mellemnvn
a.efternvn,
LTRIM(SUBSTR(a.adrnvn, INSTR(a.adrnvn,',')+1)), --z_adr_fornvn
SUBSTR(a.adrnvn,1,INSTR(a.adrnvn,',') - 1), -- z_adr_eftermnvn
a.fornvn, -- z_fornvn_solo
a.haenstart_umrk_navne, -- anvendelse ukendt
null, --stillingdto udgået
null, --mynkod_ctnavn udgået
null, --myntxt_ctnavn udgået
null, --indrap udgået
null, -- adrnvndto udgået
null, --soegnvn udgået
null --soegnvndto udgået
FROM folk.lperson2010 l, folk.laktnvnopl2010 a
WHERE l.pnr = c.pnr
AND l.pnr = a.pnr
AND l.lseq BETWEEN 1 AND 6000 )
where c.pnr in (select l.pnr FROM folk.lperson2010 l
where l.lseq BETWEEN 1 AND 6The tool doesn't seem to have done anything! :laugh: At a guess, you're looking for something like:
UPDATE
c
SET
stat = l.status,
statusdto = CASE SUBSTRING(l.statushaenstart, 7, 2)
WHEN '00' THEN NULL
ELSE CONVERT(datetime, SUBSTRING(l.statushaenstart, 1, 8))
END,
pnrgaeld = l.pnrgaeld,
koen = l.koen,
fornvnmrk = a.fornvn_mrk,
mellemnvnmrk = a.melnvn_mrk,
efternvnmrk = a.efternvn_mrk,
nvndto = CASE SUBSTRING(a.nvnhaenstart, 7, 2)
WHEN '00' THEN NULL
ELSE CONVERT(datetime, SUBSTRING(a.nvnhaenstart, 1, 8))
END,
fornvn_solo = UPPER(a.fornvn),
fornvn = SUBSTRING(CASE
WHEN a.melnvn Is Null THEN UPPER(a.fornvn)
ELSE UPPER(a.fornvn) + ' ' + UPPER(a.melnvn)
END, 1, 50),
mellemnvn = UPPER(a.melnvn),
efternvn = UPPER(a.efternvn),
adr_fornvn = UPPER(LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn) + 1, LEN(a.adrnvn)))),
adr_efternvn = UPPER(SUBSTRING(a.adrnvn, 1, CHARINDEX(',', a.adrnvn) - 1)),
stilling = UPPER(l.stilling),
z_stilling = l.stilling,
z_fornvn = SUBSTRING(CASE
WHEN a.melnvn Is Null THEN a.fornvn
ELSE a.fornvn + ' ' + a.melnvn
END, 1, 50),
z_mellemnvn = a.melnvn,
z_efternvn = a.efternvn,
z_adr_fornvn = LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn) + 1, LEN(a.adrnvn))),
z_adr_efternvn = SUBSTRING(a.adrnvn, 1, CHARINDEX(',', a.adrnvn) - 1),
z_fornvn_solo = a.fornvn,
haenstart_umrk_navne = a.haenstart_umrk_navne,
stillingdto = Null,
mynkod_ctnavn = Null,
myntxt_ctnavn = Null,
indrap = Null,
adrnvndto = Null,
soegnvn = Null,
soegnvndto = Null
FROM
folk.individ As c
INNER JOIN folk.lperson2010 As l ON l.pnr = c.pnr
INNER JOIN folk.laktnvnopl2010 a ON a.pnr = c.pnr
WHERE
l.lseq BETWEEN 1 AND 6000
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
The tool doesn't seem to have done anything! :laugh: At a guess, you're looking for something like:
UPDATE
c
SET
stat = l.status,
statusdto = CASE SUBSTRING(l.statushaenstart, 7, 2)
WHEN '00' THEN NULL
ELSE CONVERT(datetime, SUBSTRING(l.statushaenstart, 1, 8))
END,
pnrgaeld = l.pnrgaeld,
koen = l.koen,
fornvnmrk = a.fornvn_mrk,
mellemnvnmrk = a.melnvn_mrk,
efternvnmrk = a.efternvn_mrk,
nvndto = CASE SUBSTRING(a.nvnhaenstart, 7, 2)
WHEN '00' THEN NULL
ELSE CONVERT(datetime, SUBSTRING(a.nvnhaenstart, 1, 8))
END,
fornvn_solo = UPPER(a.fornvn),
fornvn = SUBSTRING(CASE
WHEN a.melnvn Is Null THEN UPPER(a.fornvn)
ELSE UPPER(a.fornvn) + ' ' + UPPER(a.melnvn)
END, 1, 50),
mellemnvn = UPPER(a.melnvn),
efternvn = UPPER(a.efternvn),
adr_fornvn = UPPER(LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn) + 1, LEN(a.adrnvn)))),
adr_efternvn = UPPER(SUBSTRING(a.adrnvn, 1, CHARINDEX(',', a.adrnvn) - 1)),
stilling = UPPER(l.stilling),
z_stilling = l.stilling,
z_fornvn = SUBSTRING(CASE
WHEN a.melnvn Is Null THEN a.fornvn
ELSE a.fornvn + ' ' + a.melnvn
END, 1, 50),
z_mellemnvn = a.melnvn,
z_efternvn = a.efternvn,
z_adr_fornvn = LTRIM(SUBSTRING(a.adrnvn, CHARINDEX(',', a.adrnvn) + 1, LEN(a.adrnvn))),
z_adr_efternvn = SUBSTRING(a.adrnvn, 1, CHARINDEX(',', a.adrnvn) - 1),
z_fornvn_solo = a.fornvn,
haenstart_umrk_navne = a.haenstart_umrk_navne,
stillingdto = Null,
mynkod_ctnavn = Null,
myntxt_ctnavn = Null,
indrap = Null,
adrnvndto = Null,
soegnvn = Null,
soegnvndto = Null
FROM
folk.individ As c
INNER JOIN folk.lperson2010 As l ON l.pnr = c.pnr
INNER JOIN folk.laktnvnopl2010 a ON a.pnr = c.pnr
WHERE
l.lseq BETWEEN 1 AND 6000
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks a lot. I did not realize that Oracle SQL and MS-SQL is that different from each other
-
Thanks a lot. I did not realize that Oracle SQL and MS-SQL is that different from each other
Have some Google Foo just to make your life richer Difference Between T-SQL and PL-SQL (with Comparison Chart) - Tech Differences[^] And Oracle WANTS TO SHOUT AT YOU.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
