Code
-- If using aggraviation function without group by --- it is the biggest group by
-- max ,min ,sum ,count ,avg -- how to handle the NULL value
select max(DECODE(lep1.ind_actual, 'A', lep1.dt_added)), --RETURN 1 RECORD FOR TABLE
min(DECODE(lep1.ind_actual, 'A', lep1.dt_added)) -- RETURN 1 RECORD FOR TABLE
/**//* DECODE(lep1.ind_actual, 'A', lep1.dt_added)*/ -- ERROR ;RETURN SAME COUNT AS TOTAL RECORDS IN TABLE
from lms_event_parts lep1
where LEP1.ID_CONTRACT = 1261
AND LEP1.ID_VERSION = 1127
AND lep1.TXT_PART_SERIAL = '4'
-- so add groub by 1 here
group by 1
一旦用了group by ,在结果集中的列,如果非group by 列,必须用聚合函数在最外层.
group by 1 == 没有group by列
-------------以下说明,having 子句也是一样的,
在Having 子句中,如果非group by 列,必须用聚合函数在最外层.
group by 1 == 没有group by列
Code
-------correction
select lep1.id_contract,
lep1.id_version,
SUM(DECODE(lep1.ind_actual, 'A', 1, 0)) a_count,
SUM(DECODE(lep1.ind_actual, 'P', 1, 0)) p_count,
max(DECODE(lep1.ind_actual,
'A',
lep1.dt_added /**//*,
to_date('12-31-1099', 'mm-dd-yyyy')*/)) dt_max_date,
lep1.TXT_PART_SERIAL /**//*,
lep1.ind_actual*/
from lms_event_parts lep1
where LEP1.ID_CONTRACT = 1261
AND LEP1.ID_VERSION = 1127
--
--
AND lep1.TXT_PART_SERIAL = '4' /**//*lep1.ind_actual in ('L','A')*/
group by lep1.id_contract, lep1.id_version, lep1.txt_part_serial
having SUM(DECODE(lep1.ind_actual, 'P', 1, 0)) != 8
---------- Error --------------
select lep1.id_contract,
lep1.id_version,
SUM(DECODE(lep1.ind_actual, 'A', 1, 0)) a_count,
SUM(DECODE(lep1.ind_actual, 'P', 1, 0)) p_count,
max(DECODE(lep1.ind_actual,
'A',
lep1.dt_added /**//*,
to_date('12-31-1099', 'mm-dd-yyyy')*/)) dt_max_date,
lep1.TXT_PART_SERIAL /**//*,
lep1.ind_actual*/
from lms_event_parts lep1
where LEP1.ID_CONTRACT = 1261
AND LEP1.ID_VERSION = 1127
--
--
AND lep1.TXT_PART_SERIAL = '4' /**//*lep1.ind_actual in ('L','A')*/
group by lep1.id_contract, lep1.id_version, lep1.txt_part_serial
having lep1.ind_actual = 'A'
