6 Replies - 611 Views - Last Post: 12 July 2012 - 11:41 AM Rate Topic: -----

#1 itsjimmy91  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 77
  • Joined: 19-January 11

SQL Query Help

Posted 12 July 2012 - 09:20 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Query Help

#2 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10183
  • View blog
  • Posts: 37,596
  • Joined: 27-December 08

Re: SQL Query Help

Posted 12 July 2012 - 09:23 AM

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');


Was This Post Helpful? 0
  • +
  • -

#3 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: SQL Query Help

Posted 12 July 2012 - 09:24 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 itsjimmy91  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 77
  • Joined: 19-January 11

Re: SQL Query Help

Posted 12 July 2012 - 10:34 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: SQL Query Help

Posted 12 July 2012 - 10:49 AM

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.
Was This Post Helpful? 1
  • +
  • -

#6 itsjimmy91  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 77
  • Joined: 19-January 11

Re: SQL Query Help

Posted 12 July 2012 - 11:02 AM

View PostIonut, 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.
Was This Post Helpful? 0
  • +
  • -

#7 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: SQL Query Help

Posted 12 July 2012 - 11:41 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1