ok, whats wrong with this?

CASE WHEN ( PODT00 > 0 ) THEN
CASE WHEN ( PODT00 - SDDT00 ) < 0 THEN 0
WHEN SDTM00 = 1700 THEN 0
WHEN SDTM00 < POTM00 THEN CAST ((CAST(CAST(CONCAT(CONCAT(CONCAT(SUBSTRING(CAST((CASE WHEN SDTM00 > 1259 THEN (CAST(CONCAT(SUBSTR(CAST(SDTM00 AS VARCHAR ( 4 ) ) , 1 , 2 ) , SUBSTR ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , 3 , 2 ) ) AS NUMERIC ( 4 , 0 ) ) - 1200 ) ELSE SDTM00 END )
AS VARCHAR ( 4 ) ) , 1 , ( CASE WHEN LENGTH ( CAST ( ( CASE WHEN SDTM00 > 1259 THEN ( CAST ( CONCAT ( SUBSTR ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , 1 , 2 ) , SUBSTR ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , 3 , 2 ) ) AS NUMERIC ( 4 , 0 ) ) - 1200 ) ELSE SDTM00 END ) AS VARCHAR ( 4 ) ) ) = 3 THEN 1 ELSE 2 END ) ) , ':' ) , SUBSTRING ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , ( CASE WHEN LENGTH ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) ) = 3 THEN 2 ELSE 3 END ) , 2 ) ) , ( CASE WHEN SDTM00 < 1200 THEN ' AM' ELSE ' PM' END ) ) AS TIME )
- CAST ( CONCAT ( CONCAT ( CONCAT ( SUBSTRING ( CAST ( ( CASE WHEN POTM00 > 1259 THEN ( CAST ( CONCAT ( SUBSTR ( CAST ( POTM00 AS VARCHAR ( 4 ) ) , 1 , 2 ) , SUBSTR ( CAST ( POTM00 AS VARCHAR ( 4 ) ) , 3 , 2 ) ) AS NUMERIC ( 4 , 0 ) ) - 1200 ) ELSE POTM00 END ) AS VARCHAR ( 4 ) ) , 1 , ( CASE WHEN LENGTH ( CAST ( ( CASE WHEN POTM00 > 1259 THEN ( CAST ( CONCAT ( SUBSTR ( CAST ( POTM00 AS VARCHAR ( 4 ) ) , 1 , 2 ) , SUBSTR ( CAST ( POTM00 AS VARCHAR ( 4 ) ) , 3 , 2 ) ) AS NUMERIC ( 4 , 0 ) ) - 1200 ) ELSE POTM00 END ) AS VARCHAR ( 4 ) ) ) = 3 THEN 1 ELSE 2 END ) ) , ':' ) , SUBSTRING ( CAST ( POTM00 AS VARCHAR ( 4 ) ) , ( CASE WHEN LENGTH ( CAST ( POTM00 AS VARCHAR ( 4 ) ) ) = 3 THEN 2 ELSE 3 END ) , 2 ) ) , ( CASE WHEN POTM00 < 1200 THEN 'AM' ELSE ' PM' END ) ) AS TIME ) AS NUMERIC ( 8 , 0 ) ) / 100 ) AS NUMERIC ( 8 , 0 ) ) ELSE 0 END WHEN ADTM00 > 0 THEN CASE WHEN ( ADDT00 - SDDT00 ) < 0 THEN 0 WHEN SDTM00 = 1700 THEN 0 WHEN SDTM00 < ADTM00 THEN CAST ( ( CAST ( CAST ( CONCAT ( CONCAT( CONCAT ( SUBSTRING ( CAST ( ( CASE WHEN SDTM00 > 1259 THEN ( CAST ( CONCAT ( SUBSTR ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , 1 , 2 ) , SUBSTR ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , 3 , 2 ) ) AS NUMERIC ( 4 , 0 ) ) - 1200 ) ELSE SDTM00 END ) AS VARCHAR ( 4 ) ) , 1 , ( CASE WHEN LENGTH ( CAST ( ( CASE WHEN SDTM00 > 1259 THEN ( CAST ( CONCAT ( SUBSTR ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , 1 , 2 ) , SUBSTR ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , 3 , 2 ) ) AS NUMERIC ( 4 , 0 ) ) - 1200 ) ELSE SDTM00 END ) AS VARCHAR ( 4 ) ) ) = 3 THEN 1 ELSE 2 END ) ) , ':' ) , SUBSTRING ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) , ( CASE WHEN LENGTH ( CAST ( SDTM00 AS VARCHAR ( 4 ) ) ) = 3 THEN 2 ELSE 3 END ) , 2 ) ) , ( CASE WHEN SDTM00 < 1200 THEN ' AM' ELSE ' PM' END ) ) AS TIME ) - CAST ( CONCAT ( CONCAT ( CONCAT ( SUBSTRING ( CAST ( ( CASE WHEN ADTM00 > 1259 THEN ( CAST ( CONCAT ( SUBSTR ( CAST ( ADTM00 AS VARCHAR ( 4 ) ) , 1 , 2 ) , SUBSTR ( CAST ( ADTM00 AS VARCHAR ( 4 ) ) , 3 , 2 ) ) AS NUMERIC ( 4 , 0 ) ) - 1200 ) ELSE ADTM00 END ) AS VARCHAR ( 4 ) ) , 1 , ( CASE WHEN LENGTH ( CAST ( ( CASE WHEN ADTM00 > 1259 THEN ( CAST ( CONCAT ( SUBSTR ( CAST ( ADTM00 AS VARCHAR ( 4 ) ) , 1 , 2 ) , SUBSTR ( CAST ( ADTM00 AS VARCHAR ( 4 ) ) , 3 , 2 ) ) AS NUMERIC ( 4 , 0 ) ) - 1200 ) ELSE ADTM00 END ) AS VARCHAR ( 4 ) ) ) = 3 THEN 1 ELSE 2 END ) ) , ':' ) , SUBSTRING ( CAST ( ADTM00 AS VARCHAR ( 4 ) ) , ( CASE WHEN LENGTH ( CAST ( ADTM00 AS VARCHAR ( 4 ) ) ) = 3 THEN 2 ELSE 3 END ) , 2 ) ) , ( CASE WHEN ADTM00 < 1200 THEN ' AM' ELSE ' PM' END ) ) AS TIME ) AS NUMERIC ( 8 , 0 ) ) / 100 ) AS NUMERIC ( 8 , 0 ) ) ELSE 0 END ELSE 0 END AS TIMEDIF

