SQL CASE checking for two conditions from the same column

Question

I currently have a CASE statement that checks to see whether certain tasks are completed or not, and then returns the date of the next task. Since the tasks are ordered, each WHEN statement becomes longer, checking each of the previous tasks to see if they're complete. For some reason, after the first WHEN statement, it's skipping straight to ELSE (it should be meeting the conditions of the second or third WHEN).

CASE
    WHEN T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS != 2 THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'TECH PEP MEETING DATE' AND PRISMILESTONE = 1)
    WHEN (T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS = 2) AND (T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS != 2) THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'BRU MEETING DATE' AND PRISMILESTONE = 1)
    WHEN (T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS = 2) AND (T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS = 2) AND (T.PRNAME = 'TSC MEETING DATE' AND T.PRSTATUS != 2) THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'TSC MEETING DATE' AND PRISMILESTONE = 1) 
    ELSE (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'END OF EVALUATE PHASE' AND PRISMILESTONE = 1)
END

Is there a specific way you need to address these types of multiple WHEN conditions?

EDIT: So after some feedback from you guys I agree with the idea that you only need to evaluate one task per WHEN because the CASE statement should exit once it finds its first TRUE statement. However, having updated it to:

CASE
    WHEN UPPER(T.PRNAME) = 'EVALUATE TECH PEP MEETING DATE' AND T.PRSTATUS != 2 THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'TECH PEP MEETING DATE' AND PRISMILESTONE = 1 AND ROWNUM = 1)
    WHEN UPPER(T.PRNAME) = 'EVALUATE BRU MEETING DATE' AND T.PRSTATUS != 2 THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'BRU MEETING DATE' AND PRISMILESTONE = 1 AND ROWNUM = 1)
    WHEN UPPER(T.PRNAME) = 'EVALUATE TSC MEETING DATE' AND T.PRSTATUS != 2 THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'TSC MEETING DATE' AND PRISMILESTONE = 1 AND ROWNUM = 1) 
    ELSE (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'END OF EVALUATE PHASE' AND PRISMILESTONE = 1)
END

I am now getting:

ORA-01427: single-row subquery returns more than one row

Not sure why this is the case, especially having put ROWNUM = 1 on the end to ensure only one result is returned.

When running the THEN by itself:

SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') 
FROM PRTASK T 
WHERE T.PRPROJECTID = INV_INVESTMENTS.ID 
AND Upper(T.PRNAME) = 'TECH PEP MEETING DATE' 
AND PRISMILESTONE = 1 
AND ROWNUM = 1

I'm getting one result. If I'm right in thinking that the CASE statement will exit once it finds its first TRUE statement, why is this finding multiple rows?

EDIT 2: Ok - so I've been playing around with this some more (because I still cannot find a logical answer and I've made some headway. I have now changed the way the query is structured to the following:

SELECT DISTINCT To_Char(T.PRFINISH, 'DD/MM/YY'),
                T.PRNAME  
FROM  PRTASK T
      LEFT OUTER JOIN INV_INVESTMENTS ON T.PRPROJECTID = INV_INVESTMENTS.ID
WHERE T.PRNAME = CASE 
                     WHEN (T.PRNAME = 'Concept Tech PEP Meeting Date' AND T.PRSTATUS != 2) THEN 'Concept Tech PEP Meeting Date'
                     WHEN (T.PRNAME = 'Concept BRU Meeting Date' AND T.PRSTATUS != 2) THEN 'Concept BRU Meeting Date'
                     WHEN (T.PRNAME = 'End of Concept Phase' AND T.PRSTATUS != 2) THEN 'End of Concept Phase'                                                                                                                
                     WHEN (T.PRNAME = 'Evaluate Tech PEP Meeting Date' AND T.PRSTATUS != 2) THEN 'Evaluate Tech PEP Meeting Date'
                     WHEN (T.PRNAME = 'Evaluate BRU Meeting Date' AND T.PRSTATUS != 2) THEN 'Evaluate BRU Meeting Date'
                     WHEN (T.PRNAME = 'Evaluate TSC Meeting Date' AND T.PRSTATUS != 2) THEN 'Evaluate TSC Meeting Date'
                     WHEN (T.PRNAME = 'End of Evaluate Phase' AND T.PRSTATUS != 2) THEN 'End of Evaluate Phase'                      
                     WHEN (T.PRNAME = 'End of Analyse Phase' AND T.PRSTATUS != 2) THEN 'End of Analyse Phase'
                     WHEN (T.PRNAME = 'End of Design Phase' AND T.PRSTATUS != 2) THEN 'End of Design Phase'
                     WHEN (T.PRNAME = 'End of Build Phase' AND T.PRSTATUS != 2) THEN 'End of Build Phase'
                     WHEN (T.PRNAME = 'End of Test Phase' AND T.PRSTATUS != 2) THEN 'End of Test Phase'
                     WHEN (T.PRNAME = 'In Service' AND T.PRSTATUS != 2) THEN 'In Service'                    
                     WHEN (T.PRNAME = 'End of Implement Phase' AND T.PRSTATUS != 2) THEN 'End of Implement Phase'  
                     WHEN (T.PRNAME = 'End of Closure Phase' AND T.PRSTATUS != 2) THEN 'End of Closure Phase'
                     ELSE 'In Service'
                  END
     AND INV_INVESTMENTS.CODE = '007058'

Now, however, I'm getting multiple WHEN statements returning values. Can anyone confirm whether or not CASE statements truly only return the first TRUE value?

Solution 1

The T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRNAME = 'BRU MEETING DATE'? I think we're having an overlapping conditions here.

My high assumption here is that you have 1 table with many task with status beside, then I think what should happen here is something as below.

First is your table of tasks, I created something my own.

CREATE TABLE #testtask
  (
    PRID INT
    , PRNAME varchar(50)
    , PRSTATUS INT
    , PREREQ INT
  )

  INSERT INTO #testtask VALUES
  (1,'TECH PEP MEETING DATE',1,0),
  (2,'BRU MEETING DATE',1,1),
  (3,'TSC MEETING DATE',1,2)

Must might be something like this

Then, I created a left join on its own table related to its pre-requisite task.

SELECT
     t1.PRNAME AS [Job]
     , t1.PRSTATUS AS [JobStatus]
     , t2.PRNAME AS [PreReqJob]
     , t2.PRSTATUS AS [PreReqStatus]
  INTO #taskList
  FROM #testtask t1
  LEFT JOIN #testtask t2
  ON
    t1.PREREQ = t2.PRID

and with this following result.

before getting into what I believe is your script checking for each task state with pre-requisite tasks.

SELECT
      CASE
        WHEN tl.[Job] = 'TECH PEP MEETING DATE' AND tl.[JobStatus] != 2
            THEN
                -- do your max select here for 'Tech pep'
        WHEN tl.[Job] = 'BRU MEETING DATE' AND tl.[JobStatus] != 2 AND tl.[PreReqStatus] = 2
            THEN
                -- do your max select here for 'Bru meet'
        WHEN tl.[Job] = 'TSC MEETING DATE' AND tl.[JobStatus] != 2 AND tl.[PreReqStatus] = 2
            THEN
                -- do your max select here for 'Tsc meet'
        ELSE
            -- do your default max date
     END AS [Date]
  FROM #taskList AS tl

Please get the concept alone as I do not have your actual tables. You would pretty much have an error if you copy the whole thing. Hopefully this helps :)

Solution 2

uhm,

i tried to sqlfiddle your question, but there is to much unknown tables used. To get better help, try to build some sqlfiddle your helpers can use.

After some research it seems to me, that oracle does not in fact checks, if your query returns only one row. it checks, if your syntax seems to allow only one row. I'm not sure about this.

Try using a max(x) around all your singular subquery-return-fields and hae a look, if oracle is accepting this. If you really get only one row, a max() does not change your value, so use it.

Solution 3

First, fix your case statement for what lad2025 points out, but not using any OR, and simplified as

CASE
    WHEN T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS != 2 THEN ...
    WHEN T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS != 2 THEN ...
    WHEN T.PRNAME = 'TSC MEETING DATE' AND T.PRSTATUS != 2 THEN ...
    ELSE ...
END

Then further simplify using the case to use the same query

(
    SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') 
    FROM PRTASK T 
    WHERE T.PRPROJECTID = INV_INVESTMENTS.ID 
        AND PRISMILESTONE = 1
        AND Upper(T.PRNAME) = 
            CASE
                WHEN T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS != 2 THEN 'TECH PEP MEETING DATE'
                WHEN T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS != 2 THEN 'BRU MEETING DATE'
                WHEN T.PRNAME = 'TSC MEETING DATE' AND T.PRSTATUS != 2 THEN 'TSC MEETING DATE'
                ELSE 'END OF EVALUATE PHASE'
            END
)

Solution 4

Trying to answer the ROWNUM=1 question, are you sure you are running in one of the THEN branches? You do not have ROWNUM=1 specified in the ELSE branch. Does it help to also add ROWNUM=1 there?

Solution 5

