Results 1 to 5 of 5

121211, 08:27 #1Registered User
 Join Date
 Sep 2011
 Posts
 220
Unanswered: Select query should return different row
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
Code:p_loc p_NOR p_det A11 1234 aaa B11 1311 WWW DEF 1111 DDD DEF 2222 EEE
Condition is
1. if the :wschk = 'P' means the select should return the P_LOC <> 'DEF' rows
2. if the :wschk = '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
Code:p_loc p_NOR p_det DEF 1111 DDD DEF 2222 EEE

121211, 08:41 #2Registered User
 Join Date
 Apr 2006
 Location
 Belgium
 Posts
 2,545
Provided Answers: 11sorry  wrong entry
Last edited by przytula_guy; 121211 at 08:42. Reason: error
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7V8V9V9.7V10.1V10.5V11 Fundamentals DB Admin  Advanced DBA Dprop..
Information Server Datastage Certified
http://www.infocura.be

121211, 22:34 #3Registered User
 Join Date
 Feb 2008
 Location
 Japan
 Posts
 3,483
Condition is
1. if the :wschk = 'P' means the select should return the P_LOC <> 'DEF' rows
2. if the :wschk = 'T' means the select should return the P_LOC = 'DEF' rows
IF condition1 THEN use condition2 ELSE IF condition3 THEN use condition4
can be implemented in SQL
condition1 AND condition2 OR NOT condition1 AND condition3 AND condition4
In your condition, condition1 and condition3 are mutually exclusive,
then "NOT condition1 AND" can be removed.
As a cosequence,
your condition can be implemented in SQL like...
condition1 AND condition2 OR condition3 AND condition4
where
condition1 is :wschk = 'P'
condition2 is P_LOC <> 'DEF'
condition3 is :wschk = 'T'
condition4 is P_LOC = 'DEF'

121311, 08:24 #4Registered User
 Join Date
 Jan 2007
 Location
 Jena, Germany
 Posts
 2,721
For clarity, I recommend to add some parenthesis. Although AND binds stronger than OR, it is obvious to the casual reader to have:
Code:( condition1 AND condition2 ) OR ( condition3 AND condition4 )
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development

121511, 12:25 #5Registered User
 Join Date
 Feb 2008
 Location
 Japan
 Posts
 3,483
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:( condition1 AND ( NOT condition2 ) AND condition3 ) OR ( condition4 ) OR ( ( NOT ( condition5 AND condition6 ) ) AND condition7 ) /* is equivalent to */ ( condition1 AND ( NOT condition2 ) AND condition3 ) OR ( condition4 ) OR ( ( ( NOT condition5 ) OR ( NOT condition6 ) ) AND condition7 )
Code:( c1 AND ( NOT c2 ) AND c3 ) OR ( c4 ) OR ( ( NOT ( c5 AND c6 ) ) AND c7 )
Code:( condition1 AND NOT condition2 AND condition3 ) OR ( condition4 ) OR ( NOT ( condition5 AND condition6 ) AND condition7 ) /* is equivalent to */ ( condition1 AND NOT condition2 AND condition3 ) OR ( condition4 ) OR ( ( NOT condition5 OR NOT condition6 ) AND condition7 )
Code:( condition1 AND NOT condition2 AND condition3 ) OR condition4 OR ( NOT ( condition5 AND condition6 ) AND condition7 ) /* is equivalent to */ ( condition1 AND NOT condition2 AND condition3 ) OR condition4 OR ( ( NOT condition5 OR NOT condition6 ) AND condition7 )
Code:condition1 AND NOT condition2 AND condition3 OR ( condition4 ) OR ( NOT ( condition5 AND condition6 ) AND condition7 ) /* is equivalent to */ condition1 AND NOT condition2 AND condition3 OR ( condition4 ) OR ( ( NOT condition5 OR NOT condition6 ) AND condition7 )
Code:condition1 AND NOT condition2 AND condition3 OR condition4 OR ( NOT ( condition5 AND condition6 ) AND condition7 ) /* is equivalent to */ condition1 AND NOT condition2 AND condition3 OR condition4 OR ( ( NOT condition5 OR NOT condition6 ) AND condition7 )
Code:condition1 AND NOT condition2 AND condition3 OR condition4 OR NOT ( condition5 AND condition6 ) AND condition7 /* is equivalent to */ condition1 AND NOT condition2 AND condition3 OR condition4 OR ( NOT condition5 OR NOT condition6 ) AND condition7
Code:c1 AND NOT c2 AND c3 OR c4 OR NOT ( c5 AND c6 ) AND c7
While I prefer (c) or (e) and sometimes use (b),
some person may prefer (a) or (b).
Stolze,
do you like (b) or (c)?Last edited by tonkuma; 121811 at 08:43. Reason: Add " and sometimes use (b)"