In my previous article I have given idea about the progress database with some basic queries used in progress database. In this article I would like to give idea about progress database sql and some important examples. In progress database sql I would like to give multiple examples of queries which are used in Progress database. The query structure is different than common SQL. That’s why we are calling it as Progress database SQL queries.
Progress Database SQL Queries :
In last article I have given the idea about progress database SQL queries. Those are the very basic queries used in progress. In this article I would like to give heads-up of different SQL queries used in progress database.
Pattern Matching in Progress SQL :
The pattern matching in SQL is done by LIKE statement. In progress we have only Contains keyword for matching multiple patterns of the data in database.
Syntax of Like in Progress :
For Each table_name no-lock
where column_name contains Value_to_be_matched.
End.
The progress uses ‘contains’ statement to check the Like patterns in the data.The Contains keyword only used for the WORD INDEX.
Real Life Example :
FOR EACH Customer no-lock
WHERE Customer_Name contains “Amit”.
Display Customer_Name Customer_No Company_Name.
The above statement will fetch the Customer_name,Customer_no and company_name from customer table where customer_name contains string ‘ Amit’.
How to handle Distinct in Progress Database :
There is not distinct keyword in progress database. We need to use Break By statement in progress database for fetching distinct values.
Syntax :
FOR EACH TABLE_NAME no-lock
WHERE Column_name1 = Column_Value1
Break By Column_name2.
If First-of (Column_name2) then
Display Column_name2.
End.
The syntax looks bit complex but it is very simple. I will explain how it works.
Real life example :
If you want to display unique customer names associated with its Customer_numbers kindly use following query ,
FOR EACH Customer no-lock
WHERE Customer_number > 0
Break by Customer_Name.
If First-of (Customer_Name)
Display Customer_number Customer_Name.
END.
Joining 2 Tables in Progress Database SQL
We have to join 2 or more table to get the business data with some logic. To join the 2 tables in SQL the syntax is different. In Progress Database SQL to join 2 table bit different syntax is used. I would like to explain the syntax and example to join 2 table in this section ,
Syntax:
FOR EACH Left_Table_name no-lock :
FOR EACH Right_Table_name no-lock
WHERE Left_Table_name.Joining_Column_name = Right_Table_name.Column_name.
Display Left_table_name.Column_name, Right_Table_Name.Column_name.
Real life industry example :
If you want to connect the Customer table with the order table. Customer_id and order_id is the joining condition. Kindly check following query to fetch records from Customer and Order table together.
FOR EACH Customer no-lock :
FOR EACH Order no-lock
WHERE Customer.Customer_id = Order.Order_id.
Display Customer.Customer_name,Order.Order_name.
The above statement will fetch Customer_name and Order_name from Customer as well as Order table.
Exporting data from Progress Database Query :
User can export the data from Progress database query. The syntax is different than SQL export syntax.
Syntax for Exporting :
OUTPUT TO “Path of Export” .
FOR EACH Table_Name NO-LOCK :
EXPORT table_name .
END.
OUTPUT CLOSE.
Real Life Example :
If user wants to export the data from order table following steps needs to use,
OUTPUT TO “c:\Order_data\order.d” .
FOR EACH Order NO-LOCK :
EXPORT Order .
END.
OUTPUT CLOSE.
The above query will export the data from Order table without any delimiter.
Import Data from Database Query :
There are so many requirements where user wants to import the data from sources.The syntax to import data is quite different than regular SQL Syntax.
Syntax:
INPUT FROM VALUE (“File Name”).
Repeat:
Create Table_name.
Import Table_name.
END.
INPUT CLOSE.
Real Life Example:
If user wants to import data from Order backup table to Order table.
Steps :
INPUT FROM VALUE (“c:\Order_bkp\Order-backup.d”).
Repeat:
Create Order.
Import Order_bkp.
END.
INPUT CLOSE.
The above table will create Order table from Order_Bkp table.