IBM Mainframe Forum

by **gopurs** » Fri Sep 09, 2016 10:40 am

Table 1

Date        Code  Key
2016-08-01  D1234 KEY1
2016-08-02  D1214 KEY2
2016-08-01  D1214 KEY3
2016-08-01  P1234 KEY4
2016-08-01  P1234 KEY5

Table 2

Select all

code  key  line
D1234 KEY1 LN1
D1214 KEY2 LN1 
D1214 KEY3 LN1
D4444 KEY4 LN1
D1234 KEY4 LN2
D2222 KEY4 LN3 
D2343 KEY4 LN4
D2324 KEY4 LN5
D1234 KEY5 LN1

Above are the two table i have and I need to find the top 5 codes for the day. Will need to count from table 2 only if the code(first byte) in table1 is not 'D'. Join field for the two tables is the key field.

Count Output

Select all

2016-08-01      D1234  3 (1 from table2)                                                           2016-08-02          D1224  3                     
                     D1214  2 (won't count from table 2)                                                         D1334  2
                      D4444  1 (only the first one for the key will be taken for that key)                          D4444  2
                                                                                                                  D4434  1
                                                                                                          D9994  1
 

The count may be different for different days and the code may be different

Can I do this using SQL? I don't want to do it using temporary table as our Business objects dont support temporary table and so i was trying to see if there is any way to do this using SQL.

Any help or guidance is helpful.

Raj

by **NicC** » Fri Sep 09, 2016 1:37 pm

Please use the code tags when posting data, code or anything requiring spacing to be maintained. Your column headings did not line up with your column data and your report was all scrunched up. This request is made so often that searching the forum, or even just poking around it, will give you the information needed.

IBM Mainframe Forum

DB2 Count Report

DB2 Count Report

Re: DB2 Count Report