Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Syntax problem

SQL Syntax problem

From
Doris Bernloehr
Date:
Hello.

I've got a problem in porting the following select statement from Oracle to
Postgres, because of the characters after "b.bet_id" and "f.ask_id" in the
where clause: (+)
I don't know what these characters mean and how I can transform these into
PostgreSql Syntax.


select        ...
from         auswahlkatalog k, anspruchkorrektur a, beteiligter b, v_betkorr f
where           k.awk_id = a.awk_id and b.bet_id(+) = a.bet_idemp
        and a.ask_id = f.ask_id(+);


Hoping for help.
Doris

Re: SQL Syntax problem

From
Tom Lane
Date:
Doris Bernloehr <bedo7@gmx.net> writes:
> I've got a problem in porting the following select statement from Oracle to
> Postgres, because of the characters after "b.bet_id" and "f.ask_id" in the
> where clause: (+)

Those are outer join markers.  The general idea is that Oracle's

    select ... from a, b where a.f1 = b.f2(+);

transforms to the SQL-standard syntax

    select ... from a left join b on (a.f1 = b.f2);

but I'm very fuzzy on the details beyond that (I'm not totally sure
whether the (+) denotes the left or right side of the join, even).
Anyway try googling for the terms "outer join" and "left join" and
you'll probably find some info on converting Oracle's notation to
standard.

BTW, I believe recent Oracle releases do accept the standard outer
join syntax, so you could consider converting over in general.

            regards, tom lane

Re: SQL Syntax problem

From
Bruno LEVEQUE
Date:
The "(+)" is for outer join.

Your query must become :

select ...
from auswahlkatalog k,anspruchkorrektur a, beteiligter b, v_betkorr f
left outer join bet_id on (b.bet_id = a.bet_idemp)
right outer join ask_id on (a.ask_id = f.ask_id)
where k.awk_id = a.awk_id

(see tutorial-join.html for the right syntax)

Maybe you can write :

select ...
from auswahlkatalog k,anspruchkorrektur a, beteiligter b, v_betkorr f
where k.awk_id = a.awk_id and b.bet_id = a.bet_idemp and a.ask_id = f.ask_id

Bruno

Doris Bernloehr wrote:

>Hello.
>
>I've got a problem in porting the following select statement from Oracle to
>Postgres, because of the characters after "b.bet_id" and "f.ask_id" in the
>where clause: (+)
>I don't know what these characters mean and how I can transform these into
>PostgreSql Syntax.
>
>
>select        ...
>from         auswahlkatalog k, anspruchkorrektur a, beteiligter b, v_betkorr f
>where           k.awk_id = a.awk_id and b.bet_id(+) = a.bet_idemp
>        and a.ask_id = f.ask_id(+);
>
>
>Hoping for help.
>Doris
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>

--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com