Обсуждение: SQL operator '*='

Hello,

I've here a smaller problem of our porting from Sybase/Oracle/Informix
code to PostgreSQL; the code reads for the mentioned DBS:


    ...
#ifdef DBSINF
    EXEC SQL DECLARE land_cursor CURSOR FOR
        SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2,
            karenz3, land.wkz, webez, we, kurs, land.del
        FROM   land, OUTER devisen
        WHERE  land.wkz = devisen.wkz AND land.brgroup = devisen.brgroup AND land.brgroup =
:brgroupHost_for_helpland_cursor
        ORDER  BY stammprio, landbez;
#endif

#ifdef DBSORA
    EXEC SQL DECLARE land_cursor CURSOR FOR
        SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2,
            karenz3, land.wkz, webez, we, kurs, land.del
        FROM   land, devisen
        WHERE  land.wkz = devisen.wkz (+) AND land.brgroup = devisen.brgroup (+) AND land.brgroup =
:brgroupHost_for_helpland_cursor
        ORDER  BY stammprio, landbez;
#endif

#ifdef DBSSYB
    EXEC SQL DECLARE land_cursor CURSOR FOR 
        SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2, 
            karenz3, land.wkz, webez, we, kurs, land.del
        FROM   land, devisen
        WHERE  land.wkz *= devisen.wkz AND land.brgroup *= devisen.brgroup AND land.brgroup =
:brgroupHost_for_helpland_cursor
        ORDER  BY stammprio, landbez;
#endif

#ifdef DBSPOS
    EXEC SQL DECLARE land_cursor CURSOR FOR 
        SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2, 
            karenz3, land.wkz, webez, we, kurs, land.del
        FROM   land, devisen
        WHERE  land.wkz *= devisen.wkz AND land.brgroup *= devisen.brgroup AND land.brgroup =
:brgroupHost_for_helpland_cursor
        ORDER  BY stammprio, landbez;
#endif

(the code for DBSPOS was just copied from Sybase). It compiles fine but
raises on execution en error about operator '*=' is not supported...

Any ideas about how to express this correctly in PostgreSQL?

Thanks

    matthias



-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut"
"Believe little, scrutinise all, think by your own: How see through manipulations"
ISBN-10: 386489218X

Matthias Apitz schrieb am 23.12.2019 um 15:33:
> I've here a smaller problem of our porting from Sybase/Oracle/Informix
> code to PostgreSQL; the code reads for the mentioned DBS:
>
>
> #ifdef DBSORA
>     EXEC SQL DECLARE land_cursor CURSOR FOR
>         SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2,
>             karenz3, land.wkz, webez, we, kurs, land.del
>         FROM   land, devisen
>         WHERE  land.wkz = devisen.wkz (+) AND land.brgroup = devisen.brgroup (+) AND land.brgroup =
:brgroupHost_for_helpland_cursor
>         ORDER  BY stammprio, landbez;
> #endif
>
> #ifdef DBSSYB
>     EXEC SQL DECLARE land_cursor CURSOR FOR
>         SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2,
>             karenz3, land.wkz, webez, we, kurs, land.del
>         FROM   land, devisen
>         WHERE  land.wkz *= devisen.wkz AND land.brgroup *= devisen.brgroup AND land.brgroup =
:brgroupHost_for_helpland_cursor
>         ORDER  BY stammprio, landbez;
> #endif
>
> (the code for DBSPOS was just copied from Sybase). It compiles fine but
> raises on execution en error about operator '*=' is not supported...

T-SQL (Sybase and SQL Server) uses *= for outer joins, just as Oracle uses (+)

Haven't used either of those outdated operators in decades, but I think the equivalent would be:

FROM land
   LEFT JOINdevisen
     on land.wkz = devisen.wkz
    AND land.brgroup = devisen.brgroup
    AND land.brgroup = :brgroupHost_for_helpland_cursor

> On 23 Dec 2019, at 15:33, Matthias Apitz <guru@unixarea.de> wrote:
>
> #ifdef DBSPOS
>     EXEC SQL DECLARE land_cursor CURSOR FOR
>         SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2,
>             karenz3, land.wkz, webez, we, kurs, land.del
>         FROM   land

         LEFT JOIN devisen ON land.wkz = devisen.wkz AND land.brgroup = devisen.brgroup

>         WHERE  land.brgroup = :brgroupHost_for_helpland_cursor
>         ORDER  BY stammprio, landbez;
> #endif

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.