convert

Question

Hi,
What is wrong with this query?
Error is:
Incorrect syntax near =

declare @Qrt tinyint = 1

select
field1,
'GrossQ' + convert(char(1), @Qrt) =
GrossQrt,
field3
from tblMain

Thanks

Answer 1

Either use COLUMN_ALIAS_NAME = expression or expression AS COLUMN_ALIAS_NAME.

Answer 2

 You cannot have both the assignment and retrieving in a single select. It seems that you want to add a string 'GrossQ'+@Qrt to field GrossQrt or name it as GrossQrt

select Field1,'GrossQ'+cast(@Qrt as char(1)) as GrossQrt,Field3 FROM tblmain

OR 

select Field1,'GrossQ'+cast(@Qrt as char(1))+ GrossQrt,Field3 FROM tblmain

---

Thanks and regards, Rishabh K

Answer 3

declare @Qrt tinyint = 1
 
select
 field1,
 'GrossQ' + convert(char(1), @Qrt) as GrossQrt,
 field3
 from tblMain

---

- Chintak (My Blog)

Answer 4

HI,
Can not use as because GrossQrt is the actual field of the table where the data is held.
I simply want to rename the field in the select query so that it shows GrossQ1 or GrossQ2, GrossQ3, GrossQ4

Any thoughts please?

Answer 5

Hi,
What is wrong with this query?
Error is:
Incorrect syntax near =

declare @Qrt tinyint = 1

select
field1,
'GrossQ' + convert(char(1), @Qrt) =
GrossQrt,
field3
from tblMain

Thanks

if you are trying to have GrossQrt as alias for the second column in the query just swap the elements before and after the assignment operator...The following should work

declare @Qrt tinyint = 1
 
select
field1,
GrossQrt = 'GrossQ' + convert(char(1), @Qrt) ,
field3
from tblMain

Here GrossQrt would be displayed as column name...

---

Murali Krishnan

Answer 6

Hi,
What is wrong with this query?
Error is:
Incorrect syntax near =

declare @Qrt tinyint = 1

select
field1,
'GrossQ' + convert(char(1), @Qrt) =
GrossQrt,
field3
from tblMain

Thanks

if you are trying to have GrossQrt as alias for the second column in the query just swap the elements before and after the assignment operator...The following should work

declare @Qrt tinyint = 1
 
select
field1,
GrossQrt = 'GrossQ' + convert(char(1), @Qrt) ,
field3
from tblMain

Here GrossQrt would be displayed as column name...

---

Murali Krishnan

You are incorrect because GrossQrt is the field of the table.
I simply want to rename this field in the select query when returning data.
Any thoughts please?
Thanks

Answer 7

Ah, how could we know that. The column alias name must be a literal, neither a variable nor an expression. From the database view it is also non-sense.  You should rename it in the front-end.

When you really think, you need this, but I really discourage from doing so: Use dynamic SQL. But read Erland's article The Curse and Blessings of Dynamic SQL before.

Answer 8

You are incorrect because GrossQrt is the field of the table.
I simply want to rename this field in the select query when returning data.
Any thoughts please?

So are you trying to update the value for the GrossQrt field in the table with the expression 'GrossQ' + convert(char(1), @Qrt). If so you have to use the update statement. You can assign the values to the variable using the select statement. But you have to use the update statement to set values for table columns

---

Murali Krishnan

Answer 9

Ah, how could we know that. The column alias name must be a literal, neither a variable nor an expression. From the database view it is also non-sense.  You should rename it in the front-end.

When you really think, you need this, but I really discourage from doing so: Use dynamic SQL. But read Erland's article The Curse and Blessings of Dynamic SQL before.

Thanks