CODEWITHSUNDEEP
sqlselectoracle11gsubquery
timetravelprimer
timetravelprimer

Reputation: 111

Can't use subquery in select statement. Returns "FROM expression missing"

I'm struggling with a subquery in a SELECT statement. I'm getting an error with FROM expression missing(ORA-00923).

I want to have the result of counts in one row thus I'm trying to use subqueries in SELECT statement but it seems that SQL doesn't recognize the table in subqueries.

Subquery in main FROM statement works just fine on its own.

I don't know what seems to be the problem.

SELECT  RETDATA.DATETIME AS "Date",
        (SELECT COUNT(RET.EAN)
         FROM RETDATA RET
         WHERE RET.GOODTYPE = 'A-goods') AS 'A-goods',
        (SELECT COUNT(RET.EAN)
         FROM RETDATA RET
         WHERE RET.GOODTYPE = 'B-goods') AS 'B-goods',
        (SELECT COUNT(RET.EAN)
         FROM RETDATA RET
         WHERE RET.GOODTYPE = 'C-goods') AS 'C-goods',
        (SELECT COUNT(RET.EAN)
         FROM RETDATA RET
         WHERE RET.WAREHOUSE = 'Bring (Norway)') AS 'Norway_returns',
        (SELECT COUNT(RET.EAN)
         FROM RETDATA RET
         WHERE RET.WAREHOUSE = 'Hermes') AS 'Hermes_returns'

FROM            (SELECT  TRUNC(V_D."DateTime") AS DATETIME, 
                 V_D2."ProductReference" AS EAN,
                 CASE WHEN V_D3."Name" = 'OK (A-Item)' THEN 'A-goods'
                 WHEN V_D3."Name" IN ('Used','Dirty') THEN 'B-goods'
                 ELSE 'C-goods' END AS GOODTYPE,
                 V_D6."Name" AS WAREHOUSE

        FROM     REPORT.V_PORTALDATAORDERS_SALESORDER V_P
                 JOIN REPORT.V_DATARETURNS_RETURN V_D ON V_P."OrderNumberExternal"=V_D."OrderReference"
                 JOIN REPORT.V_DATARETURNS_RETURNSTATUS V_D1 ON V_D."ReturnStatusId"=V_D1."Id"
                 JOIN REPORT.V_DATARETURNS_RETURNLINE V_D2 ON V_D."Id"=V_D2."ReturnId"
                 JOIN REPORT.V_DATARETURNS_RETURNCONDITION V_D3 ON V_D2."ReturnConditionId"=V_D3."Id"
                 JOIN REPORT.V_DATAGLOBAL_WORKSTATION V_D5 ON V_D2."WorkstationId"=V_D5."Id"
                 JOIN REPORT.V_DATAGLOBAL_WAREHOUSE V_D6 ON V_D5."WarehouseId"=V_D6."Id" 
        WHERE    V_P."CompanyId" = 3 
        AND      V_D1."Label" in ('Processed', 'New') 
        AND      TRUNC(V_D."DateTimeDone") = TO_CHAR(sysdate-1, 'YYYY-MM-DD')) RETDATA

Upvotes: 0

Views: 173

Answers (2)

MT0
MT0

Reputation: 168081

You appear to want to do conditional aggregation and your query is the equivalent of using windowed aggregation functions:

SELECT  DATETIME AS "Date",
        COUNT( CASE GOODTYPE WHEN 'A-goods' THEN EAN END ) OVER () AS "A-goods",
        COUNT( CASE GOODTYPE WHEN 'B-goods' THEN EAN END ) OVER () AS "B-goods",
        COUNT( CASE GOODTYPE WHEN 'C-goods' THEN EAN END ) OVER () AS "C-goods",
        COUNT( CASE WAREHOUSE WHEN 'Bring (Norway)' THEN EAN END ) OVER () AS "Norway_returns",
        COUNT( CASE WAREHOUSE WHEN 'Hermes'         THEN EAN END ) OVER () AS "Hermes_returns"

FROM    (SELECT  TRUNC(V_D."DateTime") AS DATETIME, 
                 V_D2."ProductReference" AS EAN,
                 CASE WHEN V_D3."Name" = 'OK (A-Item)' THEN 'A-goods'
                 WHEN V_D3."Name" IN ('Used','Dirty') THEN 'B-goods'
                 ELSE 'C-goods' END AS GOODTYPE,
                 V_D6."Name" AS WAREHOUSE

        FROM     REPORT.V_PORTALDATAORDERS_SALESORDER V_P
                 JOIN REPORT.V_DATARETURNS_RETURN V_D ON V_P."OrderNumberExternal"=V_D."OrderReference"
                 JOIN REPORT.V_DATARETURNS_RETURNSTATUS V_D1 ON V_D."ReturnStatusId"=V_D1."Id"
                 JOIN REPORT.V_DATARETURNS_RETURNLINE V_D2 ON V_D."Id"=V_D2."ReturnId"
                 JOIN REPORT.V_DATARETURNS_RETURNCONDITION V_D3 ON V_D2."ReturnConditionId"=V_D3."Id"
                 JOIN REPORT.V_DATAGLOBAL_WORKSTATION V_D5 ON V_D2."WorkstationId"=V_D5."Id"
                 JOIN REPORT.V_DATAGLOBAL_WAREHOUSE V_D6 ON V_D5."WarehouseId"=V_D6."Id" 
        WHERE    V_P."CompanyId" = 3 
        AND      V_D1."Label" in ('Processed', 'New') 
        AND      V_D."DateTimeDone" >= TRUNC( sysdate-1 )
        AND      V_D."DateTimeDone" <  TRUNC( sysdate )
) RETDATA

