OraFAQ Forum: Reports & Discoverer » Case Statement In Oracle Reports Query

Home » Developer & Programmer » Reports & Discoverer » Case Statement In Oracle Reports Query (Oracle Reports 10g, XP)

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

Case Statement In Oracle Reports Query [message #587182]

Thu, 13 June 2013 00:40

SandeepS
Messages: 15
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

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Case Statement In Oracle Reports Query [message #587198 is a reply to message #587183]

Thu, 13 June 2013 03:33

SandeepS
Messages: 15
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

Littlefoot
Messages: 21823
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>
      )

Report message to a moderator

Re: Case Statement In Oracle Reports Query [message #587291 is a reply to message #587236]

Thu, 13 June 2013 23:08

SandeepS
Messages: 15
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

cookiemonster
Messages: 13962
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.

Report message to a moderator

Re: Case Statement In Oracle Reports Query [message #587343 is a reply to message #587182]

Fri, 14 June 2013 03:42

SandeepS
Messages: 15
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 22:27:27 CST 2025