Recursive PL/SQL

it will be a good week ! I found an elegant way to solve a query with recursive pl/sql. an user wanted to have DHSGHDADSFDF translated in DHSGAF, that is, duplicated chars removed, order retained. here is my function : create or replace function f(v varchar2) return varchar2 is begin if (v is null) then… Continue reading Recursive PL/SQL

Published
Categorized as Blogroll, sql

static expression

Using Static Expressions with Conditional Compilation defines the kind of expression you can use with conditional compilation… I got one question on forums.oracle.com today, an user wanted to use bitand and could not. I wrote my answer but, … , the site is down and eventually my answer did not get accepted. So, to translate… Continue reading static expression

Published
Categorized as Blogroll, sql

select * from test where my_long like ‘%toto%’

A good way to learn is to try to answer user questions. Instead of referencing other posts, I tried today to answer that frequently asked question myself on developpez.com (french forum) SQL> create table test ( my_long long); Table created. SQL> insert into test values (‘hello toto !’); 1 row created. SQL> exec for r… Continue reading select * from test where my_long like ‘%toto%’

row generators performance

I wrote a few generators, and listed some existant in http://laurentschneider.blogspot.com/2005/08/pivot-table.html I decided to test them note that this is not a “good” test, it is simply an overview. I am doing a count(*), another operation may be better in one or worst in another one. all_objects is so slow (6 seconds for 50K rows)… Continue reading row generators performance

Published
Categorized as Blogroll, sql

add_years and years_between

I just wrote those two functions add_years and years_between they work similary to add_months and months_between, with the exception of leap years. there is exactly 1 year between 28-feb-2003 and 28-feb-2004 there is 1.00273224 year (1+1/366) between 28-feb-2003 and 29-feb-2004 there is 0.99726776 year (1-1/366) between 29-feb-2004 and 28-feb-2005 there is exactly 1 year between… Continue reading add_years and years_between

Published
Categorized as Blogroll, sql

ansi literals

the first time I saw ansi literals was in June 2004 in a post on otn forum by alex goodmann. I just cannot stop using them, it is so handy! Whenever I specify a date (or a timestamp or even a time), with Ansi Date, I do not rely on the NLS parameters, nor I… Continue reading ansi literals

Published
Categorized as Blogroll, sql

hierarchy

what is a hierarchy? I enjoy reading the wikipedia definition : http://en.wikipedia.org/wiki/Hierarchy In the doc the hierarchy is as a parent-child connection, CONNECT BY PRIOR defines the relationship. However, it is possible to have under certain circumstances to connect to a child, regardsless of the parent. This is no longer a practical relation. Sterile variant… Continue reading hierarchy

Published
Categorized as Blogroll, sql

pivot table part 3

one more try with model, available 10gR1 select * from (select extract(year from hiredate) h, count(*) c from emp group by extract(year from hiredate)) model dimension by (h) measures (c) rules( c[FOR h FROM 1980 to 1990 INCREMENT 1] = case when c[CV()] is present then c[CV()] else 0 end) order by h; H C… Continue reading pivot table part 3

Published
Categorized as Blogroll, sql

pivot table part 2

One more try with 10gR2 select to_number(column_value) HIREDATE, count(decode(to_number(extract(year from hiredate)), to_number(column_value), 1)) COUNT from emp,xmltable(‘for $i in 1980 to 1990 return $i’ ) group by to_number(column_value) order by to_number(column_value) / 1980 1 1981 10 1982 1 1983 0 1984 0 1985 0 1986 0 1987 2 1988 0 1989 0 1990 0 pivot table… Continue reading pivot table part 2

Published
Categorized as Blogroll, sql

stragg in 10gR2

