Sql – ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc

ora-01446oracleoracle10gsql

Just trying to insert a Row in the Customer table which is not included in the Error Table.

–View

Create View A3_SRC_CUST_VIEW As
Select SRC_CUST_A.*, rowid as row_id From SRC_CUST_A
Union All
Select SRC_CUST_B.*, rowid as row_id From SRC_CUST_B;

— Insert Statement

Insert Customer (DW_CUST_ID, CUSTID, CUSTNAME, CUSTEMAIL, CUSTLOC)
Select  Dw_Cust_Id.Nextval,  v.CID,  v.NAME, v.Email, v.LOC
From CustView v
Where v.rowid Not In
    (Select Source_RowId From A3_Error_Event  Where Filter_Id = 4 );

The error I am getting is ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
01446. 00000 – "cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc."

Best Answer

You are selecting rowid instead of row_id from the view.This causes the error.

Related Solutions

SQLite – UPSERT not INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Mysql – How to SELECT rows with MAX(Column value), PARTITION by another column in MYSQL

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

Best Answer

Related Solutions

SQLite – UPSERT *not* INSERT or REPLACE

Mysql – How to SELECT rows with MAX(Column value), PARTITION by another column in MYSQL

Related Topic

SQLite – UPSERT not INSERT or REPLACE