Home » Developer & Programmer » Reports & Discoverer » Case Statement In Oracle Reports Query (Oracle Reports 10g, XP)
Case Statement In Oracle Reports Query [message #587182] Thu, 13 June 2013 00:40 Go to next message
SandeepS
Messages: 12
Registered: April 2013
Junior Member
Hi All,

Can I use CASE statement Inside a Oracle Report Query.
I'm using Oracle Reports Builder 10g.

My Report Query looks like this,

select invh_code, invh_number, invh_date, invh_cm_code, im_description
from invoice_head, invoice_det, unit_of_measurement, item_master
where invd_invh_code = invh_code and im_code = invd_item_code
AND
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
else 1
end)

order by invh_number

:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition
else omit that condition.

But the CASE statement is throwing Error

ORA-00907 :Missing Right Paranthesis
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4))
==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)

Can anyone Please Help....


Sandeep

--Every negative event has within it the seed of an equal or greater benefit

[Updated on: Thu, 13 June 2013 00:43]

Report message to a moderator

Re: Case Statement In Oracle Reports Query [message #587183 is a reply to message #587182] Thu, 13 June 2013 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to rewrite it as
and case when :p_flag = 1 then ...
         else 1
    end
Re: Case Statement In Oracle Reports Query [message #587198 is a reply to message #587183] Thu, 13 June 2013 03:33 Go to previous messageGo to next message
SandeepS
Messages: 12
Registered: April 2013
Junior Member
Hi Littlefoot,

Thank You for the Reply...

But it is still showing the same error.

Is it really possible to write a statement like that inside a Then Clause in CASE Statement?




Thanks
Sandeep

--Every negative event has within it a the seed of an equal or greater benefit

[Updated on: Thu, 13 June 2013 03:35]

Report message to a moderator

Re: Case Statement In Oracle Reports Query [message #587236 is a reply to message #587198] Thu, 13 June 2013 06:21 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, I didn't pay attention to whole code you posted, sorry.

What is it supposed to do? What would that condition evaluate to?
- if flag = 1 == evaluates to ==> AND substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
- if flag <> 1 => evaluates to ==> what? You said "1" ==> AND 1. "AND 1" is invalid, is it not?

Could you provide test case and explain the requirement? Meanwhile, two possible workarounds:
select * from table
where substr(...) between :p_v_itm_frn and :p_v_itm_to
  and :p_flag = 1
union
select * from table
where <another condition here>
  and :p_flag <> 1

select * from table
where (    :p_flag = 1
       and substr(...) between :p_v_itm_frn and :p_v_itm_to
      )
   or (    :p_flag <> 1
       and <another condition here>
      )
Re: Case Statement In Oracle Reports Query [message #587291 is a reply to message #587236] Thu, 13 June 2013 23:08 Go to previous messageGo to next message
SandeepS
Messages: 12
Registered: April 2013
Junior Member
Hi LittleFoot,

Thank You for your Reply..

If :p_flag = 1, then means I want to include that condition [substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)], if :p_flag is set

else I don't want this condition to be included, ie I don't want the records to be filtered out...

that's why i used CASE statement


Thanks
Sandeep

--Every negative event has within it a seed of an equal or greater benefit.

[Updated on: Thu, 13 June 2013 23:12]

Report message to a moderator

Re: Case Statement In Oracle Reports Query [message #587328 is a reply to message #587291] Fri, 14 June 2013 02:40 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
So do what Littlefoot showed at the end of his last post. You don't need CASE for this, OR will do the job.
Re: Case Statement In Oracle Reports Query [message #587343 is a reply to message #587182] Fri, 14 June 2013 03:42 Go to previous message
SandeepS
Messages: 12
Registered: April 2013
Junior Member
Hi All,

That second option Worked, Littlefoot.
Thank You Littlefoot, cookiemonster .Thanks for the your Correct Answers and for Replies.



Thanks,
Sandeep



--Every negative event has within it the seed for an equal or greater benefit.

[Updated on: Fri, 14 June 2013 03:44]

Report message to a moderator

Previous Topic: Improving Report Server Performance
Next Topic: Between clause to Compare two String values
Goto Forum:
  


Current Time: Fri Apr 18 17:49:54 CDT 2014

Total time taken to generate the page: 0.05217 seconds