Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
UPPER(?) on NULL param |
Fri, Aug 2 2013 8:15 PM | Permanent Link |
Peter | Hello
I have a stored procedure in EDB 2.12 Unicode that takes a parameter and converts it to upper case. EXECUTE IMMEDIATE 'UPDATE Client SET FirstN = UPPER(?), LastN = UPPER(?) WHERE ClientID = ?' The parameters are (IN "FirstN" VARCHAR COLLATE UNI, IN "LastN" VARCHAR COLLATE UNI, ... If either parameter is NULL then the updated field has an empty string. I also use the name parameters elsewhere in the SP, so if I were to convert the values to upper case in Delphi I would need additional parameters. How can I make UPPER work with a NULL value? Regards Peter |
Fri, Aug 2 2013 8:52 PM | Permanent Link |
Terry Swiers | Hi Peter,
EXECUTE IMMEDIATE 'UPDATE Client SET FirstN = UPPER(COALESCE(?, '''')), LastN = UPPER(COALESCE(?, '''')) WHERE ClientID = ?' --------------------------------------- Terry Swiers Millennium Software, Inc. http://www.1000years.com http://www.atrex.com --------------------------------------- |
Sat, Aug 3 2013 3:58 AM | Permanent Link |
Peter | Terry
Thanks for that. I could hear COALESCE knocking on the door of my brain, but forgot the syntax. I use it elsewhere of course, but it is difficult to troll through all the stored procedures to see a previous example. I should save them all as text files. Regards Peter |
Sat, Aug 3 2013 8:42 PM | Permanent Link |
Peter | I couldn't use COALESCE, because in the example Terry provided it always returned an empty string. I tried COALESCE(UPPER(?), NULL) but UPPER always returns a string, so that was a bust. I could have wrapped that in another COALESCE but an IF statement appeared simpler...
'UPDATE Client SET FirstN = IF(NOT ? IS NULL, UPPER(?), NULL), LastN = IF(NOT ? IS NULL, UPPER(?), NULL) WHERE ClientID = ?' USING FirstN, FirstN, LastN, LastN, ClientID; ...and now I get a NULL value where there should be one. Maybe I missed something, but thanks anyway. Regards Peter |
Mon, Aug 5 2013 10:44 AM | Permanent Link |
John Hay | Peter
I am trying the following and it seems to work fine (in elevate dbmanager 2.12) as long as clientid is found! If a parameter is null the field becomes null, if it is an empty string it becomes an empty string and if it is a string it becomes the upper case of the string. BEGIN execute immediate 'UPDATE Client SET FirstN = upper(?), LastN = upper(?) WHERE ClientID = ?' USING FirstN, LastN, ClientID; END |
Wed, Aug 7 2013 6:02 AM | Permanent Link |
Peter | John
I tested your code, and of course, it worked. However, when I plugged that back into the SP that edits about 50 fields at the same time, it always resulted in an empty string. I had to revert to the IF statement. In the production SP the same IN params are reused a couple of times; the SP has an IF..END that says that IF a certain boolean IN parameter is true then the SP executes an INSERT on the ClientName table. The value is always recorded correctly in the INSERT (no change of case here), but the next statement is an UPDATE on the Client table - the case in point, if you will pardon the pun. It is as though the parameters are altered from NULL to '' during the life of the SP. The in-line IF NULL fixes it though. I cannot see why it is happening, but I suspect that I will have a eureka moment in a few weeks Thanks all Regards Peter |
This web page was last updated on Monday, May 6, 2024 at 03:23 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |