Validate data type within SQL

For all those pushing data around, especially dirty data, this one is for you.

Today I was preparing to process data I loaded from a spreadsheet.
A simple filter was required - to ignore the header row, had it been included.

I'm lucky enough to be working on 19c, and I remembered that a reasonably new function should help me out with all many of data loading issues. With a quick scan of my favourite reference manual, I found VALIDATE_CONVERSION.

For example, this gives me 'ORA-01722 invalid number' because of the header row I failed to exclude.

select c.*
from my_data_load c
order by to_number(seq);

But without the to_number, the order returns incorrectly.

SEQ
-----
1
10
12
140
2
Order

Sure, we could say
where seq != 'Order'

But this tool will have more than one use

select c.*
from my_data_load c
where validate_conversion(seq as number) = 1
order by to_number(seq);

SEQ
-----
1
2
10
12
140

Recreate this result using

select * from (
select 'Order' seq from dual
union all select '1' from dual
union all select '2' from dual
union all select '10' from dual
union all select '12' from dual
union all select '140' from dual
)
where validate_conversion(seq as number) = 1
order by to_number(seq)

And see typical return values (0 or 1) for conversion attempts using

select
   validate_conversion('1' as number) num1
  ,validate_conversion('2' as number) num2
  ,validate_conversion('1b' as number)  num_not
  ,validate_conversion('01-01-2001' as date) date1
  ,validate_conversion('30-02-2000' as date, 'dd-mm-yyyy') date2
from dual;  

      NUM1       NUM2    NUM_NOT      DATE1      DATE2
---------- ---------- ---------- ---------- ----------
         1          1          0          0          0

It's one of a few tools I'm using to make data loading life easier, and processing data in sets using SQL, not looping & context switching within PL/SQL.

The kicker, turns out this has been available since 12.2.

It turns out the usage of validate_conversion in PL/SQL will give the compilation warning PLW-06009. And so does the alternative to check if this returns null:
to_date('z-z-2001' default null on conversion error, 'dd-mm-yyyy')

More examples available from
LiveSQL
Tim Hall
Oren Nakdimon
19c Documentation

ANSI dates make life easier

This post is one of a series on what I learned while not at Kscope18.

Dimitri mentioned that he learned about the ANSI date format that allows you to return a date with the expression.

Learned something new in @cczarski session to create a date: just use date #Kscope18 pic.twitter.com/mDNeV7QeYH

— Dimitri Gielis (@dgielis) June 11, 2018

Which means this
date '2018-06-10'

Is the same as
to_char('10-Jun-2018','DD-MON-YYYY')

And you'll never want to type the latter again.

I learned this little chestnut as a trainer of SQL, but what I didn't pick up, or have since forgotten, is this

timestamp '2018-06-10 14:33:41'

Thanks again, Connor, for adding to this thread.

ANSI dates

Here's some more info on ANSI dates. It's not lazy, Tanel, it's efficient ;p

Learning is a lifetime pursuit.

SQL Analytics - Ranking with ordinal suffix

SQL Analytics provides a fairly simple mechanism for determining positional rank within a set of results.

Before I demonstrate that query - which is already found in many good libraries - I thought I'd show how we could take it a step further and add the ordinal suffix (st, nd, rd, th) to a result.

We can do this using date format masks

with placing as (select rownum rn from dual connect by level < 5)
select to_char(to_date('2013-01-'||rn,'yyyy-mm-dd')
              ,'fmddth') ordinal_suffix
from placing
/

ORDINAL_SUFFIX
--------------
1st            
2nd            
3rd            
4th

After adding the year/month to our position, we convert the result to a date - then convert it back to our desired output using TO_CHAR. The "fm" removes the leading zero, and we can obviously ignore the year/month from the output. On a side note, something I discovered while writing this query is the inability to concatenate values in the ANSI date expression.

select to_char(date '2013-01-'||1,'fmddth') from dual;

ORA-01847: day of month must be between 1 and last day of month

If you know a way around this, I'd be happy to know.

Now we can combine this expression with the dense_rank() analytical function.

select ename, sal
  ,rank() over (order by sal desc) rank 
  ,dense_rank() over (order by sal desc) dense_rank 
  ,to_char(to_date('2013-01-'||dense_rank() over (order by sal desc),'yyyy-mm-dd'),'fmddth')  rankth
