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

1. Last year 3 Quarter sales to this year 3^rd Quarter sales compression
2. Last year 25^th week sales to this year 23^rd sales year etc

 

 

System calendar layout

This have one Row for every day from January 1^st 1900 through Dec 31^st 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

 

1^st 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

 

2^nd 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

1. Permanent table
2. Set table
3. Multi set table
4. Derived Table
5. Volatile Table
6. Global Temporary
7. Queue table[V2R6 introduce]
8. Error Table[TD 12 Introduce]
9. 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

1. Its created for strong intermediate results and calculation
2. It life time is query level that means with in the query it created, once  Query execution finish table is drop automatically
3. it’s stores under spool memory
4. 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:

1. It is also design for intermediate operation
2. 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
3. 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
4. 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

1. It is also design for storing intermediate result and calculation
2. It structure is accessible across session but it’s data accessible with in the same session
3. It’s stores under temporary memory
4. It’s structure stores order data dictionary, so we can create indexes and collect statistics under same table
5. 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

1^st 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))

1. Insert into x(,),’vinay’);
2. Select*From x; only Redirect[Display]first row inserted first and last row last]
3. Select and consume top1*From x;
4. Display first row all columns and delete that row ERROR Table[TD12 introduce]
5. To handle the error at the time of insert to select(or) merge into statement which loading BUL Date
6. If these are any error to the error in error table and continuous the execution of query this tables are useful
7. 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

1. It has the ability to store all historical states of a particular Record or particular data base row.
2. Generally used for below type of Queries
3. Display valid Records in specified period
4. Display transition Records in this specified period