Need a ONLINE MAINFREAME Course/Poject Case Study) / Corporate Training/ application support ????

Lets schedule the class. Write me @ mfdtech@gmail.com, for more information refer to : http://onlinemainframe.blogspot.com/
Iam Willing to take up Freelancing projects on Cobol / MF. please mail me @ mfdtech@gmail.com

Google
 

Monday, February 4, 2008

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




1 comment:

Interview questions answers pdf said...

IBM DB2 Interview Questions and Answers
http://allinterviewquestionsandanswerspdf.blogspot.in/2016/06/top-32-ibm-db2-interview-questions-and.html