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?

Yes that makes sense. I used CASE WHEN after all and when the condition is not met I set the column to null:

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

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:

select col1, col2, col3, 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,

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

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;

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:

select
	count(*) pocet_aut,
	avg(poplatok_denny) priemerny_denny_poplatok,
		when count(*) > 1 then min(poplatok_denny)
		else null
	end minimalny_denny_poplatok,
		when count(*) > 1 then max(poplatok_denny)
		else null
	end maximalny_denny_poplatok,
group by
              
writing that type of repetitive correlated subquery has the potential of being ~seriously~ inefficient
try this –
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
 disgracian:
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:
select
	count(*) pocet_aut,
	avg(poplatok_denny) priemerny_denny_poplatok,
		when count(*) > 1 then min(poplatok_denny)
		else null
	end minimalny_denny_poplatok,
		when count(*) > 1 then max(poplatok_denny)
		else null
	end maximalny_denny_poplatok,
group by
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):
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.
 r937:
writing that type of repetitive correlated subquery has the potential of being ~seriously~ inefficient
try this –
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).
              
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,

              
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.
              
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,