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

Felipe Lima

• January 20, 2025 18:28

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

Vote

0

• 

  Keshava Best

  • January 23, 2025 17:02

  Try this:
  
  

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

  Felipe Lima

  • January 28, 2025 09:15

  Hi Keshava,  

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

  BR,
  Felipe

Please sign in to leave a comment.