DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: query "if"'s?

  1. #1
    Join Date
    Feb 2000
    Posts
    24
    Suppose I have a table:

    name type
    a 1
    a 2
    b 1
    c 2
    d null


    How can I select out the name, based on the fact If there is a type of 1 and 2, take the 2, if there's only a 1, take the 1, and if there's a null, make it a 1?

    I was thinking this:

    SELECT ....
    WHERE MAX(nvl(table.type, 1)) in (1,2)

    Is that the most efficient way?

  2. #2
    Jeya is offline Banned due to forum misuse
    Join Date
    May 2001
    Location
    *******
    Posts
    9

    Thumbs up

    Shoot it with DECODE function


    Regards
    Jeyachandran. P

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    select name, nvl(max(type),1) type from mytable group by name;


  4. #4
    Join Date
    May 2000
    Posts
    58
    There is a better function in Oracle 8i called CASE. It is like decode but more flexible and fast.

    select case
    if [condition] then ...
    if [condition] then ...
    ...
    end

  5. #5
    Join Date
    Apr 2001
    Posts
    17
    Sweet! I didn't know about the CASE function!

    Thanks Victoria!

Posting Permissions

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


Click Here to Expand Forum to Full Width