SQL Query Issue

Experts,

The attached query works until the last line.  I'm trying to add a condition where the transaction type ID is 13 and the account type ID is 31, in addition to what is already there.  However, I lose the date logic on the WHERE clause when I add it.

In other words, without that last OR statment, I only get transactions date for 20090203.  But when I add that last line, I get transactions for all dates.

What am I misssing?
SELECT cu_acct_num, tr_trans_number, tr_eff_date, tr_amount, tr_Notes, c.acct_type_id, ct.trans_type_id ,tt.trans_code 
from customer_transaction ct join customer c on ct.customer_id = c.customer_id  
JOIN TRANSACTION_TYPE tt  ON tt.Trans_Type_ID=ct.Trans_Type_ID 
where tr_eff_date='20090203'  
AND ct.Trans_Type_ID IN (6,170,181,191,203,208,230,253,268,402)  
OR (ct.Trans_Type_ID = 13 AND c.acct_type_id <> 31)

Open in new window

LVL 1
NigelRocksAsked:
Who is Participating?
 
TextReportCommented:
I think your bracket aren't quite right.
Cheers, Andrew
SELECT cu_acct_num, tr_trans_number, tr_eff_date, tr_amount, tr_Notes, c.acct_type_id, ct.trans_type_id ,tt.trans_code 
from customer_transaction ct join customer c on ct.customer_id = c.customer_id  
JOIN TRANSACTION_TYPE tt  ON tt.Trans_Type_ID=ct.Trans_Type_ID 
where tr_eff_date='20090203'  
AND (   ct.Trans_Type_ID IN (6,170,181,191,203,208,230,253,268,402)  
     OR (ct.Trans_Type_ID = 13 AND c.acct_type_id <> 31)
    )

Open in new window

0
 
reb73Commented:
You have to enclose check for ct.Trans_Type_ID within extra set of brackets. Try -


SELECT cu_acct_num, tr_trans_number, tr_eff_date, tr_amount, tr_Notes, c.acct_type_id, ct.trans_type_id ,tt.trans_code 
from customer_transaction ct join customer c on ct.customer_id = c.customer_id  
JOIN TRANSACTION_TYPE tt  ON tt.Trans_Type_ID=ct.Trans_Type_ID 
where tr_eff_date='20090203'  
AND (ct.Trans_Type_ID IN (6,170,181,191,203,208,230,253,268,402)  OR (ct.Trans_Type_ID = 13 AND c.acct_type_id <> 31))

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.