Home » Developer & Programmer » Application Express & 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 Go to next message
hudo
Messages: 151
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 Go to previous messageGo to next message
Littlefoot
Messages: 18824
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>
Re: switch for choosing different values form column [message #543791 is a reply to message #543775] Thu, 16 February 2012 19:08 Go to previous messageGo to next message
hudo
Messages: 151
Registered: May 2004
Senior Member
Littlefoot, thx it works fine.
I tried a lot with CASE (in the WHERE-clause) and also with DECODE, but did not succeed.
Is it also possible to write the select with CASE or DECODE ?

Re: switch for choosing different values form column [message #543827 is a reply to message #543791] Fri, 17 February 2012 02:32 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
But why not just add this to your where clause?
where state in ('A', 'T') or :P1_SWITCH = 'n'

It will return all entries which have a state equal A or T, and if the switch is n it will also return the rest of them. (All rows will be returned as you said)
Re: switch for choosing different values form column [message #543911 is a reply to message #543827] Fri, 17 February 2012 10:52 Go to previous message
hudo
Messages: 151
Registered: May 2004
Senior Member
c_stenersen, nice, it works too, good idea

thank you
Previous Topic: Application compatibility in versions
Next Topic: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query
Goto Forum:
  


Current Time: Thu Apr 17 18:23:38 CDT 2014

Total time taken to generate the page: 0.10647 seconds