Turning maths into spelling with SQL

Awareness is fantastic.

I was aware that somehow in Oracle you could transform a number value into words. I googled a similar phrase and found a reliable location in seconds.

my sql*plus output:

SQL> column word format a10
SQL> /

    ROWNUM WORD
---------- ----------
         1 one
         2 two
         3 three
         4 four
         5 five

5 rows selected.

SQL> l
  1* select rownum, (to_char(to_date(rownum,'j'), 'jsp')) word from dual connect by level < 6
SQL>

I figure it would be a more hyperlink clickable option for displaying a small number of people. Might allow the mind to comprehend the number quicker, too. Neuroscientists?

I think it's also having important the awareness and quick access to trusted sites supporting your various hypotheses, therefore to round off this thought crumpet...

References:
I chose the AskTom result for the SQL, which I couldn't locate on the relevant documentation page.

The OTN thread I compared it to really started to nerd up on page 2/3...

Apex collections - small case study

Where do you want to hard-code your information?

Somewhere pieces of data need to be written down for reference. They could be in a table; in some variables; within the query itself... there would be quite a few possibilities, regardless of the scenario.

Consider this statement:

select * from my_table where my_col IN ('CODE_1', 'CODE_2', 'CODE_3')

If it where the SQL source from your Apex application, it's minimal coding on your part - but what if the list of available values changes? You'd need to send your application, or at least certain components through the change control process. At the moment that can be annoying.

We could move this code to a PL/SQL package, perhaps using nested tables. The code is simple enough, I'm sure we've all done something similar before.

First we define a table of records

TYPE sql_rec IS RECORD
  (col1  VARCHAR2(200));

TYPE nt_sql_vc200 IS TABLE OF sql_rec;

Then in this case a simple function to pipe the information back will suffice.

FUNCTION param_list
RETURN nt_sql_vc200 PIPELINED IS
  r1 sql_rec;
  r2 sql_rec;
  r3 sql_rec;
BEGIN
  r1.col1 := 'CODE_1';
  r2.col1 := 'CODE_2';
  r3.col1 := 'CODE_3';

  PIPE ROW(r1);
  PIPE ROW(r2);
  PIPE ROW(r3);

END param_list;

Now we have no hard-coded literals in our SQL source, and it's just like having a sub-query. If we need to change the code list, we can modify the PL/SQL package - a little smoother for deployment.

WHERE my_col IN 
  (SELECT col1
   FROM TABLE(my_pkg.param_list));

However, in the Apex environment it's still not the most elegant. The Apex team have provided us with a wheel called Apex Collections.

In using an Apex Collection, we still need some PL/SQL, but it's all made a little simpler.
First define a process that might be called when rendering the page

DECLARE
  PROCEDURE add_col(pc VARCHAR2) IS
  BEGIN
    apex_collection.add_member
      (p_collection_name => 'PARAM_LIST'
      ,p_c001            => pc);
  END add_col;
BEGIN
  apex_collection.create_or_truncate_collection
    (p_collection_name => 'PARAM_LIST');

  add_col('CODE_1');
  add_col('CODE_2');
  add_col('CODE_3');
END  build_params_collection;

Then modify the where clause a touch

WHERE my_col IN 
 (SELECT c001
  FROM apex_collections
  WHERE collection_name = 'PARAM_LIST');

There are plenty of reasons out there why you'd want to use Apex Collections - either check out the documentation linked above, or install the sample application and check out how it's done.

Here endeth the lesson.

If anyone has other ideas for this problem, either more elegant or more esoteric, I'm all ears!

Thursday Thought - Busy busy busy

Over the next month or so, my posts may slow down a touch.

One exciting reason involves this little device pictured. I won't let too much out of the bag just yet, but an opportunity presented itself and I just had to take it.

This means I need to start prioritising a few things.

I've been trying to do a technical post every Wednesday, and this has been assisted by a flurry of posts I wrote earlier in the year that I scheduled over the weeks, with the occasional scheduling mistake - a couple of posts per day. The scheduling interface could be better, but I won't complain.

Unfortunately, these pre-written posts are drying up, I have plenty of e-mails tagged ready to write about, but the task involving the pictured device will take priority. When I get ahead on that schedule, I'll make sure my presentations for the conferences this year are on track, then I'll write some more posts - because I'm enjoying it quite a lot, and I'm also finding my own blog quite useful as a self-reference. It seems the quickest way to find information is to search where you now exactly how/where to look!

Further down on the list, I also want to re-design my blog re-design, especially after following what that Jeff Smith has been up to recently. I would like to ease the clutter and modernise a little more.

