ORA-00904 Invalid Identifier

CCSOFlag
CCSOFlag used Ask the Experts™
on
I am trying to call a function through an SQL statement within the same schema/user and it is giving me an invalid identifier.  The function name is IsDV.  Here is the SQL statement:

SELECT
DISTINCT ARMain.Arre_ID AS ArrestID,
TO_CHAR(ARMain.Date_Arr,'YYYYMMDD') AS ArrestDate,
TO_CHAR(Trim(EMMain.EMLName)||', '||Trim(EMMain.EMFName)) AS ArrestingOfficer,
ARMain.Agency AS Agency,
TO_CHAR(Trim(nmmain.Last)||', '||Trim(nmmain.First)) AS Suspect,
ARMain.Book_ID AS Book_ID,
ISDV(ARMain.Arre_ID) AS DV,
ARMain.ARR_Type AS Arr_Type,
ARMain.Age AS Age
FROM
(
 (
  LEADS.ARMain
  LEFT JOIN LEADS.EMMain
  ON ARMain.Arre_Offcr = EMMain.Empl_ID
 )
 LEFT JOIN leads.nmmain
 ON ARMain.Name_ID = nmmain.Name_ID
)
WHERE ARMain.Agency = 'FPD' AND TO_CHAR(ARMain.Date_Arr,'YYYYMMDD') BETWEEN '20090601' AND '20090630'

The Function:
ISDV (ArrestID VARCHAR2) Return NUMBER
AS
varCount Number(5);
Begin
  SELECT COUNT(*) INTO varCount FROM LEADS.Archrg WHERE ARChrg.CJI_DV = 1 AND ARChrg.Arre_ID = ArrestID;
  If varCount > 0 THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
End;

I have tried specifying the schema, but it gives the same error.  I have also tried forcing case sensitivity and doesn't work.  Any ideas?

Using:
Oracle 10g
SQL*Plus Worksheet
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
please confirm:
if you remove the function call from the sql, it works
if you call the funtion like below, it works:

select ISDV('some value') AS DV from dual;

Open in new window

Author

Commented:
if you remove the function call from the sql, it works
YES

if you call the funtion like below, it works:
NO same error.
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
ok, let's see:
create or replace ISDV (pArrestID VARCHAR2) Return NUMBER
AS
varCount Number(5);
Begin
  SELECT COUNT(*) INTO varCount 
    FROM LEADS.Archrg a 
   WHERE a.CJI_DV = 1 
     AND a.Arre_ID = pArrestID  
  ;
  If varCount > 0 THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
End;

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Grrr, I'm an idiot.  I was using the wrong user when I tried to specify it, but  on the other hand I thought you didn't have to specify the user if you are already logged in with it and in the user's schema...  Is this not correct?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
>I was using the wrong user when I tried to specify it,

where did you specify/not specify?

Author

Commented:
What I posted was the original code.  When it didn't work I tried to specify the schema, and a couple other things to try to get it working, but I used the wrong user when I did that.  

Author

Commented:
I realized it when I tried to create the function again.  Told me invalid user.  Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial