WHERE conditions in SQL (ORACLE)

People, I need a help.

I have the following code:

    ...   WHERE res.cod_ordem_producao = pla.cod_ordem_producao -- TO MAKE THE JOIN
  AND (to_char(:codordemproducao)='X' -- INITIAL VALUE FOR THE PARAMETER
       AND to_char(:grau)='X') -- INITIAL VALUE FOR THE PARAMETER
  OR ((to_char(res.cod_ordem_producao) = to_char(:codordemproducao)
       AND to_char(:grau)='X'
       AND res.COD_ESTADO_TUBO IN ('G',
                                   'W')
       AND res.DTH_ENTRADA BETWEEN :dth_inicio AND :dth_final )
      OR (to_char(pla.dsc_aco) = to_char(:grau)
          AND to_char(:codordemproducao)='X'
          AND res.COD_ESTADO_TUBO IN ('G',
                                      'W')
          AND res.DTH_ENTRADA BETWEEN :dth_inicio AND :dth_final ))
ORDER BY res.DTH_ENTRADA

We have 3 parameter here: DATE, CODORDEM, GRAU. The user can enter with;

1. ONLY the ‘date’ (without ‘GRAU’ and ‘CODORDEM’)
2. The ‘date’ AND the ‘grau’ (without ‘CODORDEM’)
3. The ‘date’ AND the ‘codordem’ (without ‘grau’)

Where is my mistake in the code?

My actual code does: 1 – the user can enter with date + grau + codordem 2 – the user can enter with date + grau

Answer

Move join condition to join clause. Add criterium for dates always. Then check if both of remaining parameters are null or only one of them and second is met.

...
from res
join pla on res.cod_ordem_producao = pla.cod_ordem_producao
where res.dth_entrada between :dth_inicio and :dth_final
  and 
    (
      (:codordemproducao is null and :grau is null)
      or (
           (
             (:codordemproducao is null and to_char(pla.dsc_aco) = to_char(:grau))
             or 
             (:grau is null and to_char(res.cod_ordem_producao) = to_char(:codordemproducao))
           )
           and res.cod_estado_tubo in ('G', 'W')
         )
    )

Replace :parameter is null with to_char(:parameter) = 'X' if they are sent as Xs. As I understood situation where you provide dates and both remaining parameters is forbidden.