it's too hard for me to tell.:shrug:

I'm an idiot.

unfornately thats how i got it

There would be no way to even approach it without formatting with returns and indents first. I do that all the time when evaluating system generated errors that contain SQL or Cobol statements that fail. They are usually not formatted after compilation, even when the actual program is.

That's nearly impossible to even make readable. Holy crap. What crap SQL did that come from? And why does it use both SUBSTR and SUBSTRING as functions?

It errors out occasinally.

that sucks worse than all the others. Holy shit, man... that code really really really sucks. I would throw it out and start from scratch from the requirement. That would be nearly impossible to figure out. All the substrings and casts which should be call outs from the main program... It just plain sucks.

Under what conditions does it fail? What's the value of SDTM00, POTM00 and ADTM00 when it fails? That's the place to start so you can narrow it down to the exact location of the sucky code.

On edit... just had a thought... maybe SUBSTRING is not a valid function? There are more SUBSTRs than SUBSTRINGs in the code, so it's possible it fails whenever it evaluates to a portion of the code that uses the SUBSTRING function.

Even cobol isn't that ugly.

I've done C, and it doesn't look familiar, and there're no brackets and no semi-colons.

I just meant to say it LOOKED like really horrible C code. In particular all those type castings -- ugh! I should have clarified in the body of my message.

Got to love the meaningless identifiers too.

Wow. I've never seen SQL like that before. What kind of system is it from?

Just curious. I certainly don't envy you.

Its DB2.

You misspelled "* is a spoiled brat whose strings are pulled by dangerous ideologue cronies who are wrecking our economy and culture as they kill thousands."

I've written some messy SQL in my learning of it, but that's a fucking disaster. Wow. Good luck. I agree with MisU's suggestion to figure out what it's supposed to do and rewrite it, I think. Ugh.