CODEWITHSUNDEEP
oracle-database
Francisco Javier Yanes Delgado
Francisco Javier Yanes Delgado

Reputation: 19

Sql Query trying use DECODE in Where ORACLE

This is my query

SELECT *
  FROM (SELECT   cexp.expediente.num_expe "Num. Expediente",
                 cexp.expediente.cod_tipo_expe Tipo,
                 cexp.expediente.nif NIF, cexp.expediente.interesado "Razon Social",
                 MAX (DECODE (a.descripcion,
                              'NUMERO_FACTURA', cexp.md_valores.valor,
                              NULL
                             )
                     ) "Numero Factura",
                 MAX (DECODE (a.descripcion,
                              'IMPORTE_FACTURA', cexp.md_valores.valor,
                              NULL
                             )
                     ) "Importe bruto",
                 MAX (DECODE (a.descripcion,
                              'IMPORTE_NETO', cexp.md_valores.valor,
                              NULL
                             )
                     ) "Importe neto",
                 MAX (DECODE (a.descripcion,
                              'FECHA_PRESENTACION_FACTURA', cexp.md_valores.valor,
                              NULL
                             )
                     ) "Fecha recepcion"
            FROM cexp.md_propiedades a

                 RIGHT JOIN
                 (cexp.expediente RIGHT JOIN cexp.md_valores
                 ON cexp.expediente.num_expe = cexp.md_valores.num_expe)
                 ON (a.propiedad = md_valores.propiedad)
               AND (a.cod_tipo_expe = md_valores.cod_tipo_expe) 
           WHERE (cexp.expediente.cod_tipo_acto = '12EO' AND (cexp.expediente.cod_tipo_expe='CM11' OR cexp.expediente.cod_tipo_expe='FA11' OR cexp.expediente.cod_tipo_expe='CM08' OR cexp.expediente.cod_tipo_expe='FA09'))
        GROUP BY cexp.expediente.num_expe,
                 cexp.expediente.interesado,
                 cexp.expediente.nif,
                 cexp.expediente.cod_tipo_expe)

When i use this code, i haven't problem. This is the result: The Result

But now i need include in the WHERE clause "Fecha recepcion" > '01/01/2016' AND "Fecha recepcion" < '06/06/2016'.

Can help me with this ?

Upvotes: 0

Views: 129

Answers (2)

Aleksej
Aleksej

Reputation: 22949

It only seems that you need to add your condition to external query, without relying on implicit conversions:

WHERE "Fecha recepcion" > to_date('01/01/2016', 'DD/MM/YYYY')
  AND "Fecha recepcion" < to_date('06/06/2016', 'DD/MM/YYYY')

Upvotes: 0

sagi
sagi

Reputation: 40481

My guess is that you are trying to add it in the wrong place, it being calculated in the inner select, therefore - you need to wrap it with another select to be able to use it in the WHERE clause (at least with Oracle) :

SELECT * FROM (
..... Your Query Here
)
WHERE "Fecha recepcion" between '01/01/2016' and '06/06/2016'.

Al though this column doesn't seem like date at all, and to use date range functions this should be formatted as a date using TO_DATE() so:

WHERE "Fecha recepcion" between to_date('01/01/2016','dd/mm/yyyy') and to_date('06/06/2016','dd/mm/yyyy').

Upvotes: 2

Related Questions