Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting ORA-00942 error when running Insert Into

Ray GoinsJun 30 2025 — edited Jul 1 2025

I am trying to insert data from a couple different tables in 2 different databases using database links. If I run the select query on it's own, it runs without error and returns the expected results. If I use the query with the insert into clause I get an ORA-00942 error which points to a line which contains the where clause for the sub query.

Right now I am testing in SQL Developer, but this will go into a cron job on the database server to update every day.

This is the query which produces the data and works perfectly fine on it's own:

select
  sde.gdb_util.next_rowid('EMAP', 'WQT_ACCOUNTS') as OBJID,
  cs.atsl_svc_loc_nbr,
  cs.meter_ser_nbr,
  cs.meter_ert,
  cs.bldgnum,
  cs.sloc_strt_nm,
  cs.sloc_strt_sfx,
  cs.sloc_city,
  cs.sloc_state,
  cs.sloc_zip,
  (cs.bldgnum || ' ' || cs.sloc_strt_nm || ' ' || ( CASE WHEN cs.sloc_strt_sfx = 'AVE' THEN 'AV' ELSE cs.sloc_strt_sfx END) || ', ' || cs.sloc_city || ', ' || cs.sloc_state) as FADDRESS,
  cs.sloc_dwelling_cd,
  cs.sloc_stop_number,
  cs.atsl_acct_nbr,
  cs.rpac_first_nm,
  cs.rpac_last_nm,
  cs.rpac_day_phone,
  cs.rpac_eve_phone,
  wsl.legacyid,
  wsl.installdate,
  wsl.material,
  wsl.diameter,
  wsl.privpipetype,
  wsl.privsideconfirm,
  (  
    CASE 
        WHEN wsl.installdate >= to_date('01-JAN-1983', 'DD-MON-YYYY') and wsl.installdate <= to_date('31-DEC-1986', 'DD-MON-YYYY') and wsl.material = 'COPPER' AND cs.sloc_dwelling_cd = '01' THEN 'Y' 
        WHEN wsl.installdate >= to_date('01-JAN-1880', 'DD-MON-YYYY') and wsl.installdate <= to_date('31-DEC-1954', 'DD-MON-YYYY') and wsl.material = 'LEAD' AND cs.sloc_dwelling_cd = '01'  THEN 'Y'  
        ELSE 'N' 
    END  
  ) as TIER1_FLAG,
  (select sysdate from dual) as IMPDATE,
  slg.shape,
  cs.maddress,
  cs.mail_suite,
  cs.mcity,
  cs.mstate,
case
  when cs.mzip4 = ' ' then cs.mzip
  when cs.mzip4 = ' ' and cs.mzip = ' ' then null
  else cs.mzip || '-' || cs.mzip4
end as mail_zip,
  cs.email,
  wsl.assetid,
  slg.pid,
  slg.structureyear,
  (CASE
    WHEN wsl.material = 'LEAD' THEN 'Y'
    WHEN wsl.privpipetype = 'LEAD' THEN 'Y'
    WHEN wsl.privpipetype = 'UN' and wsl.diameter <= 2 THEN 'Y'
    WHEN (wsl.material is null or wsl.privpipetype is null) and wsl.diameter <= 2 THEN 'Y'
    ELSE 'N'
  END) as POSSIBLE_LEAD,
  wsl.retireddate,
  slg.LAT,
  slg."LONG_"
from media_prod.cstartmp@mediaprod cs
left join
(
    select PID, STRUCTUREYEAR, SHAPE, LOCATIONID, STOPNUMBER, LAT, "LONG_" 
    from UNDBOWNER.WATERDEVICE_EVW@UNDB wd
    where wd.assetgroup = 12
    )slg on slg.LOCATIONID = cast(cs.atsl_svc_loc_nbr as varchar2(30))
left join
(
    select wsl.assetid, wsl.legacyid, to_date(to_char(wsl.installdate, 'DD-MON-YYYY'), 'DD-MON-YYYY') as installdate, 
    (CASE
        when wsl.material = 81 then 'AC'
        when wsl.material = 83 then 'CI'
        when wsl.material = 84 then 'COPPER'
        when wsl.material = 88 then 'DI'
        when wsl.material = 94 then 'PVC'
        when wsl.material = 109 then 'LEAD'
        else 'UNKNOWN'
    END) as MATERIAL, wsl.DIAMETER, wsl.PRIVPIPETYPE, wsl.PRIVSIDECONFIRM, to_date(to_char(wsl.RETIREDDATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') as RETIREDDATE
    from undbowner.waterline_evw@UNDB wsl 
    where 
        wsl.assettype in (11,12,13,14) 
        and wsl.assetgroup = 2 
        and wsl.ownedby = 1
)wsl on wsl.legacyid = cast(slg.stopnumber as varchar2(25))

If I add in the insert into clause it fails:

insert into EMAP.WQT_ACCOUNTS (OBJECTID, SVC_LOC, METER_SER_NBR, METER_ERT, BLDGNBR, STNAME, SUFFIX, CITY, STATE, ZIP, FADDRESS, SLOC_DWELLING_CD, STOP_NBR, ACCT_NBR, FNAME, LNAME, DAY_PHONE, EVE_PHONE, UNITID, INSTDATE, PIPETYPE, DIAM, PRIV_PIPE_TYPE, PRIV_SIDE_CONFIRM, TIER1_FLAG, IMPDATE, SHAPE, MAIL_ADDRESS, MAIL_SUITE, MAIL_CITY, MAIL_STATE, MAIL_ZIP, EMAIL, LAT_FAC_ID, PROP_ID, STRUC_YEAR, POSSIBLE_LEAD, PRIV_LSR_DATE, LAT_, LONG_)
select
  sde.gdb_util.next_rowid('EMAP', 'WQT_ACCOUNTS') as OBJID,
  cs.atsl_svc_loc_nbr,
  cs.meter_ser_nbr,
  cs.meter_ert,
  cs.bldgnum,
  cs.sloc_strt_nm,
  cs.sloc_strt_sfx,
  cs.sloc_city,
  cs.sloc_state,
  cs.sloc_zip,
  (cs.bldgnum || ' ' || cs.sloc_strt_nm || ' ' || ( CASE WHEN cs.sloc_strt_sfx = 'AVE' THEN 'AV' ELSE cs.sloc_strt_sfx END) || ', ' || cs.sloc_city || ', ' || cs.sloc_state) as FADDRESS,
  cs.sloc_dwelling_cd,
  cs.sloc_stop_number,
  cs.atsl_acct_nbr,
  cs.rpac_first_nm,
  cs.rpac_last_nm,
  cs.rpac_day_phone,
  cs.rpac_eve_phone,
  wsl.legacyid,
  wsl.installdate,
  wsl.material,
  wsl.diameter,
  wsl.privpipetype,
  wsl.privsideconfirm,
  (  
    CASE 
        WHEN wsl.installdate >= to_date('01-JAN-1983', 'DD-MON-YYYY') and wsl.installdate <= to_date('31-DEC-1986', 'DD-MON-YYYY') and wsl.material = 'COPPER' AND cs.sloc_dwelling_cd = '01' THEN 'Y' 
        WHEN wsl.installdate >= to_date('01-JAN-1880', 'DD-MON-YYYY') and wsl.installdate <= to_date('31-DEC-1954', 'DD-MON-YYYY') and wsl.material = 'LEAD' AND cs.sloc_dwelling_cd = '01'  THEN 'Y'  
        ELSE 'N' 
    END  
  ) as TIER1_FLAG,
  (select sysdate from dual) as IMPDATE,
  slg.shape,
  cs.maddress,
  cs.mail_suite,
  cs.mcity,
  cs.mstate,
case
  when cs.mzip4 = ' ' then cs.mzip
  when cs.mzip4 = ' ' and cs.mzip = ' ' then null
  else cs.mzip || '-' || cs.mzip4
end as mail_zip,
  cs.email,
  wsl.assetid,
  slg.pid,
  slg.structureyear,
  (CASE
    WHEN wsl.material = 'LEAD' THEN 'Y'
    WHEN wsl.privpipetype = 'LEAD' THEN 'Y'
    WHEN wsl.privpipetype = 'UN' and wsl.diameter <= 2 THEN 'Y'
    WHEN (wsl.material is null or wsl.privpipetype is null) and wsl.diameter <= 2 THEN 'Y'
    ELSE 'N'
  END) as POSSIBLE_LEAD,
  wsl.retireddate,
  slg.LAT,
  slg."LONG_"
from media_prod.cstartmp@mediaprod cs
left join
(
    select PID, STRUCTUREYEAR, SHAPE, LOCATIONID, STOPNUMBER, LAT, "LONG_" 
    from UNDBOWNER.WATERDEVICE_EVW@UNDB wd
    where wd.assetgroup = 12 <------ This is the line which fails
    )slg on slg.LOCATIONID = cast(cs.atsl_svc_loc_nbr as varchar2(30))
left join
(
    select wsl.assetid, wsl.legacyid, to_date(to_char(wsl.installdate, 'DD-MON-YYYY'), 'DD-MON-YYYY') as installdate, 
    (CASE
        when wsl.material = 81 then 'AC'
        when wsl.material = 83 then 'CI'
        when wsl.material = 84 then 'COPPER'
        when wsl.material = 88 then 'DI'
        when wsl.material = 94 then 'PVC'
        when wsl.material = 109 then 'LEAD'
        else 'UNKNOWN'
    END) as MATERIAL, wsl.DIAMETER, wsl.PRIVPIPETYPE, wsl.PRIVSIDECONFIRM, to_date(to_char(wsl.RETIREDDATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') as RETIREDDATE
    from undbowner.waterline_evw@UNDB wsl 
    where 
        wsl.assettype in (11,12,13,14) 
        and wsl.assetgroup = 2 
        and wsl.ownedby = 1
)wsl on wsl.legacyid = cast(slg.stopnumber as varchar2(25))
;

I get this error:

Error at Command Line : 64 Column : 27
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:

I can run all the queries on their own without issue. I can also insert data into the wqt_accounts table without issue.

select * from media_prod.cstartmp@media;

    select PID, STRUCTUREYEAR, SHAPE, LOCATIONID, STOPNUMBER, LAT, "LONG_" 
    from UNDBOWNER.WATERDEVICE_EVW@UNDB wd
    where wd.assetgroup = 12;

    select wsl.assetid, wsl.legacyid, to_date(to_char(wsl.installdate, 'DD-MON-YYYY'), 'DD-MON-YYYY') as installdate, 
    (CASE
        when wsl.material = 81 then 'AC'
        when wsl.material = 83 then 'CI'
        when wsl.material = 84 then 'COPPER'
        when wsl.material = 88 then 'DI'
        when wsl.material = 94 then 'PVC'
        when wsl.material = 109 then 'LEAD'
        else 'UNKNOWN'
    END) as MATERIAL, wsl.DIAMETER, wsl.PRIVPIPETYPE, wsl.PRIVSIDECONFIRM, to_date(to_char(wsl.RETIREDDATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') as RETIREDDATE
    from undbowner.waterline_evw@UNDB wsl 
    where 
        wsl.assettype in (11,12,13,14) 
        and wsl.assetgroup = 2 
        and wsl.ownedby = 1
Comments
Post Details
Added on Jun 30 2025
#dblinks, #geodatabase, #insertinto, #oracle19c, #sql, #stgeometry
7 comments
128 views