CODEWITHSUNDEEP
mysqlsqldatabase
Wah.P
Wah.P

Reputation: 99

How do I check two different conditions for one column in an INSERT INTO SELECT block?

database noob so please bear with me. Writing in Oracle MySql.

I have a block of code which notably is meant to add rows to DWCUST but change invalid gender values coming from a2custbris to valid ones by comparing them to the genderspelling table. I wrote the select code in the brackets to do this and it works. However, certain gender values are null in a2custbris, I want these to be written as 'U' in DWCUST. So how can I do both?

Here's the code: 

INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
SELECT dwcustSeq.nextval, cb.custid, Null, cb.fname, cb.sname, (select trim(gs.NEW_VALUE) FROM genderspelling gs WHERE upper(gs.INVALID_VALUE) = upper(cb.GENDER)), cb.phone, cb.postcode, cb.city, cb.state, cc.custcatname
FROM a2custbris cb
NATURAL JOIN a2custcategory cc
WHERE cb.rowid IN (SELECT source_rowid FROM A2ERROREVENT where filterid = 7);

Any help is creating appreciated!

Upvotes: 0

Views: 40

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Use a CASE expression:

INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, DWSOURCEIDMELB, FIRSTNAME, SURNAME,
                    GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
SELECT
  dwcustSeq.nextval, cb.custid, Null, cb.fname, cb.sname, 
  CASE WHEN cb.gender IS NULL THEN 'U' 
       ELSE (select trim(gs.NEW_VALUE) FROM genderspelling gs
             WHERE upper(gs.INVALID_VALUE) = upper(cb.GENDER))
  END,
  cb.phone, cb.postcode, cb.city, cb.state, cc.custcatname
FROM a2custbris cb
NATURAL JOIN a2custcategory cc
WHERE cb.rowid IN (SELECT source_rowid FROM A2ERROREVENT where filterid = 7);

If you want 'U' for every value that has no match in genderspelling, then use COALESCE instead:

INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, DWSOURCEIDMELB, FIRSTNAME, SURNAME,
                    GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
SELECT
  dwcustSeq.nextval, cb.custid, Null, cb.fname, cb.sname, 
  COALESCE(
    (select trim(gs.NEW_VALUE) FROM genderspelling gs
     WHERE upper(gs.INVALID_VALUE) = upper(cb.GENDER))
  , 'U'),
  cb.phone, cb.postcode, cb.city, cb.state, cc.custcatname
FROM a2custbris cb
NATURAL JOIN a2custcategory cc
WHERE cb.rowid IN (SELECT source_rowid FROM A2ERROREVENT where filterid = 7);

Upvotes: 1

Related Questions