Exclude a sub-query from GROUP BY clause

[first_last]

I have two attribute fields that are implemented as sub-queries.

Hospitalization\Hosp Admsn Time\Starting Date

( SELECT smart_date(@Prompt(Starting Date)) FROM dual )

Hospitalization\Hosp Admsn Time\Ending Date

( SELECT smart_date(@Prompt(Ending Date)) FROM dual )

Each sub-query calls an Oracle function (smart_date) that converts a symbolic value, supplied by a prompt, to a date/time value. For example, "T-1" is "yesterday" which is 04/03/2019 (as of the date of this posting).

The two attribute fields are used in the Hosp Admsn Time in Range filter:

@Select(Hospitalization\Hosp Admsn Time) BETWEEN @Select(Hospitalization\Hosp Admsn Time\Starting Date) AND @Select(Hospitalization\Hosp Admsn Time\Ending Date)

The sub-queries are used in the SELECT (to allow the actual dates to be displayed on the report) and in the WHERE (to filter the date/time field).

An example query:

The resulting SQL:

SELECT
  DEP.ORGANIZATION
  ,Count(Distinct ( ENC.ID ))
  ,( SELECT smart_date(@Prompt(Starting Date)) FROM dual )
  ,( SELECT smart_date(@Prompt(Ending Date))+0.99999 FROM dual )
FROM ENC
RIGHT OUTER JOIN DEP  ON (DEP.DEPT_ID=ENC.DEPT_ID)
WHERE
  (
   ( ( ENC.ADMSN_TIME ) BETWEEN ( ( SELECT smart_date(@Prompt(Starting Date)) FROM dual ) ) AND ( ( SELECT smart_date(@Prompt(Ending Date))+0.99999 FROM dual ) )  )
  )
GROUP BY
  DEP.ORGANIZATION
  ,( SELECT smart_date(@Prompt(Starting Date)) FROM dual )
  ,( SELECT smart_date(@Prompt(Ending Date))+0.99999 FROM dual )

Unfortunately, running the query results in an Oracle error:

ORA-22818: subquery expressions not allowed here

If I remove the sub-queries from the GROUP BY clause, the query works as expected:

SELECT
  DEP.ORGANIZATION
  ,Count(Distinct ( ENC.ID ))
  ,( SELECT smart_date(@Prompt(Starting Date)) FROM dual )
  ,( SELECT smart_date(@Prompt(Ending Date))+0.99999 FROM dual )
FROM ENC
RIGHT OUTER JOIN DEP  ON (DEP.DEPT_ID=ENC.DEPT_ID)
WHERE
  (
   ( ( ENC.ADMSN_TIME ) BETWEEN ( ( SELECT smart_date(@Prompt(Starting Date)) FROM dual ) ) AND ( ( SELECT smart_date(@Prompt(Ending Date))+0.99999 FROM dual ) )  )
  )
GROUP BY
  DEP.ORGANIZATION

Is there a way to configure the attributes so that they will be included in the SELECT clause, but excluded from the GROUP BY clause?

[Joe_Peters]

Try this: create a derived table to contain the prompt logic, e.g.:

SELECT smart_date(@Prompt(Starting Date)) as starting_date, smart_date(@Prompt(Ending Date)) as ending_date FROM DUAL

Call this "DT". Now join it to your ENC table and embed the BETWEEN Logic:

ENC.ADMSN_TIME BETWEEN DT.STARTING_DATE AND DT.ENDING_DATE

This should give you SQL that looks like:

SELECT
  DEP.ORGANIZATION,
  Count(Distinct( ENC.ID)),
  DT.STARTING_DATE,
  DT_ENDING_DATE
FROM ENC
  RIGHT OUTER JOIN DEP ON (DEP.DEPT_ID=ENC.DEPT_ID)
  INNER JOIN (SELECT smart_date(@Prompt(Starting Date)) as starting_date, smart_date(@Prompt(Ending Date)) as ending_date FROM DUAL) DT
      ON ENC.ADMSN_TIME BETWEEN DT.STARTING_DATE AND DT.ENDING_DATE
GROUP BY
  DEP.ORGANIZATION,
  DT.STARTING_DATE,
  DT_ENDING_DATE

(I'm assuming your prompt parameters contain more than just "Starting Date" and "Ending Date", but I left them as you had them)