IBM Mainframe Forum

by **GuyC** » Tue Dec 06, 2011 2:16 pm

I had a question via PM. that's not what PM is for.

rekhamf wrote:Hi ,

I have 55 querys similar like this in my pgm to get different count from the table , is that possible to comibine the query in to single dml to improve effeciency ?
Select all
EXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-3WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 24 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 18 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '2'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC

EXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-2WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 17 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 11 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '4'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC

EXEC SQL
SELECT COUNT(*)
INTO :WS-LINEIDX4-1WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN
TIMESTAMP(CURRENT DATE - 10 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 04 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '5'
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY
END-EXEC
rekhamf

by **GuyC** » Tue Dec 06, 2011 2:27 pm

And the answer is : yes, you can via Case and Sum

Select all

Select
,SUM(case when condition1 then 1 else 0 end) as count1
,SUM(case when condition2 then 1 else 0 end) as count2
,SUM(case when condition3 then 1 else 0 end) as count3
From Table1
where (condition1 or condition2 or condition3)
Group by Something

You should shift a little in the conditions : move the mutual ones to the where clause and combine some periods.
in your case :

Select all

select
SUM(case when LOG_TS BETWEEN TIMESTAMP(CURRENT DATE - 24 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 18 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '2'
then 1 else 0 end ) as LINEIDX4_3WEEK ,
SUM(case when LOG_TS BETWEEN TIMESTAMP(CURRENT DATE - 17 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 11 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '4'
then 1 else 0 end ) as LINEIDX4_2WEEK ,
SUM(case when LOG_TS BETWEEN TIMESTAMP(CURRENT DATE - 10 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 04 DAYS,'00:00')-1 MICROSECOND)
AND CLASS = '5'
then 1 else 0 end ) as LINEIDX4_1WEEK
FROM EMP_LOG
WHERE LOG_TS BETWEEN TIMESTAMP(CURRENT DATE - 24 DAYS,'00:00') AND
(TIMESTAMP(CURRENT DATE - 04 DAYS,'00:00')-1 MICROSECOND)
AND CLASS in ('2','4','5')
AND PLAN = '7'
AND ACTION = 'I'
GROUP BY EMPLOY

IBM Mainframe Forum

Multiple counts on same table

Multiple counts on same table

Re: Multiple counts on same table