SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Oracle select columns on condition

    Is it possible (in Oracle) to select a column from a table only when some condition is met?

    For example, I have a SELECT query that selects columns col1, col2, col3 and I want to select col4 only if some condition is met?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    well, since a query cannot select a variable number of columns, you will either have to retrieve col4 anyway and only use it if the condition is met, or run two different queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes that makes sense. I used CASE WHEN after all and when the condition is not met I set the column to null:

    Code:
    ...
    CASE WHEN (SELECT COUNT(*) FROM table2 t2 WHERE t2.id = t1.id) >= 2 THEN (SELECT MIN(col4) FROM table3 t3 WHERE t3.id = t1.id) ELSE null END column4
    ...

  4. #4
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All those nested selects look horribly inefficient. It's usually preferrable to use joins instead. I would left join to the other tables.

    Something like:
    Code:
    select
      col1, col2, col3,
      case
        when t2.id is not null min(t3.col4)
        else null
      end col4
    from t1
      left join t2 on t1.id = t2.id and count(t2.*) >= 2
      left join t3 on t1.id = t3.id
    That's the best I could make sense of your obfuscated sample query but the general approach is probably what you're after.

    Cheers,
    D.

  5. #5
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Joins are more efficient but it's easier for me to use sub selects when writing a query. It seems more logical to me and I have to think less than with joins.

    But in real applications I prefer joins of course

  6. #6
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to see the whole query here it is (but I warned you ):

    Code:
    SELECT 
    DISTINCT a.typ
    , (SELECT COUNT(*) FROM Auta WHERE typ = a.typ) pocet_aut
    , (SELECT AVG(poplatok_denny) FROM Auta WHERE typ = a.typ) priemerny_denny_poplatok
    , CASE WHEN (SELECT COUNT(*) FROM Auta WHERE typ = a.typ) >= 2 THEN (SELECT MIN(poplatok_denny) FROM Auta WHERE typ = a.typ) ELSE null END minimalny_denny_poplatok
    , CASE WHEN (SELECT COUNT(*) FROM Auta WHERE typ = a.typ) >= 2 THEN (SELECT MAX(poplatok_denny) FROM Auta WHERE typ = a.typ) ELSE null END maximalny_denny_poplatok 
    FROM Auta a;

  7. #7
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, it's all the same table you're working on? That makes it a lot simpler then. I've never worked with Oracle before but I can't see why this wouldn't work:
    Code:
    select
    	typ,
    	count(*) pocet_aut,
    	avg(poplatok_denny) priemerny_denny_poplatok,
    	case
    		when count(*) > 1 then min(poplatok_denny)
    		else null
    	end minimalny_denny_poplatok,
    	case
    		when count(*) > 1 then max(poplatok_denny)
    		else null
    	end maximalny_denny_poplatok,
    from
    	Auta
    group by
    	typ

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    writing that type of repetitive correlated subquery has the potential of being ~seriously~ inefficient

    try this --
    Code:
    SELECT DISTINCT a.typ
         , m.pocet_aut
         , m.priemerny_denny_poplatok
         , CASE WHEN m.pocet_aut >= 2 
                THEN m.minimalny_denny_poplatok 
                ELSE null END   minimalny_denny_poplatok
         , CASE WHEN m.pocet_aut >= 2 
                THEN m.maximalny_denny_poplatok 
                ELSE null END   maximalny_denny_poplatok
      FROM Auta a
    INNER
      JOIN ( SELECT type
                  , COUNT(*)   pocet_aut
                  , AVG(poplatok_denny)   priemerny_denny_poplatok
                  , MIN(poplatok_denny)   minimalny_denny_poplatok
                  , MAX(poplatok_denny)   maximalny_denny_poplatok
               FROM Auta 
             GROUP
                 BY typ )   m
        ON m.typ = a.typ
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure why you need any join at all.

    Cheers,
    D.

  10. #10
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by disgracian View Post
    Oh, it's all the same table you're working on? That makes it a lot simpler then. I've never worked with Oracle before but I can't see why this wouldn't work:
    Code:
    select
    	typ,
    	count(*) pocet_aut,
    	avg(poplatok_denny) priemerny_denny_poplatok,
    	case
    		when count(*) > 1 then min(poplatok_denny)
    		else null
    	end minimalny_denny_poplatok,
    	case
    		when count(*) > 1 then max(poplatok_denny)
    		else null
    	end maximalny_denny_poplatok,
    from
    	Auta
    group by
    	typ;
    Hmm, this gives me this error (and I added semicolon on the end of the query, in Oracle semicolons are required to end each query):

    Code:
    ORA-00936: missing expression
    But it's not important. This is just an assigment for my Oracle class and the query I made works good.

  11. #11
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    writing that type of repetitive correlated subquery has the potential of being ~seriously~ inefficient

    try this --
    Code:
    SELECT DISTINCT a.typ
         , m.pocet_aut
         , m.priemerny_denny_poplatok
         , CASE WHEN m.pocet_aut >= 2 
                THEN m.minimalny_denny_poplatok 
                ELSE null END   minimalny_denny_poplatok
         , CASE WHEN m.pocet_aut >= 2 
                THEN m.maximalny_denny_poplatok 
                ELSE null END   maximalny_denny_poplatok
      FROM Auta a
    INNER
      JOIN ( SELECT type
                  , COUNT(*)   pocet_aut
                  , AVG(poplatok_denny)   priemerny_denny_poplatok
                  , MIN(poplatok_denny)   minimalny_denny_poplatok
                  , MAX(poplatok_denny)   maximalny_denny_poplatok
               FROM Auta 
             GROUP
                 BY typ )   m
        ON m.typ = a.typ
    This works the same as my query

    There's just one typo (type instead of typ).

  12. #12
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason my query failed is because there was a comma after the last column. Remove that, add the semicolon and you should be right to go. I would be interested in the performance difference between my query and r937's with the inner join. There probably won't be any perceptible difference unless the data set is massive, but you can check out the execution plan and/or statistics.

    Cheers,
    D.

  13. #13
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Performance:

    My query: 0.02s, 0.01s, 0.02s, 0.02s, 0.02s

    rudy's query: 0.02s, 0.02s, 0.02s, 0.02s, 0.01s

    disgracian's query: 0.01s, 0.01s, 0.00s, 0.02s, 0.02s



    EDIT: I have run each query 5 times.

    EDIT2: Sorry, now it's correct.

  14. #14
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seem's like yours is the fastest, disgracian

  15. #15
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting thing is that all three queries return the rows in different order. But they all return the same rows.

  16. #16
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would say that the performance results are inconclusive overall. You would either need 5-10 times more data before performance started to really diverge.

    That is quite interesting about the order, and I'm not sure why that would happen. You can simply use an ORDER BY clause on any of the SELECT fields if you want to enforce a particular order.

    Cheers,
    D.

  17. #17
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The different order is probably caused by different programming of subselects and joins in MySQL. Subselects must be using different algorithm.


Bookmarks

Posting Permissions

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