Oracle SQL: Declaring variables to use in queries & subqueries
<p>I'm somewhat new to this Oracle database and I've inherited a large-ish query with several sub-queries. I'd like to optimize it by declaring a few variables to reference later on within the queries, but I can't seem to get it right.</p>

<p>Here is an extremely dumbed-down version of my query that, if I can get this in the right format, I think I can get the full version working:</p>

<pre><code>DECLARE
  outage_start_time INTEGER := 1456894800;
  outage_end_time INTEGER := 1457586000;
  DST_offset INTEGER := 0;
  time_zone_offset INTEGER := 4;
BEGIN
  WITH subquery AS (
  SELECT DISTINCT
    mytable.tickets AS "TicketID"
  FROM mytable
  WHERE
    mytable_start_time &gt;= outage_start_time AND
    mytable_end_time &lt; outage_end_time
  )
  SELECT DISTINCT
    subquery."TicketID" AS "Ticket ID",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.start_time/((60*60)*24)-(time_zone_offset + DST_offset)/24),'MM/DD/YYYY HH:MI:SS PM') AS "Outage Start",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.end_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage End"
    --other stuff
  FROM mytable
  LEFT OUTER JOIN subquery ON mytable.tickets = subquery."TicketID"
  ;
END;
</code></pre>

<p>The error that I'm getting is:</p>

<pre><code>Error starting at line : 1 in command -
DECLARE
  outage_start_time INTEGER := 1456894800;
  outage_end_time INTEGER := 1457586000;
  DST_offset INTEGER := 0;
  time_zone_offset INTEGER := 4;
BEGIN
  WITH subquery AS (
  SELECT DISTINCT
    mytable.tickets AS "TicketID"
  FROM T528
  WHERE
    mytable.start_time &gt;= outage_start_time AND
    mytable.end_time &lt; outage_end_time
  )
  SELECT DISTINCT
    subquery."TicketID" AS "Ticket ID",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.start_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage Start",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.end_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage End"
    --other stuff
  FROM T528
  LEFT OUTER JOIN subquery ON mytable.tickets = subquery."TicketID"
  ;
END;
Error report -
ORA-06550: line 7, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
</code></pre>

<p>It looks like my errors are coming at lines 1 and 7, so I clearly have no idea how to write this properly. A bit of help would be GREATLY appreciated. Thanks!</p>