Click here to Skip to main content
15,888,351 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.


XML
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
Comments
Mike Meinz 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 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ő 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 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ő 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.

1 solution

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900