CODEWITHSUNDEEP
sqldb2db2-400
Carlos Landeras
Carlos Landeras

Reputation: 11063

As400 - Dynamic Sql where clause

Where are trying to avoid generating two sql cursors because we just need to change the WHERE clause to a dynamic one.

We have one cursor performing this WHERE clause:

WHERE TRIM ( USUARIO ) LIKE @USUARIO ) 
AND CAST ( M15 . FECLLEGADA AS DATE ) BETWEEN @FINI AND @FFIN 
AND M15 . ESTINBOX IN ( 'A' , 'P' )
AND URGENTE LIKE ( COALESCE ( @URGENTE , '' ) CONCAT '%' )

and another cursor with this WHERE CLAUSE:

WHERE 
    CAST ( M15 . FECLLEGADA AS DATE ) BETWEEN @FINI AND @FFIN 
    AND TRIM ( USUARIO ) LIKE @USUARIO 
    AND M15 . ESTINBOX LIKE ( COALESCE ( @ESTADO , '' ) CONCAT '%' )
    AND URGENTE LIKE ( COALESCE ( @URGENTE , '' ) CONCAT '%' )

Is there any way to perform this where clause in just one statement:¿

This is what I've tried: 

WHERE 
 CASE 
      WHEN @ESTADO='PR' then ESTINBOX IN ('A','P')
      ELSE M15.ESTINBOX(COALESCE (  @ESTADO , '' )    CONCAT '%' ) 
 END

Stored procedure is not compiling. If this is posible How could I append to the CASE WHEN additional "AND" clauses

Upvotes: 1

Views: 1115

Answers (1)

mustaccio
mustaccio

Reputation: 19011

Something like this, may be?

... WHERE TRIM ( USUARIO ) LIKE @USUARIO 
      AND CAST ( M15.FECLLEGADA AS DATE ) BETWEEN @FINI AND @FFIN 
      AND URGENTE LIKE ( COALESCE ( @URGENTE , '' ) CONCAT '%' ) 
      AND ( 
         (@ESTADO = 'PR' AND M15.ESTINBOX IN ( 'A' , 'P' ))
         OR 
         (@ESTADO != 'PR' AND M15.ESTINBOX LIKE COALESCE ( @ESTADO , '' ) CONCAT '%' 
      )

Upvotes: 3

Related Questions