troubleshooting Question

Create Temp Table using ARRAY_AGG

Avatar of dhemple
dhempleFlag for United States of America asked on
SQL
6 Comments1 Solution16 ViewsLast Modified:
Hello Experts.

I'm a complete beginner when it comes to coding.  I found the below code and modified for my situation and it works.  It provides me a single row for the top CalcResultCount for each Facility_ID
Code:
#standardSQL
SELECT AS VALUE ARRAY_AGG(PPRx_FK_Grp ORDER BY CalcResultCount DESC LIMIT 1)[OFFSET(0)]
FROM TEMP_PPRx_FK_CostMinus_Grp PPRx_FK_Grp 
GROUP BY Facility_ID;

However, when I change the code to create a TEMPORARY TABLE, as I've done several times in the past, I get the following error:
#standardSQL
CREATE TEMPORARY TABLE TEMP_PPRx_FK_CostMinus_GrpTopOne AS(
SELECT AS VALUE ARRAY_AGG(PPRx_FK_Grp ORDER BY CalcResultCount DESC LIMIT 1)[OFFSET(0)]
FROM TEMP_PPRx_FK_CostMinus_Grp PPRx_FK_Grp 
GROUP BY Facility_ID
);
Query error: CREATE TABLE AS SELECT AS[VALUE|STRUCT] is unsupported

Can you assist me in changing the code so it will allow me to create a temporary table of these results?

Thank you!

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
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 1 Answer 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