More than one row returned by a subquery used as an expressin-CodePudding

This works:

SELECT DISTINCT
"public".visit.visit_id,
"public".visit.visit_name,
"public".visit.visit_admit_date,
"public".visit.visit_disch_date,
"public".visit.visit_stay_type,
"public".visit.visit_ins,
"public".ip_visit_1.ipv1_room,
"public".ip_visit_1.ipv1_ad_init,
CASE WHEN (SELECT digital_signature_images.dsigimg_acct FROM digital_signature_images where "public".visit.visit_id = digital_signature_images.dsigimg_acct AND digital_signature_images.dsigimg_title = 'IMPORTANT LETTER FROM MEDICARE') IS NOT NULL THEN 'YES'
     WHEN (SELECT patient_images.patimg_acct FROM patient_images where visit.visit_id = patient_images.patimg_acct AND patient_images.patimg_title = 'IMPORTANT LETTER FROM MEDICARE') IS NOT NULL THEN 'YES' ELSE 'NOT ON FILE' END AS IMFM_ON_FILE
         
FROM
"public".digital_signature_images
INNER JOIN "public".visit ON "public".visit.visit_id = "public".digital_signature_images.dsigimg_acct
INNER JOIN "public".ip_visit_1 ON "public".visit.visit_id = "public".ip_visit_1.ipv1_num
INNER JOIN "public".patient_images ON "public".patient_images.patimg_acct = "public".visit.visit_id
WHERE
"public".visit.visit_ins LIKE 'M%' AND
"public".visit.visit_stay_type = '1' AND
"public".visit.visit_disch_date = '2022-07-01' 
ORDER BY
"public".ip_visit_1.ipv1_room

When I try to do the previous month only in the WHERE portion, I get told "More than one row returned by a subquery used as an expression:

SELECT DISTINCT
"public".visit.visit_id,
"public".visit.visit_name,
"public".visit.visit_admit_date,
"public".visit.visit_disch_date,
"public".visit.visit_stay_type,
"public".visit.visit_ins,
"public".ip_visit_1.ipv1_room,
"public".ip_visit_1.ipv1_ad_init,
CASE WHEN (SELECT digital_signature_images.dsigimg_acct FROM digital_signature_images where "public".visit.visit_id = digital_signature_images.dsigimg_acct AND digital_signature_images.dsigimg_title = 'IMPORTANT LETTER FROM MEDICARE') IS NOT NULL THEN 'YES'
     WHEN (SELECT patient_images.patimg_acct FROM patient_images where visit.visit_id = patient_images.patimg_acct AND patient_images.patimg_title = 'IMPORTANT LETTER FROM MEDICARE') IS NOT NULL THEN 'YES' ELSE 'NOT ON FILE' END AS IMFM_ON_FILE
FROM
"public".digital_signature_images
INNER JOIN "public".visit ON "public".visit.visit_id = "public".digital_signature_images.dsigimg_acct
INNER JOIN "public".ip_visit_1 ON "public".visit.visit_id = "public".ip_visit_1.ipv1_num
INNER JOIN "public".patient_images ON "public".patient_images.patimg_acct = "public".visit.visit_id
WHERE
"public".visit.visit_ins LIKE 'M%' AND
"public".visit.visit_stay_type = '1' AND
ip_visit_1.ipv1_dis_date >= DATE_TRUNC('MONTH', CURRENT_DATE - INTERVAL '1 MONTH') AND
    ip_visit_1.ipv1_dis_date < DATE_TRUNC('MONTH', CURRENT_DATE)
ORDER BY
"public".ip_visit_1.ipv1_room

How can I return the previous month?

CodePudding user response：

use EXISTS (subquery) to determine if a subquery returns at least one row. It returns a boolean, so you can put it directly after the WHEN.