0

I'm getting absolutely crazy over this. I'm currently writing a trigger to change some dates around. For that I want to get a max value into a variable like so:

SELECT date INTO datevar
               FROM table
               WHERE date = (SELECT MAX(date)
                                    FROM table
                                    WHERE condition = loop.condition);

The trigger always throws an ORA-01422 (Too many rows returned) on the line of this query; but when I try the query in my sqldeveloper (substituting the loop value with a number) the query works fine, with any data set (works fine = returns exactly one row).

I tried different ways to select the max value into my var, but it's the same problem every time. The loop works fine (when I run the trigger as stored procedure with verbose logging, I see the loop row id's and everything), but it always starts with said error without writing to the database.

I also scoured the web, but I didn't find anything. I'd appreciate any ideas.

Satwik Nadkarny
  • 5,086
  • 2
  • 23
  • 41
Torsten N.
  • 2,051
  • 1
  • 12
  • 17
  • 2
    If you have two rows with the same date that happens to be the latest date, this will fail –  Mar 17 '16 at 15:28
  • While that can fail, and Gordon's simpler approach cannot; are you sure it is that statement that errors? Line numbers in trigger errors are counted from the start of the PL/SQL section (i.e. `declare` or `begin`), not the `create trigger` line, which can be confusing. – Alex Poole Mar 17 '16 at 15:47

1 Answers1

6

Why would you run such a complicated query? Replace it with:

SELECT MAX(date) INTO datevar
FROM table
WHERE condition = loop.condition;

This cannot generate such an error.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This was the fix I found later, but I could've sworn that this was what I tried when I wrote the Query for the first time. Seems to work for now – Torsten N. Mar 22 '16 at 10:07