Oh, and I have my regular day job, not like some people ;-)

I'll also probably be somewhat quiet in the social media arena for a while. Partially to limit the consumption of information and keep focus, and (fortunately for me) I'll be on staggered holidays over the next month.

I hope all of you aren't working too hard, and I'll see you on the other side.

Scott.

Oracle PIVOT

A common requirement for queries is to turn rows into columns, or the other way around.

In Excel, we can do this using TRANSPOSE, a bit of patience & know-how, and ctrl+shift+enter.

In Oracle, if we have aggregate data displaying months by row, the old way was to use a bunch of DECODEs (or similar)

SELECT t.name
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jan',1,0)) jan
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'feb',1,0)) feb
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'mar',1,0)) mar
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'apr',1,0)) apr
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'may',1,0)) may
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jun',1,0)) jun
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jul',1,0)) jul
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'aug',1,0)) aug
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'sep',1,0)) sep
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'oct',1,0)) oct
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'nov',1,0)) nov
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'dec',1,0)) dec
FROM events e, bookings b, resources r, resource_types t
WHERE e.event_no = b.event_no
AND r.code = b.resource_code
AND r.type_code = t.code
GROUP BY t.name;

NAME                 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
-------------------- --- --- --- --- --- --- --- --- --- --- --- ---
Catering               0   0   2   1   0   0   0   0   0   0   0   0 
Stationary             0   0   1   1   0   0   0   0   0   0   0   0 
Video equipment        0   0   1   1   1   0   0   0   1   0   0   0 
Audio equipment        0   0   0   0   0   0   0   0   0   1   0   0 
Computer equipment     0   0   1   0   0   0   0   0   0   0   0   0 
Locations              0   0   2   2   2   1   1   1   1   1   0   0 

6 rows selected 

Oracle 11g introduced pivot queries.

SELECT * FROM
 ( SELECT COUNT(*) c, t.name, TO_CHAR(start_date,'mon') mth
   FROM events e, bookings b, resources r, resource_types t
   WHERE e.event_no = b.event_no
   AND r.code = b.resource_code
   AND r.type_code = t.code
   GROUP BY t.name, to_char(start_date,'mon')
)
PIVOT
 (SUM(c) -- Add up all my counts
  FOR mth -- Transposing the months
    IN ('jan' as jan
             ,'feb','mar','apr','may','jun'
       ,'jul','aug','sep','oct','nov','dec')
);

NAME                 JAN 'feb' 'mar' 'apr' 'may' 'jun' 'jul' 'aug' 'sep' 'oct' 'nov' 'dec'
-------------------- --- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
Catering                           2     1                                                 
Stationary                         1     1                                                 
Video equipment                    1     1     1                       1                   
Audio equipment                                                              1             
Computer equipment                 1                                                       
Locations                          2     2     2     1     1     1     1     1             

6 rows selected 

Note line 12 where we can provide column aliases to the fresh output.

As with most esoteric SQL functions, there are quite a few good examples on the web that I'm not out to emulate - the prime purpose of this post was to help remind me what's going on.
That being said, Tim Hall and Arup Nanda have the most concise articles.

I particularly like Lucas Jellema's example linked by Arup using

select value
from
(
    (
        select
            'a' v1,
            'e' v2,
            'i' v3,
            'o' v4,
            'u' v5
        from dual
    )
    unpivot
    (
        value
        for value_type in
            (v1,v2,v3,v4,v5)
    )
)
/

I wonder if that might be an interesting catalyst in some future queries
Update August 2014 - yes it did! -> Unpivot

More fun with dates

Last week I had a post reminding us to consider the time component of our date data types.

Here I'm just listing some fun with dates, either from single row sql expressions or cool little queries.

select 
  sysdate today
 ,trunc(sysdate) midnight_today
 ,trunc(sysdate)+1/86400 one_second
 ,trunc(sysdate)+2/24 two_hours
 ,trunc(sysdate)+30/60/24 thirty_minutes
 ,trunc(sysdate) + interval '2:30' hour to minute two_hour_thirty
 ,trunc(sysdate,'mm') start_of_month
 ,trunc(sysdate,'yy') start_of_year
 ,add_months(trunc(sysdate,'yy'),6) financial_year
 ,extract(year from sysdate) year_as_number
 ,to_char(sysdate,'yyyy') year_as_varchar2
 ,(sysdate - add_months(sysdate,6)) year to month  interval_months
 ,(Sysdate-(sysdate+2+30/60/24)) day(9) to second interval_daysec
 ,to_date('2012','yyyy') start_of_month
 ,last_day(sysdate) end_of_month
 ,date '2012-02-28' + 1 leap_year
 ,date '2011-02-28' + 1 non_leap_year
 ,date '2012-02-28' + interval '1' day only_during_leap_year
 ,date '1582-10-4' + 1 gregorian_changeover
 ,to_date('01-01-4712bc','dd-mm-yyyybc') scaliger_start
 ,to_date(1,'j') easier_scaliger_start
 ,to_date('31-12-9999','dd-mm-yyyy') end_of_time
 ,to_date('01-01-98','dd-mm-yy') legacy_wrong_century
 ,to_date('01-01-98','dd-mm-rr') legacy_better_format
