ROW_NUMBER() window function

ROW_NUMBER() OVER function can be used as a function to limit rows by specifying no of rows to view.SELECT * FROM( SELECTROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number,COLUMNSFROM tablename) A(name of select statement)WHERE row_number <=...

read more

Select statement to Extract text file in a column and to save images

1. Text File in a columnSelect statement can be utilized as to select data from a text file in a single column where 1 is as ID, Asia as region and the last column will contain the data from a text file. SELECT 1 AS ID, 'ASIA' AS REGION, * FROM OPENROWSET ( BULK 'C:\mysql.TXT',SINGLE_CLOB) AS MYTABLEin the OPENROWSET parenthesis replace the path with your text file path.Save an image in a binary formatFollowing query will save picture in sql db in binary format.UPDATE CompanyInfo SET Logo = A.BulkColumn...

read more

Select Column Level Fields from more than one table

How many books have been purchased along with their quotations in the month of Jan 2010, in order to aggregated values from different table following is an easy way to do it.Select count(b.book_id) as "Total Books",(Select count(q.id) from tbl_q_received as "q"where date_format(b.purchase_date,'%y-%m-d')=date_format(b.purchase_date,'%y-%m-d')) as "Total Quotations"from tbl_book bwhere b.purchase_date between '2010-01-01 00:00:00' and '2010-01-3123:59:...

read more

How to Choose Date Format in MYSQL

While writing a query in MYSQL for getting result up to Hour in dates one should use date format define below because it would not duplicate the values for the same records while matching them to dimension table.Date_format('your_date','%d-%m-%y-H') note: Capital H is for (1-24 hours format) while small h is for (1-12 hours format)Example:Select date_format(registration_date,'%d-%m-%y-%H') as "Reg_date", count(emp_id)from empgroup by Reg_datein order to get not NULL value one should define a separate...

read more

Single Insertion for all values in ETL script

It is common practice to get single value from the transactional database and then add to it immediately in the database and then updating rest of the values but it is not efficient because in huge data it is very difficult to update i.e. it is time consuming therefore it is suggested to first select all the values i.e. KPIs from the transactional database using transformation in the Pentaho and store these KPIs values in variable using SET VARIABLE as mentioned belowselect your kpis query values...

read more

Hour Basis Records from Transactional Databases to Warehouse

To get hour basis records from the source to the warehouse it is recommended to have a table having a date field in the warehouse database i.e. (example 2010.01.01 17:00:00) .first extract that date and then add 29 minutes to it using add(minutes,date) function for every KPI (Key Performance Indicator) set this date value in your SET VARIABLE and then access it in every desire Transformation through out he ETL scriptIt is possible within this short time period a record may not exist in the source...

read more

Setting and Getting varibles in Pentaho Transformations

while working with the variables in pentaho Transformations one can select specific records in global and local variables with the help of SET VARIABLE AND GET VARIABLE available in the transformationsfor example:to get a record from source and then save it into SET VARIABLE there you can define the scope of the variable i.e. valid in root etc.. in the first column name the variable and then select the scope of the variable from the drop down combo box...now for accessing the same variables' value...

read more