from emp

ENAME             SAL       RANK DENSE_RANK RANKTH
---------- ---------- ---------- ---------- ------
KING             5000          1          1 1st    
FORD             3000          2          2 2nd    
SCOTT            3000          2          2 2nd    
JONES            2975          4          3 3rd    
BLAKE            2850          5          4 4th    
CLARK            2450          6          5 5th    
ALLEN            1600          7          6 6th    
TURNER           1500          8          7 7th    
MILLER           1300          9          8 8th    
WARD             1250         10          9 9th    
MARTIN           1250         10          9 9th    
ADAMS            1100         12         10 10th   
JAMES             950         13         11 11th   
SMITH             800         14         12 12th   

 14 rows selected 

Cool, huh?

Analytical functions essentially calculate another column of values based on the data queried. I've included 3 examples

1. "RANK" - demonstrates most of it is semantics, in this case you only need to provide which column you would like the ranking to order with.
2. "DENSE_RANK" - shows slightly different rules in the numbers generated in the rank. ie - do we get a bronze?
3. "RANKTH" - combines the ranking with date formatting to make it look cool

Probably nifty for these soccer world cup APEX apps I hear people are creating... just don't try go above about 30 places ;-)

Playing with dates, again

When creating LOVs for APEX I sometimes debate to myself whether to make a static or dynamic LOV.

I had one scenario where having some SQL was handy, so I started with this

SELECT TO_CHAR(NEXT_DAY(sysdate, 'MON')+ROWNUM-1,'DY')
FROM dual
CONNECT BY LEVEL <= 7;

It's possible to then place this as an inline view within a subquery factoring clause, to use fancy terminology. This makes it easy to share column data.

WITH data AS 
 (SELECT NEXT_DAY(sysdate, 'MON')+ROWNUM-1 dt
  FROM dual
  CONNECT BY LEVEL <= 7)
SELECT TO_CHAR(dt,'DY'), TO_CHAR(dt,'Day')
FROM data;

TO_CHAR(DT,'DY') TO_CHAR(DT,'DAY')
---------------- -----------------
MON              Monday            
TUE              Tuesday           
WED              Wednesday         
THU              Thursday          
FRI              Friday            
SAT              Saturday          
SUN              Sunday            

 7 rows selected 

Simple, but effective.

Anyone have thoughts on benefits of using dynamic vs static LOVs in APEX?

User friendly APEX date items

Back in my Oracle Forms days, we had a library function associated with our date fields that accepted a value of "t", which then returned today's date.

We had further variations on this, but I thought I'd see how I'd go at implementing this in the APEX environment.

Update - included .change() to invoke trigger
http://stackoverflow.com/questions/8437125/jquery-invoke-change-without-user-action-but-by-val-change

First, well, second after creating some date fields on my page - I defined a dynamic action "t in date"
Event: Key release
Selection type: jQuery Selector
jQuery Selector: .hasDatepicker -- this is a class automatically assigned to my dates, found simply with right-click -> Inspect element in Chrome
Condition: equal to
Value: t

Dynamic Action definition

You only require a true action, executing some JavaScript
$(this.triggeringElement).val(return_date('-')).change();
In my case I used a function to return a date formatted nicely for my Oracle environment - more details below.
Don't fire on page load, and set "Selection Type" to "Triggering Element"

JavaScript action

I must thank Tobias in the OTN forums for to return date function, but I've extended it a little to suit my tastes.
I also added a parameter so I could define another DA that accepts "y" for yesterday - and adjust my call to return_date('-',-1)

function return_date(p_delimiter, p_offset) {
  /* with help from
   https://forums.oracle.com/forums/thread.jspa?threadID=2186734
   http://stackoverflow.com/questions/894860/set-a-default-parameter-value-for-a-javascript-function
  */
  /* Default delimiter to . */
  p_delimiter = typeof p_delimiter !== 'undefined' ? p_delimiter : '.';
  p_offset    = typeof p_offset    !== 'undefined' ? p_offset : 0;

  /* Create date object */
  var myDate = new Date(Date.now());
  myDate.setDate(myDate.getDate()+p_offset);

  /* Create output string DD.MM.YYYY */
  /* Day */
  var myStr = (myDate.getDate() < 10 ? "0" + myDate.getDate().toString() : myDate.getDate().toString()) +  p_delimiter;
  /* Month */
      myStr = myStr + (myDate.getMonth()+1 < 10 ? "0" + (myDate.getMonth()+1).toString() : (myDate.getMonth()+1).toString()) + p_delimiter;
  /* Year */
      myStr = myStr + myDate.getFullYear().toString();

  /* Set value */
    return myStr;
}