from dual;

-- Generate first day of each month
select add_months(trunc(sysdate,'yy'),rownum-1) months
from dual connect by level <= 12;

MONTHS
---------------------
01/01/2012 00:00:00
01/02/2012 00:00:00
01/03/2012 00:00:00
01/04/2012 00:00:00
01/05/2012 00:00:00
01/06/2012 00:00:00
01/07/2012 00:00:00
01/08/2012 00:00:00
01/09/2012 00:00:00
01/10/2012 00:00:00
01/11/2012 00:00:00
01/12/2012 00:00:00

12 rows selected

-- Generate list of this year's weekends
select dt from (
  select trunc(sysdate,'yy')+rownum-1 dt
  from dual connect by level <= 365)
where to_char(dt,'dy') in ('sat','sun');

DT                  
---------------------
01/01/2012 00:00:00   
07/01/2012 00:00:00   
08/01/2012 00:00:00   
14/01/2012 00:00:00   
...
23/12/2012 00:00:00   
29/12/2012 00:00:00   
30/12/2012 00:00:00   

105 rows selected 

select sysdate@! wtf_is_this from dual;

Further reading on SQL injection with dates

http://tkyte.blogspot.com.au/2012/02/all-about-security-sql-injection.html

Remember date contains time

This is one thing I regularly mention to trainees, and recently I fell for the problem myself!

Dates may contain a time component.

Simple example, some might expect this statement to return a result (as I write this being 1st March) - it does not.
select * from dual where sysdate <= date '2012-03-01';

For the pure reason that sysdate returns century, year, month, hour, minute, second. And so may some of the dates stored in your database.

SQL> select to_char(sysdate,'dd-Mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')
------------------------------------------------------------
01-Mar-2012 20:49:28

Here is a simple test case where ideally I would get two rows -"now" & "midnight today", and the build-up to this scenario:

create table my_dates(descr varchar2(50), dt date);
insert into my_dates values ('yesterday', sysdate-1);
insert into my_dates values ('now', sysdate);
insert into my_dates values ('midnight today', trunc(sysdate));
insert into my_dates values ('midnight tomorrow', trunc(sysdate)+1);
insert into my_dates values ('tomorrow', sysdate+1);

-- all dates
select * from my_dates;

DESCR                                              DT                  
-------------------------------------------------- ---------------------
yesterday                                          29/02/2012 19:40:12   
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   
midnight tomorrow                                  02/03/2012 00:00:00   
tomorrow                                           02/03/2012 19:40:12   

-- equal to today
select * from my_dates
where dt = date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
midnight today                                     01/03/2012 00:00:00   

-- still equal to today
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
midnight today                                     01/03/2012 00:00:00   

-- better, but includes midnight tomorrow
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01'+1;

-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   
midnight tomorrow                                  02/03/2012 00:00:00   

-- up to one second before midnight
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01'+1-1/86400;
DESCR                                              DT                  
-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   

-- excluding the ability to use an index on dt
select * from my_dates
where trunc(dt) = date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   

Here are other date related posts:

• Date format tolerance
• Interval issues
• Data densification - filling dates

Scott

Oracle costed features

Oracle is expensive. Other times, however, Oracle is very much value for money.

For example, the cost to host an OracleXE instance in the cloud running Oracle Application Express? well that only depends on who you're paying for your cloud service.

For interest sake, you can check out Oracle's price lists here.

You can also query the database view v$option that enables you to see which costed options have been installed on your database. Most of these require separate licences for production databases.

Apparently Mogens has a great presentation talking Oracle licencing, but due to webinar difficulties we missed out at a Perth conference.

I always remember an (unverified) story about some people who used partitioning on their DB to eke wonderful benefits to their environment, then did a presentation on it at a local conference. Ergo, the Oracle Sales rep had a little chat with them to sort out their licences ;-)

It's all well and good to have a play around with these features in development, or your machine at home - but don't forget to pay your licences... Larry needs another yacht.

2019 Update: There is an APEX app for this, of course.

And with small businesses could use the #OracleRAD stack for free.