Wm Allen Smith
asked on
Power BI SQL Anywhere Aggregate Null error
I am trying to connect a Sybase data query via ODBC to Power BI.The query has a number of aggregate functions, all wrapped in ISNULL(field),0) Although I think I hit all of the aggregate function, I am still getting the ODBC connection errors "ODBC: SUCCESS_WITH_INFO [01003] [Sybase][ODBC Driver][SQL Anywhere]Null value eliminated in aggregate function" What am I missing?
Here is the code:
This is written in Sybase SQL. Thanks for your assistance.
Here is the code:
"SELECT Carrier, Bus_Type
, Rollover
, Expiring_Policy_Num
, Policy_Num, Insured
, State, Eff_Date
, isnull(sum(ba_prem),0) BA_Prem
, isnull(sum(gl_prem),0) GL_Prem
, isnull(sum(IM_prem),0) IM_Prem
, isnull(sum(PR_prem),0) PR_Prem
, BA_Prem+GL_Prem+IM_Prem+PR_Prem Total_Prem, Expiring_Prem
,isnull(max(Avg_BA_Sched_Mod),0) Avg_BA_Sched_Mod
,isnull( max(Avg_GL_Sched_Mod),0) Avg_GL_Sched_Mod
, isnull(max(Avg_PR_Sched_Mod),0)Avg_PR_Sched_Mod
, isnull(max(Drivers),0) Drivers
,isnull( max(Trucks),0) Trucks
,isnull(max(Expiring_Trucks),0) Expiring_Trucks
--,isnull( max(DT_Ratio_Factor),0) DT_Ratio_Factor
--,isnull( max(Op_Hours_Factor),0) Op_Hours_Factor
, isnull(max(Payroll_Density_Factor),0) Payroll_Density_Factor
, isnull(max(Telematics_Factor),0) Telematics_Factor
FROM
(
SELECT Distinct
C.CONTRACT_ID,
IF sp.Entity_ID = 2738103 THEN 'AmGUARD'
ELSE IF sp.Entity_ID = 12832100 THEN 'Plaza'
ELSE sp.Entity_Name ENDIF ENDIF Carrier,
c.Contract_Num Policy_Num,
IF c.New_To_Supplier_Ind = 'Y' THEN 'NB'
ELSE IF Carrier = 'AmGUARD' and date(c.Eff_Date) between '2017-07-01' and '2018-06-30' THEN 'NB'
ELSE 'RNL'
ENDIF ENDIF Bus_Type,
isnull(IF (SELECT Prin_Supp_Entity_ID from contract where contract_id = xContract_ID) = 12832100 THEN 'Y'
ELSE 'N' ENDIF
,'N') Rollover,
(select contract_num from contract where contract_id = XContract_ID) Expiring_Policy_Num,
e.Entity_Name Insured,
c.Contract_State State,
date(c.Eff_Date) Eff_Date,
if cn.Line_Code = 'BusAuto' THEN
(select SUM(isnull(qcc.Net_Change_Amount,0)) from Contract_Cost qcc join Contract_Article qca on qcc.Contract_ID = qca.Contract_ID and qcc.Line_ID = qca.Line_ID and qcc.Article_ID = qca.Article_ID join Contract_Line qcn on qca.Contract_ID = qcn.Contract_ID and qca.Line_ID = qcn.Line_ID where qca.Status not in ('x') and qcn.Line_Code = 'BusAuto' and qcc.Product_Cost_Type = 'p' and qcc.Contract_ID = c.Contract_ID)
Else 0
ENDIF BA_Prem,
if cn.Line_Code = 'GenLiab' THEN
(select SUM(isnull(qcc.Net_Change_Amount,0)) from Contract_Cost qcc join Contract_Line qcn on qcc.Contract_ID = qcn.Contract_ID and qcc.Line_ID = qcn.Line_ID and qcn.Line_Code = 'GenLiab' and qcc.Product_Cost_Type = 'p' and qcc.Contract_ID = c.Contract_ID)
Else 0
ENDIF GL_Prem,
if cn.Line_Code = 'CommInland' THEN
(select SUM(isnull(qcc.Net_Change_Amount,0)) from Contract_Cost qcc join Contract_Line qcn on qcc.Contract_ID = qcn.Contract_ID and qcc.Line_ID = qcn.Line_ID and qcn.Line_Code = 'CommInland' and qcc.Product_Cost_Type = 'p' and qcc.Contract_ID = c.Contract_ID)
Else 0
ENDIF IM_Prem,
if cn.Line_Code = 'Property' THEN
(select SUM(isnull(qcc.Net_Change_Amount,0)) from Contract_Cost qcc join Contract_Line qcn on qcc.Contract_ID = qcn.Contract_ID and qcc.Line_ID = qcn.Line_ID and qcn.Line_Code = 'Property' and qcc.Product_Cost_Type = 'p' and qcc.Contract_ID = c.Contract_ID)
Else 0
ENDIF PR_Prem,
if cn.Line_Code = 'BusAuto' THEN
(select avg(isnull(qcp.Value_4,0)) from Contract_Product qcp join CDAA_Vehicle qv on qcp.Contract_ID = qv.Contract_ID and qcp.Line_ID = qv.Line_ID where qcp.Contract_ID = c.Contract_ID and qcp.Value_4 is not null and qv.Class_Code NOT between '671' and '699')
Else 0
ENDIF Avg_BA_Sched_Mod,
if cn.Line_Code = 'GenLiab' THEN
(select avg(isnull(qcp.Value_4,0)) from Contract_Product qcp join CDGL_Class_Rating qg on qcp.Contract_ID = qg.Contract_ID and qcp.Line_ID = qg.Line_ID where qcp.Contract_ID = c.Contract_ID and qcp.Value_4 is not null and qcp.Product_Code not in ('TRIA'))
Else 0
ENDIF Avg_GL_Sched_Mod,
if cn.Line_Code = 'Property' THEN
(select avg(isnull(qcp.Value_4,0)) from Contract_Product qcp join CDPC_Class_Rating qpr on qcp.Contract_ID = qpr.Contract_ID and qcp.Line_ID = qpr.Line_ID where qcp.Contract_ID = c.Contract_ID and qcp.Value_4 is not null and qcp.Product_Code not in ('TRIA'))
Else 0
ENDIF Avg_PR_Sched_Mod,
if cn.Line_Code = 'BusAuto' THEN
cast((select distinct qcp.Option_1 from Contract_Product qcp join Contract qc on qcp.Contract_ID = qc.Contract_ID where qc.Revision_Num = 0 and qc.Last_Status in ('B','A','S') and qc.Contract_Num = c.Contract_Num and qcp.Line_Code = 'BusAuto' and qcp.Product_Code = 'GRDTELMFAC' ) as numeric)
Else 0
ENDIF Drivers,
if cn.Line_Code = 'BusAuto' THEN
cast((select distinct qcp.Option_2 from Contract_Product qcp join Contract qc on qcp.Contract_ID = qc.Contract_ID where qc.Revision_Num = 0 and qc.Last_Status in ('B','A','S') and qc.Contract_Num = c.Contract_Num and qcp.Line_Code = 'BusAuto' and qcp.Product_Code = 'GRDTELMFAC' ) as numeric)
Else 0
ENDIF Truck_Test,
if cn.Line_Code = 'BusAuto' and Truck_Test IS NULL THEN
(select isnull(count(*) ,0)
from Contract qc
JOIN Contract_Loc qcl on qc.Contract_ID = qcl.Contract_ID and qcl.status <> 'X' and qcl.Loc_Num is not null
JOIN Contract_Line qcn on qc.Contract_ID = qcn.Contract_ID
JOIN Contract_Article qca ON qcn.Contract_ID = qca.Contract_ID AND qcn.Line_ID = qca.Line_ID and qca.Loc_ID = qcl.Loc_ID
JOIN CDAA_Vehicle qv ON qca.Contract_ID = qv.Contract_ID AND qca.Line_ID = qv.Line_ID AND qca.Article_ID = qv.Article_ID
where qc.Contract_ID = c.Contract_Set_ID
and qca.Status not in ('x','d')
and qv.Class_Code NOT between '671' and '699'
and qv.Secondary_Class_Code = '03'
)
ELSE Truck_Test ENDIF Trucks,
if cn.Line_Code = 'BusAuto' THEN
cast((select distinct qcp.Value_1 from Contract_Product qcp join Contract qc on qcp.Contract_ID = qc.Contract_ID where qc.Revision_Num = 0 and qc.Last_Status in ('B','A','S') and qc.Contract_Num = c.Contract_Num and qcp.Line_Code = 'BusAuto' and qcp.Product_Code = 'GRDTELMFAC' ) as numeric)
Else 0
ENDIF DT_Ratio_Factor,
if cn.Line_Code = 'BusAuto' THEN
cast((select distinct qcp.Value_2 from Contract_Product qcp join Contract qc on qcp.Contract_ID = qc.Contract_ID where qc.Revision_Num = 0 and qc.Last_Status in ('B','A','S') and qc.Contract_Num = c.Contract_Num and qcp.Line_Code = 'BusAuto' and qcp.Product_Code = 'GRDTELMFAC' ) as numeric)
Else 0
ENDIF Op_Hours_Factor,
if cn.Line_Code = 'BusAuto' THEN
cast((select distinct qcp.Value_3 from Contract_Product qcp join Contract qc on qcp.Contract_ID = qc.Contract_ID where qc.Revision_Num = 0 and qc.Last_Status in ('B','A','S') and qc.Contract_Num = c.Contract_Num and qcp.Line_Code = 'BusAuto' and qcp.Product_Code = 'GRDTELMFAC' ) as numeric)
Else 0
ENDIF Payroll_Density_Factor,
if cn.Line_Code = 'BusAuto' THEN
cast((select distinct qcp.Value_4 from Contract_Product qcp join Contract qc on qcp.Contract_ID = qc.Contract_ID where qc.Revision_Num = 0 and qc.Last_Status in ('B','A','S') and qc.Contract_Num = c.Contract_Num and qcp.Line_Code = 'BusAuto' and qcp.Product_Code = 'GRDTELMFAC' ) as numeric)
Else 0
ENDIF Telematics_Factor,
if cn.Line_Code = 'BusAuto' THEN
(select isnull(count(*),0) from CDAA_Vehicle qv where qv.Contract_ID = ca.Contract_ID and qv.Line_ID = ca.Line_ID and qv.Class_Code NOT between '671' and '699')
Else 0
ENDIF Power_Units,
--aps.driver_cnt Drivers,
(select qc.Written_Prem from contract qc where qc.Last_Of_Set_Ind = 'Y' and qc.Last_Status in ('B','A','C','E') and qc.Contract_Num = c.Contract_Num) Total_Written_Prem,
(select distinct first(ce2.Contract_ID)
from Contract qc
join contract_proc qcx on qcx.New_Contract_ID = qc.Contract_ID
join Contract ce1 on ce1.Contract_ID = qcx.Contract_ID
join Contract ce2 on ce1.Contract_Set_ID = ce2.Contract_Set_ID
where
qc.Contract_Num = c.Contract_Num
and qc.Revision_Num = 0
and ce2.Last_Of_Set_Ind = 'Y'
--and ce2.Last_Status in ('A','E')
and qcx.Contract_ID <> c.Contract_ID
and ce2.Contract_Set_ID <> qc.Contract_Set_ID
and qcx.Proc_Status not in ('S','R')
) XContract_ID,
(select distinct first(ce2.Contract_Set_ID)
from Contract qc
join contract_proc qcx on qcx.New_Contract_ID = qc.Contract_ID
join Contract ce1 on ce1.Contract_ID = qcx.Contract_ID
join Contract ce2 on ce1.Contract_Set_ID = ce2.Contract_Set_ID
where
qc.Contract_Num = c.Contract_Num
and qc.Revision_Num = 0
and ce2.Last_Of_Set_Ind = 'Y'
and ce2.Last_Status in ('A','E')
and qcx.Contract_ID <> c.Contract_ID
and ce2.Contract_Set_ID <> qc.Contract_Set_ID
) XContract_Set_ID,
(select isnull( max(Full_Term_Prem),0) from contract where contract_id = XContract_ID ) Expiring_Prem,
(select isnull(count(*),0) --qv.VIN
from Contract qc
join Contract_Line cl2 on cl2.Contract_ID = qc.Contract_ID
join Contract_Article ca2 on ca2.Contract_ID = cl2.Contract_ID and ca2.Line_ID = cl2.Line_ID
join CDAA_Vehicle qv on qv.Contract_ID = ca2.Contract_ID and qv.Line_ID = ca2.Line_ID and qv.Article_ID = ca2.Article_ID
where qc.Contract_ID = XContract_ID
and ca2.Status not in ('x','d')
and qv.Class_Code NOT between '671' and '699'
and qv.Secondary_Class_Code = '03'
) Expiring_Trucks
FROM Contract c
JOIN Entity e on e.Entity_ID = c.Entity_ID
JOIN Entity sp on sp.Entity_ID = c.Prin_Supp_Entity_ID
LEFT JOIN Address ad on ad.Entity_ID = e.Entity_ID and ad.Dflt_Address_Ind = 'Y'
JOIN Entity uw ON c.Prin_Prod_Entity_ID=uw.Entity_ID
JOIN Contract_Loc cl on c.Contract_ID=cl.Contract_ID and cl.status <> 'X' and cl.Loc_Num is not null
JOIN Contract_Line cn on c.Contract_ID=cn.Contract_ID
JOIN Contract_Party cr ON c.Contract_ID=cr.Contract_ID AND cr.Line_Code=cn.Line_Code AND cr.Role_Type='SP'
JOIN Contract_Article ca ON cn.Contract_ID=ca.Contract_ID AND cn.Line_ID=ca.Line_ID and ca.Loc_ID=cl.Loc_ID
JOIN Contract_Cost cc on cc.Contract_ID=ca.Contract_ID AND cc.Line_ID=ca.Line_ID and cc.Article_ID=ca.Article_ID
LEFT JOIN Contract_Sub_Loc cs on cl.Contract_ID=cs.Contract_ID and cl.Loc_ID=cs.Loc_ID and cs.Sub_Loc_ID=ca.Sub_Loc_ID
LEFT JOIN Invoice_Item ii ON cn.Contract_ID=ii.Contract_ID and cn.Line_Code=ii.Line_Code and ii.Tran_Class='P'
LEFT JOIN Invoice i ON ii.Invoice_ID=i.Invoice_ID
JOIN Contract_Product cp ON cc.Contract_ID=cp.Contract_ID AND cc.Line_ID=cp.Line_ID AND cc.Product_ID=cp.Product_ID
JOIN Product p ON cp.Line_Code=p.Line_Code AND cp.Product_Code=p.Product_Code
LEFT JOIN Contract_Article pca ON cn.Contract_ID=pca.Contract_ID AND cn.Line_ID=pca.Line_ID and pca.Loc_ID=cl.Loc_ID and pca.Article_Type='pcund'
LEFT JOIN CDPC_Underwriting puw on puw.Contract_ID=pca.Contract_ID and puw.Line_ID=pca.Line_ID and puw.Article_ID=pca.Article_ID
LEFT JOIN Contract_Proc cx ON c.Contract_ID=cx.new_Contract_ID AND cx.Proc_Status <> 'R'
LEFT JOIN CDAA_Vehicle v ON ca.Contract_ID = v.Contract_ID AND ca.Line_ID=v.Line_ID AND ca.Article_ID=v.Article_ID
LEFT JOIN CDPC_Class_Rating pr ON ca.Contract_ID=pr.Contract_ID AND ca.Line_ID=pr.Line_ID AND ca.Article_ID=pr.Article_ID
LEFT JOIN CDGL_Class_Rating gl ON ca.Contract_ID=gl.Contract_ID AND ca.Line_ID=gl.Line_ID AND ca.Article_ID=gl.Article_ID
LEFT JOIN UD_APP_SCORE aps on aps.Contract_ID = c.Contract_ID
LEFT JOIN glj_set as GLJVS on i.glj_void_set_id = GLJVS.glj_set_id and GLJVS.post_date is not null
LEFT JOIN
(select cq.contract_id
from contract cq join invoice iq on cq.contract_id = iq.original_contract_id
where cq.revision_object_code = 'Reinstate'
and (select isnull(count(*),0) from invoice ixq where ixq.original_contract_id = cq.contract_id and date(ixq.post_Date) = date(getdate()) )=2
group by cq.contract_id
having sum(iq.Total_Prem)=0
) rx on rx.contract_id = c.contract_id
WHERE date(c.Eff_Date) >= '2017-07-01'
and c.Sub_Line = 'TOW'
--and c.Last_Of_Set_Ind = 'Y'
and c.Last_Status not in ('X','L','N')
and (SELECT if date(i.eff_date) > date(isnull(i.tran_date,'1999-12-31')) then date(i.eff_date)
else isnull(i.Tran_Date,date(c.Eff_Date))
endif) <= date(getdate())
order by Policy_Num, Eff_Date
)q
group by Carrier, Rollover, Expiring_Policy_Num, Policy_Num, Insured, State, Eff_Date, Total_Written_Prem,Expiring_Prem, Bus_Type
ORDER BY Policy_NumThis is written in Sybase SQL. Thanks for your assistance.
ASKER
It may be a warning but it Power BI will not connect to the source Sybase DB due to the error. Will try your solution--thanks so much!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Máté Farkas
Thank you for your assistance, Changing the Isnull syntax worked!
Dumb question-- where do I set the ODBC connection to treat warnings just warning not an error?
Thank you for your assistance, Changing the Isnull syntax worked!
Dumb question-- where do I set the ODBC connection to treat warnings just warning not an error?
ASKER
Thank you for your assistance, Changing the Isnull syntax worked!
Hi, I think you've marked my comment as the solution but the solution was provided by Máté Farkas
This is a normal behaviour for aggregate functions because aggregate functions (like sum) cannot work with NULL values they just ignore them.
So don't worry just set your ODBC connection to treat warnings just warning not an error.
If you want to eliminate these messages you can replace aggregates-isnull structures like this:
Open in new window
to this:Open in new window