I guess I didn't do as good as I thought at showing that the table (t1) is exactly the same in both queries. There are no missing dates as the results are limited by the same date range. I just cannot fathom why the computed column works in one query and not in the other. The only difference in the two queries is that the main table is linked to only an imported reference table in one query (computed doesn't work) and has an additional table joined in the other (computed does work). I am referencing the exact same column as the source of the computed field in both queries. COMPUTED FIELD WORKS: PROC SQL;
CREATE TABLE WORK.'GSP CLR WDLS Origin Country'n AS
SELECT DISTINCT t1.shp_dt FORMAT=MMDDYYS8. AS shp_dt,
t1.shp_trk_nbr,
/* CatCause */
(t2.sqi_grp_ctg_cd || t2.sqi_grp_caus_cd) LABEL="CatCause" AS CatCause,
/* MonthYr */
(Datepart(t1.shp_dt)) FORMAT=MONYY5. LABEL="MonthYr" AS MonthYr,
t1.orig_cntry_cd,
t2.sqi_grp_cls_cd
FROM SCANPRST.express_volume_visibility t1, SCANPRST.exprs_vol_vsbty_esqi_enhmt t2
WHERE (t1.shp_trk_nbr = t2.shp_trk_nbr AND t1.shp_dt = t2.shp_dt) AND (t1.orig_cntry_cd IN
('AF','AG','AI','AM','AN','AO','AS','AZ','BA','BD','BF','BG','BI','BJ','BN','BO','BT','BY','BZ','CD','CG',
'CI','CK','CM','CV','CY','DJ','DM','DZ','EC','EG','ER','ET','FJ','FM','FO','GA','GE','GF','GH','GI','GL','GM'
,'GN','GP','GR','GY','HN','HR','HT','ID','IL','IQ','IS','IT','JO','KE','KG','KH','KZ','LA','LB','LK','LR',
'LS','LY','MA','MD','ME','MG','MH','MK','ML','MN','MP','MR','MS','MT','MU','MV','NC','NE','NG','NI','NP','OM'
,'PE','PF','PG','PK','PT','PW','PY','QA','RE','RS','RU','RW','SA','SC','SK','SN','SR','SV','TD','TG','TL',
'TN','TO','TR','TZ','UA','UG','UZ','VI','VN','VU','WF','WS','ZW') AND t2.sqi_grp_ctg_cd IN
('C','O','P','R','S','T','V','W','X','Y')
AND t2.sqi_grp_caus_cd IN
('50','52','55','60','63','64','73','74','80','88','OV','U','UC') AND t1.shp_dt BETWEEN '1Jun2017'd AND
'28Feb2018'd AND t2.sqi_grp_cls_cd IN
(
'W',
'R'
));
QUIT; COMPUTED DOESN'T WORK: PROC SQL;
CREATE TABLE WORK.QUERY_FOR_EXPRESS_VOLUME_VISIBIL AS
SELECT DISTINCT t1.shp_trk_nbr,
t1.shp_dt FORMAT=MMDDYYS8. AS shp_dt,
t1.orig_cntry_cd,
t2.COUNTRY_NM,
/* MonthYr */
(DATEPART(t1.shp_dt)) FORMAT=MONYY5. LABEL="MonthYr" AS MonthYr
FROM SCANPRST.express_volume_visibility t1
INNER JOIN WORK.'COUNTRY NAME TABLE'n t2 ON (t1.orig_cntry_cd = t2.COUNTRY_CD)
WHERE t1.orig_cntry_cd IN ('AF','AG','AI','AM','AO','AS','AZ','BA','BD','BF','BG','BI','BJ','BN','BO','BT','BY',
'BZ','CD','CG','CI','CK','CM','CV','CY','DJ','DM','DZ','EC','EG','ER','ET','FJ','FM','GA','GE','GF','GH','GI'
,'GM','GN','GP','GR','GY','HN','HR','HT','ID','IL','IQ','IS','IT','JO','KE','KG','KH','KZ','LA','LB','LK',
'LR','LS','LY','MA','MD','ME','MG','MH','MK','ML','MN','MP','MR','MS','MT','MU','MV','NC','NE','NG','NI','NP'
,'OM','PE','PF','PG','PK','PT','PW','PY','QA','RE','RS','RU','RW','SA','SC','SK','SN','SR','SV','TG','TL',
'TN','TO','TR','TZ','UA','UG','UZ','VI','VN','VU','WS','ZW') AND t1.shp_dt BETWEEN '1Jun2017'd AND
'28Feb2018'd;
QUIT;
... View more