Results 1 to 2 of 2

Thread: case statements

  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Unanswered: case statements

    case statements in oracle
    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_numbe r, sum(transaction_amount)
    (case
    when sum(transaction_amount)
    < ‘0‘ and transaction_type =’D’ THEN ‘Negative Value’
    when sum(transaction_amount)
    > ‘0‘ and transaction_type =’C’ THEN ‘Positive Value’
    END)
    from imp_person join imp_account d on person_id = person_id
    JOIN imp_transaction i on d.account_number = i.account_number
    Group by last_name,account_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_number,card_type
    from imp_credit_card d
    join imp_transaction i on i.account_number = d.account_number
    where transaction_amount in
    (select transaction_amount
    from imp_transaction
    CASE
    WHEN transaction_amount =max(transaction_amount) and card_type = ‘A’ Then ‘High’
    WHEN transaction_amount =max(transaction_amount) and card_type = ‘B’ Then ‘High’
    WHEN transaction_amount =max(transaction_amount) and card_type = ‘C’ Then ‘High’
    END
    )



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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool No case?

    Here is something you can build on:
    Code:
    SELECT i.account_number, card_type
      FROM imp_credit_card d JOIN imp_transaction i
           ON i.account_number = d.account_number
     WHERE d.card_type IN ('A', 'B', 'C')
       AND transaction_amount = (SELECT MAX (transaction_amount)
                                   FROM imp_transaction j
                                  WHERE j.account_number = d.account_number);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •