Using 'AS' in SQL statements

[cawthor]

Hi,

I have the following SQL statement:

SELECT VAL1 + ISNULL(VAL2, '') AS ALIAS FROM TABLE1

This is concatenating 2 fields in my returned data. In my ASP Code how do I reference the above value? Eg: RS.Fields("ALIAS") returns an 'Exception occured' error.

Thanks...
Rich.

 

[Sheco]

What happens if you refer to the field by index? Eg: RS(0)

 

[19511950]

it should be the same datatype.
select convert(char(3),123)+'456'output:123456
select 123+456 output:579

 

[cawthor]

RS(0) still gives me the exception error. Both fields are text so there's no conflicting data types.

 

[Sheco]

Why not just concat them on the client end?

SELECT VAR1, VAR2 FROM table
myVar = RS(0) & RS(1)

Or is the null a problem?

 

[cawthor]

yeah I think you're right. I was concat them in the statement because I was also grouping by this contatted value. However, I think grouping by the 2 single values and then concat on the client end will have the same effect.

Thanks for the help guys.

 

[Genimuse]

Which database?

 

[Genimuse]

To clarify, if SQL Server (since it looks like T-Sql), you shouldn't need, but might need, parentheses, as in:

SELECT [b][COLOR=red]([/color][/b]VAL1 + ISNULL(VAL2, '')[b][COLOR=red])[/color][/b] AS ALIAS FROM TABLE1

 

[Tarwn]

Additionally, SQL Server's not real efficient at concatting strings, so you may want to leave it for your ASP code just based on that fact.

-T