Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
CAST & COALESCE |
Sun, Feb 15 2009 9:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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????? |
Sun, Feb 15 2009 11:47 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Feb 16 2009 2:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Feb 16 2009 2:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Feb 17 2009 3:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Feb 17 2009 1:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Feb 18 2009 2:30 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
That'll teach me to not test things out before I post Roy Lambert |
Wed, Feb 18 2009 2:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Feb 18 2009 6:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I'm guessing - is it the first output parameter that determines what the type should be? Roy Lambert |
Wed, Feb 18 2009 7:21 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |