AnsweredAssumed Answered

Using ExecuteSQL Case on a Time Field

Question asked by cynthiablue on Nov 21, 2017
Latest reply on Nov 21, 2017 by user19752

I'm using the CASE statement in my executeSQL in order to format my output and display exactly what I want.

 

This works:

case when (Placement > 4 OR Placement is null) then 0 else Placement END as SHOW

 

Placement is a Number field and it works fine within the ExecuteSQL

 

I'm trying the same thing on a TIME field, and am not able to get it to work.  If anyone knows, could they help me out with the syntax?  I've tried the following as well as some variations and so far nothing has worked:

 

case when runTime is null then 0 ELSE runTime END as SHOW

 

This is my entire SQL:

ExecuteSQL ("

  SELECT ' ' || DogRunDate || '- Trial ' || TrialNumber || ':  ' || Element || '  ' || DogLevel || ' ' || AorBClass || ' -- P/F: ' || PassFail || ', Time: ' || case when runTime is null then 0 ELSE runTime END as SHOW || ', Placement: ' || case when (Placement > 4 OR Placement is null) then 0 else Placement END as SHOW

  FROM Run

  WHERE \"__fkDog\" = ?

  AND DogLevel <> ?

  AND \"__fkEvent\" = ?

ORDER BY DogRunDate, TrialNumber, SortElement, SortLevel

  "; ""; ""; $nextDogFromSet; "Not Entered"; CurrentEvent::__pkCurrentEvent

)

Outcomes