![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
![]() |
Thu, May 30 2019 3:57 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | I have a nice little function - RCF which reverse catenates fields. It works very well and has been doing so for a number of years. The result is defined as a VARCHAR but without any length since I don't know what size inputs I'm going to have. Today, for the first time I wanted to add a field onto the end and decided to do it "the easy way"
RCF(_Forename,_Surname,', ')+' ('+CAST(_ID AS VARCHAR)+')', and it returns a null these two approaches work RCF('('+CAST(_ID AS VARCHAR)+')',RCF(_Forename,_Surname,', '),' ') AS Member, CAST(RCF(_Forename,_Surname,', ') AS VARCHAR(50))+' ('+CAST(_ID AS VARCHAR)+')' AS Member, Is this WAD or a bug? Its not much of a concern since I have two ways round it - its more of a hmmmmm? Roy Lambert CREATE FUNCTION "RCF" (IN "In1" VARCHAR COLLATE "ANSI", IN "In2" VARCHAR COLLATE "ANSI", IN "Separator" VARCHAR COLLATE "ANSI") RETURNS VARCHAR COLLATE "ANSI" BEGIN DECLARE Output VARCHAR; SET Output = COALESCE(In2,''); IF (In1 IS NOT NULL) AND (In1 <> '') THEN IF (In2 IS NOT NULL) AND (In2 <> '') THEN SET Output = Output + Separator + In1; ELSE SET Output = In1; END IF; END IF; RETURN Output; END DESCRIPTION 'Reverse combine fields' VERSION 1.00 |
Thu, May 30 2019 10:54 AM | Permanent Link |
Adam Brett Orixa Systems | Roy
If _ID is NULL then the result should be NULL, and a COALESCE would (quite rightly) be needed to "fix" this. The working versions of the SQL you show both pass _ID into a function, a process which might catch the NULL, and therefore show a not null value. If _ID is definitely NOT NULL, then: FunctionReturningVARCHAR(someVariables) + ' ' + COALESCE(CAST(_ID as VARCHAR), '') definitely should return a VARCHAR. If it doesn't I would call that a bug. Adam |
Fri, May 31 2019 1:52 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Adam
Good try but no kewpie doll ![]() _ID is NEVER null since its an autoinc used as the primary key (if it ever does generate null I'll be having serious words with Tim ![]() RCF could return null but in this case all rows have data in (its my test dataset). I suspect its more to do with how Tim handles varchars without a size specified but I'd like to know. Roy Lambert |
Mon, Jun 3 2019 4:06 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< I have a nice little function - RCF which reverse catenates fields. It works very well and has been doing so for a number of years. The result is defined as a VARCHAR but without any length since I don't know what size inputs I'm going to have. Today, for the first time I wanted to add a field onto the end and decided to do it "the easy way" RCF(_Forename,_Surname,', ')+' ('+CAST(_ID AS VARCHAR)+')', and it returns a null >> What does the input dataset look like ? (DDL + INSERTs would be great) Tim Young Elevate Software www.elevatesoft.com |
Tue, Jun 4 2019 1:45 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
Sent by email since I've wiped the test data and am putting live data in to catch problems Roy Lambert |
Tue, Jun 4 2019 12:49 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
Okay, I took the data that you sent and ran this query: SELECT _ID, RCF(_Forename,_Surname,', ')+' ('+CAST(_ID AS VARCHAR)+')' FROM Members None of the rows have NULL values for the expression column. (Using the function definition that you posted here) Tim Young Elevate Software www.elevatesoft.com |
Wed, Jun 5 2019 1:56 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
Just checked here - still get nulls, repaired the table still get nulls. What did you have the session set to? Mine's ANSI. I then had an idea and tried create temporary table fred as (SELECT _ID, RCF(_Forename,_Surname,', ')+' ('+CAST(_ID AS VARCHAR)+')' FROM Members) with data Row three should read Lambert, Roy (3) but I get Lambe So I deleted temporary to get a real table and here's the ddl CREATE TABLE "demo" ( "_ID" INTEGER, "Expression" VARCHAR(5) COLLATE "ANSI" ) Roy Lambert |
Wed, Jun 5 2019 4:07 AM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> Row three should read > > Lambert, Roy (3) > > but I get > > Lambe Maybe just me, but when something comes back about half the length it should be, it cries out as a Unicode/ANSI conversion error. And looking at the OP, you mention it is null. And you live with empty strings are null yes? Anyway, just a thought as I pass by. -- Matthew Jones |
Wed, Jun 5 2019 5:02 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Matthew
>Maybe just me, but when something comes back about half the length it should be, it cries out as a Unicode/ANSI conversion error. Good thought but with the biggest row I'd expect 18 characters and I still get 5 ![]() >And looking at the OP, you mention it is null. And you live with empty strings are null yes? Not really, I believe emptystring and null are one and the same, but I've modded TEDBTable to set any string fields that would hold an empty string to null to maintaining compatibility with .Clear Roy |
Mon, Jun 10 2019 12:52 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< Just checked here - still get nulls, repaired the table still get nulls. What did you have the session set to? Mine's ANSI. >> Yes, ANSI. << Row three should read Lambert, Roy (3) but I get Lambe >> Yes, that's normal (what you're getting is effectively the sum of the literal expressions in your expression, hence the 5 characters). EDB has to calculate the length of the result set column because it isn't explicitly specified for expressions that don't use CAST, and sometimes it simply can't correctly calculate the result because of a lack of length information for the function call, etc. The solution is to use CAST to explicitly specify how long the result set column should be directly in the query, or to modify your function so that it returns a VARCHAR with an explicit length. Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |