| using field from main query as parameter in PL/SQL code of formula field [message #310426] |
Tue, 01 April 2008 04:20  |
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  |
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 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
