SQL & PL/SQL

Hi,

Due to the convertion of the backend database of an application from mysql to oracle 10g, I need also to convert my sql query for a BIRT report to be compatible to Oracle 10g.

It is about the usage of user-defined variables in mysql. What is the equivalence in oracle of the user-defined variables in mysql for the following listed case?

Thanks much in advance!

LBäcker

SQL for Mysql was something like this:

select ID1,ID2, case when ( SORT is not null )
then @SA := SORT else @SA := @SA + 1 end as S1 ,
case when ( SORT2 is not null ) then @SB := SORT2 else @SB := @SB + 1 end as S2
from
( select @SA := 100 , ID ID1, GLOBALSORTORDER SORT, ORG ORG1
from DOCUMENT where ACTIVE = 'T' and BASELINE = 4 ) b1
FULL OUTER JOIN
( select @SB := 200 , ID ID2, GLOBALSORTORDER SORT2, ORG ORG2
from dOCUMENT where ACTIVE = 'T' and BASELINE = 5 ) b2
on b1.ORG1 = b2.ORG2
ORDER BY S2, S1

The purpose I wrote this sql code is to resolve a sorting problem

b1
ID | Globalsortorder | org |
--------------------------------------------
23 | 100 | 10
24| 101 | 11
b2
ID | Globalsortorder | org |
--------------------------------------------
38| 200 | 9
39 | 201 | 10
34| 202 | 12

expected result
ID1 | ID2 | S1 | S2
-------------------
null | 38 | 100 | 200
23 | 39 | 100 | 201
24 | null | 101 | 202
null |34 | 102 | 202