SYSTEM CALENDAR FUNITION
Real time usage
It is really difficult to write SQL for the below data and time analytically but by using system calendar we can answer easily
- Last year 3 Quarter sales to this year 3rd Quarter sales compression
- Last year 25th week sales to this year 23rd sales year etc
System calendar layout
This have one Row for every day from January 1st 1900 through Dec 31st 2100[73k#]
Calendar- date—–Standard Date
Day – of – week—-(1-7 1-sunday)
Day –of-month—-(1-13)
Day –of-year—-(1-366)
Week day – of- calendar — jillion date,startingjan,1,1900
Week day – of- month — (nth occurrence, 1-5)
Week day – of- month —(1-5 )partial week
Week day – of- calendar –(1-52)
Month – of –quarter —(0-n)
Month – of – year —(1-3)
Month – of – calendar—(1–n)
Quarter – of – year –(1-9)
Quarter – of – calendar —(1-N)
Year – of calendar —-(1900 -2100)
Queries
àDisplay the calendar with today date?
Sel*From sys – calendar. Calendar where calendar-date=current
Date;àDisplay the calendar with tomorrow date
Select *From sys-calendar.calendar where calendar – date=current date+1
->DISPLAY Quarter one sales for year2010
Create multi set sales(id integer, s date date format ‘yyyy-mm-DD’, Sale sin come integer)
Date: Insert into sales(1,’2010-02-03’,1000);
Insert into sales(2,’2010-03-04’,2000);
Insert into sales(1,’2010-01-05’,2000);
Insert into sales(1,’2010-09-04’,3000);
Insert into sales(1,’2010-09-03’,2000);
Sel id, sum(sales income) from sales S,Sys—calendar. calendar
C where c. calendar-date= s.sdate and Quarter –of-year=1 and year-of- calendar =2010 and s.id=1 Group by s. id
àDisplay the current month sales for iteam one
1st way
Sel id sum(Sales income)From sales s, sys-calendar.calendar
C where c.calendar-date= s. s date and month – of – year=9 and year- of – calendar = 2011 and sid = 1 group by s. id
2nd way
Sel id, sum(Sales income)From sales s, sys- calendar. Calendar c(Sel *From sys- calendar. Calendar where calendar- date=current-date)
Td where c. calendar-date=s. date and c. month-of- calendar=td. month-of- calendar and s.id=1
àDisplay current month sales and last year current month sales sel id, sum(Sales income)from sales s, sys- calendar. Calendar c,(Sel*From sys – calendar. Calendar where calendar-date= current – date)td
Where c. calendar-date=s.date
And
(c.month-of- calendar=td.month-of- calendar)
(or)
(c.month-of- calendar=td.month-of – calendar-12)
And s-id=1
Note – For Displays current months last monthcompression, last year current month prev
Type s of Tables
Different types of table available in tera data for different type of operation intermediate calculation
- Permanent table
- Set table
- Multi set table
- Derived Table
- Volatile Table
- Global Temporary
- Queue table[V2R6 introduce]
- Error Table[TD 12 Introduce]
- Temporal table[TD13.10]on words available
a)Permanent table –
This table is available across multiple session and user any where[both structure and date]
d)Derived Table – This table structure data available with in the query once the query execution finish no structure data available
- Its created for strong intermediate results and calculation
- It life time is query level that means with in the query it created, once Query execution finish table is drop automatically
- it’s stores under spool memory
- In other data bases we call this type of queries are in line query(or) in line view
Select party id, party name, d. sumincome,d. avincome from party,(Select sum(prty income)as sum income, avg(party income)as avincome from party)d
E) Volatile:
- It is also design for intermediate operation
- Its life time is with in the session that mean table data available with in the session, once the session no structure and data available
- Its definition not store in the data dictionary so we cannot create indexes and we can not collect statistic and data dictionary on this table
- It stores under spool memory
Note
In Tera data 13 we can collect statistic on volatile table
VOLATILE-NOTEPAD Allows volatile table uses along with the data for other tables Query in the same session
.Log on
Creating:
Create volatile table <VT Name>,Log/No Log(Column definition) on commit preserver/Delete Rows;
INSERTING Date – INSERT INTO<VT Name>select sum(Col name),Avg (Col name)From
<T name>
Using it – Select col names,<Volatile table name>.column name from<T name>,<Volatile table name>
Show * from table <volatile table>
.logoff
.log on
Show table<volatile table>
àBoth structure and date will not be available
.Logon 127.0.0.1/dbc, dbc;
Create volatile table vt- party, No log(Sum income integer, avincome integer on commit preserve rows;
Insert into vt- party select sum(Party income),Avg(Party income) From party
Select p.partyid, p.party name ,vt- party .sum income , vt- party income
From party p, vt- party;
Show tablt vt- party;
Select* From vt- party;
.Logoff
.Log on 127.0.0.1/dbc,dbc;
Show table vt – party;
Both structure and data will not be available
F)Global Temporary Table
- It is also design for storing intermediate result and calculation
- It structure is accessible across session but it’s data accessible with in the same session
- It’s stores under temporary memory
- It’s structure stores order data dictionary, so we can create indexes and collect statistics under same table
- Some people they are using this tables for materialized tables implementation,
Ex – log on 127.0.0.1 /dbc,dbc;
Create global temporary table vt_ party, log/no log(sum income integer, avincome integer) on commit preserve rows;
Insert into vt-party(10000,20000);
Show table vt-party;
Sel * from vt_ party;
.log off;
.logon 127.0.0.1/dbc,dbc;
—-
—-
Show table VI- Party;
Structure available;
Select*From VI-Party;
No data available
Note
Log – Log information maintains by using transient journal on commit preserve rows allows the use volatile tables again for other Queries in the session .
On commit Delete Rows
This is the default one means the data delete once query completed.
G[Query Table] Query Table:[V2 R6 introduce]
It is like permanent table, but created with Query options
1st column should be time stamp column
Select statement used to read the data
Select and consume To retrieve and delete the data
It maintain FIFO[Fist In first out]Cache which available at parsing engine
Creates set table x, Queue, fall back
(Tid, time stamp not null default current time stomp, pid integer pnm varcher(30))
- Insert into x(,),’vinay’);
- Select*From x; only Redirect[Display]first row inserted first and last row last]
- Select and consume top1*From x;
- Display first row all columns and delete that row ERROR Table[TD12 introduce]
- To handle the error at the time of insert to select(or) merge into statement which loading BUL Date
- If these are any error to the error in error table and continuous the execution of query this tables are useful
- We can redirect limited error (or) all errors
Syntax:
Error table
Create error table<Table name> For<Target table>
Ex – Create error table et party 2 for party2;
àInsert into party select* From party2 logging error;
àinsert into party3 select*From party2 logging error with limit of 100;
I)Temporary table[TD13.10] on words available
- It has the ability to store all historical states of a particular Record or particular data base row.
- Generally used for below type of Queries
- Display valid Records in specified period
- Display transition Records in this specified period
0 Responses on SYSTEM CALENDAR FUNCTIONS"