ORDER BY error, need help!

When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below.

If I take the ORDER by out then it works correctly, but I need the ORDER BY because of the TOP 9. Any Suggestions?
Code: ( sql )

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) AS Top9Total FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 [B]ORDER BY (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) DESC[/B]) UNION ALL (SELECT Top9.vchTrustee, ' ', (AllCases.TR - SUM(Top9.Top9Total)) AS TRMinusTop9 FROM (SELECT AllTotalReceipts.vchTrustee, SUM(AllTotalReceipts.TotalMoney) AS TR FROM (SELECT trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) AS totalAssetBalance, (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) AS TotalMoney FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 ) AS AllTotalReceipts GROUP BY AllTotalReceipts.vchTrustee) AS AllCases LEFT JOIN (SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) AS totalAssetBalance, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) AS Top9Total FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN (SELECT trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) AS TotalReceipts FROM (SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) AS TdMnyAmount FROM tblTransactionDetail td INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID INNER JOIN tblCase c ON b.intCaseID = c.intCaseID GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted, c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType, t.intTransactionMethod, c.intStatus HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0 AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43) AND c.vchTrustee = 'RLW' AND c.intStatus = 1 ) AS Trans40 GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber ) AS Trans ON c1.vchCaseNumber = Trans.vchCaseNumber GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1 ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC ) AS Top9 ON AllCases.vchTrustee = Top9.vchTrustee GROUP BY Top9.vchTrustee, AllCases.TR )

Quote:

Originally Posted by speavey

When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below.

If I take the ORDER by out then it works correctly, but I need the ORDER BY because of the TOP 9. Any Suggestions?

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1

ORDER BY (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) DESC)

UNION All

(SELECT Top9.vchTrustee, ' ', (AllCases.TR - SUM(Top9.Top9Total)) As TRMinusTop9
FROM

(SELECT AllTotalReceipts.vchTrustee, SUM(AllTotalReceipts.TotalMoney) As TR
FROM

(SELECT trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
(Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) As TotalMoney

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td
INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus
HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
) As AllTotalReceipts

GROUP BY AllTotalReceipts.vchTrustee) As AllCases

LEFT JOIN

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC
) As Top9

ON AllCases.vchTrustee = Top9.vchTrustee
GROUP By Top9.vchTrustee, AllCases.TR

)

i believe ORDER BY has to be placed on the outermost QUERY, resulting in a sorted final returned resultset|||

Quote:

Originally Posted by ck9663

i believe ORDER BY has to be placed on the outermost QUERY, resulting in a sorted final returned resultset

I tried that and it won't produce the correct order by, because the First query with the TOP 9 isn't being sorted.

So if I put it on the outermost query, it doesn't sort correctly.

Maybe you can help me with my query, its very long and probably too much code. I'm trying to create a query the will SUM a value from two tables and pull the TOP 9 cases. Then also add a TOTAL SUM value - Top 9 Sum Value.

Any help would be great!!

Order by clause work incorrect

when i try the following SQL batch, I get a result-set which is not order by
datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

who can help me?thanks in advance
...
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

DROP TABLE ##stuff_fifo
select * from #fifo_temp

the following can get a correct result:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_dateIf I am not mistaken, you have no influence on the physical order of recordsets saved in tables in MSSQL, so your table #fifo_temp will not be ordered by out_date.

Choose the order of recordsets when extracting the data from the table, so use:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')

DROP TABLE ##stuff_fifo
select * from #fifo_temp
ORDER BY out_date

Regards

kbk|||KBK is correct. A table has no inherant "order" for either columns or rows, although a result set has order for both. The only place where order makes any sense (or difference) is in the result set.

-PatP|||Set Out_Date as your clustered index and the data will be ordered the way you want, but if the order is important to you then it is best to specify it each time you select from the dataset using and ORDER BY clause.|||I see the problem.Thank all kindly friends.

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
--

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
--