Fetch Second Highest salary from table:
SELECT MAX(A.EMP_SAL)
FROM EMP_TAB A
WHERE A.EMP_SAL <
(SELECT MAX(B.EMP_SAL)
FROM EMP_TAB B)
WITH UR;
Fetch 10th(Nth) Highest salary from table:
SELECT A.EMP_SAL
FROM EMP_TAB A
WHERE 10 IN
(SELECT COUNT(*)
FROM EMP_TAB B
WHERE A.EMP_SAL <= B.EMP_SAL)
WITH UR;
Create Table Definitions with referential integrity:
CREATE TABLE
DEPT_TAB
(DEPT_ID CHAR(05) NOT NULL PRIMARY KEY,
DEPT_NAME CHAR(20) NOT NULL)
IN D13A10B1.T13A10B1;
CREATE UNIQUE INDEX INX ON DEPT_TAB(DEPT_ID;
CREATE TABLE
EMP_TAB
(EMP_ID CHAR(05) NOT NULL PRIMARY KEY,
EMP_NAME CHAR(20) NOT NULL,
EMP_SAL DECIMAL(9,2) NOT NULL,
DEPT_ID CHAR(05) NOT NULL
FOREIGN KEY(DEPT_ID)
REFEENCES DEPT_TAB
ON DELETE CASCADE)
IN D13A10B1.T13A10B1;
DB2 Datatypes and corresponding Cobol declaration with example:
CREATE TABLE EMP_TAB (
EMP_NAME CHAR(08) NOT NULL,
EMP_FULLNM VARCHAR(20) NOT NULL,
EMP_AGE SMALLINT NOT NULL,
EMP_SAL INT NOT NULL,
EMP_ACCT_NO BIGINT NOT NULL PRIMARY KEY,
EMP_TRANS_AMT DECIMAL(12,3) NOT NULL,
EMP_JOIN_DATE DATE,
EMP_LOGIN TIME,
EMP_DOB_TIMESTAMP TIMESTAMP)
IN D13DB01.T13TB01;
Cobol Declaration:
01 EMP-TAB.
05 EMP-NAME PIC X(08).
05 EMP-FULLNM.
10 EMP-FULLNM-LN PIC S9(04) COMP.
10 EMP-FULLNM-TX PIC X(20).
05 EMP-AGE PIC S(04) COMP.
05 EMP-SAL PIC S9(09) COMP or COMP-5.
05 EMP-ACCT-NO PIC S9(18) COMP-5.
05 EMP-TRANS-AMT PIC S9(09)V9(03) COMP-3.
05 EMP-JOIN-DATE PIC X(10).
05 EMP-LOGIN PIC X(08) .
05 EMP-DOB-TIMESTAMP PIC X(26).
------------------------------------------
CHAR(n)
05 EMP-NAME PIC X(08).
EMP_NAME CHAR(08) NOT NULL
VARCHAR(n)
01 EMP-FULLNM.
05 EMP-FULLNM-LN PIC S9(04) COMP.
05 EMP-FULLNM-TX PIC X(20).
SMALLINT
05 EMP-AGE PIC S(04) COMP.
EMP_AGE SMALLINT NOT NULL
INT
05 EMP-SAL PIC S9(09) COMP or COMP-5.
EMP_SAL INT NOT NULL
BIGINT
05 EMP-ACCT-NO PIC S9(18) COMP-5.
EMP_ACCT_NO BIGINT NOT NULL
DECIMAL
05 EMP-TRANS-AMT PIC S9(09)V9(03) COMP-3.
EMP_TRANS_AMT DECIMAL(12,3)
DATE
05 EMP-JOIN-DATE PIC X(10).
EMP_JOIN_DATE DATE
TIME
05 EMP-LOGIN PIC X(08) .
EMP_LOGIN TIME
TIMESTAMP
05 EMP-DOB-TIMESTAMP PIC X(26).
EMP_DOB_TIMESTAMP TIMESTAMP
Example of DCLGEN:
******************************************************************
* DCLGEN TABLE(PTI.ACCUM_STRATEGY) *
* LIBRARY(hlq.hlq1.DCLGEN1) *
* LANGUAGE(COBOL) *
* STRUCTURE(ACCUM_STRATEGY) *
* QUOTE *
* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS *
******************************************************************
EXEC SQL DECLARE PTI.ACCUM_STRATEGY TABLE
( STRATEGY_CREATOR CHAR(8) NOT NULL,
STRATEGY_NAME CHAR(8) NOT NULL,
SEQUENCE_FIELD SMALLINT NOT NULL,
INTERNAL_TYPE SMALLINT NOT NULL,
DATA VARCHAR(2048) NOT NULL
) END-EXEC.
******************************************************************
* COBOL DECLARATION FOR TABLE PTI.ACCUM_STRATEGY *
******************************************************************
01 ACCUM_STRATEGY.
10 STRATEGY-CREATOR PIC X(8).
10 STRATEGY-NAME PIC X(8).
10 SEQUENCE-FIELD PIC S9(4) USAGE COMP.
10 INTERNAL-TYPE PIC S9(4) USAGE COMP.
10 DATA.
49 DATA-LEN PIC S9(4) USAGE COMP.
49 DATA-TEXT PIC X(2048).
******************************************************************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 5 *
******************************************************************
No comments:
Post a Comment