CODEWITHSUNDEEP
sqloracle-database
tomjedi9
tomjedi9

Reputation: 19

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

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.

Upvotes: 1

Views: 197

Answers (1)

David Lukas
David Lukas

Reputation: 1229

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
;

Upvotes: 1

Related Questions