Archive
TO_CHAR(…, ‘D’)
to_char(nchar-clob-or-nclob) to_char(datetime-or-interval) to_char(datetime-or-interval, 'format-string') to_char(datetime-or-interval, 'format-string', 'nlsparam') to_char(number) to_char(number,'format-string') to_char(number,'format-string', 'nlsparam')
How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ?
solution1
With to_char()
alter session set nls_territory=germany; select to_char(sysdate,’DAY D’) from dual; TUESDAY 2
solution2
With decode()
The abbrivation of a day’s name (such as SUN, MON … can be retrieved with the format specifier DY. As to_char is sensitive to the actual nls setting, I use nls_date_language=english to make sure that the english abbreviation is returned, regardless of the nls setting.
select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ’1′, ‘TUESDAY’, ’2′, ‘…’)) from dual;
solution3
With mod()
As a reference, I take monday Jan 1st, 1000.
select mod(trunc(sysdate)-date ’1000-01-01′,7)+1 from dual;
How do I trunc date to current monday?
trunc(date, ‘D’) or here is with my solution with 1000-01-01:
select trunc((sysdate-date ’1000-01-01′)/7)*7+date ’1000-01-01′ from dual;
thanks to jan-marcel idea, I found that one
trunc(date,’IW’) for current monday and date-trunc(date,’IW’)+1 for day number
Spelling a number
select to_char(to_date('429','J'),'Jsp') from dual;
TO_CHAR(TO_DATE('429','J
------------------------
Four Hundred Twenty-Nine
Extraction of consecutive periods (without regard to the OverLap)
kikanT
ID StaD EndD
-- ---------- ----------
10 2010-06-01 2010-06-12
10 2010-06-13 2010-06-14
10 2010-06-15 null
20 2010-06-01 2010-06-11
20 2010-06-13 2010-06-15
50 2010-11-11 2010-11-13
50 2010-11-14 2010-11-20
50 2010-12-22 2010-12-30
Summarized in consecutive periods every ID.
ID StaD EndD
-- ---------- ----------
10 2010-06-01 null
20 2010-06-01 2010-06-11
20 2010-06-13 2010-06-15
50 2010-11-11 2010-11-20
50 2010-12-22 2010-12-30
Data creation script
create table kikanT(ID,StaD,EndD) as select 10,date '2010-06-01',date '2010-06-12' from dual union select 10,date '2010-06-13',date '2010-06-14' from dual union select 10,date '2010-06-15',null from dual union select 20,date '2010-06-01',date '2010-06-11' from dual union select 20,date '2010-06-13',date '2010-06-15' from dual union select 50,date '2010-11-11',date '2010-11-13' from dual union select 50,date '2010-11-14',date '2010-11-20' from dual union select 50,date '2010-12-22',date '2010-12-30' from dual;
Solution1
How to use hierarchical queries (> 10g)
select ID,min(connect_by_root StaD) as StaD,EndD
from kikanT
where connect_by_IsLeaf = 1
connect by prior ID=ID
and prior EndD+1=StaD
group by ID,EndD
order by ID,EndD;
Solution2
Analysis of the relevant number
select ID,min(StaD) as StaD,
max(EndD) Keep(Dense_Rank Last order by EndD) as EndD
from (select ID,StaD,EndD,
sum(willSum) over(partition by ID order by StaD) as GID
from (select ID,StaD,EndD,
case when StaD = 1+Lag(EndD) over(partition by ID order by StaD)
then 0 else 1 end as willSum
from kikanT))
group by ID,GID
order by ID,GID;
Summarizes the start and end dates (or how to eleminate overlaps in dates)
TimeSheet table
StartDate EndDate
---------- ----------
2005-01-01 2005-01-03
2005-01-02 2005-01-04
2005-01-04 2005-01-05
2005-01-06 2005-01-09
2005-01-09 2005-01-09
2005-01-12 2005-01-15
2005-01-13 2005-01-14
2005-01-14 2005-01-14
2005-01-17 2005-01-17
result
StartDate EndDate
---------- ----------
2005-01-01 2005-01-05
2005-01-06 2005-01-09
2005-01-12 2005-01-15
2005-01-17 2005-01-17
Data creation script
create table TimeSheets(
StartDate Date,
EndDate Date,
primary key (StartDate));
insert into TimeSheets values(to_date('20050101','yyyymmdd'),to_date('20050103','yyyymmdd'));
insert into TimeSheets values(to_date('20050102','yyyymmdd'),to_date('20050104','yyyymmdd'));
insert into TimeSheets values(to_date('20050104','yyyymmdd'),to_date('20050105','yyyymmdd'));
insert into TimeSheets values(to_date('20050106','yyyymmdd'),to_date('20050109','yyyymmdd'));
insert into TimeSheets values(to_date('20050109','yyyymmdd'),to_date('20050109','yyyymmdd'));
insert into TimeSheets values(to_date('20050112','yyyymmdd'),to_date('20050115','yyyymmdd'));
insert into TimeSheets values(to_date('20050113','yyyymmdd'),to_date('20050114','yyyymmdd'));
insert into TimeSheets values(to_date('20050114','yyyymmdd'),to_date('20050114','yyyymmdd'));
insert into TimeSheets values(to_date('20050117','yyyymmdd'),to_date('20050117','yyyymmdd'));
commit;
SQL
Solution 1
How to use an inline view
select StartDate,min(EndDate) as EndDate
from
(select a.StartDate,b.EndDate
from TimeSheets a,TimeSheets b
where a.EndDate and not exists(select 1 from TimeSheets c
where (c.StartDate < a.StartDate and a.StartDate or (c.StartDate group by StartDate
order by StartDate;
Solution 2
How to use an inline view
select a.StartDate,min(b.EndDate) as EndDate
from TimeSheets a,TimeSheets b
where a.EndDate and not exists(select 1 from TimeSheets c
where (c.StartDate < a.StartDate and a.StartDate or (c.StartDate group by a.StartDate
order by a.StartDate;
Solution 3
How to use an inline view
SELECT startdate, MIN(enddate) AS enddate
FROM (SELECT T1.startdate, T2.enddate
FROM Timesheets T1, Timesheets T2
WHERE T1.enddate T3.startdate AND T1.startdate = T3.startdate AND T2.enddate < T3.enddate)))
GROUP BY startdate;
Solution 4
How to use an inline view
SELECT startdate, MIN(enddate) AS enddate
FROM (SELECT T1.startdate AS startdate, T2.enddate AS enddate
FROM Timesheets T1, Timesheets T2, Timesheets T3
WHERE T1.enddate T3.startdate AND T1.startdate = T3.startdate AND T2.enddate < T3.enddate) THEN 1 END) = 0)
GROUP BY startdate;
Solution 5
How to use a hierarchical queries (> 10g)
select min(connect_by_root StartDate) as StartDate,EndDate from TimeSheets where connect_by_IsLeaf = 1 connect by nocycle prior EndDate between StartDate and EndDate group by EndDate order by StartDate;
Solution 6
How to use hierarchical queries (> 10g)
select connect_by_root StartDate as StartDate,EndDate
from TimeSheets a
where connect_by_IsLeaf = 1
start with not exists(select 1 from TimeSheets b
where a.RowID != b.RowID
and a.StartDate between b.StartDate and b.EndDate)
connect by nocycle prior EndDate between StartDate and EndDate
order by StartDate;
Solution 7
How to use analysis of the relevant number range
select min(StartDate) as StartDate,max(EndDate) as EndDate
from (select StartDate,EndDate,
sum(willSum) over(order by StartDate) as GID
from (select StartDate,EndDate,
case when StartDate
range between unbounded preceding
and 1 preceding)
then 0 else 1 end as willSum
from TimeSheets))
group by GID
order by StartDate;
How does one get the time difference between two date columns?
Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.
Let’s investigate some solutions. Test data:
CREATE TABLE dates (date1 DATE, date2 DATE); INSERT INTO dates VALUES (SYSDATE, SYSDATE-1); INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24); INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24); SELECT (date1 - date2) FROM dates; DATE1-DATE2 ----------- 1 .041666667 .000694444
Solution 1
SELECT floor(((date1-date2)*24*60*60)/3600) || ' HOURS ' || floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60) || ' MINUTES ' || round((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600 - (floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) )) || ' SECS ' time_difference FROM dates; TIME_DIFFERENCE -------------------------------------------------------------------------------- 24 HOURS 0 MINUTES 0 SECS 1 HOURS 0 MINUTES 0 SECS 0 HOURS 1 MINUTES 0 SECS
An enhancement to solution 1
SELECT floor((date1-date2)*24) || ' HOURS ' || mod(floor((date1-date2)*24*60),60) || ' MINUTES ' || mod(floor((date1-date2)*24*60*60),60) || ' SECS ' time_difference FROM dates;</code> TIME_DIFFERENCE -------------------------------------------------------------------------------- 24 HOURS 0 MINUTES 0 SECS 1 HOURS 0 MINUTES 0 SECS 0 HOURS 1 MINUTES 0 SECS
Solution 2
If you don’t want to go through the floor and ceiling math, try this method (contributed by Erik Wile):
SELECT to_number( to_char(to_date('1','J') +
(date1 - date2), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time
FROM dates;
DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00
Solution 3: If u want an easier method, use numtodsinterval()
NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are ‘DAY’, ‘HOUR’, ‘MINUTE’ and ‘SECOND’.
This function converts the number x into an INTERVAL DAY TO SECOND datatype.
select numtodsinterval(date1-date2,'day') time_difference from dates; TIME_DIFFERENCE ---------------------------------------------------------------- +000000001 00:00:00.000000000 +000000000 01:00:00.000000000 +000000000 00:01:00.000000000
Concatenate sets of intervals
CLEAR;
with t as (
select 11 AS ID, 1 AS val1, 'B' AS val2 from dual union all
select 12,1,'A' from dual union ALL
select 13,1,'A' from dual union all
select 14,1,'B' from dual union ALL
select 15,1,'A' from dual UNION ALL
select 16,1,'B' from dual union ALL
select 17,1,'B' from dual union ALL
select 18,1,'A' from dual union ALL
select 19,2,'B' from dual union all
select 20,2,'A' from dual union ALL
select 21,2,'B' from dual union ALL
select 22,2,'A' from dual union ALL
select 23,2,'B' from dual union ALL
select 24,2,'A' from dual union ALL
select 25,1,'B' from dual union ALL
select 26,1,'A' from dual
),
tt AS(select t.*,
case when lag (val2) over (partition by val1 order by id) || val2 in ('A', 'BA') then 1 end as start_of_group,
case when lead(val2) over (partition by val1 order by id) || val2 in ('B', 'AB') then 1 end as end_of_group
from t),
ttt AS (SELECT tt.*, COUNT(start_of_group) over(PARTITION BY val1 ORDER BY id) AS group_no FROM tt),
tttt AS (SELECT row_number() over(ORDER BY val1, group_no) AS rn,
val1,
t1.ID AS id1,
t2.ID AS id2,
t1.val2 AS VAL2A,
t2.val2 AS VAL2B
FROM (SELECT * FROM ttt WHERE start_of_group IS NOT NULL) t1
FULL JOIN (SELECT * FROM ttt WHERE end_of_group IS NOT NULL) t2
USING (val1, group_no)
ORDER BY rn)
SELECT * FROM tttt;
Oracle PL/SQL – Merge table
clear;
create table myTable
(pid number, sales number, status varchar2(6));
create table myTable2
(pid number, sales number, status varchar2(6));
insert into myTable values(2,24,'CURR');
insert into myTable values(3, 0,'OBS' );
insert into myTable values(4,42,'CURR');
insert into myTable values(6,56,'C44URR');
insert into myTable2 values(1,12,'CURR');
insert into myTable2 values(2,13,'NEW' );
insert into myTable2 values(3,15,'CURR');
select * from myTable;
select 't2' from dual;
merge into myTable2 m
using myTable d
on (m.pid = d.pid)
when matched then
update
set m.sales = d.sales, m.status = d.status
when not matched then
insert values (d.pid, d.sales, d.status);
delete myTable2 where pid in (select m.pid from myTable2 m where m.pid not in (select pid from myTable));
select * from myTable order by pid;
select * from myTable2 order by pid;
drop table myTable;
drop table myTable2;
Autoincrement primary key for Oracle
Suppose you have a database and you want each entry to be identified by a unique number. You can do this easily in mysql by specifying “auto_increment” for your number, but Oracle makes you work a little more to get it done.
Here is one way to do it by creating two database objects, a sequence and a trigger. I find myself wanting to do this every now and then but not often enough that I remember the syntax from time to time, so I decided it was time to write myself up a little cheat sheet. This is an extremely basic outline, so please try it first on a test table if you don’t know what you’re doing.
1. Let’s say we have a table called “test” with two columns, id and testdata. (This is just a dumb quick example, so I won’t bother to specify any constraints on id.)
create table test (id number, testdata varchar2(255));
2. Next we’ll create a sequence to use for the id numbers in our test table.
create sequence test_seq start with 1 increment by 1 nomaxvalue;
You could change “start with 1” to any number you want to begin with (e.g. if you already have 213 entries in a table and you want to begin using this for your 214th entry, replace with “start with 214”). The “increment by 1” clause is the default, so you could omit it. You could also replace it with “increment by n” if you want it to skip n-1 numbers between id numbers. The “nomaxvalue” tells it to keep incrementing forever as opposed to resetting at some point. i (I’m sure Oracle has some limitation on how big it can get, but I don’t know what that limit is).
3. Now we’re ready to create the trigger that will automatically insert the next number from the sequence into the id column.
create trigger test_trigger before insert on test for each row begin IF :new.id IS NULL then select test_seq.nextval into :new.id from dual; end IF; end; /
Obviously you would replace “test_trigger” with something a little more meaningful for the database table you want to use it with, “test” would be your table name, and the “id” in :new.id would be replaced with the name of the column. Every time a new row is inserted into test, the trigger will get the next number in the sequence from test_seq and set the “id” column for that row to whatever the sequence number is. Note that sequences sometimes appear to skip numbers because Oracle caches them to be sure that they are always unique, so this may not be your ideal solution if it’s really important that the id is exactly sequential and not just mostly sequential and always unique.
Greg Malewski writes:
You’ve demonstrated an implementation using triggers. This is not necessary, since instead it can be included as part of the INSERT statement. Using your example, my INSERT statement would be:
insert into test values(test_seq.nextval, 'voila!');
Here are a couple of questions the above might raise. This is pretty intuitive stuff, but I’m aiming it at the Oracle newbie since no expert would be reading this page anyway.
How do you tell what sequences and triggers are already out there?
select sequence_name from user_sequences; select trigger_name from user_triggers;
How do you get rid of a sequence or trigger you created?
drop sequence test_seq; drop trigger test_trigger;
Again, replace test_seq and test_trigger with the specific names you used. You can also keep the trigger but disable it so it won’t automatically populate the id column with every insert (and enable it again later if you want):
alter trigger test_trigger disable; alter trigger test_trigger enable;
[PLSQL] Select a random row from a database table
There is some way to select a random record or row from a database table.
SELECT col1, col2, col3 FROM (SELECT * FROM tcard SAMPLE(1) ORDER BY dbms_random.VALUE)
WHERE branch = 1
CANCELDATE > SYSDATE
AND ROWNUM <= 100);
[PL\SQL] Select a random row from a database table
There is some way to select a random record or row from a database table.
SELECT col1, col2, col3 FROM (SELECT * FROM tcard SAMPLE(1) ORDER BY dbms_random.VALUE)
WHERE branch = 1
CANCELDATE > SYSDATE
AND ROWNUM <= 100);
[PLSQL] Increasing results
data
|
WITH t AS (
SELECT 01 mnth,10 sm FROM dual UNION ALL
SELECT 02, 20 FROM dual UNION ALL
SELECT 03, 30 FROM dual UNION ALL
SELECT 04, 40 FROM dual
)
SELECT mnth, sm,
SUM(sm) over(ORDER BY mnth ROWS UNBOUNDED PRECEDING) FROM t;
result
|
Baurdotnet's Weblog 
Recent Comments