During my work lately I found out some things about SQL (or is it Oracle SQL) that I didn’t know about and have really made my life easier. Well, maybe not easier, but at least it enriched my knowledge 😉
Subquery on multiple columns
I needed to query a table but the records needed to be available in a subquery. Normally I would write a statement like this:
SELECT a.* FROM a WHERE a.field in (SELECT b.field FROM b WHERE ...)
But in this case I needed to check 2 columns for equality. I thought, hey, no problem, I’ll just rewrite the query to use an EXISTS instead of an IN:
SELECT a.* FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.field1 = a.field1 AND b.field2 = a.field2)
That was a nice idea but when I ran this query, it didn’t return any results before I decided to kill the query (after 15 minutes).
There must be a way to query a subset and compare it to multiple columns. After a quick search on the internet I learned that is was possible to use the IN set operator on multiple columns.
SELECT a.* FROM a WHERE (a.field1, a.field2) IN (SELECT b.field1, b.field2 FROM b)
This query returned results within seconds.
CASE as parameter
I have a table in which I need to replace numeric values with their successor. The field is filled like this: xxxTry where it should become xxxTryy incrementing y by 1. Ok, this may look complicated, maybe an example will explain it:
010Tr7 must become 010Tr08
010Tr8 must become 010Tr09
010Tr9 must become 010Tr10.
First I thought to nest a lot of replace statements like this:
SELECT DISTINCT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( t.column ,'Tr9','Tr10') ,'Tr8','Tr09') ,'Tr7','Tr08') ,'Tr6','Tr07') ,'Tr5','Tr06') ,'Tr4','Tr05') ,'Tr3','Tr04') ...
This looked ugly (although it does exactly what was needed). So I tried to use the CASE EXPRESSION on the query like this:
REPLACE(t.column ,CASE WHEN t.column LIKE '%Tr9' THEN 'Tr9' WHEN t.column LIKE '%Tr8' THEN 'Tr8' WHEN t.column LIKE '%Tr7' THEN 'Tr7' WHEN t.column LIKE '%Tr6' THEN 'Tr6' WHEN t.column LIKE '%Tr5' THEN 'Tr5' WHEN t.column LIKE '%Tr4' THEN 'Tr4' WHEN t.column LIKE '%Tr3' THEN 'Tr3' WHEN t.column LIKE '%Tr2' THEN 'Tr2' WHEN t.column LIKE '%Tr1' THEN 'Tr1' END ,CASE WHEN t.column LIKE '%Tr9' THEN 'Tr10' WHEN t.column LIKE '%Tr8' THEN 'Tr09' WHEN t.column LIKE '%Tr7' THEN 'Tr08' WHEN t.column LIKE '%Tr6' THEN 'Tr07' WHEN t.column LIKE '%Tr5' THEN 'Tr06' WHEN t.column LIKE '%Tr4' THEN 'Tr05' WHEN t.column LIKE '%Tr3' THEN 'Tr04' WHEN t.column LIKE '%Tr2' THEN 'Tr03' WHEN t.column LIKE '%Tr1' THEN 'Tr02' END)
This is much more verbose and much more work to type it all in, but I think itӳ nice to see that CASE EXPRESSIONS can be used as parameters in other functions. Of course, you wouldnӴ expect differently, but I nice to see it works.
By the way, the shortest query for this issue I came up with is a totally different approach:
substr(t.naam,1,5) || trim(both from to_char(to_number(substr(t.naam,6))+1,'09'))
Just a couple of things I learned lately.