Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
So my other Where parameters work fine, it's the line of code in Bold/Underlined that I can't figure out. That line of code I'm basically trying to say, "If ResponseID is not equal to 62 and QuestionID is not equal to 1888 then proceed". Anyone want to take a shot at how I can use those 2 parameters in a where clause. Thanks for any and all advice given, re-writing that line of code would be great.
 

BEGIN TRANSACTION;
 
            SELECT ROW_NUMBER() OVER (ORDER BY cat1DisplayORder, cat2DisplayORder,      QuestionOrder, ResponseOrder) AS Row, Question, Response FROM tblJobPromo j
            INNER JOIN dbo.tblJobProfile jp ON j.fkJobId = jp.fkJobId
            INNER JOIN dbo.view_PromonetProfileQuestionResponses pr ON pr.responseid= jp.fkProfileResponseId
            WHERE JobPromoId = @JobPromoId
            AND Response <> 'N/A' AND questionid NOT IN (37,38)
            AND ResponseID <> 62 AND QuestionID <> 1868
            ORDER BY cat1DisplayORder, cat2DisplayORder, QuestionOrder, ResponseOrder
 
        COMMIT TRANSACTION;
Posted 29-Jan-13 10:15am
JasonMacD1.5K
Comments
Mike Meinz at 29-Jan-13 15:21pm
   
Are you getting an error message?
If so, what?
If not, why are you saying that there is a problem with these two conditionals in the Where clause?
 
Is ResponseID in one of the tables? If so, which one?
 
Is QuestionID in one of the tables? If so, which one?
 
Why do you use QuestionID twice in the Where clause?
* questionid NOT IN (37,38)
* QuestionID <> 1868
 
Can either ResponseID or QuestionID have a NULL value? If so, you should use ISNULL().
 
Also, you don't need Begin Transaction and End Transaction since you are just reading the database and not changing anything.
JasonMacD at 29-Jan-13 15:32pm
   
both ResponseID and QuestionID are in dbo.view_PromonetProfileQuestionResponses neither are NOT NULL columns. I didnt put my try catch etc., or the end of tranasaction all that is there. What I'm trying to do is if these two conditions are met (Not Equal to) then display the output, if they (are equal to 62 and 1868) then there should be no data displayed.
Zoltán Zörgő at 29-Jan-13 15:43pm
   
But you have an other condition on questionid above. There you use not in. Try:
AND Response <> 'N/A' AND questionid NOT IN (37,38,1868)
AND ResponseID <> 62
JasonMacD at 29-Jan-13 15:48pm
   
Yeah you're right that's cleaner, Im still not getting the results I want, but thanks for trying to help.
Zoltán Zörgő at 29-Jan-13 15:59pm
   
Try doing step by step. First without where clause, than add conditions one by one. Check the result in every step! I have the feeling you have constructed a syntactically correct query, but not what you need.
Zoltán Zörgő at 29-Jan-13 15:27pm
   
Since it looks correct, the only thing I can think about, that the field names are ambiguous between the tables. Thus you will have to qualify them like tablename.fieldname as you did in the join on clause. But you should get an exception about that...
JasonMacD at 29-Jan-13 15:32pm
   
Sorry both are NOT NULL colums, they have to have value

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The conditions basically look just fine. With a quick look possible problems may be:
- you haven't used table aliases in front of field names. In order to avoid ambiquity, always define the table alias where the field is taken from
- if either of the columns may contain a NULL value, you have to take care of that separately. Null is never equal nor uneaqual to anything so this may cause undesired results
- the same applies even with your parameterised value
- if case sensitivity is used in the database 'N/A' is not equal to 'n/a' so that may cause problems.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.150302.1 | Last Updated 29 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100