MS SQL 2000 Syntax

Classic Servoy
1

While trying to set an alias, caddr1a, I’m getting an error when trying to use it in a WHERE clause…

Here’s the SQL statement:

var	sAddress1 = "",
	sAddress2 = "",
	sPhone1 = "",
	sPhone2 = "";
var sQuery = "SELECT ccustno,ccompany,"; 
sQuery += "caddr1a = CASE WHEN ISNUMERIC(LEFT(caddr1,CHARINDEX(' ',caddr1)-1))=1 THEN LEFT(caddr1,CHARINDEX(' ',caddr1)-1) ELSE '' END,"; 
sQuery += "caddr2a = CASE WHEN ISNUMERIC(LEFT(caddr2,CHARINDEX(' ',caddr2)-1))=1 THEN LEFT(caddr2,CHARINDEX(' ',caddr2)-1) ELSE '' END,"; 
sQuery += "cphone1a = CASE WHEN LEFT(cphone1,3) = '011' THEN REPLACE (REPLACE(REPLACE(cphone1,'-',''),'.',''),' ','') WHEN LEFT(cphone1,1) = '1' THEN substring(REPLACE(REPLACE(REPLACE (cphone1,'-',''),'.',''),' ',''),2,10) ELSE LEFT(REPLACE(REPLACE (REPLACE(cphone1,'-',''),'.',''),' ',''),10) END,";
sQuery += "cphone2a = CASE WHEN LEFT(cphone2,3) = '011' THEN REPLACE (REPLACE(REPLACE(cphone2,'-',''),'.',''),' ','') WHEN LEFT(cphone2,1) = '1' THEN substring(REPLACE(REPLACE(REPLACE (cphone2,'-',''),'.',''),' ',''),2,10) ELSE LEFT(REPLACE(REPLACE (REPLACE(cphone2,'-',''),'.',''),' ',''),10) END,"
sQuery += "vzip = RTRIM(LTRIM(czip)), ccity,cstate,cphone1,cphone2 FROM arcust "; 
sQuery += "WHERE (caddr1a + vzip)=? OR (caddr1a + vzip)=? OR (caddr2a+vzip) =? OR (caddr2a+vzip)=? OR cphone1a=? OR cphone1a=? OR cphone2a=? or cphone2a=?;";
var ds = databaseManager.getDataSetByQuery("VAM", sQuery, [sAddress1,sAddress2,sAddress1,sAddress2,sPhone1,sPhone1,sPhone2,sPhone2], -1);

application.output(ds.getExceptionMsg())

Here’s the error:

com.inet.tds.l: Msg 207, Level 16, State 3, Line 1, Sqlstate S0022
[VAMSQL]Invalid column name 'caddr1a'. [VAMSQL]Invalid column name 'caddr1a'.

Any ideas on this non-conformist MS SQL crap?

Thanks for you time in advance!

2

Will the sql string as built fire against the database (in the db sql editor) without returning this error code or a portion of this error code?

3

You are talking about an alias. Is there a column with that name? Adn: it’s easier to read if you post the parsed query instead of the string concatenation you are doing to cunstruct it.