SQL查询未输出预期结果，请求协助排查（附查询语句）

Let's break down the potential problems with your SQL query step by step, since it's not returning the expected results:

First, the most obvious issue in your provided code snippet is that your CASE expression is incomplete—this is likely causing immediate syntax errors or broken behavior:

• The final WHEN clause (WHEN [Error Messages] LIKE '%mismatch%' AND [protocol] LIKE ...) cuts off mid-condition, and there's no END keyword to close the CASE statement. This will either prevent the query from running entirely or return invalid results if the database tries to parse it anyway.

Beyond that, here are other key areas to investigate:

1. SELECT INTO Table Creation Problems #

• SELECT * INTO [HowToFix_onefile] FROM [actions_onefile] creates a new table with the same schema as actions_onefile and inserts all rows. If [HowToFix_onefile] already exists in your database, this statement will throw an error (this is true for SQL Server, for example). Double-check that the target table doesn't already exist, or switch to INSERT INTO ... SELECT if you need to add data to an existing table.
• Make sure your user account has permissions to create new tables in the database.

2. CASE Expression Logic Flaws #

Let's dig into the conditional logic for your computed HowToFix column:

• Wildcard placement: Your first LIKE condition uses 'Different Security Type%', which only matches error messages that start with that exact string. If your actual error messages have text before that phrase (e.g., "Warning: Different Security Type detected"), this condition won't trigger. Consider using '%Different Security Type%' (wildcards on both ends) to match the phrase anywhere in the string, unless you specifically need to target only leading text.
• NULL handling: The condition NOT [Actions] = 'not being scanned' will ignore rows where [Actions] is NULL. In SQL, comparing a NULL value to a string returns UNKNOWN, not TRUE or FALSE. If you want to include rows where [Actions] is NULL (i.e., treat NULL as not equal to 'not being scanned'), adjust the condition to [Actions] <> 'not being scanned' OR [Actions] IS NULL.
• Condition order: CASE statements evaluate conditions top to bottom and return the first matching result. If a row could match multiple WHEN clauses (e.g., an error message that includes both "Pruned" and "mismatch"), the first matching condition will determine the HowToFix value. Make sure the order of your WHEN clauses aligns with your priority for which fix should be applied in overlapping cases.
• Finish the incomplete condition: Don't forget to complete the final WHEN clause (e.g., WHEN [Error Messages] LIKE '%mismatch%' AND [protocol] LIKE 'your_target_protocol%' THEN 'Your Specific Fix') and add the END keyword to close the CASE (e.g., ADD [HowToFix] AS CASE ... END).

3. Data Type Compatibility #

Verify that [Error Messages], [Actions], and [protocol] are all string-based data types (e.g., VARCHAR, NVARCHAR). Using LIKE on non-string types (like numeric or date columns) will either throw an error or return unexpected matches.

Example Fixed Query Snippet #

Here's a cleaned-up version of your query with the incomplete CASE closed and NULL handling adjusted:

SELECT * INTO [HowToFix_onefile] FROM [actions_onefile];

ALTER TABLE HowToFix_onefile 
ADD [HowToFix] AS 
    CASE 
        WHEN [Error Messages] LIKE '%Different Security Type%' AND ([Actions] <> 'not being scanned' OR [Actions] IS NULL) THEN 'Change to NFS'
        WHEN [Error Messages] LIKE '%Pruned%' AND ([Actions] <> 'not being scanned' OR [Actions] IS NULL) THEN 'Change to NFS'
        WHEN [Error Messages] LIKE '%mismatch%' AND ([Actions] <> 'not being scanned' OR [Actions] IS NULL) THEN 'Change to NFS'
        WHEN [Error Messages] LIKE '%mismatch%' AND [protocol] LIKE 'your_target_protocol%' THEN 'Your Other Fix'
        -- Add additional WHEN clauses as needed
        ELSE 'No Action Needed' -- Optional: Add an ELSE to handle unmatches
    END;

Start by fixing the incomplete CASE statement first, then test small subsets of your data to verify each condition works as expected.

内容的提问来源于stack exchange，提问作者Ruth