We help IT Professionals succeed at work.

query works but insert doesn't how do i fix it

sikyala
sikyala asked
on
when i do a select I get 1 row returned.

select * from holdings where SITE = 'TAT' AND DB_INSERTED > to_date('03-NOV-11','DD-MON-RR') AND NOT EXISTS (SELECT holding_id from WS_RECORD ws WHERE HOLDINGS.ID = HOLDING_ID);

But when I try to insert that row into another table I get an error

INSERT INTO WS_RECORD (HOLDING_ID, TITLE) SELECT ID, TITLE FROM HOLDINGS BH WHERE SITE = 'TAT' AND DB_INSERTED > to_date('03-NOV-11','DD-MON-RR') AND NOT EXISTS (SELECT holding_id from WS_STI_RECORD where HOLDINGS.ID =
HOLDING_ID);

When I run this, I get the following error:
SQL Error: ORA-00904: "HOLDINGS"."ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

If I include the holdings table in my subquery at the end

(SELECT holding_id from WS_RECORD ws, HOLDINGS bh where bh.ID =
ws.HOLDING_ID);

I get no rows returned. How do i change the insert so that it inserts the same record I get for my select statement?
Comment
Watch Question

Senior Oracle DBA
BRONZE EXPERT
Commented:
The HOLDINGS table has an alias of BH, you need to use that.


INSERT INTO WS_RECORD (HOLDING_ID, TITLE) SELECT ID, TITLE FROM HOLDINGS BH WHERE SITE = 'TAT' AND DB_INSERTED > to_date('03-NOV-11','DD-MON-RR') AND NOT EXISTS (SELECT holding_id from WS_STI_RECORD where BH.ID =
HOLDING_ID);
In your insert, you aliased HOLDINGS but then tried to refer to it as HOLDINGS instead of BH.

Try this:
INSERT INTO WS_RECORD (HOLDING_ID, TITLE)
      SELECT ID, TITLE
      FROM HOLDINGS BH
      WHERE [SITE] = 'TAT'
            AND DB_INSERTED > to_date('03-NOV-11','DD-MON-RR')
            AND NOT EXISTS (SELECT holding_id from WS_STI_RECORD where BH.ID = HOLDING_ID);
I think the problem is that in your select query, you do not use a table alias for holdings, but in your insert query you do. However, you do not use the alias when you refer to the table in the subquery.

Your query:

INSERT INTO WS_RECORD (HOLDING_ID, TITLE) SELECT ID, TITLE FROM HOLDINGS BH WHERE SITE = 'TAT' AND DB_INSERTED > to_date('03-NOV-11','DD-MON-RR') AND NOT EXISTS (SELECT holding_id from WS_STI_RECORD where HOLDINGS.ID =
HOLDING_ID);

Try instead:

INSERT INTO WS_RECORD (HOLDING_ID, TITLE) SELECT ID, TITLE FROM HOLDINGS BH WHERE SITE = 'TAT' AND DB_INSERTED > to_date('03-NOV-11','DD-MON-RR') AND NOT EXISTS (SELECT holding_id from WS_STI_RECORD where BH.ID =
HOLDING_ID);



sikyalaSenior Database Administrator

Author

Commented:
thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.