CODEWITHSUNDEEP
sqloracle-database
Revious
Revious

Reputation: 8146

Oracle: Mixing NVL with Outer Join

I've got the following query.

The only "left" table is the one with the alias "o".

I want to specify the following. How can I do? Should I use a WITH temp construct?

AND (   NVL (domb.DOMB_CONTO_CORRENTE, ' ') != o.campo43
OR NVL (abi.abi_descrizione, ' ') != o.campo41
OR NVL (cab.cab_descrizione, ' ') != o.campo42)

Here's the complete statement:

  SELECT /*+ parallel(o 64) */
        o.stato, COUNT (1)
    FROM CONF_RAGGRUPPAMENTI_FORN rgf,
         CRD_RID_REL_DOMICILIAZIONE crrd,
         CRD_DOMICILIAZIONI domb,
         uff_abi abi,
         uff_abi_cab cab,
         CONTO_CLIENTE_T809 o,
         eni_flussi_hub c,
         eni_monitor mon
   WHERE     1 = 1
         --RGF - OUT
         AND rgf.RGF_CODICE_RAGGRUPPAMENTO(+) = o.campo1
         --Join tra OUT e la ENI_FLUSSI_HUB
         AND o.id_messaggio = c.flh_id_messaggio(+)
         AND o.d_pubblicazione = c.flh_data_elaborazione(+)
         --Join tra ENI_FLUSSI_HUB e ENI_MONITOR
         AND c.FLH_ID_MESSAGGIO = MON.MON_ID_MESSAGGIO(+)
         AND c.FLH_TIPO_PROCESSO_COD = MON.MON_COD_TP_PROCESSO(+)
         AND c.flh_flag_ann(+) = 'N'
         AND mon_flag_ann(+) = 'N'
         --Join da RGF a DOMB
         AND rgf.UITR_IDENT_TIPI_RAGGR_ID(+) = 'MP'
         AND rgf.RGF_RAGGRUPPAMENTO_FORN_ID = crrd.RGF_RAGGRUPPAMENTO_FORN_ID(+)
         AND crrd.DOMB_DOMICILIAZIONE_ID = domb.DOMB_DOMICILIAZIONE_ID(+)
         AND CRRD.CRRD_RID_REL_DOM_ID = crrd.crrd_storico_id
         AND CRRD.CRRD_FLAG_ANN (+) = 'N'
         AND domb.domb_flag_ann (+) = 'N'
         AND rgf.rgf_flag_ann(+) = 'N'
         --Join tra domb e abi e cab
         AND DOMB.ABI_ID = abi.ABI_ID(+)
         AND DOMB.CAB_ID = cab.CAB_ID(+)
         --Filtro sulle date
         AND o.d_pubblicazione BETWEEN TO_DATE ('06-apr-2013')
                                   AND TO_DATE ('14-apr-2013')
         --Solo i flussi che producono variazioni
         AND (   NVL (domb.DOMB_CONTO_CORRENTE, ' ') != o.campo43
              OR NVL (abi.abi_descrizione, ' ') != o.campo41
              OR NVL (cab.cab_descrizione, ' ') != o.campo42)
GROUP BY o.stato

Upvotes: 0

Views: 10932

Answers (2)

hol
hol

Reputation: 8423

You do not need to use the + on the NVLs because you establish the outer join already on the ID of domp, abi, and cab.

I agree with the others that using JOIN statement makes more readable SQL Selects.

Do you have any trouble with the query?

Upvotes: 1

WoMo
WoMo

Reputation: 7246

If you can, I would recommend rewriting your query in modern ANSI syntax. This makes the query not only more readable, but it's easier to apply predicates on the optional table in a clear manner. I've rewritten 'old' Oracle queries and it's usually a quick cut and paste job to move the join conditions from the WHERE clause to the FROM ... JOIN ... ON ... clause.

Then, any predicates that apply to the optional table are listed under the OUTER JOIN condition, not under the WHERE. For example (in a very rough example):

SELECT 
  mt.col1, 
  mt.col2, 
  ot.col3
  -- other columns ...
FROM main_table mt
LEFT OUTER JOIN optional_table ot ON mt.col1 = ot.col1 
  AND ot.col2 = 'N' 
  AND NVL (ot.some_column, 'x') != mt.col5
-- Now the where for the result set and main table
WHERE
 AND mt.col4 BETWEEN TO_DATE ('06-apr-2013') AND TO_DATE ('14-apr-2013')
-- Other conditions on the total result set.

I've found this easiest way to apply conditions to the optional table without excluding rows from the final set.

Upvotes: 2

Related Questions