CASE WHEN statement in WHERE clause Postgresql

January 20, 2023

I have been struggling to figure out how to put a CASE statement in WHERE in Postgresql. I need to convert the string to a date (as seen in line 3). This works fine. When I try to pull CURRENT_DATE in the WHERE statement I run into errors. Is this the best way to do this? Any suggestions would be very much welcomed.

SELECT 
CASE WHEN
multi_app_documentation.nsma1_code = 'DATE' THEN TO_DATE(multi_app_documentation.nsma1_ans, 'MMDDYYYY') END AS "Procedure Date",
    ' ' AS "Case Confirmation Number",
    ip_visit_1.ipv1_firstname AS "Patient First", 
    ip_visit_1.ipv1_lastname AS "Patient Last", 
    visit.visit_sex AS "Patient Gender", 
    TO_CHAR(visit.visit_date_of_birth, 'MM/DD/YYYY') AS "DOB", 
    visit.visit_id AS "Account Number", 
    visit.visit_mr_num AS "MRN",
    ' ' AS "Module",
    ' ' AS "Signed off DT", 
    CASE WHEN
    multi_app_documentation.nsma1_code = 'CRNA' THEN multi_app_documentation.nsma1_ans END AS "Primary CRNA",
    ' ' AS "Secondary CRNA",
    ' ' AS "Primary Anesthesiologist",
' ' AS "Secondary Anesthesiologist",
' ' AS "Canceled Yes/No" 
FROM
    multi_app_documentation
    INNER JOIN ip_visit_1 ON multi_app_documentation.nsma1_patnum = ip_visit_1.ipv1_num
    INNER JOIN visit ON ip_visit_1.ipv1_num = visit.visit_id 
WHERE
CASE
        
        WHEN ( multi_app_documentation.nsma1_code = 'DATE' AND TO_DATE( multi_app_documentation.nsma1_ans, 'MMDDYYYY' ) = CURRENT_DATE END ) 
        AND multi_app_documentation.nsma1_ans IS NOT NULL 
ORDER BY
    ip_visit_1.ipv1_lastname ASC

>Solution :

Your parentheses are unbalanced. Also, you don’t even need a CASE expression in the WHERE clause. Use this version:

WHERE
    multi_app_documentation.nsma1_code = 'DATE' AND
    TO_DATE(multi_app_documentation.nsma1_ans, 'MMDDYYYY') = CURRENT_DATE

In addition, it is undesirable to store text dates in the nsma1_ans column. It would be better to use a bona-fide date column. If you must store the dates as text, then at least use the format YYYY-MM-DD, which then could simply be cast to date using nsma1_ans::date.