Is it possible to put conditional in query text?

[brobar]

I have a report with a query that pulls all documents based on a variable "createdBy" which signifies who created the document. The query looks like this:

SELECT * FROM documents WHERE createdBy = {createdBy}

Normally createdBy is going to be an integer that is a userID of the person who created the document. This works great so far but now we have realized there will be instances where some people will need to pull documents created by all users. If that is the case... instead of the variable createdBy being an integer such as 15 it would be the string "all".

I wondered if it was possible to put a conditional in the query so that we can evaluate createdBy and if it is equal to "all" we have one query otherwise we have another query.

For example if createdBy = all I would like the query passed to be:

SELECT * FROM documents

and if the createdBy != all the query passed would be:

SELECT * FROM documents WHERE createdBy = {createdBy}

Hopefully I explained that well enough to make sense.

Thanks for the assistance.

-BB

[Ivan]

Hello,

Please try to use the following expression, for example:

Code: Select all

SELECT * FROM documents {IIF(createdBy = "all", "WHERE createdBy = {" + createdBy + "}", "")}

Thank you.

[brobar]

Thank you very much for the example. I'll give that a try!

[brobar]

I had to tweak it just a little bit to get it to work but I'll throw it up here so it will hopefully assist others with the same problem.

This seems to work just fine:

Code: Select all

SELECT * FROM documents {IIF(createdBy=="all", "", "WHERE createdBy = "+createdBy+"")}

Thanks for the help. That worked out perfectly!

[Andrew]

Great! :biggrin: