Hi,
Using oracle 11.2.0.3 on aix and have following sql - if remove having clause runs fine.
Have tried else 0 as weel - still same problem.
plant_cust_iss_rtrn_summaries and media range-hash partitioned.
{code}
SELECT
LATEST_RETAILER_MV.MULT_NAME,
count (
case when PLANT_CUST_ISS_RTRN_SUMMARIES.COPIES_INVOICED > 0 and MEDIA.ISS_TYPE_CODE NOT IN (SELECT ISSUE_TYPE FROM ISSUE_TYPES_EXC_FOR_QTIES)
then LATEST_RETAILER_MV.OUT_NUM
end
),
SUM(CASE WHEN PLANT_CUST_ISS_RTRN_SUMMARIES.SELLOUT_TYPE = 'y' and MEDIA.ISS_TYPE_CODE NOT IN (SELECT ISSUE_TYPE FROM ISSUE_TYPES_EXC_FOR_QTIES) then 1 else 0 end ) ,
sum(CASE WHEN MEDIA.ISS_TYPE_CODE NOT IN (SELECT ISSUE_TYPE FROM ISSUE_TYPES_EXC_FOR_QTIES) THEN PLANT_CUST_ISS_RTRN_SUMMARIES.COPIES_INVOICED END),
SUM(case when MEDIA.ISS_TYPE_CODE NOT IN (SELECT ISSUE_TYPE FROM ISSUE_TYPES_EXC_FOR_QTIES) then PLANT_CUST_ISS_RTRN_SUMMARIES.COPIES_INVOICED - PLANT_CUST_ISS_RTRN_SUMMARIES.CREDITS end),
nvl(DW.LATEST_PRODUCT_MV.PROD_FAMILY_NAME,DW.LATEST_PRODUCT_MV.PROD_NAME),
DW.LATEST_PRODUCT_MV.PROD_NAME,
MEDIA.ISS_OFFICIAL_ON_SALE_DATE,
MEDIA.ISS_RECALL_DATE_M,
MEDIA.ISS_EAN
FROM
LATEST_RETAILER_MV,
PLANT_CUST_ISS_RTRN_SUMMARIES,
MEDIA,
DW.LATEST_PRODUCT_MV,
LATEST_WHOLESALER_MV,
WHOLESALER
WHERE
( WHOLESALER.SPO_NUM=LATEST_WHOLESALER_MV.SPO_NUM )
AND ( PLANT_CUST_ISS_RTRN_SUMMARIES.CUS_PLANT_ID=WHOLESALER.DIMENSION_KEY )
AND ( PLANT_CUST_ISS_RTRN_SUMMARIES.PLANT_ISSUE_ID=MEDIA.DIMENSION_KEY )
AND ( MEDIA.PROD_NUM = DW.LATEST_PRODUCT_MV.PROD_NUM and MEDIA.DIMENSION_KEY = MEDIA.PLIS_ID )
AND ( PLANT_CUST_ISS_RTRN_SUMMARIES.OUT_NUM=LATEST_RETAILER_MV.OUT_NUM )
AND ( WHOLESALER.SPO_ID IS NOT NULL )
AND
(
LATEST_WHOLESALER_MV.BRA_NAME = 'DUBLIN'
AND
MEDIA.ISS_RECALL_DATE_M >= '01-JAN-2013'
AND
(
MEDIA.ISS_OFFICIAL_ON_SALE_DATE < '07-JAN-2013
AND
MEDIA.ISS_OFFICIAL_ON_SALE_DATE > '01-01-2013 00:00:00'
)
)
GROUP BY
LATEST_RETAILER_MV.MULT_NAME,
nvl(DW.LATEST_PRODUCT_MV.PROD_FAMILY_NAME,DW.LATEST_PRODUCT_MV.PROD_NAME),
DW.LATEST_PRODUCT_MV.PROD_NAME,
MEDIA.ISS_OFFICIAL_ON_SALE_DATE,
MEDIA.ISS_RECALL_DATE_M,
MEDIA.ISS_EAN
HAVING
sum(CASE WHEN MEDIA.ISS_TYPE_CODE NOT IN (SELECT ISSUE_TYPE FROM ISSUE_TYPES_EXC_FOR_QTIES) THEN PLANT_CUST_ISS_RTRN_SUMMARIES.COPIES_INVOICED END) > 0
{code}