Detect unset $VARIABLE in query for trac report
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 开发者_如何学CAS __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.)
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
精彩评论