Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » switch for choosing different values form column (oracle xe 10g)
switch for choosing different values form column [message #543761] |
Thu, 16 February 2012 13:39 |
hudo
Messages: 165 Registered: May 2004
|
Senior Member |
|
|
CREATE TABLE TEST
( LFD NUMBER
, FIRSTNAME VARCHAR2(20)
, STATE VARCHAR(1)
);
INSERT INTO TEST (LFD, FIRSTNAME, STATE)
VALUES ( 1, 'JOHN', 'A');
INSERT INTO TEST (LFD, FIRSTNAME, STATE)
VALUES ( 2, 'JIM', 'X');
INSERT INTO TEST (LFD, FIRSTNAME, STATE)
VALUES ( 3, 'JERRY', 'T');
INSERT INTO TEST (LFD, FIRSTNAME, STATE)
VALUES ( 4, 'JACK', 'A');
INSERT INTO TEST (LFD, FIRSTNAME, STATE)
VALUES ( 5, 'JOSHUA', 'X');
--TRUNCATE TABLE TEST;
In the Apex mask, there is a switch returning y for yes and n for no.
If switch returns y the sql-select should only return the rows with state A (active) and T (transient).
If switch returns n all rows (with state A or T or X ) should be returned.
|
|
|
Re: switch for choosing different values form column [message #543775 is a reply to message #543761] |
Thu, 16 February 2012 15:17 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Suppose that "switch" is, actually, an item called P1_SWITCH. This could be your region's query:
with
t_yes as
(select lfd, firstname, state
from test
where state in ('A', 'T')
),
t_no as
(select lfd, firstname, state
from test
where state in ('A', 'T', 'X')
)
select lfd, firstname, state
from t_yes
where :P1_SWITCH = 'y'
union
select lfd, firstname, state
from t_no
where :P1_SWITCH = 'n'
order by lfd;
Here's how it works (SQL*Plus example):
SQL> set verify off
SQL>
SQL> with
2 t_yes as
3 (select lfd, firstname, state
4 from test
5 where state in ('A', 'T')
6 ),
7 t_no as
8 (select lfd, firstname, state
9 from test
10 where state in ('A', 'T', 'X')
11 )
12 select lfd, firstname, state
13 from t_yes
14 where '&&switch' = 'y'
15 union
16 select lfd, firstname, state
17 from t_no
18 where '&&switch' = 'n';
Enter value for switch: y
LFD FIRSTNAME S
---------- -------------------- -
1 JOHN A
3 JERRY T
4 JACK A
SQL> undefine switch
SQL> /
Enter value for switch: n
LFD FIRSTNAME S
---------- -------------------- -
1 JOHN A
2 JIM X
3 JERRY T
4 JACK A
5 JOSHUA X
SQL>
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 16:51:39 CST 2024
|