CODEWITHSUNDEEP
sqljoinplsqlright-join
tseres
tseres

Reputation: 37

changing from old style to new style joins

I have been trying to change my old style of SQL from cross to joins, but I can't manage to make it work.

Here is my code:

SELECT 'EFE' tipo,
    c.empnum,
    c.succlave,
    d.tipopago,
    d.tjcredito
FROM detcobros d,
    cobros c,
    masterbancos b,
    monedas m
WHERE     d.empnum = c.empnum
    AND d.succlave = c.succlave
    AND d.cobfolio = c.cobfolio
    AND d.PaisClave = b.PaisClave(+)
    AND d.bannum = b.bannum(+)
    AND d.monNum = m.monNum
    AND d.tipopago = 'EF'
    AND (   (c.status IN ('AU', 'US', 'UP'))
         OR (    c.status = 'CA'
             AND NVL (c.cortecaja, 0) <> NVL (c.cortecajacanc, 0)))
    AND c.empnum = 255
    AND c.succlave = 'CDCU'
    AND c.cortecaja = 3004

And here what I've tried:

SELECT 'EFE' tipo,
    c.empnum,
    c.succlave,
    d.tipopago,
    d.tjcredito
FROM detcobros d
    JOIN cobros c ON c.empnum   = d.empnum
    and c.succlave = d.succlave
    and c.cobfolio = d.cobfolio
    RIGHT JOIN masterbancos b ON b.PaisClave = d.PaisClave
    and  b.bannum    = d.bannum
    JOIN monedas m ON m.monNum  = d.monNum
WHERE d.tipopago = 'EF'
    AND (   (c.status IN ('AU', 'US', 'UP'))
         OR (    c.status = 'CA'
             AND NVL (c.cortecaja, 0) <> NVL (c.cortecajacanc, 0)))
    AND c.empnum = 255
    AND c.succlave = 'CDCU'
    AND c.cortecaja = 3004

The first query returns data but the second doesn't get any data.

Upvotes: 2

Views: 1611

Answers (3)

Andr&#233; M&#252;ller Pereira
Andr&#233; M&#252;ller Pereira

Reputation: 68

Try it with LEFT JOIN because the + operator is on the right.

syntax + operator

Upvotes: 3

William Robertson
William Robertson

Reputation: 16001

Here's what I got:

select 'EFE' tipo
     , c.empnum
     , c.succlave
     , d.tipopago
     , d.tjcredito
from   cobros c
       join detcobros d
            on   d.empnum = c.empnum
            and  d.succlave = c.succlave
            and  d.cobfolio = c.cobfolio
       join monedas m
            and  m.monnum = d.monnum
       left join masterbancos b
            and  b.paisclave = d.paisclave
            and  b.bannum = d.bannum
where  c.empnum = 255
and    c.succlave = 'CDCU'
and    c.cortecaja = 3004
and    d.tipopago = 'EF'
and    (   (c.status in ('AU', 'US', 'UP'))
        or (c.status = 'CA' and nvl(c.cortecaja, 0) <> nvl(c.cortecajacanc, 0)) );

My personal preference is to place the outer joins at the end of the from clause. Also the keywords outer and inner are redundant clutter so I never use them.

cobros seemed to be the logical starting place as it has the most predicates in the where clause. (The optimiser doesn't care, of course.)

A right join is just a left join written backwards. I never use them, I just arrange the tables the other (normal) way around.

The filtering conditions remain in the where clause (unless they apply to an outer join in which case they need to be included in the join - but there weren't any like that here). I'm sure this can be argued either way, but I like to see joins and filtering conditions listed separately.

I've also fixed your old-style uppercase for you ;)

Upvotes: 1

Shawn
Shawn

Reputation: 4786

As I mentioned above, think of your data in the sets of data that you want to get from each table.

Essentially, a SQL engine will process your query in the following basic order:

FROM (incl JOINs) -> CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

For your query, I would recommend moving all of your relevant conditions up into your JOINs to minimize the sets of data that will be JOINed together.

I would recommend:

SELECT 'EFE' AS tipo
    , c.empnum
    , c.succlave
    , d.tipopago
    , d.tjcredito
FROM detcobros d
INNER JOIN cobros c ON d.empnum = c.empnum
    AND d.succlave = c.succlave
    AND d.cobfolio = c.cobfolio
    AND c.empnum = 255
    AND c.succlave = 'CDCU'
    AND c.cortecaja = 3004
    AND (   
          c.status IN ('AU', 'US', 'UP')
          OR (
              c.status = 'CA'
              AND NVL (c.cortecaja, 0) <> NVL (c.cortecajacanc, 0)
          )
    )
LEFT OUTER JOIN masterbancos b ON d.PaisClave = b.PaisClave
    AND d.bannum = b.bannum
INNER JOIN monedas m ON d.monNum = m.monNum
WHERE d.tipopago = 'EF'

This does include some of my own personal preferences (like using AS in SELECT column aliases but not in table aliases. Also, I believe your query broke on JOIN monedas m ON m.monNum = d.monNum. You specified two different types of JOINs between the queries.

Upvotes: 1

Related Questions