SQL operator '*='

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL operator '*='

Matthias Apitz

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, ‚úČ [hidden email], 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


Reply | Threaded
Open this post in threaded view
|

Re: SQL operator '*='

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



Reply | Threaded
Open this post in threaded view
|

Re: SQL operator '*='

Alban Hertroys-4
In reply to this post by Matthias Apitz

> On 23 Dec 2019, at 15:33, Matthias Apitz <[hidden email]> 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.