Reputation: 99
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
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