Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: How does one do this?

    In Access SQL, one can write a SQL statement like this:

    Code:
    SELECT [field_name] IN('01','03') AS Test from tblTable
    and the evaluation of the expression [field_name] IN('01','03') returns a boolean, true, or false, under the heading Test.

    I've tried the same, simple code in MSSQL, and it doesn't work, despite all kinds minor alterations.

    The reason this is so useful is, suppose you have a very complex WHERE clause, with lots of IN, or LIKE, operators, and you don't know which portion of the clause is causing the problem.

    In Access, you can just copy and past the WHERE clause into the SELECT area, break it apart into separate clauses, and it will allow you to examine what is breaking your query.

    If I have to re-write every one of my WHERE conditions into a CASE, then I loose the authenticity of my evaluation.

    Is there an easy way to transfer a WHERE clause to the SELECT portion of a query?

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: How does one do this?

    Hi,

    try something like this:

    Code:
    SELECT case when [field_name] IN('01','03') then 1 else 0 end AS Test from tblTable
    Markus

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Hey Markus,

    Thank you for the reply.

    As my original posting stated, I can re-write the criteria logic from the WHERE clause using the CASE statement. But if I've got, say 10 IN operators in a WHERE clause, and each IN contains a list of 10 items, then I might as well have written the WHERE clause using the CASE statement in the first place. The reason I used the IN operator, is to save typing, time and the possibility of errors.

    The nice part of Access' functionality is that I can simply copy the whole WHERE clause into the SELECT list, change the ANDs/ORs to commas, and then I can directly view the data that the WHERE clause is using.

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Hi,

    maybe i missed you, but why don't use ONE case around your conditional clause. this way you can check your condition step by step,
    use comments to disable parts of it etc..


    BTW, i don't think it is possible to put the where clause as it is into the columns-part of an select.

    Markus

Posting Permissions

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