When I am inserting records into the table it is inserting 0 records. Though there are no errors-CodePudding

CREATE TABLE new_details_staging (
    e_id         NUMBER(10),
    e_name       VARCHAR2(30),
    portal_desc  VARCHAR2(50),
    CONSTRAINT pk_new_details_staging PRIMARY KEY ( e_id )
);

INSERT INTO new_details_staging VALUES (
    11,
    'A',
    'AA'
);

INSERT INTO new_details_staging VALUES (
    22,
    'B',
    'BB'
);

CREATE TABLE lookup_ref (
    ref_id        NUMBER(10),
    ref_typ       VARCHAR2(30),
    ref_typ_desc  NUMBER(20),
    CONSTRAINT pk_lookup_ref PRIMARY KEY ( ref_id )
);

INSERT INTO lookup_ref VALUES (
    181,
    'portal',
    'AA'
);

INSERT INTO lookup_ref VALUES (
    182,
    'portal',
    'BB'
);

CREATE TABLE new_details_main (
    e_id    NUMBER(10),
    e_name  VARCHAR2(30),
    portal  NUMBER(20),
    CONSTRAINT pk_new_details_main PRIMARY KEY ( e_id )
);

When I am inserting records into the table it is inserting 0 records. Can someone help with this why no rows are getting inserted into the table? Basically, I want to populate the lookup id from the lookup_ref table based on the staging table column portal_desc.

Insert query :

insert
    INTO new_details_main (
        e_id,
        e_name,
        portal
    )
SELECT
    n.e_id,
    n.e_name,
    case 
WHEN n.portal_desc = 'AA' THEN (select l.ref_id from lookup_ref where l.ref_typ='portal' and l.ref_typ_desc = 'AA')
when n.portal_desc = 'BB' then (select l.ref_id from lookup_ref where l.ref_typ= 'portal' and l.ref_typ_desc = 'BB') end
FROM new_details_staging n cross join lookup_ref l;

Expected Output

CodePudding user response：

You have a couple of bad INSERTs

You have defined LOOKUP_REF.REF_TYPE_DESC as NUMBER(20), but then you attempt to insert a values of 'AA' and 'BB' into it.

That fails with

ORA-01722: invalid number

Then for your query -

SELECT
    n.e_id,
    n.e_name,
    case 
WHEN n.portal_desc = 'AA' THEN (select l.ref_id from lookup_ref where l.ref_typ='portal' and l.ref_typ_desc = 'AA')
when n.portal_desc = 'BB' then (select l.ref_id from lookup_ref where l.ref_typ= 'portal' and l.ref_typ_desc = 'BB') end
FROM new_details_staging n cross join lookup_ref l;

Those rows with AA and BB aren't there to be 'joined' with, so the resultset is empty.

Let's try and fix this.

ALTER TABLE LOOKUP_REF 
    MODIFY ( REF_TYP_DESC varchar2(20) )
Table ADMIN.LOOKUP_REF altered.

Run the INSERTs again.

INSERT INTO lookup_ref VALUES (
    181,
    'portal',
    'AA'
);

INSERT INTO lookup_ref VALUES (
    182,
    'portal',
    'BB'
);

1 row inserted.

Elapsed: 00:00:00.009


Copy to clipboard

Show info

1 row inserted.

Elapsed: 00:00:00.005

Now run the query again -

Closer. You now get

ORA-01427: single-row subquery returns more than one row

You say:

I want to populate the lookup id from the lookup_ref table based on the staging table column portal_desc

This very much sounds like there should be a foreign key constraint. You would then use the FK value in the 'child' table to 'look up' the details in the 'parent' table based on the matching 'ID.' That column needs a unique or primary key constraint to ensure there can be, only one matching value.

CodePudding user response：

Aside from the mis-declaration of LOOKUP_REF.REF_TYP_DESC, which should be changed to VARCHAR2(20) as pointed out by @thatjeffsmith, it appears to me that you should not be doing a cross join of LOOKUP_REF. Instead, I suggest doing an inner join so that you only join the rows you care about:

INSERT INTO new_details_main
  (e_id, e_name, portal)
SELECT n.e_id,
       n.e_name,
       l.ref_id AS PORTAL
  FROM new_details_staging n
  INNER JOIN lookup_ref l
    ON l.REF_TYP = 'portal' AND
       l.REF_TYP_DESC = n.PORTAL_DESC

This produces your desired results.

db<>fiddle here