66374 - A FEDSQL CAS action results in the error message "GROUP BY position xx is not in target list"

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.

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;

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 ;

create table LGSources.LG_USG_DAILY1 {options replace=true} as

   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

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

Type:	Problem Note
Priority:	high

Date Modified:	2021-01-27 09:27:24
Date Created:	2020-07-27 02:35:20

Support

Problem Note 66374: A FEDSQL CAS action results in the error message "GROUP BY position xx is not in target list"

Operating System and Release Information