Peter


The other news is this

CREATE PROCEDURE "SPGetContx2" ()
BEGIN
DECLARE Result CURSOR WITH RETURN For stmt;
DECLARE SQLStatement VARCHAR DEFAULT 'SELECT C.*,
RCF(RCF(C.FamilyName,C.GivenName,'' ''),C.Title,'' '') ContactN,
RCF(RCF(C.Address3,C.Address2,'', ''),C.Address1,'', '')  Addr1_3
FROM Contact2 C';
PREPARE Stmt FROM SQLStatement;
OPEN Result;
END
VERSION 1.00


Works. So yes SQL functions can be used in SPs so try it and see if it gives you the right result.


Roy Lambert [Team Elevate]

Roy

You've won me! It works a treat. I should have tested it earler, but I was trying to muddle through the coalesce function. I guess that calling the RCF function won't cost anything in speed - it appears to load the tiny table in a flash.

There is definitely something odd going on with the code I was using, but RCF works the same in EDBManager and the app, so I'll fold in your function and move on.

Thanks heaps, you've done it again

Regards

Peter

Peter,

if you don't allow empty strings in your table but always set empty
fields to NULL you can use this simple statement:

SELECT COALESCE(Address1 + ', ','') +
COALESCE(Address2 + ', ','') +
COALESCE(Address3,'') as Addr1to3
FROM Contact

Uli

Peter

>There is definitely something odd going on with the code I was using, but RCF works the same in EDBManager and the app, so I'll fold in your function and move on.

Maybe someone who actually understands SQL can comment when they go past next

All I know is I was major difficulty getting my head round it when you had more than one itsm so I can up with RCF - its a "bit" more like writeing Delphi which I can cope with.

>Thanks heaps, you've done it again

Nada

Roy Lambert [Team Elevate]

Uli

>if you don't allow empty strings in your table but always set empty
>fields to NULL you can use this simple statement:
>
>SELECT COALESCE(Address1 + ', ','') +
>COALESCE(Address2 + ', ','') +
>COALESCE(Address3,'') as Addr1to3
>FROM Contact

Nope because it gives you the very annoying trailing comma. You can get it to work in this specific case by altering it to

SELECT COALESCE(Address1 ,'') +
COALESCE( ', ' + Address2  ,'') +
COALESCE(', '+Address3,'') as Addr1to3
FROM Contact2

As long as Address1 isn't null because then you might have a leading comma. Not to likely but possible.

Which should work, but then so should Peter's original SQL (even if it was a bit weird).

Roy Lambert [Team Elevate]

Roy,

+1

Uli