SqlDbx Forum

Billbjr10

Member

Defining one date parameter for multiple date parameters

I have a sql query where I have to enter the date range in a number of rows. To save time, I thought I would 'define' the date range and apply it to all of the date parameters using the "SET" function. But I keep getting errors. Specifically ORA-00904: "VAR_DATE2" invalid identifier.

Here is the script:

--SPORTSBOOK WAGERS BY DATE RANGE
SET DEFINE OFF;
SET var_date1 = '07/01/2023';
SET var_date2 = '07/19/2023';

SELECT
  var_date1 || ' to ' || var_date2 AS DATE_RANGE,
  PTNID,
  FIRSTNAME,
  LASTNAME,
  SUM(SPORT_WAGERS) AS SPORT_WAGERS
FROM
(
  SELECT
    s.PTNID,
    s.FIRSTNAME,
    s.LASTNAME,
    a.DATEINSERTED,
    a.CREDITAMT AS SPORT_WAGERS
  FROM
    VW_ODBC_DAILYPTNALL a
    INNER JOIN VW_ODBC_DAILYPTNSUM s ON a.PTNID = s.PTNID
    AND a.DATEINSERTED BETWEEN TO_DATE(var_date1, 'MM/DD/YYYY') AND TO_DATE(var_date2, 'MM/DD/YYYY')
    AND s.STATUS = 1
    AND DEVTYPID = 4
  GROUP BY
    s.PTNID,
    s.FIRSTNAME,
    s.LASTNAME,
    a.DATEINSERTED,
    a.CREDITAMT
  ORDER BY
    s.PTNID,
    a.DATEINSERTED
)
GROUP BY
  PTNID,
  FIRSTNAME,
  LASTNAME;

sqldbxhelp

Administrator

Re: Defining one date parameter for multiple date parameters

SET is not Oracle SQL command. It's SQL*Plus specific construct
You can do what you want by using SqlDbx builtin script variables

http://www.sqldbx.com/Help/scr/html/ScriptVariables.htm