Receiving error : ORA-01747 While trying to calculate COUNT(*) of a specific table

oracle-11gplsqlquery

Hope you're doing well.
I have two tables and as I far as I know for this scenario there is no need for their structures.

When I run this query :

SELECT COUNT(T.*) AS X
FROM table1 T INNER JOIN table2 M
     ON T.col1= M.col2

I receive this error:

ORA-01747:invalid user.table.column, table.column,or column specification

Is there something I'm missing here?? Dose this query have something which is against database rule?
thanks in advance

Best Answer

That is not valid syntax.

SQL> select count(t.*) as x from dual t;
select count(t.*) as x from dual t
               *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification


SQL> select count(*) as x from dual t;

         X
----------
         1

SQL>

Related Solutions

Oracle SQL for left outer join to rownum = 1 of another query

Basically, I think you should just get the max date, if that is all you are looking for here, using your same filter, etc. You already have STATUS (=COMP) and WONUM (JOIN). If you needed the whole record from this table, and it was more complicated than this, I would recommend the oracle inline analytic functions with over/partition by logic to filter by the max date.

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM AS STATUSWONUM 
                               , STATUS AS STATUS
                               , MAX(CHANGEDATE) AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        GROUP  BY WONUM, STATUS)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM ) 

;

ORA-00904 error while querying the Oracle database table

When ORA-00904: invalid identifier occurs, we must enter a valid column name as it is either missing or the one entered is invalid. This error most commonly happens when we are referencing an invalid alias in a select statement. Oracle's voice on ORA-00904 error:

ORA-00904 string: invalid identifier

Cause: The column name entered is either missing or invalid.

Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

To avoid ORA-00904, column names must

begin with a letter.

consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them.

be less than or equal to thirty characters.

Best Answer

Related Solutions

Oracle SQL for left outer join to rownum = 1 of another query

ORA-00904 error while querying the Oracle database table

ORA-00904 string: invalid identifier

Related Question