CODEWITHSUNDEEP
sqlcase
Matthew Paxman
Matthew Paxman

Reputation: 247

WHERE CASE returning multiple rows

I want this query to return only one row. I have put there WHEN clauses of the CASE statement in the order they would most likely occur in, so the most likely TRUE statement will always be evaluated first. However, it seems like every WHEN statement is being evaluated before the query finishes, despite it finding multiple TRUE statements.

SELECT To_Char(T.PRFINISH, 'DD/MM/YY') FINISH,
       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'

This is returning more than one row, even when the first WHEN clause is true. I thought that CASE statements finished when they found a TRUE statement.

EDIT: Perhaps I'm not explaining this well, let's try this:

In each ID in the INV_INVESTMENTS table is a project. Each project has a set of tasks in the PRTASK table. In this PRTASK table, we will have the names of tasks (PRNAME), a status (PRSTATUS), and a finish date (PRFINISH). The list of tasks, in the order they occur in a project are:

1. Concept Tech PEP Meeting Date
2. Concept BRU Meeting Date
3. End of Concept Phase
4. Evaluate Tech PEP Meeting Date
5. Evalute BRU Meeting Date
6. Evaluate TSC Meeting Date

The rest of the tasks are in the order I have them listed in the CASE statement. What I want this query to return, is the finish date for the first task in the list with a PRSTATUS of 0 or 1.

Upvotes: 0

Views: 2112

Answers (2)

Alex Buyny
Alex Buyny

Reputation: 3185

Try this. You can put your known names into a table, and then use it in a join. This will simplify your select to the following:

SELECT TOP 1 To_Char(T.PRFINISH, 'DD/MM/YY') FINISH,
       T.PRNAME  
FROM  PRTASK T
      LEFT OUTER JOIN INV_INVESTMENTS ON T.PRPROJECTID = INV_INVESTMENTS.ID
      JOIN ( 
      SELECT 'Concept Tech PEP Meeting Date' as Name, 1 as [Order]
      UNION All
      SELECT '<your next PRNAME here', 2 as [Order]
      UNION ALL ...
      ) names on names.Name = T.PRNAME
where PRSTATUS IN (0,1) AND PRINV_INVESTMENTS.CODE = '007058'
ORDER BY names.Order

Upvotes: 1

Turophile
Turophile

Reputation: 3405

I still don't understand what you are trying to do (no sample data/results given) but I would restructure it like this:

SELECT To_Char(PRFINISH, 'DD/MM/YY') AS FINISH, PRNAME
FROM ( 
  SELECT MAX(PRFINISH),
         PRNAME  
  FROM  PRTASK AS T
  WHERE  (PRNAME = 'In Service' OR PRSTATUS != 2)
  AND    PRPROJECTID IN 
    (SELECT ID 
     FROM INV_INVESTMENTS
     WHERE CODE = '007058' )
  GROUP BY PRNAME
  ) AS TABLE1

Note, this is assuming that you listed all the possible values of PRNAME in your query.

Upvotes: 0

Related Questions