Home » SQL & PL/SQL » SQL & PL/SQL » using field from main query as parameter in PL/SQL code of formula field (oracle 8i)
using field from main query as parameter in PL/SQL code of formula field [message #310426] Tue, 01 April 2008 04:20 Go to next message
rahulredij
Messages: 2
Registered: February 2008
Location: cHIPLUN
Junior Member

Hi
Everybody

i have to generate report between given dates.

I have written following main query

SELECT 'MIN' AS X,a.tran_date,
SUM(DECODE(C.SERVICE,'IND',A.QTY_TRANSFER/1000)) AS IND_COLL,
SUM(DECODE(C.SERVICE,'DOM',A.QTY_TRANSFER))/1000 AS DOM_COLL
FROM ENV_TANK_DAILY_TRAN A,ENV_SHIFT_TIME B,ENV_ASSET_MASTER C
where a.TRAN_DATE>=:FR_dt
and a.TRAN_DATE<=:to_DT
AND A.SHIFT_CD=B.SHIFT_CD
AND A.TRAN_TIME=B.SHIFT_TIME
AND A.TRAN_TIME='07.00'
AND A.SHIFT_CD='S001'
group by a.tran_date

i want to use it's tran_date field in

function CF_Max_IndFormula return Number is
IND NUMBER;
begin

SELECT
SUM(DECODE(C.SERVICE,'IND',A.QTY_TRANSFER/1000)) into ind
FROM ENV_TANK_DAILY_TRAN A,ENV_SHIFT_TIME B,ENV_ASSET_MASTER C
where to_date(a.TRAN_DATE)=:TRAN_DATE
AND A.SHIFT_CD=B.SHIFT_CD
AND A.TRAN_TIME=B.SHIFT_TIME
AND A.TRAN_TIME='07.00'
AND A.SHIFT_CD='S003'
group by a.tran_date;
RETURN IND;
end;

but program is giving error
"REP-1517 Column 'CF_Max_Ind' refrences column 'tran_date' which has incompatible frequency"

kindly guide me

Regards

rahul

Re: using field from main query as parameter in PL/SQL code of formula field [message #310504 is a reply to message #310426] Tue, 01 April 2008 07:57 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member

where to_date(a.TRAN_DATE)=:TRAN_DATE

can't you see what is causing the problem

where have you declared =:TRAN_DATE


Don't you think that when you are running your query from sql plus this value is passed by some substitution variable.And you just pasted the same query in your pl/sql code. Now how the hell pl/sql can know about that variable.

Read about pl/sql variables.
Declare a variable in your pl/sql code and use that instead of what you have just passed in your pl/sql code

Re: using field from main query as parameter in PL/SQL code of formula field [message #310506 is a reply to message #310426] Tue, 01 April 2008 08:03 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

It depends on the context you want to use this.
For instance:
SQL> col dummy new_value val
SQL> select dummy from dual;
D
-
X

1 row selected.

SQL> def val
DEFINE VAL             = "X" (CHAR)
SQL> select * from dual where dummy = '&val';
D
-
X

1 row selected.


Regards
Michel
Previous Topic: No records fetch in Cursor.
Next Topic: Set Role from procedure
Goto Forum:
  


Current Time: Mon Dec 05 21:15:52 CST 2016

Total time taken to generate the page: 0.09305 seconds