Home » SQL & PL/SQL » SQL & PL/SQL » help me please to migrate from MS SQL to Oracle (Oracle 11.2.0.2.0)
icon5.gif  help me please to migrate from MS SQL to Oracle [message #603539] Tue, 17 December 2013 04:36 Go to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
Hi all,

we are in process of migration from MS SQL to Oracle.
Please help me to perform the same in Oracle:

select number+1 as rowid,number*6+1 as rowbeg,number*6+6 as rowend 
from master.dbo.spt_values
where type='P'


Our Oracle version:
select * from v$version
 
BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production   
PL/SQL Release 11.2.0.2.0 - Production                                         
CORE 11.2.0.2.0 Production                                                     
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production                        
NLSRTL Version 11.2.0.2.0 - Production                                         
 
5 rows selected.


Thanks for your attention.
Re: help me please to migrate from MS SQL to Oracle [message #603541 is a reply to message #603539] Tue, 17 December 2013 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
what's number do in that example?
Re: help me please to migrate from MS SQL to Oracle [message #603544 is a reply to message #603539] Tue, 17 December 2013 04:51 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
forums.oracle.com
Are they not fast enough? Smile
The people here, do look there to.
Re: help me please to migrate from MS SQL to Oracle [message #603546 is a reply to message #603544] Tue, 17 December 2013 05:03 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It seems you are looking for a row generator
Re: help me please to migrate from MS SQL to Oracle [message #603547 is a reply to message #603544] Tue, 17 December 2013 05:04 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Nothing wrong with posting the same question on more than one forum martijn. Yes, there is an intersect of users on both fora, but there are also plenty of people who do not use both, therefore posting in more than one forum will reach a larger audience.
Re: help me please to migrate from MS SQL to Oracle [message #603548 is a reply to message #603547] Tue, 17 December 2013 05:06 Go to previous messageGo to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
Here is part of output:

1 1 6
2 7 12
3 13 18
4 19 24
5 25 30
6 31 36
7 37 42
8 43 48
9 49 54
10 55 60
11 61 66
12 67 72
13 73 78

...

and much more...
Re: help me please to migrate from MS SQL to Oracle [message #603549 is a reply to message #603548] Tue, 17 December 2013 05:09 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
The problem here is that we have no idea what your sql server code is meant to do, how, therefore, are we to write something in Oracle? We could guess, but that would be pointless. YOU need to tell us what it is that your code is trying to achieve. Basically you need to give us a clear set of requirements.
Re: help me please to migrate from MS SQL to Oracle [message #603550 is a reply to message #603549] Tue, 17 December 2013 05:11 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
In addition to the above, this conversation has been had, many times, on more than one forum. You kinda need to forget (to an extent) about the code itself, it is a fool's errand to try to directly convert the code from one db into another db and expect it to be equivalent. As I said above, work out the requirements write the code in oracle to deliver those requirements.
Re: help me please to migrate from MS SQL to Oracle [message #603551 is a reply to message #603548] Tue, 17 December 2013 05:11 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
that doesn't really answer my question.
Is number a column in the table or is it the equivalent of rownum.
Re: help me please to migrate from MS SQL to Oracle [message #603553 is a reply to message #603547] Tue, 17 December 2013 05:20 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
hmmmm....okay, I guess you are right.
It's probably a personal thing, but I (and I do mean I) get a little annoyed when the very same question is posted twice in different fora.

Anyway...maybe I should be a little less grumpy Smile
Re: help me please to migrate from MS SQL to Oracle [message #603555 is a reply to message #603553] Tue, 17 December 2013 05:23 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
LOL, I'm in NO position to accuse or deride anyone for being grumpy Smile
Re: help me please to migrate from MS SQL to Oracle [message #603557 is a reply to message #603553] Tue, 17 December 2013 05:26 Go to previous messageGo to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
solution 1:
SELECT tab.RN +1   AS "ROWID",
        tab.RN *6+1 AS ROWBEG,
        tab.RN*6+6 AS ROWEND
   from  (
     SELECT (rownum -1) rn
       FROM DUAL
      connect by rownum < 15
      ) tab;


solution 2:
select 1 as rnum, 1 as rowbeg, 6 as rowend from dual
union all
select rownum+1 as rnum,rownum*6+1 as rowbeg,rownum*6+6 as rowend
from all_objects


Thanks again for your attention! Sorry for double posts, I'm hurry now...
Re: help me please to migrate from MS SQL to Oracle [message #603559 is a reply to message #603555] Tue, 17 December 2013 05:34 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
Laughing
I'll refrain from commenting on your statement Smile

[Updated on: Tue, 17 December 2013 05:34]

Report message to a moderator

Re: help me please to migrate from MS SQL to Oracle [message #603560 is a reply to message #603557] Tue, 17 December 2013 05:37 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Your two 'solutions' will return different resultsets.
Re: help me please to migrate from MS SQL to Oracle [message #603561 is a reply to message #603548] Tue, 17 December 2013 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select level, 6*(level-1)+1, 6*level from dual connect by level <= 10;
     LEVEL 6*(LEVEL-1)+1    6*LEVEL
---------- ------------- ----------
         1             1          6
         2             7         12
         3            13         18
         4            19         24
         5            25         30
         6            31         36
         7            37         42
         8            43         48
         9            49         54
        10            55         60

Re: help me please to migrate from MS SQL to Oracle [message #603563 is a reply to message #603539] Tue, 17 December 2013 06:19 Go to previous message
mokarem
Messages: 109
Registered: November 2013
Location: Dhaka
Senior Member



WITH T1 (col,col2,col3) AS
(
SELECT 1 col, 1 col2, 6 col3
FROM DUAL
UNION ALL
SELECT col+1 col, col*6+1 col2,col*6+6 col3
FROM T1
WHERE col < 15
)
SELECT * FROM T1;
Previous Topic: update the date column
Next Topic: How to add and multiply values in columns
Goto Forum:
  


Current Time: Thu Apr 25 05:17:18 CDT 2024