Home » SQL & PL/SQL » SQL & PL/SQL » Conditional SELECT (11g, Server 2003)
Conditional SELECT [message #410790] Tue, 30 June 2009 05:12 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Gurus,

I have two tables, imp_t1 and imp_t2, with different table structures. And I want to fire SELECT statement on these tables based on some condition.
Let's say FLAG.
If FLAG=1 then SELECT * from IMP_T1 should be fired
and SELECT * FROM IMP_T2 otherwise.
Don't know how to do this? Please help.

Following is the demo test case
create table imp_t1
(col1 integer,
col2 varchar2(10),
col3 date default sysdate);

create table imp_t2
col4 integer,
col5 varchar2(10));

insert into imp_t1 values(1,'Naresh',null);
insert into imp_t1 values(2,'Naresh',null);
insert into imp_t1 values(3,'Naresh',null);

insert into imp_t2 values(1,'Raja');
insert into imp_t2 values(2,'Raja');
insert into imp_t2 values(3,'Raja');



regards,
Delna
Re: Conditional SELECT [message #410793 is a reply to message #410790] Tue, 30 June 2009 05:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DECLARE
  v_flag   number := :b1;
  v_count  number;
BEGIN
  IF v_flag = 1 then
    SELECT count(*) into v_count from table_1;
  ELSE
    SELECT count(*) into v_count from table_2;
  END IF;
END;
/
Re: Conditional SELECT [message #410795 is a reply to message #410790] Tue, 30 June 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT * from IMP_T1 where &flag = 1;
SELECT * FROM IMP_T2 where &flag != 1;

Regards
Michel

Re: Conditional SELECT [message #410799 is a reply to message #410790] Tue, 30 June 2009 05:59 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ohhh Laughing perfect solution!!!

BUT
I am required to do this in single SELECT only.
This SELECT statement should by executed according to the value of FLAG. If value is 1 then all the rows from IMP_T1 and for other values, all the rows from IMP_T2 should be displayed.

and sorry for incomplete information in previous post.
regards,
Delna
Re: Conditional SELECT [message #410801 is a reply to message #410790] Tue, 30 June 2009 06:05 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
Add a union to michel's answer along with a null for the missing column on imp_t2.
Re: Conditional SELECT [message #410802 is a reply to message #410799] Tue, 30 June 2009 06:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What are yuo using to execute this query?

Re: Conditional SELECT [message #410805 is a reply to message #410790] Tue, 30 June 2009 06:12 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Right cookiemonster sir,

But in real case these two tables are fully different in number of columns, column name and types, as I said in OP.

regards,
Delna
Re: Conditional SELECT [message #410810 is a reply to message #410805] Tue, 30 June 2009 06:55 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If these tables are completely different, why do you want to use a single SELECT statement?

Here's how I imagine the problem: there are EMP and DEPT tables (in Scott's schema). Depending on a certain condition (your "flag"), you'd want to select records from one of these tables and do *something* (what, exactly?) with the result set. Now, what business requirement says that the same SELECT has to be used, regardless the fact you are dealing with employees or departments (which are very different, so they should probably be handled differently as well)?
Re: Conditional SELECT [message #410816 is a reply to message #410790] Tue, 30 June 2009 07:08 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Actually, I am using this query in generating report.
According to the user's selection of parameters, I am required to generate query based on different tables having different structures. And front-end limits to create only one report on one page.

regards,
Delna
Re: Conditional SELECT [message #410820 is a reply to message #410790] Tue, 30 June 2009 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the tables involved have significantly different structures such that you can't use the union trick then the only sensible thing to do is have two different reports and decide which is run based on the parameters.
Re: Conditional SELECT [message #410822 is a reply to message #410805] Tue, 30 June 2009 07:54 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
delna.sexy wrote on Tue, 30 June 2009 07:12
Right cookiemonster sir,



Sorry, I really do not have anything relevant to ad here. it's just when I saw that line, it really cracked me up. It's probably just me...I'm sorry.
Re: Conditional SELECT [message #410829 is a reply to message #410816] Tue, 30 June 2009 08:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'l try again: Which report writer are you using?
Re: Conditional SELECT [message #410832 is a reply to message #410816] Tue, 30 June 2009 09:02 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
delna.sexy wrote on Tue, 30 June 2009 14:08
Actually, I am using this query in generating report.
According to the user's selection of parameters, I am required to generate query based on different tables having different structures. And front-end limits to create only one report on one page.



So why don't you want to execute one of two different queries from your report then. It would be much more clever. Generally an sql query returns data always with the same structure.
The only exception would be a query to a pipelined table function returning an AnyDataSet object, but it would be very useless to implement a so complex solution for your case, also because this kind of things never offer solutions with great performances.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dcitblfnsref.htm#i76723


Other ways you may always use a query like this

select *
from (
		select *
		from t1
		where &flag = 1
	) cross join (
		select *
		from t2
		where not &flag = 1
	)


Bye Alessandro
Re: Conditional SELECT [message #410915 is a reply to message #410790] Wed, 01 July 2009 01:06 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you all.

Actually I am using Oracle ApEx and in which There is only one interactive report is allowed on one page.
I was also thinking to use two different pages with different report, having different queries as data source.
But wanted to just know, if this is possible with single report.

Thanks again.

regards,
Delna
Previous Topic: partitioned table
Next Topic: High Query Execution Time
Goto Forum:
  


Current Time: Thu Dec 08 06:03:17 CST 2016

Total time taken to generate the page: 0.11384 seconds