Your CASE WHEN condition conflicts to each other, you have problem in using AND. Your 2nd and 3rd WHEN will always return FALSE, why ? look at this line..

    WHEN (T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS = 2) 
    AND (T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS != 2)
    THEN...

This one T.PRNAME = 'TECH PEP MEETING DATE is conflicted to the AND condition next to it AND (T.PRNAME = 'BRU MEETING DATE' that condition will never return TRUE same on your 3rd WHEN

Using AND will only return true, if the two condition between AND is true.

By reading your codes, it seems your AND in each WHEN of your code needs to replace by OR to correct/remove the conflicting condition in your column T.PRNAME

Below code are yours, i modified it replacing the AND by OR to correct your condition.

CASE
    WHEN T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS != 2 
        THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'TECH PEP MEETING DATE' AND PRISMILESTONE = 1)
    WHEN (T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS = 2) OR (T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS != 2) 
        THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'BRU MEETING DATE' AND PRISMILESTONE = 1)
    WHEN (T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS = 2) OR (T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS = 2) AND (T.PRNAME = 'TSC MEETING DATE' AND T.PRSTATUS != 2) 
        THEN (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'TSC MEETING DATE' AND PRISMILESTONE = 1) 
    ELSE (SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'END OF EVALUATE PHASE' AND PRISMILESTONE = 1)
END

Hope this helps.

View more solutions

Comments

[Indra Prakash Tiwari]

You should use or statement instead of AND in second and third case. e.g. statement (T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS = 2) AND (T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS != 2) should be written like this (T.PRNAME = 'TECH PEP MEETING DATE' AND T.PRSTATUS = 2) OR (T.PRNAME = 'BRU MEETING DATE' AND T.PRSTATUS != 2)

[japzdivino]

@Matthew base on your error now, the problem is in your SELECT sub query , it return more than 1 row/record, try to check your SELECT statement speciailly on WHERE condition if why it returns more than 1 rows.

[japzdivino]

@Matthew did you try the 3rd WHEN , there is also select statement there SELECT TO_CHAR(MAX(T.PRFINISH),'DD/MM/YY') FROM PRTASK T WHERE T.PRPROJECTID = INV_INVESTMENTS.ID AND Upper(T.PRNAME) = 'TSC MEETING DATE' AND PRISMILESTONE = 1 AND ROWNUM = 1, kindly try if will also return 1 record.

[singhswat]

Can you please list out all the tables used in the query and their relationship... I can try a different approach to fulfil the requirement!

[pkuderov]

@Matthew Paxman, Just fix your ELSE statement. It lacks of ROWNUM = 1 condition

[Matthew Paxman]

That did not fix the issue. Even when the the case of the PRNAME doesn't affect the query, I always get the ELSE clause returning, even when I know the other conditions are TRUE.

[PaoloC]

Please simplify your case and provide SQL to create tables with data, then detail results you're expecting. A link to sqlfiddle.com would be great.

[Dave Costa]

It seems like the current problem is that you are getting to the ELSE clause when you think one of the WHEN conditions should be true. What are the values of T.PRNAME and T.PRSTATUS in the row(s) that you are evaluating this CASE expression for? In particular, I'm wondering if T.PRSTATUS might be NULL, which would cause none of the WHEN conditions to be true.

[Matthew Paxman]

I suppose I should have considered the fact that CASE exits once it finds the first true value (I believe that's the case), in which case you're right. I over complicated it I think.

[Matthew Paxman]

Hi Japz, unfortunately using OR wouldn't help me in this CASE, but after realising that the CASE would exit when it found a TRUE condition anyway I realised it wouldn't matter. I have edited the AND part out of the code in the bottom half of my post. The returning more than one result error is my current problem. Still trying to find out why that is.

[japzdivino]

@MatthewPaxman i see, ok lets proceed to your current problem now, you said you run the SELECT by itself ? , and it return only 1 row ? , did you try the other SELECT statement there if it is also returning only 1 row ?

[Matthew Paxman]

Yes they all return one row. Interestingly, if I change UPPER(T.PRNAME) to just T.PRNAME the query will return no error, however it will always give me the ELSE result, despite the fact that I can see that one of the WHEN clauses are true. Similarly if I change TECH PEP MEETING DATE to Tech PEP Meeting Date, I get the error back again. Not sure why case sensitivity is breaking this as each of these PRNAMEs are unique.

[Eric]

@MatthewPaxman The duplication of your sub-query in CASE statement make it hard to find out the error by eye-ball check, and the result is you are missing some condition (ROWNUM = 1) on the last statement.