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

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

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

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

Tim


Sent by email since I've wiped the test data and am putting live data in to catch problems


Roy Lambert

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

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

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

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

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