Oracle to MS SQL error

Hello,
I have this query that was written for Oracle, but when I run it in MS
SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
I can't seem to figure it out, can you someone please point in the
right direction?
select 99, login_name, (full_name)
from shr_wr_approvers
where ACCOUNTING_NUMBER = ''
union select level, C240000005, ( C240000001)
from T265
where level in (2,3,4)
start with upper(C240000006) = '0397639'
connect by prior upper(C800000108) = upper(C240000006)
union select 0,'','No Data' from dual where not exists (select
login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
exists (select C1 from T265 where level in (2,3,4) start with
upper(C240000006) = '0397639' connect by prior upper(C800000108) = upper(C240000006)) order by 1,2Did you try running the query through the Microsoft SSMA (SQL Server
Migration Assistant) tool first? It's a free download from Microsoft.com and
it will convert PL/SQL to T-SQL for you so you can use it in SQL Server.
BTW ... Is this a Remedy database schema that you are converting?
BR, mkro
--
mkro
"Ybleu" wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
>|||Hi
start with and connect by prior is not available in SQL server as they are
proprietary Oracle extensions
You could use recursive CTEs in SQL 2005, if you are using SQL 2000 you may
have to resort to using a temporary table.
Check out Joe Celko's "Trees and Hierarchies" ISBN 1-55860-920-2 which talks
about this and methods of implementing hierarchical data.
Posting DDL, example data and expected results is always useful when
answering this sort of question see
http://www.aspfaq.com/etiquette.asp?id=5006
John
"Ybleu" wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
>|||Ybleu wrote:
> Hello,
> I have this query that was written for Oracle, but when I run it in MS
> SQL I get */*** ERROR *** Line 9: Incorrect syntax near 'start'.
> I can't seem to figure it out, can you someone please point in the
> right direction?
> select 99, login_name, (full_name)
> from shr_wr_approvers
> where ACCOUNTING_NUMBER = ''
> union select level, C240000005, ( C240000001)
> from T265
> where level in (2,3,4)
> start with upper(C240000006) = '0397639'
> connect by prior upper(C800000108) = upper(C240000006)
> union select 0,'','No Data' from dual where not exists (select
> login_name from shr_wr_approvers where ACCOUNTING_NUMBER = '') and not
> exists (select C1 from T265 where level in (2,3,4) start with
> upper(C240000006) = '0397639' connect by prior upper(C800000108) => upper(C240000006)) order by 1,2
START WITH... CONNECT BY is not standard SQL it's an Oracle special.
SQL Server has the ANSI/ISO standard syntax for recursive queries.
Lookup the WITH keyword in Books Online (SQL Server 2005 only).
If you want an exact solution then it would help if you could post DDL,
sample data and show your required end result. Also tell us what
version you are using.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--