CODEWITHSUNDEEP
where-clauseinformix
Sean Mshan
Sean Mshan

Reputation: 57

informix : dynamic condition in where clause

I want to write a query in which I would like to have condtions in the WHERE clause which are dependant on the values in the result set itself.

for example,if I have a STUDENT table with columns student_id,name,age,post_code,num_subjects_enrolled

I want to get all the names of the students living in the post_code = 'POSTCODE' and (if they take more than one subject I want to filter by age < 20 else age > 20)

I want to do all in one query .

I am using informix and any help is appreciated.

Upvotes: 0

Views: 915

Answers (1)

Michał Niklas
Michał Niklas

Reputation: 54302

I think you can built it with some AND and OR logic:

SELECT name FROM student WHERE post_code = '12345'
AND
 (
   (num_subjects_enrolled > 1 AND age < 20)
   OR
   (num_subjects_enrolled <= 1 AND age >= 20)
 )

Upvotes: 1

Related Questions