CODEWITHSUNDEEP
sqlpostgresql
Saggio
Saggio

Reputation: 2274

Running complicated sql query within CASE

I have a new report created in postgres - it should only run if there are 3 completed cases, and then determine an ultimate outcome (pass/fail) based on the individual cases. Depending on if it passes or fails, different information needs to be displayed.

I have the three main queries that I need, but now I'm at a loss of how to combine them into one.

The main query that contains the conditional logic for a case is here:

SELECT CASE 
WHEN (SELECT COUNT(*) FROM "Alloc" WHERE "CodeID" = 1 AND "StatusCodeID" IN (2,6)) = 3
THEN 
    --Determine if case passes based on the 3 individual Assessments
    CASE 
    WHEN (SELECT COUNT(*) FROM "Answer" ans
        LEFT JOIN "AnswerOption" ansop ON ansop."AnswerOptionID" = ans."AnswerOptionID"
        LEFT JOIN "QuestionTextInstance" qtxti ON qtxti."QuestionTextInstanceID" = ans."QuestionTextInstanceID"
        LEFT JOIN "Alloc" aloc ON aloc."AllocID" = qtxti."AllocID"
        LEFT JOIN "QuestionText" qtxt ON qtxt."QuestionTextID" = qtxti."QuestionTextID"
        LEFT JOIN "Code" bcode ON aloc."CodeID" = bcode."CodeID" 
        WHERE bcode."CodeID" = 1 AND qtxt."QuestionTextID" = 11 AND ans."Value" = 0) >= 1 --At least 2 must have answered 'Yes'
    THEN 'Passed' --Execute 'Pass.sql'
    ELSE 'Did NOT Pass' --Execute 'NotPass.sql
    END
ELSE 'Report did Not Run'
END

This runs correctly and gives the correct results based on the conditions. However, within the THEN and ELSE blocks on the inner CASE statement, I need to display different information that includes many columns and many joins (which I currently have in different .sql files) instead of Pass/Did NOT Pass, but I can not find a way to implement this because it seems that any query within THEN or ELSE blocks can only return a single value.

How can I accomplish this?

Upvotes: 1

Views: 2313

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

It can be done in plain SQL in various ways. One is with well known composite / row types:

SELECT (x).*
FROM (
   SELECT CASE WHEN cond_a_here THEN
                (SELECT t FROM t WHERE x = 1)
          ELSE  (SELECT t FROM t WHERE x = 2) END AS x
   ) sub

Note the parentheses in (x).*. Those are required for a composite type to make the syntax unambiguous.

It's simpler to understand with PL/pgSQL, but you need to understand how to handle composite types. I have posted many related answers ...

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS SETOF t AS
$func$
BEGIN
IF cond_a_here THEN
   RETURN QUERY
   SELECT * FROM t WHERE x = 1;
ELSE
   RETURN QUERY
   SELECT * FROM t WHERE x = 2;
END IF;
END
$func$ LANGUAGE plpgsql;

Upvotes: 2

Related Questions