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.

SQL Stored Procedures

Hi Guys,

Today I am writing about Stored Procedures. What are stored Procedures, How to write them, How to compile and how to call them?

Stored Procedures

Stored procedures are programs that are called from SQL. These programs can be written using the SQL procedure language, but they may also be written using RPG.

So how do you write SQL Procedure? It is simple. You just need to write SQLRPGLE with “CREATE PROCEDURE” SQL Statement

Example 1.

C/Exec SQL

C+CREATE PROCEDURE GetName 

C+ (IN ProgLang CHARACTER(10), 

C+IN Comp CHARACTER(20), 

C+INOUT FOUND INTEGER(4)) 

C+LANGUAGE SQL 

C+SELECT COUNT(*) INTO FOUND FROM TestLib/NameData 

C+WHERE TechLang = ProgLang AND Employeer = Comp

C/End-Exec

How to compile the Procedure?

You can compile the above code like any other SQLRPGLE code

How to call above procedure?

Create another RPGLE code as below

D GetName        PI

     D  ProgLang                     10A

     D  COMP                         20A

     D  Found                         4 

.

.

.

.

    Call GetName( :EMPID, :Comp, :Found);

Example 2.

c/EXEC SQL                                                

c+ Create procedure OrderSumm(                            

c+   in    partnerID            NUMERIC(5,0),             

c+   in    storeNumber          NUMERIC(7,0) )            

C+ LANGUAGE RPGLE NOT DETERMINISTIC                       

c+ READS SQL DATA                                         

c+ EXTERNAL NAME EXTPGM01                                 

c+ PARAMETER STYLE GENERAL                                

c/END-EXEC                      

Above code will be called as below

Create another RPGLE code as below

D OrderSumm        PI

     D  PartnerID                     5

     D  StoreNumber                   7

.

.

.

.

    Call OrderSumm( :PartnerId, :StoreNumber);

In example 2 when OrderSumm is called, it in returns call further a new program “EXTPGM01”

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

Happy Learning

XML Parsing using XML-INTO

Something on XML-INTO

IBM has introduce a new OP-Code XML-INTO, which has make XML parsing as smooth as butter..:)

Steps to use XML-INTO

1.        Define a Data structure or array of data structures that matches the layout of XML document.

2.        Run XML-INTO (This loads the values from XML to Data Structure

Below sample code will help you write simple XML Parsing code.

D qualName        DS                   QUALIFIED

D   FrstName                    10A

D   LastName                    10A

D EmpInfo        DS                   QUALIFIED

D* Employee Name

D   EMPNme                             LIKEDS(qualName)

D* Manager Name

D   MName                              LIKEDS(qualName)

D* Manager Last Name

D MLName          S             10A    VARYING

 //  Assume file Emplpoyee.xml contains the following lines:

 //<empinfo>

 // <EMPNAME><Frstname>Scott</Frstname><LastName>Sherbert</LastName></EMPNAME>

 // <MNANE Frstname="Ken" LastName="Mahajan"></MNAME>

 //</empinfo>

 /free

    xml-into empInfo %XML('Employee.xml' : 'doc=file');

 // EMPInfo.EMPNme.Frstname='Scott' EMPInfo.EMPNme.Lastname = 'Sherbert'

 // EMPInfo.MName.Frstname='Ken' EMPInfo.MName.Lastname = 'Mahajan'

 // Parse the "empinfo/Mname/Lastname" information into variable

 // "MLName".  Use the "path" option to specify the location

 // of this information in the XML document.

  xml-into MLName %XML('Employee.xml'

                   : 'doc=file path=empinfo/MName/lastname';

 // MLName = 'Mahajan'

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

Happy Learning

Advantages and Disadvantages of AS400

Hi Guys, 

One of my friend over Linkedin want to know the advantages and disadvantages of AS400. So thought of sharing the same.

Advantage of AS400

The main advantages of AS400 over other are

 1. Stability

You would have sometime re-booted your desktop twice or thrice a day. But can you afford to do the same with your Servers. I know few organizations where IT admin are not aware of AS/400 Machine reboot date

2.        Integration

Using AS400 you need not worry about integration of database, Programing languages to use

Will the security system work with the database and will the database work with the operating system, etc are questions that do not apply to AS/400. It is amazing how much time can be invested in making these things work together, but on iSeries (as with AS/400 and S/38), the question is simply irrelevant.

3.        Security

OS/400 is an object orientated operating system it is virtually immune to viruses.

Disadvantage of AS400

To count disadvantages of AS400, there would be less. Still to count, below are the few disadvantages of AS400

1.       Cost - 

          The hardware costs for NEW machines are significantly higher than other platforms.

2.       Developer Resource – 

        Nowadays RPG Developer are a rare breed of developers which comes with high Dollar value as compare to other development languages like JAVA

3.       Mindset - 

        The biggest problem is mindset of people. An AS400 is seen as a clumsy box capable of only green screen applications and suitable only for large businesses having large cash transactions. This is simply untrue. The box needs almost no supervision. All you ever have to do once it's loaded up is change the backup tapes. BUT, it is perceived as old technology when in fact it is probably more robust and cutting edge than most of it's competitors.

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

Happy Learning

Few SQL Questions

Hi Guy, 

This time I am back with Few SQL Questions....

What will runsqlstm will do?

If we want to execute set of SQL statement then we can write all the SQL statement to be including with the source.

Type as SQL

Only insert, update and delete and no select is allowed.

Then use STRSQLSTM to execute the program

RUNSQLSTM   SRCFILE (LIB/TEST) SRCMBR (SQL01) COMMIT (*NONE)

Embedded SQL in RPGLE (SQLRPGLE)

Hi Guys,

Today I am here with Embedded SQL Program.

How to write embedded SQL Program.

SQLRPGLE is very simple if we can understand the flow of program. SQLRPGLE basically consist of below keyword

1.        Declare Cursor

2.        Open Cursor

3.        Fetch Cursor

4.        Close Cursor