SqlDbx
You are not logged in.
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;
Offline
SET is not Oracle SQL command. It's SQL*Plus specific construct
You can do what you want by using SqlDbx builtin script variables
Offline