SQLnPLSQLGuru

Using the IN keyword, we can construct a more compact statement,where the set of possible values are enclosed in parentheses and separated by commas. Each row of Entry is investigated, and if TourID is one of the values in the set, then the WHERE condition is true, and that row will be returned. Using IN keyword SELECT e.MemberID FROM Entry e WHERE e.TourID IN (36, 38, 40) Using NOT IN keyword SELECT e.MemberID FROM Entry e WHERE e.TourID NOT IN (36, 38, 40) Conclusion The above examples useful to use IN and NOT IN key words in your SQL.

With the outer join, some table reordering is possible and recommended for efficiency.  Normally, hierarchical structures are built top-down, but when subviews are used, right-sided nesting can cause the structure to be built bottom-up.  Top-down execution is more efficient than bottom-up execution because bottom-up execution can cause throwaways. Throwaways are rows that are retrieved into the working set and then later discarded. SQL Expanded (Bottom-Up): SELECT* FROM Department LEFT JOIN         Employee LEFT JOIN Dependent           ON EmpNo=DpndEmpNo            ON DeptNo=EmpDeptNo   SQL Rewritten (Top-Down): SELECT * FROM Department LEFT JOIN Employee ON DeptNo=EmpDeptNo LEFT JOIN Dependent ON EmpNo=DpndEmpNo

We can compile DB2 PL/SQL either in: DB2 command line processor (CLP) DB2 CLPPlus IBM® Data Studio client The procedure which are written purely in SQL PL are called native SQL procedures. The procedures which are written in other than SQL/PL are called external stored procedures Cobol,PL/I,REXX,Java,C,C++,Assembler. The calling procedure interacts with WLM SAMPLE PROCEDURE DB2 ZO/S: OUT P_SUMSAL DECIMAL(11,2),OUT P_SQLCODE INTEGER)PACKAGE OWNER XYZ  QUALIFIER ABCDRESULT SETS 0  LANGUAGE SQL CREATE PROCEDURE SPA81(OUT P_CNT1 SMALLINT, VERSION V1 ISOLATION LEVEL CS  VALIDATE BIND P1: BEGIN DECLARE SQLCODE INEGER DEFAULT 0; SELECT COUNT(*), SUM(SALARY) INTO P_CNT1,P_SUMSAL FROM EMP; SET P_SQLCODE = SQLCODE END P1

The following are the key statements we use in SQL PL of DB2: Variable related statements DECLARE <variable> DECLARE <condition> SET statement (assignment statement) Conditional statements IF CASE expression Looping statements FOR WHILE Transfer of control statements GOTO ITERATE LEAVE RETURN Error management statements SIGNAL GET DIAGNOSTICS Detailed discussion I will give in the next sessions. Keep reading.