Home » Developer & Programmer » Reports & Discoverer » Select stmt in Decode
Select stmt in Decode [message #236611] Thu, 10 May 2007 06:41 Go to next message
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 messageGo to next message
Littlefoot
Messages: 20893
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;
Re: Select stmt in Decode [message #236709 is a reply to message #236611] Thu, 10 May 2007 12:12 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
No it is not the problem of version. you try the report now.
Ashu
Re: Select stmt in Decode [message #236772 is a reply to message #236693] Thu, 10 May 2007 23:53 Go to previous message
pragatimathur
Messages: 42
Registered: July 2006
Location: DELHI
Member
Thanks!! I will use aggregate function.
Previous Topic: Adding Items and their values in Title Block of Discoverer
Next Topic: Regarding headings in discoverer
Goto Forum:
  


Current Time: Mon Dec 05 05:06:37 CST 2016

Total time taken to generate the page: 0.06384 seconds