Note how much more difficult it seems to default parameters in JavaScript compared to PL/SQL.

What do you think? The only problem I've found is if you tab quick enough after typing "t", the trigger does not fire.
Oh, and IE8 seems to have a problem with the date constructor - but I've all but lost my patience pandering to IE.

An example can be found here:
http://apex.oracle.com/pls/apex/f?p=SWESLEY_FORUM:6:0::NO::P6_MODE:E

Scott

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...

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

Changing Oracle APEX calendar icon

Sometimes I stumble across little features that after a moment I realise - hey, that wasn't in the last version. It's good to see the APEX team tackling little improvements as well as the big ones, ever maturing product.

Then I stumble on something that I discover is not a new feature after all, just one I never really paid attention to.

When editing a theme, you can select a different icon to use as the date picker.

So instead of the elegant default:




you could use the ugly retro looking #IMAGE_PREFIX#date.gif



And the reason why I know it's from 3.x, Paulo Vale picked it up in green 2008.

Date format tolerance

This is just a little ditty to remind me which date format mask is more tolerant.

SQL> select to_date('21-03-2011','dd-mon-yyyy') from dual;
select to_date('21-03-2011','dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date('21-mar-2011','dd-mm-yyyy') birthday from dual;

BIRTHDAY
-------------------
21-03-2011 00:00:00

1 row selected.

Note that it accepts 'MAR' when using the MM format, but not numerics when using the MON format.

You may like to consider this when defining an application wide default format.

Shared Components -> Globalization Attributes

Scott

Interval Issues

Date functions have been ubiquitous within our database for years. For the most part date calculations are robust and can solve many problems.

Oracle even caters for a drift noted in the 8th century fixed in the 16th century - due to some issues with the dates chosen for Easter by the catholic church.

select date '1582-10-04' + 1 gregory from dual;

GREGORY
-------------------
15-10-1582 00:00:00

Let's take for instance one method of adding one month to a given day.
Here I add a month to the last day of August:

select add_months(last_day(date '2009-08-01'), 1) end_of_sept from dual;

END_OF_SEPT
-------------------
30-09-2009 00:00:00

And I safely get the last day of September (which has one less day).

Likewise, the documentation states:

For example, the MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.

So these three expressions will return slightly different results

select months_between(last_day(date '2008-02-01'), (date '2008-02-01')) is_28_days
      ,months_between(last_day(date '2009-08-01'), (date '2009-08-01')) is_30_days
      ,months_between(last_day(date '2009-09-01'), (date '2009-09-01')) is_29_days
from dual;

IS_28_DAYS IS_30_DAYS IS_29_DAYS
---------- ---------- ----------
.903225806 .967741935 .935483871

However if you're working with intervals here is a little trap to watch out for.

select last_day(date '2009-08-01')
+ INTERVAL '1' MONTH end_of_sept
from dual;

+ INTERVAL '1' MONTH int
*
ERROR at line 2:
ORA-01839: date not valid for month specified

Interestingly if you subtract one month interval from the end of September you get 30th August. Similar behaviour occurs when subtracting from a leap February. It seems an interval of one month is considered as 31 days, but it can't allow properly for smaller months.
The same error will occur however if you attempt to subtract from a month such as July.

select last_day(date '2009-09-01')
 - INTERVAL '1' MONTH end_of_aug
from dual;

END_OF_AUG
-------------------
30-08-2009 00:00:00

The 11g documentation states:

When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error...
SELECT TO_DATE('31-AUG-2004','DD-MON-YYYY') + TO_YMINTERVAL('0-1') FROM DUAL;
...
The first fails because adding one month to a 31-day month would result in September 31, which is not a valid date.

Personally I can't quite grasp why there should be a difference between interval arithmetic and functions such as months_between, but just be aware if you need to be pedantic with your dates.

Perhaps this behaviour with intervals will be modified in a future release?