I am trying to use following conditions in my where clause but trying make sure that i ma using correct way.
Requirement: need to change, see below:
a. Program <> 35 and acct1 <> 1
b. Program = 30, 31 and acct1 =6
My Where clause is correct as below?
WHERE TYPE <> 35 AND ACCT1 <> 2
AND TYPE IN (30, 31) AND ACCT1 = 6
hi there is a logical issue here
acct1 = 6 means acct1 <> 1
so you can use acct1 = 6 only
no need for acct1 <> 1
unless acct1 in acct1 = 6 is different from acct1 in acct1 <> 1
Not sure by mean that but i think user wants the condition which i have mentioned so i am little confused if i am using both then i am not getting expected results.
Maybe you need an Or in between (just making a guess here), so either rule a. is true or rule b. is true, but even then it is not correct. Run the following sample data, the first 2 rows in the output are fine but the 3rd row contradicts rule b.(assuming you really need an Or condition); until rule a. says Type=35 instead of Type <> 35. I think you may need to revisit your requirement, then tweak this code to check if the result is what you exactly want. Also, un-comment the And clause and see the output as to why your requirement will not work with an And:
DECLARE @TBL TABLE(TYPE INT,ACCT1 INT)
INSERT INTO @TBL (TYPE, ACCT1)
WHERE (TYPE <> 35 AND ACCT1 <> 2)
--AND (TYPE IN (30, 31) AND ACCT1 = 6)
OR (TYPE IN (30, 31) AND ACCT1 = 6)
TYPE <> 35 AND ACCT1 <> 2 (should this be 1?) already includes TYPES = 30 and 31 and ACCT1 = 6. These are conflicting requirements...
Yes these are conflicting requirements, hopefully the OP runs this example to understand this conflict and rectifies the requirement.
I am agreed that requirements is conflicting.
Thanks everyone for your response.