Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: Decode function in Update statement

    Hello everyone,

    I'm trying to write a query where I can update a pastdue_fees column based on a difference between return_dte and due_dte columns.

    I am using Oracle SQL. This is what I have so far for my decode function:

    SQL> SELECT
    2 DECODE(SIGN((return_dte - due_dte)*2),
    3 '-1', '0',
    4 '1', '12', 'Null')
    5 FROM book_trans;

    DECO
    ----
    Null
    12
    Null
    0

    So the logic is that if the sign is -1, the value in return_dte column should be 0; if it's +1 then it's 12 and everything else is Null.

    So now, I need to enter my decode function into the update statement to update the columns. However, I get error messages.

    The logic should be:
    UPDATE book_trans SET PastDue_fees = decode(expression)

    I've given it a couple of different tries with the following results:

    SQL> UPDATE book_trans
    2 SET pastdue_fees = SELECT
    3 DECODE(SIGN((return_dte - due_dte)*2),
    4 '-1', '0',
    5 '1', '12', 'Null')
    6 FROM book_trans;
    SET pastdue_fees = SELECT
    *
    ERROR at line 2:
    ORA-00936: missing expression


    SQL> UPDATE book_trans
    2 SET pastdue_fees =
    3 DECODE(SIGN((return_dte - due_dte)*2),
    4 '-1', '0',
    5 '1', '12', 'Null')
    6 FROM book_trans;
    FROM book_trans
    *
    ERROR at line 6:
    ORA-00933: SQL command not properly ended

    Any help or tips would be greatly appreciated as I've been taking SQL for about six weeks and not very proficient!

    Thanks!

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by aoetitan View Post
    The logic should be:
    UPDATE book_trans SET PastDue_fees = decode(expression)
    Right; so why do you not follow it and use FROM clause?

    By the way, the SIGN function returns numbers, not strings; you should compare it with numbers. Also, the result of DECODE statement is string. Do you really want to store 'Null' (note, it is different from NULL - no value) in PASTDUE_FEES? Without further description (at least the data types of all columns), it is impossible to suggest more.
    Code:
    UPDATE book_trans
    SET pastdue_fees =
     DECODE(SIGN((return_dte - due_dte)*2),
     -1, '0',
     1, '12', 'Null');

Posting Permissions

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