Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2013

    Unanswered: where clause array


    I'm trying to use case...when... in the where clause.

    The user is able to choose a value from a dropdown that has two options (X and Y). If the user chooses X, he should get all entries with Column = X. If he chooses Y he should get all entries with Column = B or Column = E or Column = N or Column = R. There are more possible Column entries like 'L' or 'M' which should never be part of the result.

    Here's my code:

    WHERE ... AND
    sos.status in
    case when ?='X' then ('X')
    else ('B','E','N','R')

    I'm getting a synthax error from that.

    Thanks in advance!


  2. #2
    Join Date
    Sep 2011
    Pont l'Abbé, Brittany, France
    Provided Answers: 1
    Hummm, not sure at all you can use a case statement in a where clause.
    also not sure at all the 'else' branch can be stated with several values...
    and by the way, what would the '?' stand for ?

    In any case, this is not an easy to understand SQL statement, bad for maintenance...

  3. #3
    Join Date
    Jul 2013

    thanks for your reply.
    It is possible to use case statements in a where clause.
    When I use
    ...sos.status = case when ?='X' then 'X' else 'Y' end
    then the SQL is running. But I need the possibility to query more than one char.

    The '?' is a place holder. The value respectively the key which was chosen by the user in the dropdown will be inserted there.

    The background is the following:
    I'm programming a statistic portal for a university. One query will be which students are studying in a chosen subject with which certificate etc.
    You are also allowed to qualify your query by choosing whether you want to see the enrolled or the exmatriculated students. Exmatriculated students are marked with an 'X'. Enrolled can have different states like furloughed ('B') or newly enrolled ('N') etc.
    Within my SQL I have to get all these states. But these states are not all entries without those which are marked with an 'X'.

    Hope this helps a little bit.

  4. #4
    Join Date
    Sep 2011
    Pont l'Abbé, Brittany, France
    Provided Answers: 1

    sorry for the delay. Could ou finalize your goal?
    First did you check whether this is possible with INFORMIX? I know this is stupid question but SQL is not fully standardized and some topics can be done with one RDBMS and not in others...

    Else to find exactly where the error is:
    run dbaccess
    choose database
    run Query / new
    type you query
    Run it
    when you get the error, type 'Use Editor' instead of modify
    choose vi or your preferred editor
    the editor will show you the query, and put a ^^ character exactly under the error location

    If you tried all this, call me private and I will give you the contact of a friend in Brazil


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts