Home » SQL & PL/SQL » SQL & PL/SQL » How to use switch case to run either of two select statements?
How to use switch case to run either of two select statements? [message #262832] Tue, 28 August 2007 06:16 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
I wish to choose between two select statements depending on the input that is entered by the user.

Could you guys help me out on the logic and syntax for this?
Re: How to use switch case to run either of two select statements? [message #262835 is a reply to message #262832] Tue, 28 August 2007 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
BEGIN
IF <condition 1> THEN
  SELECT <columns>
  INTO   <variables>
  FROM   <tables>;
ELSIF condition 2> THEN
  SELECT <other columns>
  INTO   <other variables>
  FROM   <other tables>;
END IF;
Re: How to use switch case to run either of two select statements? [message #262839 is a reply to message #262835] Tue, 28 August 2007 06:24 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Thanks a lot for the quick reply.

I know that it is very easy to implement in PL/SQL Block.

However, I wish to use the same in a single statement query without using PL/SQL.

Any comments on that?
Re: How to use switch case to run either of two select statements? [message #262840 is a reply to message #262832] Tue, 28 August 2007 06:27 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Set Serveroutput On

CREATE OR REPLACE PROCEDURE xx(v_empno emp.empno%type)
is
V_ENAME EMP.ENAME%TYPE;
BEGIN
IF V_EMPNO IS NOT NULL THEN
SELECT ENAME INTO V_ENAME
FROM EMP
WHERE EMPNO = V_EMPNO;

DBMS_OUTPUT.PUT_LINE('NAME :'||V_ENAME);

ELSE
DBMS_OUTPUT.PUT_LINE('EMPNO IS NOT AVAILABLE');
END IF;
End;
/


SQL> EXEC XX(7900);
NAME :JAMES

PL/SQL procedure successfully completed.



SQL> EXEC XX(NULL);
EMPNO IS NOT AVAILABLE

PL/SQL procedure successfully completed.

Thanks and Regards,
Thani..........
Re: How to use switch case to run either of two select statements? [message #262847 is a reply to message #262840] Tue, 28 August 2007 06:35 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
I dont wish to use PL/SQL, just a one statement query...
Re: How to use switch case to run either of two select statements? [message #262848 is a reply to message #262847] Tue, 28 August 2007 06:38 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Give us a clearer example of what you want to do. Give us some 'air code'
Re: How to use switch case to run either of two select statements? [message #262849 is a reply to message #262840] Tue, 28 August 2007 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: How to use switch case to run either of two select statements? [message #262850 is a reply to message #262847] Tue, 28 August 2007 06:44 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Give full your requirement...... what your excepting... how your getting the parameter....

how you want result....

Thanks and Regards,
Thani.......
Re: How to use switch case to run either of two select statements? [message #262856 is a reply to message #262848] Tue, 28 August 2007 06:51 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hey... thanks a lot for such amazing response...

just tweaked the where condition a bit and got the required output... thanks a lot..-
Re: How to use switch case to run either of two select statements? [message #262862 is a reply to message #262839] Tue, 28 August 2007 06:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the two queries return the same set of columns, you could do something like this:
SELECT <columns>
FROM   <tables>
WHERE  <stuff>
AND    :variable_1 is not null
UNION ALL
SELECT <other_columns>
FROM   <other_tables>
WHERE  <other_stuff>
AND    :variable_2 is not null
Re: How to use switch case to run either of two select statements? [message #262863 is a reply to message #262856] Tue, 28 August 2007 07:00 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
oops.. false alarm.. the code isnt workin..

Accept Table_Name prompt "Enter Table Name : "
Accept Owner prompt "Enter Owner : "

select distinct owner SCHEMA, referenced_name PARENT_TABLE, name OBJECT_NAME, type OBJECT_TYPE from all_dependencies
where referenced_name = upper('&table_name') and owner = upper('&owner')
union
select distinct owner, table_name, synonym_name, 'SYNONYM' from all_synonyms where table_name = upper('&table_name')
and owner = upper('&owner')
union
select owner, table_name, constraint_name, decode(constraint_type,'P','PRIMARY_KEY','R','FOREIGN_KEY','U','UNIQUE','C',
'CHECK','N','NULL') from all_constraints where table_name = upper('&table_name') and owner = upper('&owner')
union
select distinct owner, table_name, index_name,'INDEX' from all_indexes where table_name = upper('&table_name')
and owner = upper('&owner')
union
select distinct owner, table_name, trigger_name,'TRIGGER' from all_triggers where table_name = upper('&table_name')
and owner = upper('&owner'), null)
union
select distinct p.owner, p.table_name, q.table_name, 'TABLE' from all_constraints p,all_constraints q
where p.owner = q.r_owner and p.constraint_name = q.r_constraint_name and p.table_name = upper('&table_name')
and owner = upper('&owner')
/

The above code gives all the required for the owner that is entered by the user.

I wish to add an enhancement, that if the user just presses enter for owner after having entered the table name, he should get the details for the same table in all schemas if present i.e. for all the owners in the database.
Re: How to use switch case to run either of two select statements? [message #262865 is a reply to message #262832] Tue, 28 August 2007 07:03 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
sorry, null should not be included for trigger section of the code.
Re: How to use switch case to run either of two select statements? [message #262868 is a reply to message #262863] Tue, 28 August 2007 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 28 August 2007 13:42
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel


Re: How to use switch case to run either of two select statements? [message #262871 is a reply to message #262863] Tue, 28 August 2007 07:13 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

select distinct A.owner SCHEMA, referenced_name PARENT_TABLE, name OBJECT_NAME, type OBJECT_TYPE
from all_dependencies A
where referenced_name = upper('&table_name')
and A.owner = upper('&owner')
union
select distinct B.owner, table_name, synonym_name, 'SYNONYM'
from all_synonyms B
where table_name = upper('&table_name')
and B.owner = upper('&owner')
union
select C.owner, table_name, constraint_name, decode(constraint_type,'P','PRIMARY_KEY','R','FOREIGN_KEY','U','UNIQUE','C',
'CHECK','N','NULL')
from all_constraints C
where table_name = upper('&table_name')
and C.owner = upper('&owner')
union
select distinct D.owner, table_name, index_name,'INDEX'
from all_indexes D
where table_name = upper('&table_name')
and D.owner = upper('&owner')
union
select distinct E.owner, table_name, trigger_name,'TRIGGER'
from all_triggers E
where table_name = upper('&table_name')
and E.owner = upper('&owner')
union
select distinct p.owner, p.table_name, q.table_name, 'TABLE'
from all_constraints p,all_constraints q
where p.owner = q.r_owner and p.constraint_name = q.r_constraint_name
and p.table_name = upper('&table_name')
and P.owner = upper('&owner')
/



SQL> COLUMN PARENT_TABLE FORMAT A20
SQL> COLUMN OBJECT_NAME FORMAT A30
SQL> /
Enter value for table_name: EMP
Enter value for owner: SCOTT
old 2: where referenced_name = upper('&table_name') and A.owner = upper('&owner')
new 2: where referenced_name = upper('EMP') and A.owner = upper('SCOTT')
Enter value for table_name: EMP
old 4: select distinct B.owner, table_name, synonym_name, 'SYNONYM' from all_synonyms B where table_name = upper('&table_name')
new 4: select distinct B.owner, table_name, synonym_name, 'SYNONYM' from all_synonyms B where table_name = upper('EMP')
Enter value for owner: SCOTT
old 5: and B.owner = upper('&owner')
new 5: and B.owner = upper('SCOTT')
Enter value for table_name: EMP
Enter value for owner: SCOTT
old 8: 'CHECK','N','NULL') from all_constraints C where table_name = upper('&table_name') and C.owner = upper('&owner')
new 8: 'CHECK','N','NULL') from all_constraints C where table_name = upper('EMP') and C.owner = upper('SCOTT')
Enter value for table_name: EMP
old 10: select distinct D.owner, table_name, index_name,'INDEX' from all_indexes D where table_name = upper('&table_name')
new 10: select distinct D.owner, table_name, index_name,'INDEX' from all_indexes D where table_name = upper('EMP')
Enter value for owner: SCOTT
old 11: and D.owner = upper('&owner')
new 11: and D.owner = upper('SCOTT')
Enter value for table_name: EMP
old 13: select distinct E.owner, table_name, trigger_name,'TRIGGER' from all_triggers E where table_name = upper('&table_name')
new 13: select distinct E.owner, table_name, trigger_name,'TRIGGER' from all_triggers E where table_name = upper('EMP')
Enter value for owner: SCOTT
old 14: and E.owner = upper('&owner')
new 14: and E.owner = upper('SCOTT')
Enter value for table_name: EMP
old 17: where p.owner = q.r_owner and p.constraint_name = q.r_constraint_name and p.table_name = upper('&table_name')
new 17: where p.owner = q.r_owner and p.constraint_name = q.r_constraint_name and p.table_name = upper('EMP')
Enter value for owner: SCOTT
old 18: and P.owner = upper('&owner')
new 18: and P.owner = upper('SCOTT')

SCHEMA PARENT_TABLE OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------- ------------------------------ -----------------
SCOTT EMP FK_DEPTNO FOREIGN_KEY
SCOTT EMP LOC TABLE
SCOTT EMP PK_EMP INDEX
SCOTT EMP PK_EMP PRIMARY_KEY
SCOTT EMP P_EMPPRO PROCEDURE
SCOTT EMP XX PROCEDURE

6 rows selected.


Thanks and Regards,
Thani.......
Re: How to use switch case to run either of two select statements? [message #262874 is a reply to message #262871] Tue, 28 August 2007 07:18 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
I wish to get details for one table name across all owners.

For e.g.

SCHEMA PARENT_TABLE OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------- ------------------------------ -----------------
SCOTT EMP FK_DEPTNO FOREIGN_KEY
AIX EMP LOC TABLE
JOE EMP PK_EMP INDEX
JOSEPH EMP PK_EMP PRIMARY_KEY
DEV2005 EMP P_EMPPRO PROCEDURE
DEV2006 EMP XX PROCEDURE

The output that you have indicated is what I have already done.
Re: How to use switch case to run either of two select statements? [message #262893 is a reply to message #262871] Tue, 28 August 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 28 August 2007 13:42
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel


[Updated on: Tue, 28 August 2007 08:07]

Report message to a moderator

Re: How to use switch case to run either of two select statements? [message #262894 is a reply to message #262874] Tue, 28 August 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 28 August 2007 13:42
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel


Re: How to use switch case to run either of two select statements? [message #262967 is a reply to message #262832] Tue, 28 August 2007 11:49 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
SET LINESIZE 80

COLUMN PARENT_TABLE FORMAT A20
COLUMN OBJECT_NAME FORMAT A20
COL schema FORMAT A15
COL object_type FORMAT A15

UNDEF table_name
UNDEF owner

select distinct A.owner SCHEMA, referenced_name PARENT_TABLE, name OBJECT_NAME, type OBJECT_TYPE 
from all_dependencies A
where ( referenced_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( A.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select distinct B.owner, table_name, synonym_name, 'SYNONYM' 
from all_synonyms B 
where ( table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( B.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select C.owner, table_name, constraint_name, decode(constraint_type,'P','PRIMARY_KEY','R','FOREIGN_KEY','U','UNIQUE','C',
'CHECK','N','NULL') 
from all_constraints C 
where ( table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( C.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select distinct D.owner, table_name, index_name,'INDEX' 
from all_indexes D 
where ( table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( D.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select distinct E.owner, table_name, trigger_name,'TRIGGER' 
from all_triggers E 
where ( table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( E.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
union
select distinct p.owner, p.table_name, q.table_name, 'TABLE' 
from all_constraints p,all_constraints q
where p.owner = q.r_owner and p.constraint_name = q.r_constraint_name 
and   ( p.table_name = NVL(upper('&&table_name'), '*ALL*') 
   or   '&&table_name' IS NULL )
and   ( P.owner = NVL(upper('&&owner'), '*ALL*')
   or   '&&owner' IS NULL )
/


Enter value for table_name: EMP
Enter value for owner: <enter>

SCHEMA          PARENT_TABLE         OBJECT_NAME          OBJECT_TYPE
--------------- -------------------- -------------------- ---------------
SCOTT           EMP                  FK_DEPTNO            FOREIGN_KEY
SCOTT           EMP                  P                    PROCEDURE
SCOTT           EMP                  PK_EMP               INDEX
SCOTT           EMP                  PK_EMP               PRIMARY_KEY

UNDEF table_name
UNDEF owner

/

Enter value for table_name: <enter>
Enter value for owner: <enter>


SCHEMA          PARENT_TABLE         OBJECT_NAME          OBJECT_TYPE
--------------- -------------------- -------------------- ------------
CTXSYS          ALL_INDEXES          DRVDDL               PACKAGE BODY
CTXSYS          ALL_OBJECTS          DRVODM               PACKAGE BODY
CTXSYS          ALL_TRIGGERS         DRVDDL               PACKAGE BODY
CTXSYS          ANYDATA              DRIPARX              PACKAGE
CTXSYS          ANYDATA              DRIPARX              PACKAGE BODY
CTXSYS          ANYDATA              TEXTINDEXMETHODS     TYPE BODY
....
Previous Topic: Button Problem PL/SQL
Next Topic: show index from table in oracle???
Goto Forum:
  


Current Time: Thu Dec 08 10:08:22 CST 2016

Total time taken to generate the page: 0.12463 seconds