Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Newbie SQL Insert Question

    I copy/manipulate data from TABLE1 to TABLE2.

    I typically use :
    INSERT INTO TABLE2
    (FIELD1, FIELD2)
    SELECT FIELDA, FIELDB FROM TABLE1 WHERE....

    How do I specify that if the FIELDA is null, I want to substitute "ABC" ?

    Does SQL have an Intermediate IF function?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    INSERT INTO TABLE2
    (FIELD1, FIELD2)
    SELECT decode(FIELDA,null,'ABC',FIELDA), FIELDB FROM TABLE1 WHERE....

    HTH
    Gregg

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Or more simply:

    INSERT INTO TABLE2
    (FIELD1, FIELD2)
    SELECT NVL(FIELDA,'ABC'), FIELDB FROM TABLE1 WHERE....

    You can write "COALESCE" instead of "NVL" if you prefer - COALESCE is the ANSI standard.

    Now I'm left wondering: what is an "Intermediate IF function"?

  4. #4
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Thanks everyone.

    re: Intermediate IF
    Shows my roots - and possibly my age.
    Other, less robust databases have an Intermediate IF statement that can be used "inline".
    IIF(Condition, If true action, if false action)

    IIF can also be used in excel type sheets as well.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    In PL/SQL
    if condition is true
    then
    execute true condition statements
    else
    execute false condition statement
    end if;

    or use CASE function
    case when condition = true
    then true condition
    else false condition
    end;

    HTH
    Gregg

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by jer99
    re: Intermediate IF
    Shows my roots - and possibly my age.
    Other, less robust databases have an Intermediate IF statement that can be used "inline".
    IIF(Condition, If true action, if false action)

    IIF can also be used in excel type sheets as well.
    Ah, I have seen that before, but never knew what IIF stood for!

  7. #7
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Gregg,

    But I couldn't use the CASE or IF statements in the middle of the INSERT INTO/SELECT clause, could I?

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can use the case in the middle of a SQL statement

    insert into tablea (col1, col2)
    select
    col1_value,
    case when (select distinct(rl.gl_account_id) from receivable_line where invoice_id = r.invoice_id) like '26000.%.000'
    then (select sum(rl.amount) from receivable_line where invoice_id = r.invoice_id group by invoice_id)
    else 0 END
    from othertable;

    Gregg

  9. #9
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    maybe I'm showing my age, but I thought IIF was Immediate IF, not Intermediate.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Acronym finder says that, among other meanings (such as "Iraqi Intervention Forces" (?!?) or "Income Interest Fund"), IIF stands for "Immediate IF". "Intermediate" is never mentioned ...

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by jer99
    But I couldn't use the CASE or IF statements in the middle of the INSERT INTO/SELECT clause, could I?
    If only there was a free online list of Oracle SQL commands.

Posting Permissions

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