SELECT itemid, Qty, actualPrice, subtotal FROM SalesOrderDetail s WHERE actualPrice< 1000 COMPUTE SUM(Qty), SUM(subtotal)
Labels
- AVG() Function (1)
- Aggregate Functions (1)
- Candidate Key (1)
- Composite and Compound Keys (1)
- EXISTS (1)
- FIRST() Function (1)
- Group By Statement (1)
- Having Clause (1)
- INSERT (1)
- LIKE (1)
- SELECT AS CLAUSE (1)
- SQL Distinct Unique Records (1)
- SQL IN (1)
- SQL Joins (1)
- SQL Last() Function (1)
- SQL NULL Conditional (1)
- SQL Not Exists (1)
- SQLComparison Operators (1)
- Sql Max() Function (1)
- Sql Min() Function (1)
- Sql SUM() Function (1)
- WHERE (1)
Wednesday, July 21, 2010
Calculate Grand Total in SQL Query
at
10:09 AM
Posted by
M.Junaid Khan
0
comments
Wednesday, June 23, 2010
Date wise SQL Query
at
8:54 AM
Posted by
M.Junaid Khan
0
comments
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.
Tuesday, May 25, 2010
Generate Mapping against target Step
at
11:20 AM
Posted by
M.Junaid Khan
0
comments
Pentaho Spoon Installation
at
10:58 AM
Posted by
M.Junaid Khan
1 comments
chmod +x *.sh
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
- The Spoon GUI is supported on the following platforms:
- Microsoft Windows: all platforms since Windows 95, including Vista
- Linux GTK: on i386 and x86_64 processors
- Apple's OSX: works both on PowerPC and Intel machines
- Solaris: using a Motif interface (GTK optional)
- AIX: using a Motif interface
- HPUX:
Friday, May 21, 2010
Group by SQL Query
at
9:36 AM
Posted by
M.Junaid Khan
0
comments
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
Sunday, May 16, 2010
How to Insert Extracted Values in Pentaho Spoon
at
11:47 AM
Posted by
M.Junaid Khan
0
comments
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.
Loading XML Data Using Pentaho ETL Tool
at
6:47 AM
Posted by
M.Junaid Khan
0
comments
Go to the design mode, drill down the input folder and drag & drop the XML Input Step.
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.
To view all these contents of your XML file you can use the XML OUTPUT Step available under the Out put folder.
SQL Query For Monthly Calculation
at
6:29 AM
Posted by
M.Junaid Khan
0
comments
Saturday, May 15, 2010
Concatenation in Pentaho Kettle
at
11:19 PM
Posted by
M.Junaid Khan
0
comments
How to Split a String in pentaho Kettle
at
10:54 PM
Posted by
M.Junaid Khan
0
comments
var ID = employee [0];
var Name= employee [1];
var Address = employee [2];
Thursday, May 13, 2010
Date to String Conversion in Pentaho Kettle
at
9:04 AM
Posted by
M.Junaid Khan
0
comments
How to get Distinct Count in Pentaho
at
8:35 AM
Posted by
M.Junaid Khan
0
comments
How to Write ETL Script in Pentaho
at
8:12 AM
Posted by
M.Junaid Khan
0
comments
Wednesday, May 12, 2010
Check Table Column Existence in Pentaho
at
8:52 AM
Posted by
M.Junaid Khan
0
comments
- Drag & Drop the above icon.
- Double click the step and name it , defined the connection for MYSQL or SQL SERVER etc.
- Table Name: The name of the column to whom the column belongs.
- Table name in Field: Enabled it if you want to read the name of the table from the input field.
- Tablename Field:Set fields here that have parameters and its type
- Columnname Field:Set the name of the column field reading from the input field.
- Result fieldname:Resulting Boolean flag that shows weather it exist or not.
Performing Calculations in Pentaho Spoon
at
8:21 AM
Posted by
M.Junaid Khan
0
comments
- Go to Transformation in the design mode
- Drill Down the Transformation folder and drag & drop the Calculator
-
- Double Click the Calculator Step a pop window same as below will appear
- In the New Field column name your new Field
- Select the type of calculation from the list box on the fields you want to perform calculations.
- Field A, Field B and Field C combos are available to select your fields that are need to be performed calculations
- Select the value type from the combo box and set decimal precision.
Aggregate Functions in Pentaho Spoon
at
7:57 AM
Posted by
M.Junaid Khan
0
comments
-
Drill Down the Input in the design mode and select Text Input File -
-
Double Click the Text File Input in the File tab and brows your file add it to the selected path -
In the Content tab select File Type -
Go to Fields tab and click the Get Fields , it will extract all the fields along with data their data types, reading each coma separated field -
Go to the design mode again find Deprecated the 3rd last folder drill down it and drag & drop the Aggregate Rows Step as the same icon mentioned below. -
-
Double click the Aggregate Rows a pop window will open click Get Fields it will extract all the fields as it has read from the Text Input File, set the name for each field in the New Name column and finally select the aggregate function from the combo box.
What is Checksum in Pentaho Spoon
at
7:23 AM
Posted by
M.Junaid Khan
0
comments
- Go to Design
- Drill Down Transformation
-
Drag & Drop Add a Checksum -
-
Name the Step -
Define the Checksum Algorithm most used Algorithm for Checksum is MD-5 -
Click the Get Fields Button, this will extract and show all the fields coming from the previous step at which your going to apply checksum might be extracting using Input Step from the database table or Stored Procedure
Tuesday, May 11, 2010
Hex to integer Conversion in Kettle
at
12:10 PM
Posted by
M.Junaid Khan
0
comments
Drill Down the Scripting step.
Drag and drop the Modified Java Script Value.
var nummerke = Packages.java.lang.Integer.valueOf(Hex.getString(), 16).intValue();
How to Set Environment Variables in Kettle
at
9:05 AM
Posted by
M.Junaid Khan
0
comments
Monday, May 10, 2010
What is Get Variable in Pentaho Kettle
at
12:16 PM
Posted by
M.Junaid Khan
0
comments
- Go to the Job Folder drag & drop a Get Variable Step
- Nam the value of accessing variable that you can use in other steps
- in the second column give the same name of the required variable as you have declared it in Set Variable Step in below format
- ${variable} or %%variable%%