Case expression with Boolean from PostgreSQL to SQL Server

I am translating a query from PostgreSQL to SQL Server. I didn’t write the query in PostgreSQL and it’s quite complicated for my knowledge so i don’t understand every piece of it.

From my understand: we are trying to find the max version from p_policy and when insurancestatus = 7 or 14 / transactiontype = CAN, we compare two dates (whose format are BIG INT).

This is the PG Query:

SELECT * 
FROM BLABLABLA 
WHERE
pol.vnumber =  (
                  SELECT MAX(pol1.vnumber)
                  FROM p_policy pol1
                  AND ( CASE WHEN   pol1.insurancestatus IN (7,14)  
                                 or pol1.transactiontype IN ('CAN')
                                -- ('CAN','RCA')
                             THEN pol1.veffectivedate = pol1.vexpirydate
                             ELSE pol1.veffectivedate <> pol1.vexpirydate
                             END
                       )
AND pol1.vrecordstatus NOT IN (30,254) 
etc.

I am used to have a where statement where I compare it to a value. I understand here from the Case statement we will have a boolean, but still that must be compared to something?
Anyway the main purpose is to make it work in SQL, but I believe SQL can’t read a CASE statement where THEN is a comparison.

This is what I tried:

SELECT * 
FROM BLABLABLA 
WHERE pol.vnumber =  
            (
              SELECT MAX(pol1.vnumber)
              FROM p_policy pol1
            
              WHERE sbuid = 4019
              

              AND ( CASE WHEN   pol1.insurancestatus IN (7,14)  
                             or pol1.transactiontype IN ('CAN')
                         THEN   CASE  
                                    WHEN pol1.veffectivedate = pol1.vexpirydate THEN 1
                                    WHEN pol1.veffectivedate <> pol1.vexpirydate THEN 0
                                 END
                    END
                   )

AND pol1.vrecordstatus NOT IN (30,254) 
etc.

And then I get this error from SQL Server (which directly the last line of the current code – so after the double case statement)

Msg 4145, Level 15, State 1, Line 55
An expression of non-boolean type specified in a context where a condition is expected, near ‘AND’.

Thank you !Let me know if it is not clear

Answer

I think you want boolean logic. The CASE expression would translate as:

(
    (
        (pol1.insurancestatus IN (7,14) OR  pol1.transactiontype = 'CAN')
        AND pol1.veffectivedate = pol1.vexpirydate
    ) OR (
        NOT (pol1.insurancestatus IN (7,14) OR pol1.transactiontype = 'CAN')
        AND pol1.veffectivedate <> pol1.vexpirydate
    )
)

Advertisement

Answer