This is what I'm currently doing

COALESCE('Staff: ' + S._Staff,'')+ IF(T._Turnover IS NOT NULL AND S._Staff IS NOT NULL,', ','') + COALESCE('T/O: ' +  T._Turnover,'') AS Size,

Any suggestions to do it better? It works, may be the most efficient way, but just doesn't look very elegant, especially when there are three or four fields involved.

Roy Lambert

Roy,

<< This is what I'm currently doing

COALESCE('Staff: ' + S._Staff,'')+ IF(T._Turnover IS NOT NULL AND S._Staff
IS NOT NULL,', ','') + COALESCE('T/O: ' + T._Turnover,'') AS Size,

Any suggestions to do it better? It works, may be the most efficient way,
but just doesn't look very elegant, especially when there are three or four
fields involved. >>

Does it work ?  If so, then you're fine.   In case you haven't noticed, SQL
in general isn't very "elegant".

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Does it work ? If so, then you're fine. In case you haven't noticed, SQL
>in general isn't very "elegant".

As I said, it works, and yes I have noticed

Roy Lambert

Still not massively elegant but better

RCF(RCF(_Town,_SiteName,', '),C._Name,' - ') AS Company,
RCF('T/O: ' +  T._Turnover,'Staff: ' + S._Staff,',') AS Size,

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 THEN
IF In2 IS NOT NULL THEN
 SET Output = Output + Separator + In1;
 ELSE SET Output = In1;
END IF;
END IF;
RETURN Output;
END
DESCRIPTION 'Reverse combine fields'


Best I can come up with until I can supply a variable number of arguments to a function.

Roy Lambert

Tim


Minor problem with this approach. ElevateDB can no longer figure out the size the column should be and uses 60 so I guess I'll have to

CAST(RCF(RCF(_Town,_SiteName,', '),C._Name,' - ') AS VARCHAR(102) AS Company,

or is there a better way?

Roy Lambert

Roy,

<< Minor problem with this approach. ElevateDB can no longer figure out the
size the column should be and uses 60 so I guess I'll have to

CAST(RCF(RCF(_Town,_SiteName,', '),C._Name,' - ') AS VARCHAR(102) AS
Company,

or is there a better way? >>

The best way is to declare the length in the RETURNS clause for the
function.  If you omit the length, then EDB will not be able to figure out
what the length is at compile time, so you should stick with functions, etc.
that omit the length when they are going to be used primarily in SQL/PSM
expressions (IF, CASE, WHILE, etc.) or WHERE/JOIN clauses and specify the
length when the functions are used primarily in SELECT expressions.

--
Tim Young
Elevate Software
www.elevatesoft.com