Select query should return different row

Billa007 · #1 (**permalink**) 12-12-11, 07:27

Hi;

Below simple select query should return two different result set based on
the condition

Code:

SELECT t1.p_loc
     , t1.p_nor
     ,p_det 

      from table1 t1

Table1

Code:

p_loc   p_NOR        p_det
A11     1234         aaa
B11     1311         WWW
DEF     1111         DDD
DEF     2222         EEE

We will pass one flag variable(:ws-chk) value to the query from screen whether it will be 'P' or 'T'

Condition is

1. if the :ws-chk = 'P' means the select should return the P_LOC <> 'DEF' rows

2. if the :ws-chk = 'T' means the select should return the P_LOC = 'DEF' rows

Expected output

Based on condition 1

Code:

p_loc   p_NOR        p_det
A11     1234         aaa
B11     1311         WWW

Based on condition 2

Code:

p_loc   p_NOR        p_det
DEF     1111         DDD
DEF     2222         EEE

Please help

przytula_guy · #2 (**permalink**) 12-12-11, 07:41

sorry - wrong entry

tonkuma · #3 (**permalink**) 12-12-11, 21:34

Quote:

Condition is

1. if the :ws-chk = 'P' means the select should return the P_LOC <> 'DEF' rows

2. if the :ws-chk = 'T' means the select should return the P_LOC = 'DEF' rows

Generally speaking, a condition
IF condition-1 THEN use condition-2 ELSE IF condition-3 THEN use condition-4
can be implemented in SQL
condition-1 AND condition-2 OR NOT condition-1 AND condition-3 AND condition-4

In your condition, condition-1 and condition-3 are mutually exclusive,
then "NOT condition-1 AND" can be removed.

As a cosequence,
your condition can be implemented in SQL like...
condition-1 AND condition-2 OR condition-3 AND condition-4
where
condition-1 is :ws-chk = 'P'
condition-2 is P_LOC <> 'DEF'
condition-3 is :ws-chk = 'T'
condition-4 is P_LOC = 'DEF'

stolze · #4 (**permalink**) 12-13-11, 07:24

For clarity, I recommend to add some parenthesis. Although AND binds stronger than OR, it is obvious to the casual reader to have:

Code:

( condition-1 AND condition-2 ) OR ( condition-3 AND condition-4 )

It's just about maintainability...

tonkuma · #5 (**permalink**) 12-15-11, 11:25

Stolze,

I agree with you about OR.

I feel parentheses(for readability) are not so neccesary for AND or NOT.

For example:
order of readable and easy to understand representations may be (a) >= (b) >= (c) > (d) > (e) > (f).

(a) parenthesize conditions connected by OR and conditions prefiexed by NOT

Code:

       ( condition-1 AND ( NOT condition-2 ) AND condition-3 )
   OR  ( condition-4 )
   OR  ( ( NOT ( condition-5 AND condition-6 ) ) AND condition-7 )
/* is equivalent to */
       ( condition-1 AND ( NOT condition-2 ) AND condition-3 )
   OR  ( condition-4 )
   OR  ( ( ( NOT condition-5 ) OR ( NOT condition-6 ) ) AND condition-7 )

or

Code:

 ( c-1 AND ( NOT c-2 ) AND c-3 ) OR ( c-4 ) OR ( ( NOT ( c-5 AND c-6 ) ) AND c-7 )

(b) parenthesize conditions connected by OR

Code:

       ( condition-1 AND NOT condition-2 AND condition-3 )
   OR  ( condition-4 )
   OR  ( NOT ( condition-5 AND condition-6 ) AND condition-7 )
/* is equivalent to */
       ( condition-1 AND NOT condition-2 AND condition-3 )
   OR  ( condition-4 )
   OR  ( ( NOT condition-5 OR NOT condition-6 ) AND condition-7 )

(c) parenthesize compound conditions connected by OR

Code:

       ( condition-1 AND NOT condition-2 AND condition-3 )
   OR  condition-4
   OR  ( NOT ( condition-5 AND condition-6 ) AND condition-7 )
/* is equivalent to */
       ( condition-1 AND NOT condition-2 AND condition-3 )
   OR  condition-4
   OR  ( ( NOT condition-5 OR NOT condition-6 ) AND condition-7 )

(d) parenthesize conditions of right to OR

Code:

       condition-1 AND NOT condition-2 AND condition-3
   OR  ( condition-4 )
   OR  ( NOT ( condition-5 AND condition-6 ) AND condition-7 )
/* is equivalent to */
       condition-1 AND NOT condition-2 AND condition-3
   OR  ( condition-4 )
   OR  ( ( NOT condition-5 OR NOT condition-6 ) AND condition-7 )

(e) parenthesize compound conditions of right to OR

Code:

       condition-1 AND NOT condition-2 AND condition-3
   OR  condition-4
   OR  ( NOT ( condition-5 AND condition-6 ) AND condition-7 )
/* is equivalent to */
       condition-1 AND NOT condition-2 AND condition-3
   OR  condition-4
   OR  ( ( NOT condition-5 OR NOT condition-6 ) AND condition-7 )

(f) only neccesary parentheses

Code:

      condition-1 AND NOT condition-2 AND condition-3
  OR  condition-4
  OR  NOT ( condition-5 AND condition-6 ) AND condition-7
/* is equivalent to */
      condition-1 AND NOT condition-2 AND condition-3
  OR  condition-4
  OR  ( NOT condition-5 OR NOT condition-6 ) AND condition-7

or

Code:

 c-1 AND NOT c-2 AND c-3 OR c-4 OR NOT ( c-5 AND c-6 ) AND c-7

While I prefer (c) or (e) and sometimes use (b),
some person may prefer (a) or (b).

Stolze,

do you like (b) or (c)?

Internet Services

Web Development

Digital Marketing

Consumer Tech