[itsjimmy91]

Hey guys,

I feel like this should be simple, but I'm having trouble. First let me explain what I'm trying to do. I have three variables - JournalVisible, OnlineVisible, NJMVisible. If the respective variable is true, then I want to return rows in my query where those (either Journal, Online or NJM) exist.

For instance, if JournalVisible and OnlineVisible are true, then I want to return the rows where the JobType columns are either Journal or Online, but not NJM.

Here is what I have in my Where clause, but it is giving me an error....

and (CASE WHEN @JournalVisible = 1 THEN mj.JobTypeID = 10 END)
and (CASE WHEN @OnlineVisible = 1 THEN mj.JobTypeID = 9 END)
and (CASE WHEN @NJMVisible = 1 THEN mj.JobTypeID = 11 END)

Thanks.

[macosxnerd101]

Quote

Here is what I have in my Where clause, but it is giving me an error....

And they are...?

Just a suggestion, but the IN keyword sounds more like what you want to use.

WHERE column_name IN ('val1', 'val2', 'val3', ..., 'valn');

[Ionut]

Remove the assignment

THEN mj.JobTypeID = 10

should be

THEN mj.JobTypeID

You have to display the value in that column. If you want to display a value(like you put there 10), just write it

(CASE WHEN @JournalVisible = 1 THEN 10 END as JournalVisible)

I missread the post.
I'll think about it and return with a more competent response.

EDIT:
Maybe this will help you

(@JournalVisible = 0 or mj.JobTypeID = 10) and
(@OnlineVisible = 0 or mj.JobTypeID = 9) and
(@NJMVisible = 0 or mj.JobTypeID = 11 )
)

This post has been edited by Ionut: 12 July 2012 - 09:40 AM

[itsjimmy91]

Thank you both for the replies.. maybe I am misunderstanding or maybe I am not explaining what I am trying to do clearly.

Each row that I am looking through has a "JobType" column, which is a numerical value that represents a certain job type (Journal, Online, NJM). When the JournalVisible variable is TRUE, then I want to pull rows where the value in JobType is the numerical value for Journal (10).

The catch is that the three job type variables (JournalVisible, OnlineVisible, and NJMVisible) can have any combination of values. They could all be true, all false, one true, etc, etc...

Oops, did not see your second reply ionut. Going to try that out now... Thanks.

[Ionut]

It might not work because they are connected by And. Translated, it would be
JobId = 10 and JobId = 9 and JobID = 11(probably no record will fulfill this condition). In conclusion, these ones have to connected by OR.

To explain my example

(@JournalVisible = 0 or mj.JobTypeID = 10)

The first condition @JournalVisible = 0 : In the case of @JournalVisible = 0, that condition is true so the entire clause will be ignored. If @JournalVisible = 1 first part will be false, so the second one has to be taken into consideration and your query will be filtered after the number is JobTypeId.

[itsjimmy91]

Ionut, on 12 July 2012 - 10:49 AM, said:

It might not work because they are connected by And. Translated, it would be
JobId = 10 and JobId = 9 and JobID = 11(probably no record will fulfill this condition). In conclusion, these ones have to connected by OR.

To explain my example

(@JournalVisible = 0 or mj.JobTypeID = 10)

The first condition @JournalVisible = 0 : In the case of @JournalVisible = 0, that condition is true so the entire clause will be ignored. If @JournalVisible = 1 first part will be false, so the second one has to be taken into consideration and your query will be filtered after the number is JobTypeId.

You were correct in that connecting them with and's returned null. However, will this work with or's? What if Journal and Online are true, but NJM is false? It should pull the rows with both Journal and Online.

[Ionut]

Connecting them by OR assures you the evaluation of each condition. The one that is false (@Variable = 0) is ignored, the rest are added to final condition.