-
Kris Kringle the Database – What a Great GROUP
Often what starts off as a simple aggregation… SQL> select channel_id, promo_id, sum(amount_sold) tot 2 from sh.sales 3 group by channel_id, promo_id 4 order by 1,2; CHANNEL_ID PROMO_ID TOT ———- ———- ———- 2 350 548779.15 2 999 25797563.2 3 350 1329476.49 3 351 1208839.02 3 999 55336945.1 4 350 321125.26 4 351 15664.24 4 999… Read more
-
Kris Kringle the Database! What DAY is it?
This one catches me out more often than I’d like to admit. It all starts off easy – I take a look at what day of the week it is. SQL> select to_char(sysdate,’DAY’) from dual; TO_CHAR(SYSDATE,’DAY’) ———————————— WEDNESDAY No problem. Now I’ll build some code logic around that query in order to run certain tasks… Read more
-
Kris Kringle the Database – Friday RANT
Ever seen code like this? select * from EMP where 1=1 and sal > 10 and dept_no = 20 You would probably wonder: Why is there a “WHERE 1=1” predicate? Surely that does nothing? And you would be absolutely right…IT DOES NOTHING. I should clarify – it does nothing, if you were referring to the… Read more
-
Kris Kringle the Database – Taking the LEAP
What is a quick way of working out if a year happens to be a leap year? Here’s my first attempt SQL> create or replace 2 function f1(y number) return boolean is 3 x date; 4 begin 5 x := to_date(‘2902’||y,’ddmmyyyy’); 6 return true; 7 exception 8 when others then return false; 9 end; 10… Read more
-
Kris Kringle the Database – No Fault in DEFAULT
A lot of people are unaware that when you have a column with a DEFAULT attached to it, you do not need to go hunting around in the data dictionary to get access to it. For example, let me start with this simple people table where the default for ACTIVE is “YES” SQL> create table… Read more
-
Kris Kringle the Database – Know your ABC
If you open up the SQL reference manual and scroll down through the commands… …you will start at ADMINISTER and eventually you will land on UPDATE, the last SQL statement in the documentation. But here’s a pop quiz for you. Is there a command that comes after UPDATE that didn’t make it into the docs?… Read more
-
Kris Kringle the Database – Get the HINT
One of my favourite quotes from Maria Colgan is: “Optimizer hints should only be used with extreme care” Let me give you an example of why that is the case. As anyone would know, one of the key aims of anyone building an application running on a database is the minimize risk. We have things… Read more
-
Kris Kringle the Database – Getting the BAND together
Ever heard of a BAND join? It’s not actually new, it has been around since 12.2. Jonathan Lewis wrote up a nice post on it was back in 2017 but in a nutshell MERGE join done without a band join SQL> select 2 e1.last_name|| ‘ has salary roughly between ‘|| e2.last_name comparison 3 from 4… Read more
-
Kris Kringle the Database – Sequence Values
It is commonplace to use a sequence to populate a surrogate key in your table. Whether its a manually created sequence or an IDENTITY column (which is still backed by a sequence), most of us know that we can use the RETURNING clause to collect the value that was used on INSERT. SQL> insert into… Read more
-
Kris Kringle the Database – Why NVL is smart
NVL smarts A common issue for reports and any query where users can pass parameters is how to handle the “optional” parameter. Here’s a typical example: “Table CP can be queried where column X is equal to optional parameter P.” Should we code: select * from CP where ( X = 😛 or 😛 is… Read more
-
Kris Kringle the Database – A Foreign Foreign Key Option
You’re probably familiar with the ON DELETE CASCADE option in a foreign key. For example, if I have DEPT and EMP as per below SQL> select * from dept; DEPTNO DNAME LOC ———- ————– ————- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from emp order by… Read more
-
Kris Kringle the Database – The BEST error
Normally, whenever you get an error it can be cause for concern. But not this one. Let me present to you – the best error message in the entire Oracle documentation library! Tragically, someone evidently didn’t like this little bit of humour because it is now missing from the more recent documentation references Bah humbug… Read more