![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
![]() |
Sat, Apr 3 2010 9:22 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Mon, Apr 5 2010 3:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Tue, Apr 6 2010 2:27 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Tue, Apr 6 2010 8:07 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Tue, Apr 6 2010 2:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Tue, Apr 6 2010 2:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Wed, Apr 7 2010 3:17 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>Actually, I meant the CREATE FUNCTION definition, but never mind, the other >SQL that you posted does the trick. My misunderstanding Roy Lambert |
Wed, Apr 7 2010 3:22 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Apr 8 2010 7:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Fri, Apr 9 2010 2:22 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |