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
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
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
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:
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
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))
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
You liked the article?
Like : 0
Vote for difficulty
Current difficulty (Avg): Medium
1/15
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
Stay Updated
Get stories of change makers and innovators from the startup ecosystem in your inbox