Oracle SQL*Plus to T-SQL

Question

Post reply

Oracle SQL*Plus to T-SQL

ainsley.lounds

SSC Enthusiast

Points: 100
More actions
October 3, 2008 at 9:11 am

#382937

HELP! I have the following Oracle SQL*Plus script I am trying to migrate to SQL Server 2005. I need to find an equivelent for ROWNUM,
any help is appreciated.
update residemo res
set (relatn_cod,
city_code,
prn_last,
prn_first,
prn_middle,
prn_addr,
prn_city,
prn_state,
prn_zip,
prn_phone,
prn_alttel,
alttelextn,
occupation,
employer,
wrkaddr,
wrkcity,
wrkstate,
wrkzip,
wrktel,
wrkextn,
wrkhrsfrom,
wrkhrsto,
extramail,
resides,
responsibl,
edulevel,
emailaddr ) = ( Select ap.relation,
cit.city_code,
ap.prn_last,
ap.prn_first,
ap.prn_middle,
substr(ap.address,1,20),
substr(ap.city,1,20),
substr(ap.state,1,2),
substr(ap.zipcode,1,10),
ap.telephone,
ap.alttel,
ap.alttelextn,
ap.occupation,
ap.employer,
ap.wrkaddr,
ap.wrkcity,
ap.wrkstate,
ap.wrkzip,
ap.wrktel,
ap.wrkextn,
ap.wrkhrsfmom,
ap.wrkhrsto,
ap.extramail,
ap.resides,
ap.resp,
ap.edulevel,
ap.emailaddr
From aprn ap, citystat cit
where ap.stulink = res.stulink
and ap.curr_loc = res.curr_loc
and upper(cit.city) = upper(res.city)
and rownum = 1)
where
exists (select 'x'
from sasi.aprn ap
where ap.stulink = res.stulink
and ap.curr_loc = res.curr_loc)
Thanks, in advance
Ainsley

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no direct equivalent. SQL doesn't have the concept of a row's position in the table.

You can use row_number(), but you need to specify the ordering for the allocation of row numbers'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Actually, there is a direct replacement for RowNum=1 in SQL Server... it's called TOP 1. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

By the way, Ainsley... SQL Server doesn't support multi column updates using a list of targets vs a list of sources separated by a single "=" sign. You have to identify each column pair with an "=" sign.

Also, much like a SELECT, the UPDATE statement in SQL Server supports the use of a FROM clause where you can easily join the target of the update to different sources without having to do all that EXISTS stuff and correlated subqueries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Bottom line is... you do not migrate queries in between Transact-SQL and Oracle's SQL*Plus, SQL or PL/SQL; you translate them meaning you read the source query, understand what it does and you rewrite it.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

gints.plivna Ten Centuries Points: 1256 More actions · Answer 5

GilaMonster (10/3/2008)
There's no direct equivalent. SQL doesn't have the concept of a row's position in the table.

BTW rownum has nothing to do with the row position in table. Rownum get's assigned as soon as row satisfying where clause is fetched from table(-s), but before order by.

Gints Plivna
http://www.gplivna.eu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

gints.plivna (11/1/2008)
GilaMonster (10/3/2008)
There's no direct equivalent. SQL doesn't have the concept of a row's position in the table.
BTW rownum has nothing to do with the row position in table. Rownum get's assigned as soon as row satisfying where clause is fetched from table(-s), but before order by.

Thanks. My Oracle's rather rusty.

There's still no direct equivalent. In SQL, before the order by is applied there's no concept of position and a table is considered an unordered set.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gints.plivna Ten Centuries Points: 1256 More actions · Answer 7

Yea, there isn't direct equivalent, but often rownum is used to reduce just number of rows.

rownum <= 1 is the same as rownum = 1 and means no more than 1 row

rownum <= n means no more than n rows

rownum = n means nothing and returns 0 rows

So AFAIK as Jeff already said in this case TOP 1 without order by functionally does the same.

Gints Plivna
http://www.gplivna.eu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

gints.plivna (11/2/2008)
So AFAIK as Jeff already said in this case TOP 1 without order by functionally does the same.

Providing you don't mind which rows you get back, yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rlondon SSCarpal Tunnel Points: 4653 More actions · Answer 9

Here is a good column on how RONUM works. It may be helpful in finding an alternative in T-SQL.

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

ainsley.lounds SSC Enthusiast Points: 100 More actions · Answer 10

Thanks All for your suggestions and insights. I rewrite the script and used the T-SQL TOP (1) clause. It is working fine now.

