Trying to run consecutive queries in Oracle-CodePudding

I am creating a temp table

CREATE TABLE TMP_PRTimeSum AS 
SELECT DISTINCT p.employee,
       SUM(p.wage_amount) AS wage_sum,
       SUM(p.hours) AS hour_sum

I then want to do a select from that temp table and show the results. I want to do it all in one query. It works if I run them as two separate queries. Is there any way to do this in one query?

CodePudding user response：

CREATE GLOBAL TEMPORARY TABLE a
ON COMMIT PRESERVE ROWS
AS
select * from b;

(add where 1=0 too if you didn't want to initially populate it for the current session with all the data from b).

CodePudding user response：

Is there any way to do this in one query?

No, you need to use one DDL statement (CREATE TABLE) to create the table and one DML statement (SELECT) to query the newly created table.

If you want to be really pedantic then you could do it in a single PL/SQL statement:

DECLARE
  cur SYS_REFCURSOR;
  
  v_employee VARCHAR2(20);
  v_wage     NUMBER;
  v_hours    NUMBER;
BEGIN
  EXECUTE IMMEDIATE
    'CREATE TABLE TMP_PRTimeSum (employee, wage_sum, hour_sum) AS
       SELECT P.EMPLOYEE,
              SUM(P.WAGE_AMOUNT),
              SUM(P.HOURS)
       FROM   table_name p
       GROUP BY p.Employee';

  OPEN cur FOR 'SELECT * FROM TMP_PRTimeSum';
  
  LOOP
    FETCH cur INTO v_employee, v_wage, v_hours;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_employee || ', ' || v_wage || ', ' || v_hours);
  END LOOP;
END;
/

db<>fiddle here

However, you are then wrapping the two SQL statements in a PL/SQL anonymous block so, depending on how you want to count it then it is either one composite statement, two SQL statements or three (2 SQL and 1 PL/SQL) statements.