SQL - CASE WHEN Error?

Question

Could somebody help me out? I'm not sure why this code keeps not executing:

'0' || CASE
WHEN (EAmain.ActivityStopMin - EAmain.ActivityStartMin) < 0 THEN         (DATEDIFF(hh,EAmain.ActivityStartTimeHours || ':' ||     EAmain.ActivityStartTimeMin,EAmain.ActivityStopTimeHours || ':' ||     EAmain.ActivityStopTimeMin)-1)
WHEN (EAmain.ActivityStopMin - EAmain.ActivityStartMin) >= 0 THEN
(DATEDIFF(hh,EAmain.ActivityStartTimeHours || ':' ||     EAmain.ActivityStartTimeMin,EAmain.ActivityStopTimeHours || ':' ||     EAmain.ActivityStopTimeMin))
END || ':' || (DATEDIFF(n,EAmain.ActivityStartTimeHours || ':' ||     EAmain.ActivityStartTimeMin,EAmain.ActivityStopTimeHours || ':' ||     EAmain.ActivityStopTimeMin)#60) || '0' AS ActivityTotalTime,

EDIT: I found the error code, here it is:

ERROR #5540: SQLCODE: -29 Message: Field 'EAMAIN.ACTIVITYSTOPMIN' not found in the applicable tables^ SELECT Name , Start_Time , Stop_Time , Visit_Date , Total_Time , ActivityStartTime , ActivityStopTime , CASE WHEN LEN ( ActivityTotalTime ) = ? THEN SUBSTR ( ActivityTotalTime , ? , ? ) WHEN LEN ( ActivityTotalTime ) = ? THEN ActivityTotalTime END AS ActivityTotalTime , Discipline FROM ( SELECT Demographics . Name , Visits . Start_Time , Visits . Stop_Time , Visits . Visit_Date , Visits . Total_Time , EAmain . ActivityStartTime , EAmain . ActivityStopTime , ? || CASE WHEN ( EAmain . ActivityStopMin -

EDIT AGAIN!: This code was what was implemented before the CASE statement, and it worked:

'0' || DATEDIFF(hh,EAmain.ActivityStartTimeHours || ':' ||    
EAmain.ActivityStartTimeMin,EAmain.ActivityStopTimeHours || ':' ||     
EAmain.ActivityStopTimeMin) || ':' || (DATEDIFF(n,EAmain.ActivityStartTimeHours || ':' || 
EAmain.ActivityStartTimeMin,EAmain.ActivityStopTimeHours || ':' ||    
EAmain.ActivityStopTimeMin)#60) || '0' AS ActivityTotalTime,

It's only a small part of a query. But do you get any error messages? — GolezTrol, Aug 31 '12 at 20:26
So we just have to guess? I assume you get either a syntax error or a runtime error. — GolezTrol, Aug 31 '12 at 20:29
Well that's clear then: the field ACTIVITYSTOPMIN doesn't exist in EAmain. — GolezTrol, Aug 31 '12 at 20:31
Yes it does. As I said below, the whole query worked fine until the CASE statement was added. It has something to do with my CASE syntax — eatonphil, Aug 31 '12 at 20:32

GolezTrol · Accepted Answer · 2012-08-31T20:33:24.630

1

~~On the last line you got EAmain.ActivityStartTimeMin,EAmain.ActivityStopTimeHours. That comma seems to be misplaced. As is the )#60) a couple of characters further on that last line.~~

EAmain.ACTIVITYSTOPMIN doesn't exist. :) I see in your error message that EMain isn't a table but a query. Make sure you select that field in the query as well.

edited Aug 31 '12 at 20:33

answered Aug 31 '12 at 20:27

GolezTrol

114,394
18
182
210

No that is how it is supposed to be. This whole code worked until I added the CASE statement to check if the stopmin-startmin value was negative. – eatonphil Aug 31 '12 at 20:29
See my modified response^ please – eatonphil Aug 31 '12 at 20:34
@phileaton: Could you please post the before code that worked? – Shannon Severance Aug 31 '12 at 20:35
@phileaton in the code before the CASE statement, you did not refer to the field `ActivityStopMin` at all. Are you sure you don't mean `ActivityStopTimeMin` or something? Please read your code closely. The case statement is the only place where `ActivityStopMin` is used. It's no syntax error. – GolezTrol Aug 31 '12 at 20:35
Wonderful, I'm very sorry for my ignorance, that worked perfectly! Sorry, and thank you! – eatonphil Aug 31 '12 at 20:37

SQL - CASE WHEN Error?

1 Answers1