SQL Report: Use a static string if variable is not passed

Felipe Lima

Hi Experts,

This is related to the SQL Report function with variables.

I'm not sure if I'm missing something here, but I'd like to pass a static string if no variable is informed by the user. To exemplify: When I pass WMS999, which is an existent project, it works fine, but, if my WHERE clause is readable by B1UP, the 'WMS%' should be retrieved if no input is supplied. See below:

SELECT
     PrjCode AS 'Project Code'     
   , PrjName AS 'Project Name'     
   , ValidFrom AS 'Valid From'     
FROM OPRJ
WHERE PrjCode LIKE COALESCE([%0], 'WMS%')

Passing an existent project:


Leaving variable as blank:


Any thoughts about it? Thanks in advance!

BR,
Felipe

Comentarios

2 comentarios

Fecha Votos
  • Comment author
    Keshava Best

    Try this:

    SELECT
    PrjCode AS 'Project Code', 
    PrjName AS 'Project Name', 
    ValidFrom AS 'Valid From'
    FROM OPRJ 
    WHERE PrjCode LIKE COALESCE(NULLIF([%0], ''), 'WMS%')
    0
  • Comment author
    Felipe Lima

    Hi Keshava,  

    I followed your suggestion, and it worked perfectly! Thank you!  

    BR,
    Felipe

    0

Iniciar sesión para dejar un comentario.