Tim

I've figured out how to make it work, but can you explain the why of it to me please.

ElevateDB Error #700 An error was found in the statement at line 5 and column 7 (ElevateDB Error #100 There is an error in the metadata for the column Expression in the derived table T1 (The size is below the minimum of 1))

SELECT
Soundex(COALESCE(_Surname,'')+COALESCE(_Forename,''),8) + ' ('+X1+')',
Soundex(COALESCE(_Surname,'')+COALESCE(_Forename,''),8) + CAST(_fkCompanies AS VARCHAR(10)) +CAST(_fkSites AS VARCHAR(10)),
_fkCompanies
FROM (SELECT

Soundex(COALESCE(_Surname,'')+COALESCE(_Forename,''),8), <<<<<<<<<<<<<<< adding this is the "fix"

_Surname, _Forename, _fkCompanies, _fkSites, CAST(COUNT(*) AS VARCHAR(3)) AS X1
     FROM Career C1
     LEFT OUTER JOIN Contacts Who ON Who._ID = _fkContacts
     WHERE (_Status <> 'Erase' OR _Status IS NULL) AND  _fkCompanies IS NOT NULL AND _fkSites IS NOT NULL AND _Left IS NULL AND Soundex(_Surname+_Forename,8) IS NOT NULL
     GROUP BY Soundex(COALESCE(_Surname,'')+COALESCE(_Forename,''),8), _fkCompanies, _fkSites
     HAVING Count(*) > 1) AS T1




Roy Lambert

Roy,

<< I've figured out how to make it work, but can you explain the why of it
to me please.

ElevateDB Error #700 An error was found in the statement at line 5 and
column 7 (ElevateDB Error #100 There is an error in the metadata for the
column Expression in the derived table T1 (The size is below the minimum of
1)) >>

What does the SOUNDEX function look like ?  I'll need the definition in
order to say anything further.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


else if ckn = 'soundex' then begin
 if ParamValues.FindByName('sLength').Null
  then ParamValues.FindByName('Result').AsString := Soundex(ParamValues.FindByName('FldStr').AsString, 4)
 else ParamValues.FindByName('Result').AsString := Soundex(ParamValues.FindByName('FldStr').AsString, ParamValues.FindByName('sLength').AsInteger)
end;

Roy Lambert

Tim


Here's another one

SELECT REPLACE(' ','',_Switchboard), CAST(COUNT(*) AS VARCHAR(3))
FROM Sites
JOIN Companies C On C._ID = _fkCompanies
WHERE
(_Status <> 'Erase' OR _Status IS NULL)
AND
_Switchboard IS NOT NULL
GROUP BY SOUNDEX(_Name,8), REPLACE(' ','',_Switchboard)
HAVING COUNT(*) > 1


ElevateDB Error #100 There is an error in the metadata for the column Expression2 in the temporary table DELLNB3036496NLH3 (The size is below the minimum of 1)



Roy Lambert

Roy,

Actually, I meant the CREATE FUNCTION definition, but never mind, the other
SQL that you posted does the trick.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< Here's another one >>

Actually, scratch my other reply - I still need to see the SOUNDEX
definition.  It looks like you're defining it without a length, which if
that's the case, then you'll need to CAST() it to an expression with a
length in order to get it to work, even in the GROUP BY.

SQL SELECT statements, because they return a result set, must always have
definitive lengths for any expressions used in the SELECT, ORDER BY, or
GROUP BY.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Actually, I meant the CREATE FUNCTION definition, but never mind, the other
>SQL that you posted does the trick.

My misunderstanding

Roy Lambert

Tim


>Actually, scratch my other reply - I still need to see the SOUNDEX
>definition. It looks like you're defining it without a length, which if
>that's the case, then you'll need to CAST() it to an expression with a
>length in order to get it to work, even in the GROUP BY.
>

Just for completeness

FUNCTION "Soundex" (IN "FldStr" VARCHAR COLLATE ANSI, IN "sLength" INTEGER)
RETURNS VARCHAR COLLATE ANSI
EXTERNAL NAME "nlhFunctions"

>SQL SELECT statements, because they return a result set, must always have
>definitive lengths for any expressions used in the SELECT, ORDER BY, or
>GROUP BY.

OK, that explains it. I thought from a previous thread that ElevateDB applied a length itself

Roy Lambert

Roy,

<< OK, that explains it. I thought from a previous thread that ElevateDB
applied a length itself >>

I'll check that out, but the bottom line is don't rely on EDB to pick a
correct length - always use a definitive length in your expressions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I'll check that out, but the bottom line is don't rely on EDB to pick a
>correct length - always use a definitive length in your expressions.

No that I know I shall. It wasn't a problem I just didn't understand what was going on.

Roy Lambert