Calculate Grand Total in SQL Query

To calculate a grand total within an SQL query, the keyword available for this purpose is COMPUTE Keyword which is used with out a BY clause

SELECT itemid, Qty, actualPrice, subtotal
FROM SalesOrderDetail s
WHERE  actualPrice< 1000
COMPUTE SUM(Qty), SUM(subtotal)

read more

Date wise SQL Query

Column Level SQL Query to extract data with date as condition

Select employee_id, employee_name
(Select count(*) from employee_leave el where e.employee_id=el.employee_id
and el.leave_date between e.hire_Date and '2010-06-20 23:59:59')
from tbl_employee e
where e.hire_date between '2010-06-01 00:00:00' and '2010-06-01 23:59:59'

here in the above query for extracting the total leaves the final date has been set from the employee hire date.

read more

Generate Mapping against target Step

In cases where you have a fixed target table, you will want to map the fields from the stream to their corresponding fields in the target output table. This is normally  accomplished using a Select Values step in your transformation. The 'Generate mapping against target' option provides you with an easy to use dialog for defining these mappings that will automatically create the resulting Select Values step that can be dropped into your transformation flow prior to the table output step. To access the 'Generate mapping against target' option is accessed by right clicking on the table output step.

After defining your mappings, select OK and the Select Values step containing your mappings will appear on the workspace. Simply, attach the mapping step into your transformation immediately Attach the mapping step into your transformation just before the table output step.

read more

Pentaho Spoon Installation

The first step is the installation of Sun Microsystems Java Runtime Environment version 1.4 or higher. You can download a JRE

After this, you can simply unzip the zipfile: Kettle3.0.0. zip in a directory of your choice. In the Kettle directory where you unzipped the file, you will find a number of files. Under Unixlike environments (Solaris, Linux, MacOS, …) you will need to make the shell scripts executable. Execute these commands to make all shell scripts in the Kettle directory executable:

cd Kettle
chmod +x *.sh

Starting Spoon:.

To launch Spoon on the different platforms these are the scripts that are provided:
Spoon.bat: launch Spoon on the Windows platform.
sh spoon.sh: launch Spoon on a Unixlike
platform: Linux, Apple OSX, Solaris, ...
If you want to make a shortcut under the Windows platform an icon is provided: “spoon.ico” to set the correct icon. Simply point the shortcut to the Spoon.bat file.

Supported platforms

1. The Spoon GUI is supported on the following platforms:
2.  Microsoft Windows: all platforms since Windows 95, including Vista
3.  Linux GTK: on i386 and x86_64 processors
4.  Apple's OSX: works both on PowerPC and Intel machines
5.  Solaris: using a Motif interface (GTK optional)
6.  AIX: using a Motif interface
7.  HPUX:

read more

Group by SQL Query

To extract Country wise sales count for the period  MAY-2010 use below code.

Select c.country_name, count(s.id)
from tbl_sales s inner join tbl_country c on s.country_id=c.country_id
where s.saels_date between '2010-05-01 00:00:00' and '2010-05-31 23:59:59'
group by c.coutnry_name 

read more

How to Insert Extracted Values in Pentaho Spoon

To insert the extracted records  from the transactional source database into the destination or data warehouse  following is simple way to do it.

Open the Pentaho Spoon go to file menu and open new Transformation.

in the design mode drill down the Input Folder and drag & drop the Table Input Step, double click the Table Input  and set the source database (How to Set Connection Parameters in Pentaho Spoon)connection parameters this will extract all required fields

Again go to the design mode and this time drag & drop the INERT / UPDATE Step lying under the OUT PUT Folder now hold the Shift key and place your mouse icon over the TABLE INPUT and drag upto the INERT / UPDATE Step this will generate a Green color Arrow called a hope with its tail at the TABLE INPUT and head towards INERT / UPDATE Step.

You can enable, disable or even delete the hope by placing your mouse over it and with right click. now double click the  INERT / UPDATE Step

and set target Database Connection by clicking the New Button for the connection and Edit if you have it already.

For the first grid Click the Get Fields Button this will fill grid with all values you have queried in the previous Table Input Step here in this grid keep only those fields on the basis of which you will insert record.

read more

Loading XML Data Using Pentaho ETL Tool

To load the data from a XML file using pentaho kettle do the followings. 
Go to the design mode, drill down the input folder and drag & drop the XML Input Step.

Double click the XML Input Step as shown in the image below

in the first tab of file click the Brows Button and add your desire XML File by clicking Add Button this will add your file(s) inside the selected files Grid under the File /Directory Grid Label.
Below this Grid there is a Show File(s) options that allow you to view your complete files addresses.
Nnow go to the next tab of Fields followed by Content tab.

here in this Fields tab click the Get Fields Button lying below the Grid clicking this will extract all the contents of the XML file along with their data types and positions.
To view all these contents of your XML file you can use the XML OUTPUT Step available under the Out put folder. 

read more

SQL Query For Monthly Calculation

To get individual library member's monthly status report use the following column level query that includes how many books have been issued and returned in the month of may 2010 for those members who have their registration date in the same month here is the query

select m.member_id as "Member ID",m.member_name as "Member Name"

         ,(select count(book_id) 

         from tbl_book_issue b

         where m.member_id=b.member_id

        and b.status=1)as "Total Issued Books"  

       , (select count(book_id) 

         from tbl_book_issue b

           where m.member_id=b.member_id

        and b.status=2)as "Total Returned Books"  #status=1 for issued and status=2 for return

from tbl_member m

where m.registration_date between '2010-05-01 00:00:00' and '2010-05-31 23:59:59'

read more

Concatenation in Pentaho Kettle

To get the  concatenated resulted values of the two variables do the followings.

in the design mode drill down the Scripting folder drag and drop the  Modified Java Script Value step

Double click the icon  and go the java script value Text area, copy the below code and past it into the Script Value Text Area

var fullName =  FirstName+ LastName;

here in this code of java “var” is the reserved word for declaring String variables while “fullName is  the name of variable that will hold the resulted or concatenated value, you can view the “fullName” variable contains the resulted value showing in the grid by clicking get Variable button at bottom of the step.

read more

How to Split a String in pentaho Kettle

To get values from a comma separated string we can spilt it in following way.

In the design mode drill down the Scripting folder drag and drop the  Modified Java Script Value step

Double click the the icon  and go the java script value Text area, copy the below code and past it into the Script Value Text Area

var employee = EmployeeInfo.getString().split("\\,");
var ID = employee [0];
var  Name= employee [1];
var Address = employee [2];

here in this code of java “var” is the reserved word for declaring String variables while “ID”,”name”,”address” are the names of variables that will hold the values after splitting and the EmployeeInfo is the String that contains the ID, Name and Address in the form i.e. 1234,Ali,pakistan

read more