However, you may be looking for non-windowed aggregation functions and a GROUP BY expression:

SELECT  DATETIME AS "Date",
        COUNT( CASE GOODTYPE WHEN 'A-goods' THEN EAN END ) AS "A-goods",
        COUNT( CASE GOODTYPE WHEN 'B-goods' THEN EAN END ) AS "B-goods",
        COUNT( CASE GOODTYPE WHEN 'C-goods' THEN EAN END ) AS "C-goods",
        COUNT( CASE WAREHOUSE WHEN 'Bring (Norway)' THEN EAN END ) AS "Norway_returns",
        COUNT( CASE WAREHOUSE WHEN 'Hermes'         THEN EAN END ) AS "Hermes_returns"

FROM    (SELECT  TRUNC(V_D."DateTime") AS DATETIME, 
                 V_D2."ProductReference" AS EAN,
                 CASE WHEN V_D3."Name" = 'OK (A-Item)' THEN 'A-goods'
                 WHEN V_D3."Name" IN ('Used','Dirty') THEN 'B-goods'
                 ELSE 'C-goods' END AS GOODTYPE,
                 V_D6."Name" AS WAREHOUSE

        FROM     REPORT.V_PORTALDATAORDERS_SALESORDER V_P
                 JOIN REPORT.V_DATARETURNS_RETURN V_D ON V_P."OrderNumberExternal"=V_D."OrderReference"
                 JOIN REPORT.V_DATARETURNS_RETURNSTATUS V_D1 ON V_D."ReturnStatusId"=V_D1."Id"
                 JOIN REPORT.V_DATARETURNS_RETURNLINE V_D2 ON V_D."Id"=V_D2."ReturnId"
                 JOIN REPORT.V_DATARETURNS_RETURNCONDITION V_D3 ON V_D2."ReturnConditionId"=V_D3."Id"
                 JOIN REPORT.V_DATAGLOBAL_WORKSTATION V_D5 ON V_D2."WorkstationId"=V_D5."Id"
                 JOIN REPORT.V_DATAGLOBAL_WAREHOUSE V_D6 ON V_D5."WarehouseId"=V_D6."Id" 
        WHERE    V_P."CompanyId" = 3 
        AND      V_D1."Label" in ('Processed', 'New') 
        AND      V_D."DateTimeDone" >= TRUNC( sysdate-1 )
        AND      V_D."DateTimeDone" <  TRUNC( sysdate )
) RETDATA
GROUP BY DATETIME

Note: you also appear to be mixing comparing TRUNC( V_D."DateTimeDone" ) and TO_CHAR( sysdate-1, 'YYYY-MM-DD' ); don't do this as one is a DATE and the other is a string data type and Oracle will do an implicit cast of the string back to a date using the NLS_DATE_FORMAT session parameter and this can be changed by each individual user in their own session which will make your query fail for that user. Instead use TRUNC on the current date.

Note 2: AS 'A-goods' is not a valid declaration of an alias; you want to use double quotes instead of single quotes.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try below query -

SELECT  RETDATA.DATETIME AS "Date",
        COUNT(CASE WHEN GOODTYPE = 'A-goods' THEN EAN) AS 'A-goods',
        COUNT(CASE WHEN GOODTYPE = 'B-goods' THEN EAN) AS 'B-goods',
        COUNT(CASE WHEN GOODTYPE = 'C-goods' THEN EAN) AS 'C-goods',,
        COUNT(CASE WHEN WAREHOUSE = 'Bring (Norway)' THEN EAN) AS 'Norway_returns',
        COUNT(CASE WHEN WAREHOUSE = 'Hermes' THEN EAN) AS 'Hermes_returns'
FROM (SELECT  TRUNC(V_D."DateTime") AS DATETIME, 
              V_D2."ProductReference" AS EAN,
              CASE WHEN V_D3."Name" = 'OK (A-Item)' THEN 'A-goods'
                   WHEN V_D3."Name" IN ('Used','Dirty') THEN 'B-goods'
                   ELSE 'C-goods' END AS GOODTYPE,
              V_D6."Name" AS WAREHOUSE
      FROM REPORT.V_PORTALDATAORDERS_SALESORDER V_P
      JOIN REPORT.V_DATARETURNS_RETURN V_D ON V_P."OrderNumberExternal" = V_D."OrderReference"
      JOIN REPORT.V_DATARETURNS_RETURNSTATUS V_D1 ON V_D."ReturnStatusId" = V_D1."Id"
      JOIN REPORT.V_DATARETURNS_RETURNLINE V_D2 ON V_D."Id" = V_D2."ReturnId"
      JOIN REPORT.V_DATARETURNS_RETURNCONDITION V_D3 ON V_D2."ReturnConditionId" = V_D3."Id"
      JOIN REPORT.V_DATAGLOBAL_WORKSTATION V_D5 ON V_D2."WorkstationId" = V_D5."Id"
      JOIN REPORT.V_DATAGLOBAL_WAREHOUSE V_D6 ON V_D5."WarehouseId" = V_D6."Id" 
      WHERE V_P."CompanyId" = 3 
      AND V_D1."Label" in ('Processed', 'New') 
      AND TRUNC(V_D."DateTimeDone") = TO_CHAR(sysdate-1, 'YYYY-MM-DD')) RETDATA
GROUP BY DATETIME

Upvotes: 0

Related Questions