Home » SQL & PL/SQL » SQL & PL/SQL » Error in Query
Error in Query [message #221685] Wed, 28 February 2007 01:13 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi.
I am having two tables

see the query

select a.*,b.* from abc a,wxy b
where
(
case
when b.mgrno is null
then
'a.empno is null'
else

'a.ename=b.ename'
end
)

From the above query, i need to give condition dynamically depending up on the codition....
The above query is not working, could you please assist in this.

Thanks,
Shahnaz.
Re: Error in Query [message #221707 is a reply to message #221685] Wed, 28 February 2007 02:27 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you just want to vary the where clause depending on some external condition, you could define a Ref Cursor, and then open that for a query defined in a String:(untested code)
DECLARE
  cur   sys_refcursor;
  sql   varchar2(32767);
BEGIN
  v_sql := 'SELECT a.*,b.* '||chr(10)||
           'from abc a,wxy b'||chr(10)||
           'where ';
  If <condition 1> then
    v_sql := v_sql||' a.emp_no is null'
  else 
    v_sql := v_sql||'a.ename=b.ename'
  end if;

  open cur for v_sql;
  <do stuff>
END;


If you're actually trying to change the where clause in the middle of running the query, then you can't.

You can rewrite the query you have posted in a way that will let it work:
select a.*,b.* 
from abc a,wxy b
where (b.mgrno is null and a.empno is null)
or (b.mgrno is not null and a.ename=b.ename)
Previous Topic: How to code this?
Next Topic: How to get the Months (Jan to Dec) of the year through SQL Query?
Goto Forum:
  


Current Time: Sat Dec 03 20:11:39 CST 2016

Total time taken to generate the page: 0.10482 seconds