Detect unset $VARIABLE in query for trac report

Question

I've kludged a trac report that does something like this:

SELECT status, 
   id AS ticket, summary, priority, keywords,  
   datetime(changetime/1000000, 'unixepoch') AS last_updated, 
   milestone AS __group__,
   reporter, owner
  FROM ticket t
  WHERE status NOT IN ('closed', 'resolved') AND milestone in ('$MILESTONE')
  ORDER by t.milestone ASC, p.value

However, if the user hasn't specified a value for $MILESTONE, I'd like to display the results for each of our three teams, grouped by team:

SELECT status, 
   id AS ticket, summary, priority, keywords,  
   datetime(changetime/1000000, 'unixepoch') AS last_updated, 
   milestone AS __group__,
   reporter, owner
  FROM ticket t
  WHERE status NOT IN ('closed', 'resolved') AND milestone in ('project1', 'project2', 'project3')
  ORDER by t.milestone ASC, p.value

How can I detect whether a variable is set or not in a sql query? Barring that, is there an easy way to set a default value for $TEAM to be displayed when the user first clicks to the report?

(Note: if there's not a way that's general across sql implementations, please untag this as "sql" or post a comment to that effect so I can do it.)

Answer 1

You should be able to use a CASE WHEN ELSE statement in the WHERE clause like this:

SELECT status, 
id AS ticket, summary, priority, keywords,  
datetime(changetime/1000000, 'unixepoch') AS last_updated, 
milestone AS __group__,
reporter, owner
FROM ticket t
WHERE status NOT IN ('closed', 'resolved') 
AND (CASE WHEN '$MILESTONE' != '' THEN (milestone='$MILESTONE') 
     ELSE (milestone in ('project1', 'project2', 'project3')) END)
ORDER by t.milestone ASC, p.value