Web Development

Ask something
Question Locked

SQL - Creating a field name form SELECT

0 helpful
5 replies

by martinbullock15

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi

I am setting up a set of queries and for one of them i need to create a field name which is part self typed (constant) and part concatenated with a result of a single row field

SELECT 'ENDDATE_P' + PERIODNO

so 'ENDDATE_P' is the constant which i have typed and PERIODNO is the result from the single row field.

This is so as we move through the year the name of the column move, so the resulting field name would be: ENDDATE_P1, ENDDATE_P2 ENDDATE_P3.....

when i try to run the query i get the following error

"Error converting data type varchar to float."

Thanks in advance for any suggestions or alternatives

Kind Regards

Martin

5 replies

by larryd4

How does this post from 2007 end up at the top of my list???

by larryd4

Looking at join date,

But to answer your question.

It looks like you are trying to do math on two different types. The constant you defined, I assume, is a text string and the data your bringing in looks like its defined as a number in the table.

Then you try to add the two together rather then concatenate the two to create a string to hand off to the select statement.

Try building your SELECT statement outside of the actual SELECT so you just feed the string name

strSearch = ENDDATE_P & PERIODNO
SELECT strSearch

by tony hopkinson

e.g

Declare @sqlText Varchar(4095)

Select @sqlText = 'Select ENDDATE_P' + Convert(VarChar(1),PERIODNO) + ' From SomeTable'
Exec(@sqlText)

by larryd4

Been in the VB Script world for to long, I need to go back to SQL..

This discussion is closed to new replies.