well, you all know string aggregration http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php here is a suggestion with xquery in 10gR2 select deptno,   replace( replace( replace(     XMLQUERY(‘for $cc in ora:view(“emp”) let $ename:=$cc/ROW/ENAME/text()     where $cc/ROW/DEPTNO/number()=$deptno/d/number() return <e>{$ename}</e>’     passing by value xmltype(‘<d>’||deptno||'</d>’) as “deptno”     returning content   ),'</e><e>’, ‘,’),'<e>’),'</e>’) enames from dept / DEPTNO ENAMES —— ——————————————     10 CLARK,KING,MILLER     20 SMITH,JONES,SCOTT,ADAMS,FORD     30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES     40

Published
Categorized as Blogroll, sql

dynamic number of columns

probably one of the most frequently asked question, you want to have a table like             ACCOUNTING OPERATIONS RESEARCH SALES ANALYST              0          0        2     0 CLERK                1          0        2     1 MANAGER              0          0        1     1 PRESIDENT            1          0        0     0 SALESMAN             0          0        0     4 but the number and name of columns must be dynamic. typically, I answer : this is not possible in plain sql. you need to use plsql. well. It is not too much beautifoul, but… Continue reading dynamic number of columns

Published
Categorized as Blogroll, sql

pivot table

big mission today : explain the mess with pivot table. let’s look the following query SQL> select extract(year from hiredate) year, count(*) from emp group by extract(year from hiredate) order by year YEAR COU —– — 1980   1 1981  10 1982   1 1987   2 how do we get the years without hiredate with 0? 1)… Continue reading pivot table

Published
Categorized as Blogroll, sql

group by does not sort

An user just posted an interresting question today. Why the Group By is crazy? I summarize his example SQL> SELECT NUM FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;     NUM ——-     400  220000     310 Well, group by is “sorting”, but how? this seems crazy.… Continue reading group by does not sort

Published
Categorized as Blogroll, sql

TO_CHAR(…, ‘D’)

How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ? With to_char() alter session set nls_territory=germany; select to_char(sysdate,’DAY D’) from dual; TUESDAY 2 With decode() select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ‘1’, ‘TUESDAY’, ‘2’, ‘…’)) from dual; With mod() As a reference, I take monday Jan 1st, 1000. select mod(trunc(sysdate)-date ‘1000-01-01’,7)+1 from dual; 2 How do I… Continue reading TO_CHAR(…, ‘D’)

deterministic

if I create a function for a materialized view with query rewrite or for a function based index, I must create hte deterministic. f(x) = x*2 is deterministic. for a give x, f(x) will always be the same, f(5) will be always 10; always. f(y) = sysdate+y is non-deterministic. For a given y, the return… Continue reading deterministic

How old are you?

I just come back from holiday, I am quite busy at the moment. Here is a tiny function to get the age trunc((to_char(sysdate,’YYYYMMDD’)-to_char(birthdate,’YYYYMMDD’))/10000) it is much safer than add_months, because add_months do some conversion at the end of the month, and I would never accept to wait until Feb 29th, 2008 (28-2-1990 + 18*12 months)… Continue reading How old are you?

exotic constraints

Today I read a post on metalink where the user wanted a unique constraint for not-null values… Sounds easy, because Oracle never indexes null in btree index. If I have only one column, I simply index it, it will work. SQL> create table t66 ( n number); Table created. SQL> create unique index i66 on… Continue reading exotic constraints

unexpected results !

It makes you cry! It makes you claim you have found a bug! but it is working as specified! 1) subquery refers to a column of the main query select * from emp where ename in (select ename from dept where deptno=10); the query does not complain that column does not exist in dept. It… Continue reading unexpected results !

The time has gone

Sure we know date. Oracle always store date and time in an internal format. It could be understood as a number of seconds since Jan 1st, -4712. It must be smaller than Jan 1st, 10000. Let’s say we do not care of the Y10K nowadays… It works well and it is easy to add days… Continue reading The time has gone

order by to_number ?

to_number is often subject to generate ORA-01722: invalid number. When I deal with integers, I prefer lpad. ex: sort pseudo-numeric select * from t order by lpad(col,20); 1 2 10 It performs well, and do not generate ora-01722. ora-01722 does not mean that I have bad data. select name,value from v$parameter where name like ‘%pool_size’… Continue reading order by to_number ?

sys_connect_by_path

sys_connect_by_path is the only function provided by Oracle to get the the hierarchy path in one field, and it is only concatenating. I just found out a way of doing a sum of the path : let’s imagine I want the sum of the salary of all my hierarchy. select ename, sys_connect_by_path(ename,’/’) hierarchy, length(replace(sys_connect_by_path(lpad(‘ ‘,sal/10),’/’),’/’))*10… Continue reading sys_connect_by_path

Recursive SQL

One of the most common school exercice about recursion is the factorial. Guess what, I am going to do it in sql with hierarchies! I use the following ln property : x1*…*xn = exp(ln(x1)+..+ln(xn))) Ok, here it is SQL> select n, (select exp(sum(ln(level))) from dual connect by level

One example about hierarchies

Today morning I just received a question from a friend where I used hierarchies : > Let’s assume a couple of persons have bought some cakes togeher and they want to eat it: > > Create table cake_owners > (owner# number, > cake# number, > constraint cake_pk primary key (owner#,cake#) > using index); > >… Continue reading One example about hierarchies

Hierarchical queries

The typical hierarchical query is you want to select your boss, and the boss of your boss, etc. It could look like select prior ename ename, ename mgr from emp connect by prior mgr=empno start with ename=’SCOTT’; SCOTT SCOTT JONES JONES KING I start with Scott and the hierarchy is built. I can use the… Continue reading Hierarchical queries