brucewayne brucewayne - 1 month ago 10
SQL Question

selecting specific cards in with a double nested sql query sybase

SELECT card_num,
COUNT(merchant_code)
from e_transaction
where trans_date between '20161017 00:00' and '20161017 23:59'
and channelid='02'
and trans_code='P'
and card_num in (select card_num,
sum(trans_amount)
from e_transaction
where trans_date between '20161017 00:00' and '20161017 23:59'
and channelid='02'
and trans_code='T'
and card_num not in (select card_num
from e_transaction
where trans_date between '20160724 00:00' and '20161016 23:59')
group by card_num
having sum(trans_amount) > 100000)
group by card_num
having count (merchant_code) > 1


I keep getting a syntax error for this query but can't seem to find where the error is. I am trying to spool multiple card_num with same merchant_code for today with trans_amount greater than 100000 and not found between 20160724 and yesterday

error messages


[Incorrect syntax near ','] [Incorrect syntax near the keyword
'group']

Answer

After properly formatting the query it is easy to see the problem. In your first nested select you wrote:

and card_num in (select card_num, 
                        sum(trans_amount) 
                 from e_transaction 
                 ...)

The in is expecting a single column but your nested select is giving two. All you need to do is remove the sum(trans_amount) from the nested select as follows:

and card_num in (select card_num
                 from e_transaction 
                 ...)
Comments