nested sql - error in function???

[dps]

I am getting the following error in sqlplus when running a package containing -

PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( ) - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current exists max min prior sql stddev sum
variance execute multiset the both leading trailing forall
year month DAY_ HOUR_ MINUTE_ second TIMEZONE_HOUR_
TIMEZONE_MINUTE_ time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL stri

Even when I just compile function by itself.

It seems to point at the second select statement after the '-'.

What can I also do to re-write the statemtn. Is it something to do with conversion functions needed?

DAte fields are : af_ab_o and af.af_an_o

NUMBER fields : af_id and AF_AC_REG_ID

create or replace FUNCTION F_LATEST_MGT(p_af_id IN number) RETURN number IS

v_latest_mgt number;
BEGIN
v_latest_mgt := 0;
SELECT 1440.0 * (af_ab_o - (SELECT af.af_an_o
FROM act_flight af
WHERE af.af_id =
(SELECT A2.AF_ID
FROM ACT_FLIGHT A1, ACT_FLIGHT A2
WHERE A1.AF_ID = p_af_id
AND A2.AF_AC_REG_ID = A1.AF_AC_REG_ID
AND A2.AF_PLKZ <> 'C'
AND A2.AF_AB_S < A1.AF_AB_S
AND A2.AF_AB_S =
(SELECT MAX(A3.AF_AB_S)
FROM ACT_FLIGHT A3
WHERE A3.AF_AC_REG_ID =
A1.AF_AC_REG_ID
AND A3.AF_PLKZ <> 'C'
AND A3.AF_AB_S < A1.AF_AB_S
AND A3.AF_ID != A1.AF_ID)
) ) )
INTO v_latest_mgt
FROM act_flight
WHERE af_id = p_af_id;
RETURN v_latest_mgt;
END F_LATEST_MGT;

 

[sem]

I suppose you can not use such constructs in pl/sql (at least in 8i). The common problem is that pl/sql engine remains behind sql one, so some sql features are implemented only in the next pl/sql release. Regards, Dima

 

[Sridharan]

Make sure that the inner SQL returns only one value since you are equating it to af.af_if

ie. WHERE af.af_id = ( SELECT A2.AF_ID ........)

also the outer SQL should be returning one value that will be multiplied with 1440.0.

Sri

 

[dps]

value is returned for

MAX(A3.AF_AB_S)
--------------------
30-APR-2003 17:00:00

middle qry returns no row

 

[jee]

I think SEM's reply is on target. Your query of:

SELECT 1440.0 * (af_ab_o - (SELECT af.af_an_o
FROM act_flight af
WHERE af.af_id =
.
.
.
is legal for SQL, but not yet legal for SQL within PL/SQL. It probably works in 9i. As SEM says, the PL/SQL engine does not always keep up with SQL. You can often get around such limitations by executing the query within PL/SQL using Dynamic SQL.

 

[dps]

Ok what if I break it down within the function

Have two cursors : -

cursor c1 is
SELECT (1440.0 * af_ab_o) -- multiplying a number with a date column (need to convert the date into minutes first how??)
FROM act_flight
WHERE af_id = p_af_id;

cursor c2 is
SELECT af.af_an_o
FROM act_flight af
WHERE af.af_id =
(SELECT A2.AF_ID
FROM ACT_FLIGHT A1, ACT_FLIGHT A2
WHERE A1.AF_ID = p_af_id
AND A2.AF_AC_REG_ID = A1.AF_AC_REG_ID
AND A2.AF_PLKZ <> 'C'
AND A2.AF_AB_S < A1.AF_AB_S
AND A2.AF_AB_S = (SELECT MAX (A3.AF_AB_S)
FROM ACT_FLIGHT A3
WHERE A3.AF_AC_REG_ID = A1.AF_AC_REG_ID
AND A3.AF_PLKZ <> 'C'
AND A3.AF_AB_S < A1.AF_AB_S
AND A3.AF_ID != A1.AF_ID));

c2_rec c2%ROWTYPE;

put them into variables and then do the substraction.

 

[dps]

effectively I need to minus to dates (the af_ab_o and the af_an_o bit and multply result with 1440!!