Oracle SQL code to MS-SQL

Question

question

BVBorgstrom asked Nov 25, '20

Oracle SQL code to MS-SQL

Hi!

Can anybody help translating this Oracle SQL to something that runs on Microsoft SQL-server?

Best regards, Brian

The code:

UPDATE individ c
   SET (c.z_linie2, c.z_linie3, c.z_linie4) =
	(SELECT SUBSTR(   v.z_vejnavn || ' '
	       || DECODE(c.husnr,
			 NULL, DECODE(c.bnr,
				      NULL, '',
					    'B-' || LTRIM(c.bnr,'0')),
			       LTRIM(SUBSTR(c.husnr,1,3),'0')
			       || DECODE(SUBSTR(c.husnr,4),
					 NULL, '',
					       ' ' || SUBSTR(c.husnr,4)))
	       || DECODE(LTRIM(c.z_etage,' '),
			 NULL, '',
			       ',' || LTRIM(c.z_etage,'0'))
	       || DECODE(LTRIM(c.z_sidedoer,' '),
			 NULL, '',
			 'mf', ' mf',
			 'th', ' th',
			 'tv', ' tv',
			       ',-' || LTRIM(c.z_sidedoer,' 0')),
	  1,34),
	v.z_bygde,
	v.postnr || '  ' || p.z_postdr
   FROM postnr p, vej v
  WHERE (c.vejkod = v.vejkod )
    AND (c.komkod = v.komkod )   
    AND (v.postnr = p.postnr (+)))
where c.pnr in (select l.pnr FROM ladropl2010 l
	  	where  l.lseq BETWEEN &fra AND &til )
AND c.komkod like '9%'

sql-server oracle code

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-11-25T15:21:16Z

MikeyBronowski answered Nov 25, '20

What version of SQL Server are you running?

What errors do you get?
One function I could see that might need replacement is SUBSTR() with SUBSTRING() https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question