Special Registers

Hi Guys,

This time, I know I am back with long interval....

I would like to thank Issak Brender to provide me examples on Special Register....

A special register is a storage area that is defined for an application process by the database manager and is used to store information that can be referenced in SQL statements. A reference to a special register is a reference to a value provided by the current server

SQL Query with CASE keyword

SQL example CASE WHEN

SQL CASE is a very unique conditional statement providing if/then/else logic for any ordinary SQL command. It then provides when-then-else functionality (WHEN this condition is met THEN do_this).

CASE can be used with both SELECT Clause and WHERE Clause

Select Name,DOB,

        Case

          When Substr( Digits( DOB ), 4, 2 ) = '01' Then 'JAN'

          When Substr( Digits( DOB ), 4, 2 ) = '02' Then 'FEB'

          When Substr( Digits( DOB ), 4, 2 ) = '03' Then 'MAR'

          When Substr( Digits( DOB ), 4, 2 ) = '04' Then 'APR'

          When Substr( Digits( DOB ), 4, 2 ) = '05' Then 'MAY'

          When Substr( Digits( DOB ), 4, 2 ) = '06' Then 'JUN'

          When Substr( Digits( DOB ), 4, 2 ) = '07' Then 'JUL'

          When Substr( Digits( DOB ), 4, 2 ) = '08' Then 'AUG'

          When Substr( Digits( DOB ), 4, 2 ) = '09' Then 'SEP'

          When Substr( Digits( DOB ), 4, 2 ) = '10' Then 'OCT'

          When Substr( Digits( DOB ), 4, 2 ) = '11' Then 'NOV'

          When Substr( Digits( DOB ), 4, 2 ) = '12' Then 'DEC'

        End , DeptMst.DeptManager

   From EmpData, DeptMst

Where DeptMst.Dept =

        Case

          When Substr( EMPID , 1, 1 ) = 'P' Then 'Purchase'

          When Substr( EMPID , 1, 1 ) = 'Q' Then 'Quality'

          Else ‘Sale’

          End

The above query will provide data from Employee Data file and Department Master File. It will have 4 columns as Employee Name, Date of Birth, Month of Birth and Department Manager’s Name. The query gets department Id from first Character of Employee ID.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

XML host Variable in ILE RPG

Hi Guys,

In Continuation of my previous post.

The ILE RPG precompilers support an XML host variable type:

To exchange XML data between the database server and an embedded SQL application, you need to declare host variables in your application source code.

To declare XML host variables in embedded SQL applications, in the declaration section of the application declare the XML host variables AS LOB data types

Host Variable

Host Variable Usage

Host variables are used in SQL statements to pass values between the database and the application program.

Using Variables in Statements

Host variables may be used:

·         to receive information from the database (SELECT INTO, FETCH, CALL and SET statements)

·         to assign values to columns in the database (CALL, INSERT and UPDATE statements)

·         to manipulate information taken from the database or contained in other variables (in expressions)

·         in dynamic SQL statements.

In all these contexts, the data type of the host variable or database column must be compatible with the data type of the corresponding database value or host variable.

Note: The colon is not part of the host variable name, and should not be used when the variable is referenced in host language statements.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

Whats new in Embedded SQL Programming for V7.1

Hi Guys,

I just thought of sharing the new components added by IBM for Embedded SQL Programming in V7.1. I would be covering each below point as a separate blog.

The ILE RPG precompilers support an XML host variable type:

ILE RPG does not have variables that correspond to the SQL data type for XML. In V7.1 IBM has extended the usage of SQLTYPE for declaring XML Host variable.

XML can be define as

1.        XML_BLOB

2.        XML_CLOB

3.        XML_DBCLOB

The ILE RPG precompilers support result set locator host variable type:

ILE RPG does not have variables that correspond to the SQL data type for Result Set. In V7.1 IBM has extended the usage of SQLTYPE for declaring Result Set variable using keyword “Result_Set_Locator”.

The DBGENCKEY (Debug encryption key) parameter is passed to the compiler for ILE programs and service programs.

The DBGENCKEY value from the CRTSQLxxx command is specified on the CRTxxxMOD or CRTBNDxxx commands.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

SQL where Clause (Row Value Expressions)

Hi Guys,

Again thanks to Issak (my linkedin Friend) who made me re-call the new feature which was introduce in V5R4 for SQL Where clause

Quoting his words.
SQL WHERE clause usually checks if a condition is true, false or unknown. In the past, each condition was evaluated for a single value. Starting with V5R4 groups or "rows" of values can be evaluated at once. These groups of values are called "row value expressions." 

Below is a simple example with constants: 

SELECT * FROM DEPTBL WHERE (DEPT, BRANCH)=('Shipping', '0012M')

The number of columns must be equal and of compatible data types. 

Statement provides the same result as 

SELECT * FROM DEPTBL WHERE DEPT='Shipping' AND BRANCH='0012M' 

Please note the parentheses usage in first example. 

The same technique applies to IN (SELECT…) predicate. However a constant list is not allowed when using a row value expression in this case. 

This saves some coding, but could look confusing in the beginning. It is just the matter of preferences.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

OLAP Functions in i-Series

 Hi Guys, 

One of my Linkedin friend asked me about OLAP Functions in i-Series, So just thought of writing a brief on OLAP.

OLAP functions in i-Series

 IBM gave developers an impressive set of Online Analytical Processing (OLAP) tools to aid with common ranking and numbering issues. The significance of these OLAP tools lies in the amount of work they do and the time it will save database programmers.

ROW_NUMBER

To start, the ROW_NUMBER is a huge blessing as it finally furnishes SQL with a built-in counter function. Here's a trivial example that adds a row number column to a result set:

Insert Into OrderItm

Select OrderNo,

       Row_Number() Over (Partition By OrderNo

                          Order By OrderNo,ItemNo),

       ItemNo,

       QtyOrdered,

       SalePrice

In this scenario, ROW_NUMBER will continually increment the ItemSeq field over all orders that are being imported and counter would get reset for every change in order number.