October 3, 2008 at 9:11 am
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
October 3, 2008 at 10:59 am
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
October 3, 2008 at 8:25 pm
Actually, there is a direct replacement for RowNum=1 in SQL Server... it's called TOP 1.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2008 at 8:30 pm
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
Change is inevitable... Change for the better is not.
October 4, 2008 at 5:19 am
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.November 1, 2008 at 1:39 pm
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
November 1, 2008 at 2:15 pm
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
November 2, 2008 at 7:07 am
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
November 2, 2008 at 12:38 pm
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
November 3, 2008 at 8:37 am
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
November 3, 2008 at 9:52 am
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)))
November 3, 2008 at 12:57 pm
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
November 4, 2008 at 9:27 am
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
November 4, 2008 at 9:32 am
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
November 4, 2008 at 12:58 pm
Thanks All, for your suggestions.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy