Wah.P
Wah.P

Reputation: 99

Why am I getting an "invalid identifier" oracle error running this code?

I'm trying to run the following code but I'm getting the invalid identifier error for cb.state, I don't understand why?

INSERT INTO DWCUST (DWCUSTID, DWSOURCEIDBRIS, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME)
SELECT dwcustSeq.nextval, cb.custid, cm.custid, cb.fname, cb.sname, cb.gender, 
        cb.phone, cb.postcode, cb.city, cb.state, cc.custcatname
   FROM a2custbris cb
NATURAL JOIN a2custcategory cc
NATURAL JOIN a2custmelb cm
  WHERE cb.rowid NOT IN ( SELECT source_rowid FROM A2ERROREVENT );

If it helps at all, state and cb.state are blue text in Oracle, I don't know why.

Expected result:

No error, rows added into dwcust

Actual result:

SQL Error: ORA-00904: "cb.state": invalid identifier 00904. 00000 - "%s: invalid identifier"

Any help is much appreciated!

Upvotes: 1

Views: 2520

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

It means that table whose alias is cb (a2custbris) doesn't contain a column whose name is state.

If you see it in a table, pay attention to letter case. If it is, by any chance, created so that column names are enclosed into double quotes (you'll see them not in uppercase, which is default), then you'll have to use the same letter case every time you reference that column, e.g.

select cb."state"
from a2custbris cb

[EDIT: regarding invalid number]

This is what you're doing:

INSERT INTO DWCUST (DWCUSTID,         --> dwcustseq.nextval
                    DWSOURCEIDBRIS,   --> cb.custid
                    DWSOURCEIDMELB,   --> null
                    FIRSTNAME,        --> cb.fname
                    SURNAME,          --> cb.sname
                    GENDER,           --> upper(cb.gender)
                    PHONE,            --> cb.phone
                    POSTCODE,         --> cb.postcode
                    CITY,             --> cb.city
                    STATE,            --> cb.state
                    CUSTCATNAME)      --> cc.custcatname

Note the first 3 columns: those look suspicious to me:

INSERT INTO DWCUST (DWCUSTID,         --> dwcustseq.nextval
                    DWSOURCEIDBRIS,   --> cb.custid
                    DWSOURCEIDMELB,   --> null

Everything matches, but those 3 don't. Shouldn't cb.custid go into dwcustid?

Upvotes: 1

Related Questions