Top 10 DB2 SQL quries

RETRIEVE ONLY DUPLICATE ROWS FROM A TABLE:

SELECT A, B, C
FROM TABLE_A
GROUP BY B
HAVING COUNT(*) > 1

QUERY:

IF THE CHAR FIELD HAS FIRST DIGIT AS NUMBER
E.G. 1AA THEN THE VALUE SHOULD BE CONSIDERED AS '1'
12A THEN THE VALUE SHOULD BE CONSIDERED AS '12'
123 THEN THE VALUE SHOULD BE CONSIDERED AS '123'
A11 THEN THE VALUE SHOULD BE CONSIDERED AS '0'

ANSWER:

SELECT

CASE

SUBSTR(F1,1,3) BETWEEN '000' AND '999'

THEN INTEGER(SUBSTR(F_1,1,3))

WHEN SUBSTR(F1,1,2) BETWEEN '00' AND '99'

THEN INTEGER(SUBSTR(F_1,1,2))

WHEN SUBSTR(F1,1,1) BETWEEN '0' AND '9'

THEN INTEGER(SUBSTR(F_1,1,1))

ELSE 0

END

AS OUT

How to get the MAX count when group by some other field

SELECT USER_UPDATED, COUNT (USER_UPDATED) AS COUNT1

FROM CGDEV4.TWC39

GROUP BY USER_UPDATED

HAVING COUNT (USER_UPDATED) >= ALL

(SELECT COUNT (USER_UPDATED)

FROM CGDEV4.TWC39

GROUP BY USER_UPDATED);

How to Subtract the years, months and days separately from DATE

SELECT DATE ('2003-10-13') - DEC ('03') YEARS - DEC ('07') MONTHS - DEC ('13') DAYS FROM SYSIBM.SYSDUMMY1;

SELECT DATE ('2003-10-13') - DEC ('2000') YEARS –

DEC ('27') MONTHS - DEC ('45') DAY

FROM SYSIBM.SYSDUMMY1;

How To eliminate duplicate records except one

DELETE FROM TZCHC TD1

WHERE HOLIDAY_DATE<> (SELECT MIN (HOLIDAY_DATE)

FROM TZCHC TD2

WHERE TD1.LONG_DESC = TD2.LONG_DESC

AND TD2.LONG_DESC = 'CHRISTMAS DAY’);

How to find N th max or min

SELECT DISTINCT A.TIMESTAMP_UPDATED, A.USER_UPDATED

FROM CGDEV4.TWC39 A

WHERE 5 =

(SELECT COUNT (DISTINCT B.TIMESTAMP_UPDATED)

FROM CGDEV4.TWC39 B

WHERE B.TIMESTAMP_UPDATED >= A.TIMESTAMP_UPDATED)

How to Execute the SQL through JCL using utility

//CREATE EXEC PGM=IKJEFT01,DYNAMNBR=20

//SYSTSPRT DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DB2T)

RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) LIB('DB2T.RUNLIB.LOAD')

END

/*

//SYSIN DD *

SELECT COUNT(USER_UPDATED)

FROM CGDEV4.TWC39

WHERE USER_UPDATED='DPPLSPP'

GROUP BY USER_UPDATED

/*

How to count occurrence of letter in a string

SELECT (LENGTH('SUBASH') - LENGTH(REPLACE('SUBASH','S',''))) AS S_COUNT FROM SYSIBM.SYSDUMMY1

How to delete duplicated based on certain column retaining one

DELETE FROM TABLE1 A WHERE A.PRIMARYKEY<>(SELECT MIN(PRIMARYKEY) FROM TZCHC B WHERE A.COLUMN1= B.COLUMN1);

Correlated Sub Query

SELECT A.DRIVER_REFERENCE

FROM CGDEV4.TU4ML A

WHERE A.LICENSE_NUMBER = 'POTAPA*355B2' AND

A.DRIVER_REFERENCE =

(SELECT C.CLIENT_REFERENCE

FROM CGDEV4.TZ1SR C

WHERE A.DRIVER_REFERENCE = C.CLIENT_REFERENCE AND

SUBSTR(C.Z_UPPER_FIRST_NAME,1,1) = 'A' AND

SUBSTR(C.Z_UPPER_LAST_NAME,1,3) = 'JZB')

How to get the value with leading zeros

SELECT POLICY_NUMBER, DIGITS (BASE_PREM_TOT)

FROM DB2K.CGIST.TW4T0

If the value of BASE_PREM_TOT is 472.00 then it will be selected as like this 00000047200

How to get the characters after space including first character with in one string?

SELECT SUBSTR (LAST_NAME_COY_NAME, 1, 1)

SUBSTR (LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1, 1)

SUBSTR (LAST_NAME_COY_NAME,

LOCATE (' ', LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1) +1, 1)

FROM CGDEV4.TZ1SR WHERE CLIENT_REFERENCE <= '0000000001';

Here POSSTR (LAST_NAME_COY_NAME,' ') +1 will give the position after space.

LOCATE (' ', LAST_NAME_COY_NAME, POSSTR (LAST_NAME_COY_NAME,' ') +1) +1, 1)

Will Returns the starting position of the first occurrence of expression1 within expression2. If the optional expression3 is specified, it indicates the character position in expression2 at which the search is to begin. If expression1 is not found within expression2, the value 0 is returned

How to get the fields which contain low values OR high values at any place

SELECT ADDR

FROM TABLE1

WHERE LENGTH (ADDR) > LEBGTH (REPLACE (ADDR, X’00’,’’) ;

If you want high values then replace X’00’ with X’FF’

For best performance you can use this.

SELECT TOWN_SUBURB

FROM DB2T.CGDEV5.TZ1SR

WHERE TOWN_SUBURB LIKE '% %'

FETCH FIRST 20 ROWS ONLY

After writing this, go to command prompt and turn on hex. I.e. HEX ON

Then you will get like this

DCDC4764674 39250DC0CD0

Go and change above 4 (highlighted in red color) as 0 and save the changes. Then it will become low value in that position. Then turn off hex. I.e. HEX OFF

Then it will select the all TOWN_SUBURB fields which contain low value at any place

How to get seat_no from which continuously 4 seats available

SELECT A.seat_no

FROM Table1 A

WHERE A.availabilty = 'Y'

AND EXISTS

( SELECT COUNT(*) FROM Table1 B

WHERE B.seat_no BETWEEN

A.seat_no AND A.seat_no + 2

AND B.availability = 'Y'

HAVING COUNT(*) = 4 )

FETCH FIRST 1 ROWS ONLY WITH UR

How to left pad?

SQL>create table test( id number);

SQL>insert into test values(1);
SQL>insert into test values(2);
SQL>insert into test values(3);
SQL>insert into test values(4);

SQL> select * from test;

ID
----------
1
2
3
4

SQL> select id,lpad(id,8,'00') from test;

ID LPAD(ID,
---------- --------
1 00000001
2 00000002
3 00000003
4 00000004

Syntax of lpad function is Lpad(String, max length, character to be padded);

SQL> select id,lpad(id,8,'0') from test;

ID LPAD(ID,
---------- --------
1 00000001
2 00000002
3 00000003
4 00000004