0

I'm running into the error trying to compile a function.

PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only

Here is the Function.

 create or replace function get_ulq (
    i_vcust_cd IN VARCHAR2,
    i_vordr_type IN VARCHAR2,
    i_vpart_no IN VARCHAR2
  ) RETURN VARCHAR2 
  AS
    ulq_count varchar2(50 char) := null; 
  BEGIN 
    CASE WHEN exists ( SELECT count(*)
                         FROM P1.p7ulqpm0
                        WHERE cust_cd = i_vcust_cd
                          AND src     = i_vordr_type
                          AND part_no = i_vpart_no)
        then  select ulq 
                into ulq_count
               from p1.p2partm0
              where src = i_vordr_type
                and part_no = i_vpart_no;
        ELSE ulq_count := 1;
    END CASE;
    RETURN ulq_count;
  END get_ulq;
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
Jules
  • 215
  • 1
  • 8
  • 13
  • 2
    And count(*) exists always :) – Mottor Jul 19 '16 at 20:03
  • Thanks for the help. Any advice on how to alter this statement? Should I add >= 1 for the count? – Jules Jul 19 '16 at 20:05
  • 1
    (Select count(*) ... ) >= 1. Or you can make select count into var1 and use if – Mottor Jul 19 '16 at 20:06
  • The issue was around `EXISTS`, not `CASE`. If you'd [searched the manual](https://docs.oracle.com/apps/search/search.jsp?word=exists&product=e50529-01&book=LNPLS) you might have noticed that your `if exists (cursor)` construction doesn't appear anywhere. – William Robertson Jul 20 '16 at 09:48

0 Answers0