It would help if you explained in words what the logic of that query is.
Why is it grouping by a value that is NOT in the SELECT list?
Why is it NOT grouping by a value COV that IS in the select list?
Looks like you could either code this as a series of INSERT statements. Or code it as one large query that UNIONs a series of indvidual sub queries.
One way to simplify that might be to make a little macro that just generates the SELECT statement for one COV value.
%macro one(COV);
SELECT
CASE WHEN state IN ('05','55','75') THEN 'California'
WHEN state IN ('43','53') THEN 'Texas'
WHEN state IN ('32','52') THEN 'New York'
WHEN state = '09' THEN 'District of Columbia'
ELSE initcap(u92.statename_u(state))
END AS STATE
, %bquote('&COV') as Cov
, int(coalesce(nullif(asln_&COV,''),'0')) as asln
, double(sum(
u92.Ep_auto_annual_u(date_effective_date, date_expiration_date
,date_accounting_date, Integer(201712), prem_wp_&COV)
)) as prem_ep_ttl
FROM v23.auto_prem_v
WHERE state not IN( '60','61','64' )
AND business_group LIKE 'B01%'
AND YEAR IN ( 2017, 2016 )
GROUP BY
state
, cov
, asln
%mend one;
Then you could use it in either structure.
create table work.asln as
select * from connection to db2
( %one(A) )
;
insert into work.asln
select * from connection to db2
( %one(B) )
;
...
Or
create table work.asln as
select * from connection to db2
( %one(A)
union
%one(B)
union .....
)
;
If you wanted you could either then use CALL EXECUTE or macro logic to generate the code above.
But for your little example of 19 names it might just be easier to use the editor to copy and paste and then change the value passed to the macro call.
... View more