Here is the final T-SQL script, any suggestions for improvement is welcome. Thanks again.

UPDATE tblRESIDEMO

SET RELATN_COD = ap.RELATION, PRN_LAST = ap.PRN_LAST, PRN_FIRST = ap.PRN_FIRST, PRN_MIDDLE = SUBSTRING(ap.PRN_MIDDLE, 1, 1),

PRN_ADDR = SUBSTRING(ap.ADDRESS, 1, 20), PRN_CITY = SUBSTRING(tblRESIDEMO.CITY, 1, 20), PRN_STATE = SUBSTRING(ap.STATE, 1, 2),

PRN_ZIP = SUBSTRING(ap.ZIPCODE, 1, 10), PRN_PHONE = ap.TELEPHONE, PRN_ALTTEL = ap.ALTTEL, ALTTELEXTN = ap.ALTTELEXTN,

OCCUPATION = ap.OCCUPATION, EMPLOYER = ap.EMPLOYER, WRKADDR = ap.WRKADDR, WRKCITY = ap.WRKCITY, WRKSTATE = ap.WRKSTATE,

WRKZIP = ap.WRKZIP, WRKTEL = ap.WRKTEL, WRKEXTN = ap.WRKEXTN, WRKHRSFROM = ap.WRKHRSFMOM, WRKHRSTO = ap.WRKHRSTO,

EXTRAMAIL = ap.EXTRAMAIL, RESIDES = ap.RESIDES, RESPONSIBL = ap.RESP, EDULEVEL = ap.EDULEVEL, EMAILADDR = ap.EMAILADDR

FROM aprn AS ap INNER JOIN

tblRESIDEMO ON ap.STULINK = tblRESIDEMO.STULINK AND ap.CURR_LOC = tblRESIDEMO.CURR_LOC

WHERE EXISTS

(SELECT TOP (1) STATUS, CURR_LOC, STULINK, SEQUENCE, RELATION, SOCSECNUM, PRN_LAST, PRN_FIRST, PRN_MIDDLE, SALUTATION,

BIRTHPLACE, ADDRESS, CITY, STATE, ZIPCODE, TELEPHONE, ALTTEL, ALTTELEXTN, OCCUPATION, EMPLOYER, WRKADDR, WRKCITY,

WRKSTATE, WRKZIP, WRKTEL, WRKEXTN, WRKHRSFMOM, WRKHRSTO, EXTRAMAIL, USCITIZEN, PL874MIL, CONTACTNA, RESIDES,

RESP, EDULEVEL, USRCODE1, USRCODE2, USRCODE3, USRCODE4, USRCODE5, GENDER, LIFESTFTHR, LIFESTMTHR, PRNRTSMALE,

PRNRTSFEM, COUNTRY, PROVINCE, EMAILADDR, USERSTAMP, DATESTAMP, TIMESTAMP, ROWID

FROM aprn AS ap

WHERE (STULINK = tblRESIDEMO.STULINK) AND (CURR_LOC = tblRESIDEMO.CURR_LOC) AND (tblRESIDEMO.SCHOOL_YEAR =

(SELECT ISNULL(BEGIN_SCH_YEAR, '') + '-' + ISNULL(END_SCH_YEAR, '') AS expr

FROM schoolyear)))

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Top 1 not necessary in an exists. Exists just checks if there's any rows or not, it doesn't care how many rows. Remove the top 1 and the behaviour will be identical.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mtougui Valued Member Points: 66 More actions · Answer 12

The easiest way to convert rownum(oracle) to SQL SERVER 2005 is to use top(x).

You ll have something like :

Oracle:

select col1, col2, ....

from tableX

where

clauses

And rownum = 5

SQL SERVER 2005

select top(5) col1, col2 ....

from tableX

where

clauses

gints.plivna Ten Centuries Points: 1256 More actions · Answer 13

mtougui (11/4/2008)
The easiest way to convert rownum(oracle) to Oracle:
select col1, col2, ....
from tableX
where
clauses
And rownum = 5

In SQL Server and every other dbms it is equivalent to

select col1, col2, ....

from tableX

where 1=0

How about reading previous posts and especially article provided by rlondon?

Gints Plivna
http://www.gplivna.eu

ainsley.lounds SSC Enthusiast Points: 100 More actions · Answer 14

ainsley.lounds

SSC Enthusiast

Points: 100

November 4, 2008 at 12:58 pm

#893838

Thanks All, for your suggestions.