I write a script on PL / SQL with the condition:

if ((select * from table1 where quant>1000) is not null) then ... else RAISE error$e; end if;

When compiling produces the following error:

PLS-00103: Encountered the symbol "SELECT" when looking for one of the following:

Is it possible to substitute a query directly into a condition, and not create a new variable?

  • 2
    And since when did SELECT return a boolean value to use in an if-else? - Shamanis
  • is not null gives a boolean value, if that - Specter
  • In this case, the query should return one column and one row, i.e. one estimated value. - msi

2 answers 2

Apparently implied

 if exists (select * from table1 where quant>1000)
  • Are you sure what it is? Maybe in sql and where instead of if? exec if exists (select 1 from dual) then null; end if; PLS-00204: function or pseudo-column 'EXISTS' SQL statement only - 0xdb
  • Not sure. But in IF there should be a logical condition - I suggested it. - msi
  • one
    And, so this is then pseudo code, exists in plsql is not separately, only as a method in the collection. - 0xdb
  • I do not remember what this question looked like in 2011 when I answered it. It is possible that the Oracle tag was not specified then. - msi

It is impossible. A select ... expression select ... is a pointer to a cursor, IF expects a logical expression. If it were even possible, I'd rather have wrapped the request in a function. Something like this:

 create table table1 (quant number); insert into table1 values (0); declare function existsHighQuant return boolean is begin for r in (select 1 from table1 where quant>1000) loop return true; end loop; return false; end; -- ΠΈΠ»ΠΈ Ρ‚Π°ΠΊ - Π½Π΅ ΠΎΡ‡Π΅Π½ΡŒ изящный ΠΏΠΎ ΠΌΠΎΠ΅ΠΌΡƒ мнСнию Π²Π°Ρ€ΠΈΠ°Π½Ρ‚ function existsRow(stmt varchar2) return boolean is curs sys_refcursor; dummy number; begin open curs for stmt; fetch curs into dummy; return curs%found; end; begin if existsHighQuant then null; --do something else raise_application_error(-20000, 'quant gt 1000 not found'); end if; if existsRow('select 1 from table1') then dbms_output.put_line('at least 1 row exists'); end if; end; / ORA-20000: quant gt 1000 not found insert into table1 values (2000); -- ΠΏΠΎΠ²Ρ‚ΠΎΡ€ΠΈΡ‚ΡŒ Π²Ρ‹Π·ΠΎΠ² Π±Π»ΠΎΠΊΠ° PL/SQL procedure successfully completed. at least 1 row exists