We are using Netezza 4.6.2.
We are trying to execute the following query
select distinct T.DATE_ as COLLECTIONDATE, T.Market_Code, T.Market_Name, T.DS_ID, T.Distribution_Short_Name, T.AFFILIATIONNAME, T.SS_Name,
coalesce( max(T.Total_Tuning),0) as Total_Tuning, coalesce(max(T.ACONTENT_DataType ),0)as ACONTENT_DataType,
coalesce( max( T.AFINAL_DataType),0)as AFINAL_DataType, coalesce(max(T.ASIG_DataType),0)as ASIG_DataType,
coalesce(max(T.VCONTENT_DataType),0)as VCONTENT_DataType, coalesce(max(T.VFINAL_DataType),0)as VFINAL_DataType
from (
select distinct T.METERCOLLECTIONDATE as DATE_, Sample.MARKETNAME as Market_Name, Sample.MARKETCODE as Market_Code, DS.DISTRIBUTIONSOURCEID as DS_ID,
DS.DISTRIBUTIONSOURCESHORTNAME as Distribution_Short_Name, DS.AFFILIATIONNAME as AFFILIATIONNAME, SS.SIGNALSOURCENAME as SS_Name,
(sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME))/60 as Total_Tuning,
case when (CE.DATATYPEID= 4) then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID ,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))/60 end as ACONTENT_DataType,
case when (CE.DATATYPEID= 2) then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))/60 end as AFINAL_DataType,
case when (CE.DATATYPEID= 6) then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))/60 end as ASIG_DataType,
case when (CE.DATATYPEID= 5) then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))/60 end as VCONTENT_DataType,
case when (CE.DATATYPEID= 3) then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))/60 end as VFINAL_DataType
from SAMPLE Sample, DISTRIBUTIONSOURCE DS, SIGNALSOURCE SS, CREDITENGINE CE, FINALCREDITMEDIAEVENTMINUTEBYDEVICE T
where Sample.DMACODE<>0 and
T.METERCOLLECTIONDATE >= ‘2010-10-01’
and
T.METERCOLLECTIONDATE <= ‘2010-10-31’ and
((Sample.SAMPLEID = T.SAMPLEID) and
(Sample.MARKETCODE = T.MARKETCODE)) and
(DS.DISTRIBUTIONSOURCEKEY = T.DISTRIBUTIONSOURCEKEY) and
(CE.CREDITENGINEID = T.CREDITENGINEID) and
((Sample.EFFECTIVESTARTDATE <= T.METERCOLLECTIONDATE) and (Sample.EFFECTIVEENDDATE >= T.METERCOLLECTIONDATE)) and
(T.RELEASEDFORPROCESSINGFLAG = 'true') and
(SS.SIGNALSOURCEID = T.SIGNALSOURCEID) and
DS.DISTRIBUTIONSOURCEID NOT IN ( 2,0,888000,888111,888222,888333,888444,888555,888666,999999,999000,17770,777777,10202, 10199,10200,10213,10201,17796,10201,555555,10198)
) T
group by T.DATE_, T.Market_Name, T.Market_Code, T.DS_ID, T.Distribution_Short_Name, T.AFFILIATIONNAME, T.SS_Name
Explanation:
When we executed the query, the errror was logged as follows.
Can you articulate why this error occurred and suggest way to resolve?
logs (output)
2011-02-04 05:25:09.794228 EST [25252] ERROR: Unable to identify an operator '//' for types 'NUMERIC' and 'NUMERIC'
You will have to retype this query using an explicit cast
==============================================================
in the above SQL there are multiple references to //0, as evidence:
==================
sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID
,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME))//0 as Total_Tuning,
case
when (CE.DATATYPEID= 4)
then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID
,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))//0
end as ACONTENT_DataType
,
case when (CE.DATATYPEID= 2)
then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID
,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))//0
end as AFINAL_DataType,
case when (CE.DATATYPEID= 6)
then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID
,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))//0
end as ASIG_DataType,
case when (CE.DATATYPEID= 5)
then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID
,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))//0
end as VCONTENT_DataType,
case when (CE.DATATYPEID= 3)
then (sum(T.RANGEROUNDEDCOUNTOFSECONDS)over ( partition by T.METERCOLLECTIONDATE,Sample.MARKETNAME,DS.DISTRIBUTIONSOURCEID
,DS.DISTRIBUTIONSOURCESHORTNAME,DS.AFFILIATIONNAME,SS.SIGNALSOURCENAME,CE.DATATYPEID))//0
end as VFINAL_DataType
from
SAMPLE Sample,
