OraFAQ Forum: Reports & Discoverer » Select stmt in Decode

Home » Developer & Programmer » Reports & Discoverer » Select stmt in Decode

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select stmt in Decode [message #236611]

Thu, 10 May 2007 06:41

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

Littlefoot
Messages: 21813
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;

Report message to a moderator