Search code examples
sqloracle-databaseobiee

OBIEE - Is it possible to have a sub query in SELECT statement

When I add the following line, I get an error: COUNT(select "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" FROM "Workforce Management - Worker Assignment Real Time" where "Worker"."Person Number" > 1000)

What I want to do is add a new column and have a COUNT for the number of employees with a person number > than 1000.

===================

SELECT 
   0 s_0,
   "Workforce Management - Worker Assignment Real Time"."Worker"."Employee First Name" s_1,
   "Workforce Management - Worker Assignment Real Time"."Worker"."Person ID" s_2,
   "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" s_3,
   "Workforce Management - Worker Assignment Real Time"."Worker"."Person Start Date" s_4,
   "Workforce Management - Worker Assignment Real Time"."Worker"."Termination Date" s_5,
   DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Worker Category") s_6,
   "Workforce Management - Worker Assignment Real Time"."Worker Assignment"."Head Count" s_7,
    COUNT(select "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" FROM "Workforce Management - Worker Assignment Real Time" where "Worker"."Person Number" > 1000) s_8

FROM "Workforce Management - Worker Assignment Real Time"

The error I get with Over()

Error generating view. Error getting cursor in GenerateHead
  Error Details
Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P:OI2DL65P
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)
State: HY000. Code: 27002. [nQSError: 27002] Near <(>: Syntax error (HY000)
State: HY000. Code: 26012. [nQSError: 26012] . (HY000)
SQL Issued: {call NQSGetQueryColumnInfo('SELECT COUNT(CASE WHEN "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" > 1000 THEN 1 END) OVER() FROM "Workforce Management - Worker Assignment Real Time"')}
SQL Issued: SELECT COUNT(CASE WHEN "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" > 1000 THEN 1 END) OVER() FROM "Workforce Management - Worker Assignment Real Time"
Solution

  • Use COUNT() window function with a CASE expression:

    COUNT(CASE WHEN "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" > 1000 THEN 1 END) OVER() s_8