Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    62

    Unanswered: Msg 156, Level 15, State 1

    Hi everybody,

    I have some bad time trying to make this work. I could not interpret SQLServer message and obviously could not see any error. Maybe you find out what's wrong.
    Thanks in advance
    Zevang

    -----------------------------------------------------------
    Msg 156, Level 15, State 1, Procedure Q_SPECIFICS, Line 96
    Incorrect syntax near the keyword 'IN'
    (Error msg concerning the line with *)

    select COMPLEMENT, FILE_PATH
    from EVENTS
    where EVENT_CODE not IN (30,73)
    AND
    (
    CASE
    * WHEN (FILE_PATH='' OR FILE_PATH=NULL) THEN (COMPLEMENT IN (SELECT COMPLEMENT FROM Q_SPECIFIC WHERE COMPLEMENT = EVENTS.COMPLEMENT))

    ELSE (COMPLEMENT IN (SELECT COMPLEMENT FROM Q_SPECIFIC WHERE COMPLEMENT = EVENTS.COMPLEMENT AND FILE_PATH = EVENTS.FILE_PATH))
    AND
    (FILE_PATH IN (SELECT FILE_PATH FROM Q_SPECIFIC WHERE COMPLEMENT = EVENTS.COMPLEMENT AND FILE_PATH = EVENTS.FILE_PATH))

    END
    )

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    I do not find it very obvious from your code what you're trying to do. Is it something like
    Code:
    ... THEN (SELECT COMPLEMENT FROM Q_SPECIFICT WHERE COMPLEMENT = EVENTS.COMPLEMENT)
    [...]
    END AS COMPLEMENT
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Dec 2009
    Posts
    62
    Hi,

    Thanks for your reply.

    The whole idea is to substitute an enormous query and see if it works better the new way. The original is something like this:

    SELECT a, b FROM table1
    WHERE
    (a like '%text1%' and b like '%othertext1')
    OR
    (a like '%text2%' and b like '%othertext2')
    OR
    ...

    These three dots actually stand for over 1000 lines. My idea is to create a new 'table2', put into its two columns the values of 'text...' and 'othertext...' and use it in a subquery to check existence of 'a' and 'b' from 'table1'. Note: In some cases I will need to check only 'a'. In others I need to search for 'a' and 'b'.

    In MySQL I would do something like this:

    SELECT a, b FROM table1
    WHERE
    CASE WHEN ISNULL(b) OR b='' THEN
    (a) IN (SELECT c FROM table2 WHERE c=table1.a)
    ELSE
    (a,b) IN (SELECT c,d FROM table2 WHERE c=table1.a AND d=table1
    .b)
    END

    But... SQLServer does not accept this syntax. So I thought it would go like this:

    SELECT a,b FROM table1
    WHERE
    CASE
    WHEN b='' or b IS NULL
    THEN a IN (SELECT c FROM table2 WHERE c=table1.a)
    ELSE (a IN (SELECT c FROM table2 WHERE c=table1.a AND d=table1.b))
    AND
    (b IN (SELECT d FROM table2 WHERE c=table1.a AND d=table1.b))

    Is this logic correct?

    Note: I'm a completely novice to SQLServer, though I have pretty much experience with MYSql and DB2.
    Last edited by zevang; 08-12-11 at 16:40.

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Have you read my previous post? I printed the syntax there. Which is the same as MySQL and DB2, which you state to have pretty much experience with.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Dec 2009
    Posts
    62
    Wow, thanks very much for your kind help...

  6. #6
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You're welcome. If you want further information on the CASE syntax: CASE (Transact-SQL)
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Whew! Assuming that you meant:
    Code:
    SELECT COMPLEMENT, FILE_PATH
       FROM EVENTS
       WHERE  EVENT_CODE NOT IN (30,73)
          AND (
             CASE 
                WHEN (FILE_PATH='' OR FILE_PATH=NULL)
                   THEN (COMPLEMENT IN (SELECT COMPLEMENT
                      FROM Q_SPECIFIC 
                      WHERE COMPLEMENT = EVENTS.COMPLEMENT))
                ELSE    (COMPLEMENT IN (SELECT COMPLEMENT
                      FROM Q_SPECIFIC 
                      WHERE COMPLEMENT = EVENTS.COMPLEMENT
                         AND FILE_PATH = EVENTS.FILE_PATH))
                   AND  (FILE_PATH IN (SELECT FILE_PATH
                      FROM Q_SPECIFIC 
                      WHERE COMPLEMENT = EVENTS.COMPLEMENT
                         AND FILE_PATH = EVENTS.FILE_PATH))
             END
          )
    Then the way that I'd do it would be:
    Code:
    SELECT COMPLEMENT, FILE_PATH
       FROM EVENTS
       WHERE  EVENT_CODE NOT IN (30,73)
          AND EXISTS (SELECT *
             FROM Q_SPECIFIC 
             WHERE  Q_SPECIFIC.COMPLEMENT = EVENTS.COMPLEMENT
                AND ( EVENTS.FILE_PATH = ''
                   OR EVENTS.FILE_PATH IS NULL
                   OR EVENTS.FILE_PATH = Q_SPECIFIC.FILE_PATH))
    The problem is that the IN operator can be used in a WHERE clause, but it can't be used as a traditional boolean operator. That means that you can't evaluate the IN and pass the result as an rvalue, which is what you seem to be trying to do with the CASE statement.

    Besides that, using the IN operator on result sets is usually pretty inefficient. It forces the entire result set to be materialized by the server, then searched. Using the EXISTS clause allows indicies to be used if they are available, and only has to retrieve one value instead of the whole result set.

    Give this code a shot, I think that it should work and will perform a lot better than the code you originally posted could have if it was syntactically acceptable.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Dec 2009
    Posts
    62
    Now we're talking :-)

    Thanks a lot Pat! I'll try it on monday and post results.

    have a good weekend!

    Zevang

  9. #9
    Join Date
    Dec 2009
    Posts
    62

    Msg 156, Level 15, State 1 - RESOLVED

    Hi Pat,

    This is just to thank you for your private reply, and to declare this thread as resolved.

    regards,

    Zevang

Posting Permissions

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