-1

I have the below SQL query but it gives the error message ORA-00904: "KUST_ADR"."KU_NR": invalid identifier even though those are the correct table and column names. What else could be the cause?

update auf_adr
  set email = (select k.ku_email
               from auf_kopf k join
                    kust_adr ka
                    on k.kunr = ka.ku_nr
               where auf_adr.auf_nr = k.auf_nr and
                     ka.ku_adr_art = 1 and
                     auf_adr.email <> ka.ku_email and
                     (select sum(s.rg_anz)
                      from auf_stat s
                      where s.auf_nr = k.auf_nr
                     ) = 0
              )
    where auf_adr.adr_art = 2 and
          exists (select 1
                  from auf_kopf k join
                       kust_adr ka
                       on k.kunr = ka.ku_nr
                  where auf_adr.auf_nr = k.auf_nr and
                        ka.ku_adr_art = 1 and
                        auf_adr.email <> ka.ku_email and
                        (select sum(s.rg_anz)
                         from auf_stat s
                         where s.auf_nr = k.auf_nr
                        ) = 0
                  );
user6888062
  • 353
  • 1
  • 3
  • 16
  • 4
    Please post the table structures – shrek May 01 '18 at 18:23
  • in the update statement, there's no `"KUST_ADR"."KU_NR"` combination but `ka.ku_nr`, are you sure this gives the error ..? – Barbaros Özhan May 01 '18 at 18:25
  • It's aliased ... – user6888062 May 01 '18 at 18:27
  • I see that's aliased but the message is irrelevant, i.e. as if, not aliased. – Barbaros Özhan May 01 '18 at 18:28
  • If you created your table with `"` double quotes around the column names, then the column names are case sensitive. – Aaron Dietz May 01 '18 at 18:55
  • There is nothing wacky in your posted SQL (assuming the revised statement is now the one your actually running). Which means the usual cause of ORA-00904 applies: your UPDATE statement references a table or a column which doesn't exist in your schema. Your question title refutes that, but after 25 years in the business I *always* trust the compiler in these matters. If you want us to spot your typo you must post the tables' DDL statements so we can reproduce the error. Possibly you will find it helpful [to read this SO answer](https://stackoverflow.com/a/6030439/146325). – APC May 02 '18 at 06:49

1 Answers1

3

There is a "and" missing after each of the "where" clause line, this could be the issue.

where auf_adr.auf_nr = k.auf_nr AND
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
shrek
  • 887
  • 6
  • 12
  • 1
    check your tables -- are they created with lower case and with double quotes around the table name and column names? If so, oracle will give an error if you don't put double quotes around the names in the select with lower case. Once you start using double quotes around table/column names, oracle makes sure that any sql also has double quotes and lower case. If you create the table without double quotes, it won't mind either case (lower or upper) – Peter M May 01 '18 at 18:56