troubleshooting Question

Power BI SQL Anywhere Aggregate Null error

Avatar of Wm Allen Smith
Wm Allen Smith asked on
Sybase DatabaseSQL
6 Comments2 Solutions94 ViewsLast Modified:
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:
"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_Num


This is written in Sybase SQL. Thanks for your assistance.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros