Select stmt in Decode [message #236611] |
Thu, 10 May 2007 06:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pragatimathur
Messages: 42 Registered: July 2006 Location: DELHI
|
Member |
|
|
Below query is running fine in Sql Prompt but its not working
in formula column of report.
Showing error in select statement in decode.
I am not getting the problem.
Is this a version problem as my Oracle is 9i and reports is 6.
Select Sl_Description Into :Cp_Sl
From Sub_Ledger_Master
Where Sl_Company_Code = Decode((Select Tr_Company_Code
From Financial_Transaction F, S A
Where A.Loc = :Loc
And A.Gl_Code = :Gl_Code
And A.Sl_Code = :Sl_Code
And A.Voucher_No = :Voucher_No
And F.Tr_Voucher_No = A.Voucher_No
And F.Tr_Gl_Account_Code = A.Gl_Code
And F.Tr_Sl_Account_Code = A.Sl_Code
And F.Tr_Branch_Code = A.Loc)
,Null,'BB',Sl_Company_Code )
And Sl_Branch_Code = :Loc
And Sl_Gl_Account_Code = :Gl_Code
And Sl_Account_Code = :Sl_Code
And Rownum =1;
[Mod-edit] Added [code] tags.
Next time, please, use [code] and [/code] tags to make your code readable.
[Updated on: Thu, 10 May 2007 11:22] by Moderator Report message to a moderator
|
|
|
Re: Select stmt in Decode [message #236693 is a reply to message #236611] |
Thu, 10 May 2007 11:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
SQL engine and PL/SQL engine aren't the same; what works in SQL, doesn't necessarily have to work in PL/SQL (as you've already seen).
As DECODE function returns a single value, break your query into two of them: first find what the SELECT (which is now part of the DECODE function) returns, and then use this value in the DECODE statement. Something like this: l_decode sub_ledger_master.Sl_Company_Code%type;
begin
-- use aggregate function in order to avoid NO-DATA-FOUND error.
-- Or, incapsulate this SELECT into its own BEGIN - EXCEPTION - END
-- block which would take care of it
select max(tr_company_code)
into l_decode
from financial_transaction f, s a
where a.loc = :loc
and ...;
select Sl_Description
Into :Cp_Sl
From Sub_Ledger_Master
Where Sl_Company_Code = decode(l_decode, null, 'BB', Sl_Company_Code)
and ...;
end;
|
|
|
|
|