Out of interest is there a sensible reason (ie not just the standard says so) why the collate has to be inside a case rather than outside

eg

where cast(_latestcv as clob collate ansi_CI) like '%C%'

works

where cast(_latestcv as clob) collate ansi_CI like '%C%'

doesn't

Roy Lambert

Roy,

<< Out of interest is there a sensible reason (ie not just the standard says
so) why the collate has to be inside a case rather than outside >>

You can't force expressions into a specific collation in EDB, only column
references and CASTs.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Out of interest is there a sensible reason (ie not just the standard says
>so) why the collate has to be inside a case rather than outside >>
>
>You can't force expressions into a specific collation in EDB, only column
>references and CASTs.

I see my fingers got out of sync with my brain. My post should have read

Out of interest is there a sensible reason (ie not just the standard says so) why the collate has to be inside a cast rather than outside.

Roy Lambert

Roy,

<< Out of interest is there a sensible reason (ie not just the standard says
so) why the collate has to be inside a cast rather than outside. >>

I don't do it entirely because of the standard, but rather because the
parsing is a mess if one tries to look for a COLLATE clause after any valid
expression.  Remember, the CAST is relegated to anything *inside* of the
function parentheses.  Outside of those, it's a whole different
expression/construct to the parser.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Out of interest is there a sensible reason (ie not just the standard says
>so) why the collate has to be inside a cast rather than outside. >>
>
>I don't do it entirely because of the standard, but rather because the
>parsing is a mess if one tries to look for a COLLATE clause after any valid
>expression. Remember, the CAST is relegated to anything *inside* of the
>function parentheses. Outside of those, it's a whole different
>expression/construct to the parser.

Having just been involved in a bit of parsing my own generated SQL I'd say that is a very sensible reason.

Roy Lambert [Team Elevate]