SQL条件分支实现咨询：现有条件判断代码存在语法错误求解

Let's break down what's going wrong with your current code and fix it step by step.

The Problem with Your Original Snippet #

Your code is missing logical operators (OR/AND) to connect the two conditional branches, and the parentheses aren't properly grouping the logic—this is why you're hitting syntax errors. From what I can tell, you want to apply one set of conditions when :P_PRESTAY is 'true', and a different set when :P_POSTSTAY is 'true', while keeping your base conditions intact.

Corrected SQL Code #

Here's how to properly implement that conditional logic:

AND R.RES_ESTRES = 'CM' 
AND R.RES_SISCRE IN ('MPH','HPH')
AND (
    -- Branch 1: When :P_PRESTAY is 'true'
    (:P_PRESTAY LIKE 'true' AND BOOKINGENGINECODE = :P_BOOKINGENGINECODE)
    OR
    -- Branch 2: When :P_POSTSTAY is 'true'
    (:P_POSTSTAY LIKE 'true' AND TRUNC(R.RES_FECINI) = TRUNC(SYSDATE)+2 AND TRUNC(R.RES_FECFIN) = TRUNC(SYSDATE)-1)
)

Key Improvements Explained #

• Proper Parentheses Grouping: The outer parentheses ensure the entire conditional block is treated as a single unit combined with your base conditions.
• Logical OR: Connects the two branches so only one needs to be true (adjust to AND if both conditions should apply, but that doesn't make sense for your use case).
• Clear Branching: Each conditional branch is explicitly grouped, making the code easier to read and maintain.

Optional: Using CASE for More Complex Scenarios #

If you ever need more nuanced conditional logic, you can use a CASE expression in the WHERE clause (though for this simple branch, the above approach is more efficient):

AND R.RES_ESTRES = 'CM' 
AND R.RES_SISCRE IN ('MPH','HPH')
AND CASE
    WHEN :P_PRESTAY LIKE 'true' THEN (BOOKINGENGINECODE = :P_BOOKINGENGINECODE)
    WHEN :P_POSTSTAY LIKE 'true' THEN (TRUNC(R.RES_FECINI) = TRUNC(SYSDATE)+2 AND TRUNC(R.RES_FECFIN) = TRUNC(SYSDATE)-1)
    ELSE FALSE -- Adjust if you need a default behavior
END = TRUE

Just a quick note: If :P_PRESTAY and :P_POSTSTAY are actual boolean parameters (not string 'true'/'false'), you can drop the LIKE 'true' checks and use :P_PRESTAY = TRUE directly.

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