Tim

I can guess what's happening, but I'm not sure its right

select _Start, _End, CAST(COALESCE(_Start,'?') AS VARCHAR(5)) from Diary

ElevateDB Error #700 An error was found in the statement at line 1 and column 43 (Expected NULL or Time expression but instead found '?')

Shouldn't ElevateDB be looking at the VARCHAR I want as the output rather than just the type of _Start?

I worked out that

select _Start, _End, COALESCE(CAST(_Start AS VARCHAR(5)),'?') from Diary

works but I think the first version should as well.

Roy Lambert

ps Have I ever told you I hate the silent fail caused by NULL <> EmptyString?????

Roy,

<< I can guess what's happening, but I'm not sure its right

select _Start, _End, CAST(COALESCE(_Start,'?') AS VARCHAR(5)) from Diary

ElevateDB Error #700 An error was found in the statement at line 1 and
column 43 (Expected NULL or Time expression but instead found '?')

Shouldn't ElevateDB be looking at the VARCHAR I want as the output rather
than just the type of _Start? >>

How do you figure that ?  COALESCE requires that all involved parameter
values are of the same type.  You cannot have it returning different types,
depending upon which one is NOT NULL.  The CAST is irrelevant in this case.

<< ps Have I ever told you I hate the silent fail caused by NULL <>
EmptyString????? >>

Hmmmm, doesn't ring a bell......

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>How do you figure that ? COALESCE requires that all involved parameter
>values are of the same type.

Reading the manual - no mention of must be the same type just a dirty great list of types.

Also I was looking at the CAST not the COALESCE.

Roy Lambert

Roy,

<< Reading the manual - no mention of must be the same type just a dirty
great list of types. >>

It's an implicit rule with SQL that a function always returns the same data
type since SQL uses strong-typing.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>It's an implicit rule with SQL that a function always returns the same data
>type since SQL uses strong-typing.

Which is fine if you know who has implied what. And just to be annoying IF(), IFNULL().....

Are these not functions, can these not return a different data type?

Roy Lambert

Roy,

<< Are these not functions, can these not return a different data type? >>

No, they cannot.  They will return the same error as COALESCE() if you use
different data types.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


That'll teach me to not test things out before I post

Roy Lambert

Tim


Now I have tested IF at least

select _fkProjects, if(_fkprojects > 3,'xxx','cc') from calls - works

select _fkProjects, if(_fkprojects > 3,'xxx',89) from calls - does not work

_fkProjects is integer

So your statement should be

It's an implicit rule with SQL that all potentially returned data types must be the same, but can be different from the input data type.

Roy Lambert

Tim

I'm guessing - is it the first output parameter that determines what the type should be?


Roy Lambert

Roy,

<< I'm guessing - is it the first output parameter that determines what the
type should be?  >>

It's the first non-NULL constant output parameter, yes.   If a NULL constant
is specified, then ElevateDB has to try the next one, and so on.

--
Tim Young
Elevate Software
www.elevatesoft.com