risoknop — 2009-10-07T17:55:36-04:00 — #1
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?
r937 — 2009-10-07T17:59:36-04:00 — #2
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
risoknop — 2009-10-07T18:37:27-04:00 — #3
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
disgracian — 2009-10-07T19:43:48-04:00 — #4
All those nested selects look horribly inefficient. It's usually preferrable to use joins instead. I would left join to the other tables.
col1, col2, col3,
when t2.id is not null min(t3.col4)
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.
risoknop — 2009-10-07T19:59:09-04:00 — #5
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
risoknop — 2009-10-07T20:00:27-04:00 — #6
If you want to see the whole query here it is (but I warned you :D):
, (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;
disgracian — 2009-10-07T20:15:50-04:00 — #7
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:
when count(*) > 1 then min(poplatok_denny)
when count(*) > 1 then max(poplatok_denny)
r937 — 2009-10-07T20:17:39-04:00 — #8
writing that type of repetitive correlated subquery has the potential of being ~seriously~ inefficient
try this --
SELECT DISTINCT a.typ
, CASE WHEN m.pocet_aut >= 2
ELSE null END minimalny_denny_poplatok
, CASE WHEN m.pocet_aut >= 2
ELSE null END maximalny_denny_poplatok
FROM Auta a
JOIN ( SELECT type
, COUNT(*) pocet_aut
, AVG(poplatok_denny) priemerny_denny_poplatok
, MIN(poplatok_denny) minimalny_denny_poplatok
, MAX(poplatok_denny) maximalny_denny_poplatok
BY typ ) m
ON m.typ = a.typ
disgracian — 2009-10-07T20:28:56-04:00 — #9
I'm not sure why you need any join at all.
risoknop — 2009-10-08T05:10:57-04:00 — #10
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.
risoknop — 2009-10-08T05:24:14-04:00 — #11
This works the same as my query
There's just one typo (type instead of typ).
disgracian — 2009-10-08T06:10:33-04:00 — #12
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.
risoknop — 2009-10-08T06:38:18-04:00 — #13
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.
risoknop — 2009-10-08T06:43:56-04:00 — #14
Seem's like yours is the fastest, disgracian
risoknop — 2009-10-08T06:55:52-04:00 — #15
Interesting thing is that all three queries return the rows in different order. But they all return the same rows.
disgracian — 2009-10-09T19:03:59-04:00 — #16
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.
risoknop — 2009-10-09T19:59:48-04:00 — #17
The different order is probably caused by different programming of subselects and joins in MySQL. Subselects must be using different algorithm.