Setting 0 as null value in Oracle SQL throwing "%s: invalid identifier"

Question

In OracleSQL, I am trying to add a colunm to a table that holds the amount of times the APPLICANT is found in APPLIES (Identified by anumber).

I started with:

ALTER TABLE APPLICANT
ADD numapp NUMBER(2);

and then used an insert statement:

INSERT INTO APPLICANT(numapp) SELECT COUNT(anumber), NVL(bonus, 0) FROM APPLIES GROUP BY anumber;

I have been told that the NVL statement is supposed to set any null value to 0 in the insert, but I haven't been able to get it working, as I get the following error whenever trying to run it.

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

I am very new to SQL so apologies if this is a very obvious fix that I'm missing.

When I use `numapp` (the colunm where I want the zero to go), it throws the same error: ``` SQL Error: ORA-00904: "NUMAPP": invalid identifier 00904. 00000 - "%s: invalid identifier" ``` I think I tried bonus because that is what I saw online, but `numapp` has the same issue — tomjedi9, Mar 09 '22 at 13:03

David Lukas · Answer 1 · 2022-03-10T07:45:23.067

An insert statement would be used if the APPLICANT table is empty.
An update statement if all the ANUMBER is in the APPLICANT.
The most general is a merge statement, which does both.
Since the APPLICANT table already existed, I assume it isn't empty.
Therefore, I will give an example with a merge.
See this page: Oracle SQL: Update a table with data from another table

This is the basic underlying query:

  select anumber,
  count(anumber) numapp
  from applies
  group by anumber
  union
  select anumber,0 numapp
  from applicant
  where
   anumber not in (select anumber from applies)
 order by 1
;

It can be simpler for an insert and an update.
It shows the resulting data.
The null values don't arise for me.

When incorporated into a merge:

merge into applicant a
using (
  select anumber,
  count(anumber) numapp
  from applies
  group by anumber
  union
  select anumber,0 numapp
  from applicant
  where
   anumber not in (select anumber from applies)
 ) b
 on (a.anumber = b.anumber)
 when matched then
 update set a.numapp = b.numapp
 when not matched then
 insert (a.anumber,a.numapp)
 values (b.anumber,b.numapp);
select * from applicant order by anumber;
commit;

Result:

ANUMBER	NUMAPP
1	3
2	1
3	0
4	0
5	1

My testing DDL:

CREATE TABLE APPLICANT
    ("ANUMBER" int, "NUMAPP" varchar2(4))
;

INSERT ALL 
    INTO APPLICANT ("ANUMBER", "NUMAPP")
         VALUES (1, NULL)
    INTO APPLICANT ("ANUMBER", "NUMAPP")
         VALUES (2, NULL)
    INTO APPLICANT ("ANUMBER", "NUMAPP")
         VALUES (3, NULL)
    INTO APPLICANT ("ANUMBER", "NUMAPP")
         VALUES (4, NULL)
SELECT * FROM dual
;



CREATE TABLE APPLIES
    ("ANUMBER" int)
;

INSERT ALL 
    INTO APPLIES ("ANUMBER")
         VALUES (1)
    INTO APPLIES ("ANUMBER")
         VALUES (1)
    INTO APPLIES ("ANUMBER")
         VALUES (1)
    INTO APPLIES ("ANUMBER")
         VALUES (2)
        INTO APPLIES ("ANUMBER")
         VALUES (5)
SELECT * FROM dual
;

Edit1:

When creating a merge, I assumed that the update would be just a slight variation.
About an insert I still think so. :-)
I was unable to make an updateable select in this case.
Probably because of a group clause.
So I used the aggregation using the nested where criterion t.anumber=s.anumber.

You probably assume the data in a database look like tables. The data in the DB is trees/hash spaces/clusters. Tables forms during a presentation. All rows/records have to be connected using a joining criterion. You can find a better explanation in the link above.

SQL:

ALTER TABLE APPLICANT
ADD NUMAPP NUMBER(2);

update applicant t set 
 t.numapp = (
  select count(s.anumber)
  from applies s where t.anumber=s.anumber
 )
;
select * from applicant order by anumber;
commit;

Output:

ANUMBER	NUMAPP
1	3
2	1
3	0
4	0

DDL:

CREATE TABLE APPLICANT
    ("ANUMBER" int)
;

INSERT ALL 
    INTO APPLICANT ("ANUMBER")
         VALUES (1)
    INTO APPLICANT ("ANUMBER")
         VALUES (2)
    INTO APPLICANT ("ANUMBER")
         VALUES (3)
    INTO APPLICANT ("ANUMBER")
         VALUES (4)
SELECT * FROM dual
;

CREATE TABLE APPLIES
    ("ANUMBER" int)
;

INSERT ALL 
    INTO APPLIES ("ANUMBER")
         VALUES (1)
    INTO APPLIES ("ANUMBER")
         VALUES (1)
    INTO APPLIES ("ANUMBER")
         VALUES (1)
    INTO APPLIES ("ANUMBER")
         VALUES (2)
SELECT * FROM dual
;

Hey, thankyou so much for this answer, it's super helpful, but I've realised that I didn't add enough context for the overall task. Applicant has an `anumber` and their number is also in Applies. The amount of times their `anumber` shows up in Applies is how many times they have applied for a position. I want a count of that number to be a new colunm in Applicant. That's why I added `numapp` to applicant, and I now want to populate that colunm with the count of how many times that applicant has applied — tomjedi9, Mar 10 '22 at 01:03

Setting 0 as null value in Oracle SQL throwing "%s: invalid identifier"

1 Answers1

Edit1: