Use of multiple "AND" statements

[dphilli1]

I'm brand spanking new at SQL - tutorials, internet searches, and the help menu aren't helping me with this.

Here's my code:
select distinct GLD_COMPANY,GLD_FISCAL_YEAR,GLD_FISCAL_PERIOD,
acc_acct, acc_sub_acct_level01,GLD_ACCT_KEY,GLD_AMOUNT

from DW_GL_TRANSACTIONS inner join dw_gl_account
on DW_GL_TRANSACTIONS.GLD_ACCT_KEY = dw_gl_account.ACC_ACCT_KEY

Where GLD_company between 4600 and 4620
and GLD_FISCAL_YEAR = 2004
and GLD_FISCAL_PERIOD = 0
and acc_acct < 400000
and (acc_acct <> 323000 and acc_sub_acct_level01 = 0001)

Basically, I need to select records where the (acc_acct) field value is less than 400000. But I don't want to include (acc_acct) records that have a value of 323000 *AND* the value of 0001 from the (acc_sub_acct_level01) field.

I was hoping that I'm using the wrong brackets to separate out this additional &quot;AND&quot; statement component, or not enough, etc.

Thanks in advance!

 

[JayKusch]

Try This ...

Where GLD_company between 4600 and 4620
and GLD_FISCAL_YEAR = 2004
and GLD_FISCAL_PERIOD = 0
and (acc_acct < 323000
OR acc_acct > 323000)
and acc_sub_acct_level01 = 0001


Thanks

J. Kusch

 

[JohnDTampaBay]

Does this do the trick for you?

select distinct GLD_COMPANY,GLD_FISCAL_YEAR,GLD_FISCAL_PERIOD,
acc_acct, acc_sub_acct_level01,GLD_ACCT_KEY,GLD_AMOUNT

from DW_GL_TRANSACTIONS inner join dw_gl_account
on DW_GL_TRANSACTIONS.GLD_ACCT_KEY = dw_gl_account.ACC_ACCT_KEY

Where GLD_company between 4600 and 4620
and GLD_FISCAL_YEAR = 2004
and GLD_FISCAL_PERIOD = 0
and acc_acct < 400000
and NOT (acc_acct <> 323000 and acc_sub_acct_level01 = 0001)



--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[skuhlman]

basically what your looking for are those that are

AND NOT (acc_acct = 323000 and acc_sub_acct_level01 = 0001)

when you move the NOT into the and statement, it switches to an or. you should be using

AND (acct_acct <> 323000 OR acc_sub_acct_level01 <> 0001)

this means that it will select it if the account is NOT 32300. it will select it if the sub account is not 0001. if an account meets one of the criteria but not the other, it's still selected since it's an OR statement. It will only exclude those records where both conditions are satisfied.

 

[JayKusch]

Also saw a possible issue ...

IS acc_sub_acct_level01 a Char/VarChar or is it an Integer??? If it is a VarChar you need to enclose it in a couple of ticks like this ...

Where GLD_company between 4600 and 4620
and GLD_FISCAL_YEAR = 2004
and GLD_FISCAL_PERIOD = 0
and (acc_acct < 323000
OR acc_acct > 323000)
and acc_sub_acct_level01 = '0001'


Thanks

J. Kusch

 

[dphilli1]

Thanks for all your help folks!!!! Your suggestions are GREATLY appreciated.

Angel seems to have it right with AND NOT, I'll need to perform some additional validation to be sure.


Thanks again, DP

 

[dphilli1]

After a little data validation Angel's method definitely works. I spent quite a while trying to figure this out this morning - thanks again.

DP

 

[JohnDTampaBay]

No problem. Glad to help.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.