Hello

EDB Unicode version 2.12 build 12, and Delphi XE3.

I have an odd problem in that the SQL that I am using gives a different result in EDBManager to the result that is displayed in a DBGrid in my app. Yes, I know that the EDBManager is a Delphi app, so it is puzzling.

Tha data is in a "Contact" table, which has more fields than the 3 shown here...

Address1                      Address2                     Address3
Apartment 12   47 Berry Lane   NULL
Suite 99                     Plaza Place   97              Green Street
21 Main Street   NULL                     NULL

The SQL is ...
SELECT COALESCE(Address1, null, '') +
COALESCE(IF(Address2 <> '',  ', ' + Address2, null), '') +
COALESCE(IF(Address3 <> '', ', ' + Address3, null), '') Addr1to3
FROM Contact

The result, in the EDBManager is...

Apart 12, 47 Berry Lane
Suite 99, Plaza Place, 97 Green Street
21 Main Street

The result, in my app is...
Apart 12, 47 Berry Lane,
Suite 99, Plaza Place, 97 Green Street
21 Main Street, ,

Note the trailing comma(s) in the 1st and 3rd line. I appreciate that I may be mis-using the COALESCE method, but the difference makes it difficult for me to learn how to handle the concatenation of the 3 fields succesfully.

Is there something that I have overlooked? Your help will be appreciated.

Regards & TIA

Peter

Peter


My eyeballs are obviously not working because I can't figure it out. It may be something to do with the Delphi version (EDBManager is unlikely to be compiled under XE3) but I doubt it.

Whilst it doesn't answer you question you might be interested in this function (below). I wrote it because I wanted to do what you do and got fed up trying to figure it out. If the second field (ie the first parameter just to confuse) is not null the result has the separator otherwise not. It was written with the second field as the first parameter because that seemed the best way to nest them. eg

RCF(Address3,RCF(Address2,Address1,','),',')


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 Lambert

Roy

I'm sorry to have wasted your time, but when I said that the SQL i was using had the COALESCE call, I didn't say that it was in a a Stored Procedure. That means that I can't use the RCF function in the SP, doesn't it?

I have displayed the SP's data in both a DevEx grid and a standard TDBGrid, and both show the trailing commas.

Weird.

Regards

Peter

Peter

I am puzzled by your inclusion of "null" in the COALESCE expressions.

Malcolm

The other issue may be that '' is not necessarily the same as null and
you may have configured EDB differently in the IDE and Manager (??)

Malcolm

>I am puzzled by your inclusion of "null" in the COALESCE expressions.

So was I but its valid, it will just never be used since COALESCE uses the first non-null expression

Roy Lambert [Team Elevate]

Malcolm

>The other issue may be that '' is not necessarily the same as null and
>you may have configured EDB differently in the IDE and Manager (??)

It isn't so you need to test for null and <> ''

Roy Lambert [Team Elevate]

Peter

>I'm sorry to have wasted your time, but when I said that the SQL i was using had the COALESCE call, I didn't say that it was in a a Stored Procedure. That means that I can't use the RCF function in the SP, doesn't it?

No idea - I don't use stored procedures BUT it can be included in another function, its all sql so it may work. Try it and let us know.

>I have displayed the SP's data in both a DevEx grid and a standard TDBGrid, and both show the trailing commas.

I think its the logic that's wrong. As Malcom said NULL and emptystring are not the same in ElevateDB.

Trying to get my head round it

COALESCE(Address1, null, '') ->Apartment 12
COALESCE(IF(Address2 <> '',  ', ' + Address2, null), '')-> , Berry Lane

COALESCE(IF(Address3 <> '', ', ' + Address3, null), '')

Address3 <> '' is going to return TRUE since Address3 is NULL and NULL is not the same as ''

so that means the IF will return ', '+NULL which reduces to NULL so the COALESCE should then return an emptystring.

So even if I thing the logic is wron and there are superfluous NULLs in the COALESCEs I have no idea where the trailing comma is coming from. There has to be something else affecting it, or you're operating on different sets of data.

Can you post the entire SP?

Roy Lambert [Team Elevate]

Malcolm & Roy

"...you may have configured EDB differently in the IDE and Manager". That sounded promising, and I found the Engine.StandardNullBehaviour was not True. I couldn't see why that would affect the result, but changed to the default True, re-built the app, and there was no change.

I also thought that the use of NULL in the SP was Ok, but I can see how it would benefit from a test for an empty string as well. I'm not sure how to do that.

I've attached a text file that builds the table, with data, and the procedure. I have left out a bunch of fields but I called this SP from the app, with the same trailing commas.

The reason for the strange "SQLStatement" declaration is because in the "real" Procedure I dynamically append a WHERE statement, if a parameter is passed. The declaration of the variable might be the cause of the problem, but this simple demo illustrates the difference between the EDBManager and the Delphi app, so I still think it bears investigating.

Thanks for your help

Regards

Peter

Peter


Tried it and I get the same in EDBManger and Delphi. I'm still on D2006 and non-unicode so that might have a bearing.

Roy Lambert [Team Elevate]