Can I create a variable in SQL?

[ronh42]

Can I or am I creating the Variable GSE correctly? The code follows.

strSqlCd = "SELECT RPTYEAR , "
strSqlCd = strSqlCd + "'FNMA' AS [GSE], "
strSqlCd = strSqlCd + "STATE, "
strSqlCd = strSqlCd + "NSERV AS [SERVED UNDERSERVED], "
strSqlCd = strSqlCd + "FIRST AS [BUYER TYPE], "
strSqlCd = strSqlCd + "RACE AS [ETHNIC GROUP], "
strSqlCd = strSqlCd + "GEND AS [GENDER], "
strSqlCd = strSqlCd + "BORATIO AS [BORROWER RATIO], "
strSqlCd = strSqlCd + "SUM(COUNT) AS [NumberLoans] "
strSqlCd = strSqlCd + "FROM tblFNMA1996 "
strSqlCd = strSqlCd + " WHERE STATEFP = '06' AND"
strSqlCd = strSqlCd + " GEND = 'MALE' AND"
strSqlCd = strSqlCd + " FIRST = 'FIRST' AND"
strSqlCd = strSqlCd + " RACE = 'WHT'"
strSqlCd = strSqlCd + " GROUP BY RPTYEAR, GSE, STATE, NSERV, FIRST, "
strSqlCd = strSqlCd + " RACE, GEND, BORATIO "
strSqlCd = strSqlCd + " ORDER BY NSERV, FIRST, BORATIO;"

Thanks in advance.

 

[chiph]

Yes.

But it's not called a variable, it's called a column alias.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[BiggerBrother]

If you are concatenating strings, you need to use the "&" rather than the "+" sysmbol. Also, is FNMA the column name, because if so, it does not need to be inside quotes.

BB

 

[Golom]

Yes, you are ... but your GROUP BY should be
[tt]
strSqlCd = strSqlCd + " GROUP BY RPTYEAR, 'FNMA', STATE, NSERV, FIRST, "
strSqlCd = strSqlCd + " RACE, GEND, BORATIO "
[/tt]
You cannot use an alias name in a Group By. You must use the source name or value ... thus 'FNAM' and not [GSE]

I would also enclose field names like "FIRST" and "COUNT" in brackets (i.e. [FIRST] or [COUNT]) since they are reserved words in SQL.