asked on Create Temp Table using ARRAY_AGG
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:
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:
Can you assist me in changing the code so it will allow me to create a temporary table of these results?
Thank you!
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
);
Can you assist me in changing the code so it will allow me to create a temporary table of these results?
Thank you!
SQL
Last Comment
PortletPaul
I would expect you have first to create the temporay table with the field definitions and than use INSERT in a second step to insert the data from a select statement.
ASKER
Hello Bembi and thank you for commenting. Are you able to provide an example of what you mean?
Is the database Postgres?
(Please identify which DB vendor because "SQL" just isn't enough by itself)
Why create a temp table at all? What are your requirements?
What is in the table TEMP_PPRx_FK_CostMinus_Grp?
How did that get created? (Can we see that query?)
Can you just use a CTE (common table expression) instead of a temp table?
These allow you to give the result of that query a name that you can subsequently use in some following query.
e.g.
(Please identify which DB vendor because "SQL" just isn't enough by itself)
Why create a temp table at all? What are your requirements?
What is in the table TEMP_PPRx_FK_CostMinus_Grp?
How did that get created? (Can we see that query?)
Can you just use a CTE (common table expression) instead of a temp table?
These allow you to give the result of that query a name that you can subsequently use in some following query.
e.g.
with myCTE 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_I
)
select *
from myCTE
Yes, it depends from your SQL dialect..., so I'm coming more from the MS SQL sites...
There you would define...
The second puts data into this table.
The format has to be the same, so the fields in the temporary table and what the select delivers.
There you would define...
DECLARE @tables2 TABLE (UserLogin NVARCHAR(400));
INSERT INTO @tables2
SELECT ...
The second puts data into this table.
The format has to be the same, so the fields in the temporary table and what the select delivers.
ASKER
Hello PortletPaul. thank you for your response. I'm working within Google BigQuery. I'm using a Temp table so that I may pass the values latter in the process. This is a method I've been using and has worked very well in the past. However, when I introduced the VALUE ARRAY_AGG functionality, I'm having issues getting the Temp table created.
.TEMP_PPRx_FK_CostMinus_Grp is created with the following code:
The next create temp table code (the one I'm having problems with) would isolate the rows boxed in red for use later in the process.
Any help you can provide would be appreciated. Actual examples of the code would also help as I'm not well versed in SQL.
Thank you!!
.TEMP_PPRx_FK_CostMinus_Grp is created with the following code:
CREATE TEMPORARY TABLE TEMP_PPRx_FK_CostMinus_Grp AS(
SELECT Facility_ID, CalcCostMinus, SUM(NdcCount) AS CalcResultCount, SUM(TOTALSPEND) AS TOTALSPEND
FROM TEMP_PPRx_FK_CostMinus
GROUP BY Facility_ID, CalcCostMinus
ORDER BY Facility_ID ASC, CalcResultCount DESC
);
The next create temp table code (the one I'm having problems with) would isolate the rows boxed in red for use later in the process.
Any help you can provide would be appreciated. Actual examples of the code would also help as I'm not well versed in SQL.
Thank you!!
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.