case statements in oracle

[amitabh1987]

1.List the card holder’s name, account number, card number, and the total value of all transactions against each card. Remember that if the transaction type is a ‘D’, the transaction amount will be a negative value; if the transaction type is a ‘C’, the transaction amount will be a positive value. Order the output on the person’s last name, then by account and card number.


select last_name,first _name,d.account _number,i.card_ number, sum(transaction _amount)
(case
when sum(transaction _amount)
< ‘0‘ and transaction_typ e =’D’ THEN ‘Negative Value’
when sum(transaction _amount)
> ‘0‘ and transaction_typ e =’C’ THEN ‘Positive Value’
END)
from imp_person join imp_account d on person_id = person_id
JOIN imp_transaction i on d.account_numbe r = i.account_numbe r
Group by last_name,accou nt_number,card_ number

2.List the account number and card type (code only) for all accounts that have the highest debit transaction amount for each card type.
select i.account_numbe r,card_type
from imp_credit_card d
join imp_transaction i on i.account_numbe r = d.account_numbe r
where transaction_amo unt in

(select transaction_amo unt
from imp_transaction
CASE
WHEN transaction_amo unt =max(transactio n_amount) and card_type = ‘A’ Then ‘High’
WHEN transaction_amo unt =max(transactio n_amount) and card_type = ‘B’ Then ‘High’
WHEN transaction_amo unt =max(transactio n_amount) and card_type = ‘C’ Then ‘High’
END
)



i don't know how you do this case question. any help please ?

[debasisdas]

the following thread might be helpful to you

link

why are you comparing a number within quotes ?

[amitabh1987]

select last_name,first _name,d.account _number,i.card_ number
case sum(transaction _amount)
WHEN transaction_typ e ='D' THEN transaction_amo unt < 0
Else
transaction_typ e ='C' THEN transaction_amo unt > 0
END AS Total
from imp_person join imp_account d on person_id = person_id
JOIN imp_transaction i on d.account_numbe r = i.account_numbe r
Group by last_name,accou nt_number,card_ number


i am bit confused with total value and negative number, i don't know how do it

[sgxbytes]

(CASE
WHEN sum(transaction _amount)< 0 and transaction_typ e =’D’ THEN ‘Negative Value’
WHEN sum(transaction _amount)> 0 and transaction_typ e =’C’ THEN ‘Positive Value’
ELSE 0
END)