SUPPORT / SAMPLES & SAS NOTES
Problem Note 66374: A FEDSQL CAS action results in the error message "GROUP BY position xx is not in target list"
 |  |  |  |  |
Executing the FEDSQL procedure with GROUP BY and SELECT DISTINCT statements in SAS® Cloud Analytic Services (CAS) on SAS® Viya® 3.5 can result in error messages similar to the following:
ERROR: GROUP BY position 4 is not in target list
ERROR: The action stopped due to errors.
ERROR: The action stopped due to errors.
This syntax example can cause this issue:
proc fedsql sessref=mysession _diag _method;
create table LGSources.LG_USG_DAILY1 {options replace=true} as
SELECT DISTINCT pk_customer_id,
intck('MONTH',makedate(usage_year,usage_month,1),
(select max(makedate(usage_year,usage_month,1)) from LGSources.LG_USG_DAILY ))
as mago,
sum(curr_sec_outld_offpk) as curr_sec_outld_offpk
FROM LGSources.LG_USG_DAILY
GROUP BY pk_customer_id, usage_year, usage_month
;
quit;
create table LGSources.LG_USG_DAILY1 {options replace=true} as
SELECT DISTINCT pk_customer_id,
intck('MONTH',makedate(usage_year,usage_month,1),
(select max(makedate(usage_year,usage_month,1)) from LGSources.LG_USG_DAILY ))
as mago,
sum(curr_sec_outld_offpk) as curr_sec_outld_offpk
FROM LGSources.LG_USG_DAILY
GROUP BY pk_customer_id, usage_year, usage_month
;
quit;
The following workaround can circumvent the error:
-
Run the query without the SELECT DISTINCT * statement and write the output to a temporary table.
-
Then run the SELECT DISTINCT * statement on the temporary table.
Here is an example of the syntax for the workaround:
create table temp_table {options replace=true} as
SELECT
pk_customer_id,
intck('MONTH',makedate(usage_year,usage_month,1),
(select max(makedate(usage_year,usage_month,1))
from LGSources.LG_USG_DAILY )) as mago,
sum(curr_sec_outld_offpk) as curr_sec_outld_offpk
FROM
LGSources.LG_USG_DAILY
GROUP BY
pk_customer_id, usage_year, usage_month ;
SELECT
pk_customer_id,
intck('MONTH',makedate(usage_year,usage_month,1),
(select max(makedate(usage_year,usage_month,1))
from LGSources.LG_USG_DAILY )) as mago,
sum(curr_sec_outld_offpk) as curr_sec_outld_offpk
FROM
LGSources.LG_USG_DAILY
GROUP BY
pk_customer_id, usage_year, usage_month ;
create table LGSources.LG_USG_DAILY1 {options replace=true} as
select distinct * from temp_table;
select distinct * from temp_table;
drop table temp_table;
Click the Hot Fix tab in this note for a link to instructions about accessing and applying the software update.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Viya | Linux for x64 | 3.5 | 2020.0.4 | Viya | Viya |