{"id":535,"date":"2011-09-14T05:07:28","date_gmt":"2011-09-14T05:07:28","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=535"},"modified":"2011-09-14T05:07:28","modified_gmt":"2011-09-14T04:07:28","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=535","title":{"rendered":"SQL things to remember"},"content":{"rendered":"

During my work lately I found out some things about SQL (or is it Oracle<\/a> 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 \ud83d\ude09<\/p>\n

\n\n<\/div>\n

Subquery on multiple columns<\/h2>\n

I needed to query a table but the records needed to be available in a subquery. Normally I would write a statement like this:
\n<\/p>\n

\r\nSELECT a.* \r\n  FROM a \r\n WHERE a.field in (SELECT b.field \r\n                     FROM b \r\n                    WHERE ...)\r\n<\/pre>\n
<\/font>\n<\/p>\n
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:
\n<\/p>\n
\r\nSELECT a.* \r\n  FROM a \r\n WHERE EXISTS (SELECT 1 \r\n                 FROM b \r\n                WHERE b.field1 = a.field1\r\n                  AND b.field2 = a.field2)\r\n<\/pre>\n
<\/font>\n<\/p>\n
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).
\n  
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. <\/p>\n
<\/p>\n
\r\nSELECT a.* \r\n  FROM a \r\n WHERE (a.field1, a.field2) IN (SELECT b.field1, b.field2\r\n                                  FROM b)\r\n<\/pre>\n
<\/font>\n<\/p>\n
This query returned results within seconds.<\/p>\n
CASE as parameter<\/h2>\n
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:
\n  
010Tr7 must become 010Tr08
\n  
010Tr8 must become 010Tr09
\n  
010Tr9 must become 010Tr10.<\/p>\n
First I thought to nest a lot of replace statements like this:
\n<\/p>\n
\r\nSELECT DISTINCT REPLACE( \r\n                  REPLACE( \r\n                    REPLACE( \r\n                      REPLACE( \r\n                        REPLACE( \r\n                          REPLACE( \r\n                            REPLACE( \r\n                              t.column \r\n                            ,'Tr9','Tr10') \r\n                          ,'Tr8','Tr09') \r\n                        ,'Tr7','Tr08') \r\n                      ,'Tr6','Tr07') \r\n                    ,'Tr5','Tr06') \r\n                  ,'Tr4','Tr05') \r\n                ,'Tr3','Tr04') \r\n                ...\r\n<\/pre>\n
<\/font>\n<\/p>\n
This looked ugly (although it does exactly what was needed). So I tried to use the CASE EXPRESSION on the query like this:
\n<\/p>\n
\r\nREPLACE(t.column \r\n       ,CASE \r\n          WHEN t.column LIKE '%Tr9' THEN 'Tr9' \r\n          WHEN t.column LIKE '%Tr8' THEN 'Tr8' \r\n          WHEN t.column LIKE '%Tr7' THEN 'Tr7' \r\n          WHEN t.column LIKE '%Tr6' THEN 'Tr6' \r\n          WHEN t.column LIKE '%Tr5' THEN 'Tr5' \r\n          WHEN t.column LIKE '%Tr4' THEN 'Tr4' \r\n          WHEN t.column LIKE '%Tr3' THEN 'Tr3' \r\n          WHEN t.column LIKE '%Tr2' THEN 'Tr2' \r\n          WHEN t.column LIKE '%Tr1' THEN 'Tr1' \r\n        END \r\n       ,CASE \r\n          WHEN t.column LIKE '%Tr9' THEN 'Tr10' \r\n          WHEN t.column LIKE '%Tr8' THEN 'Tr09' \r\n          WHEN t.column LIKE '%Tr7' THEN 'Tr08' \r\n          WHEN t.column LIKE '%Tr6' THEN 'Tr07' \r\n          WHEN t.column LIKE '%Tr5' THEN 'Tr06' \r\n          WHEN t.column LIKE '%Tr4' THEN 'Tr05' \r\n          WHEN t.column LIKE '%Tr3' THEN 'Tr04' \r\n          WHEN t.column LIKE '%Tr2' THEN 'Tr03' \r\n          WHEN t.column LIKE '%Tr1' THEN 'Tr02' \r\n        END)\r\n<\/pre>\n
<\/font>\n<\/p>\n
This is much more verbose and much more work to type it all in, but I think it\u04f3 nice to see that CASE EXPRESSIONS can be used as parameters in other functions. Of course, you wouldn\u04f4 expect differently, but I nice to see it works.
\n  
By the way, the shortest query for this issue I came up with is a totally different approach: <\/p>\n
<\/p>\n
\r\nsubstr(t.naam,1,5) || trim(both from to_char(to_number(substr(t.naam,6))+1,'09'))\r\n<\/pre>\n
<\/font>\n<\/p>\n
(Note the trim function needed in the query. Check out Alex Nuijten<\/a>s blog<\/a> on why this is).<\/p>\n
Just a couple of things I learned lately.
\n  <\/p>\n","protected":false},"excerpt":{"rendered":"
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 \ud83d\ude09<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,9],"tags":[],"class_list":["post-535","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/535","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=535"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/535\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=535"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=535"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=535"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}