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(subtot...

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 da...

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...

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...

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...

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...

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        ...

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...

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...

read more