Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002

    Red face Unanswered: SQL Query crashes SQL 7 sp3&4


    This is a challenge for SQL gurus only.

    I developed the query below to return the "best description" from a lookup table. The application requires us to show the most detailed description available or more generic ones if not available.

    Our requirements changed and we needed to handle two minor exceptions. I planned to handle this with a CASE statement on L.Field_Desc. This crashes SQL Server 7 on both SP3 and SP4 every time.

    I had done similar queries in Oracle, I think with NEXTVAL/PRIORVAL comparisons instead of TOP. Is there a more reliable way to do this "Best Match logic" in SQL Server?

    , L.Field_Desc Best_Match_Description --Where I added a CASE which made it Crash
    FROM Transaction_Table A
    LEFT OUTER JOIN Description_Lookup L
    ON L.Field_1_Code+L.Field_Code+L.Field_3_Code =
    (SELECT TOP 1 X.Field_1_Code+X.Field_2_Code+X.Field_3_Code
    FROM Transaction_Table Z
    LEFT OUTER JOIN Description_Lookup X
    ON Z.Transaction_Field_1 LIKE X.Field_1_Code
    AND Z.Transaction_Field_1 LIKE X.Field_2_Code
    AND Z.Transaction_Field_1 LIKE X.Field_3_Code)
    WHERE Z.Key_ID = A.Key_ID
    ORDER BY LEN(REPLACE( X.Field_1_Code ,'%','')) DESC
    , LEN(REPLACE( X.Field_2_Code ,'%','')) DESC
    , LEN(REPLACE( X.Field_3_Code ,'%','')) DESC
    , X.Field_1_Code DESC
    , X.Field_2_Code DESC
    , X.Field_3_Code DESC

    Mike Rowland

  2. #2
    Join Date
    Oct 2001
    What is the case statement.
    And what is the error.

Posting Permissions

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