ORDER BY on generated column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ORDER BY on generated column

Author

Topic

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-09-28 : 09:25:59

I thought I had done this in the past with success, but I get an error with this query:

SELECT {First]+' '+[Last] AS FullName FROM Table ORDER By FullName

Error: No value given for one or more required parameters.

It is referring to the column FullName. I thought I could reference it in the ORDER BY.

It's an easy fix, I just ORDER BY [Last], [First], but why won;t the original query work?

kpg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-28 : 09:30:47

i don't see any problem with that beside the typo of { in [First]

It is perfectly alright to order by the column alias

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:18:54

why are you using { is it a typo?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-10-17 : 09:45:26

Yes, it is a typo, the actual routine that has the error is below:

[CODE]
Private Sub LoadDoctors()

Dim dbConnect As String = ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString

Dim cnn As New OleDbConnection(dbConnect)

'Dim strSQL As String = "SELECT ID, [First]+' '+[Last] AS Display FROM Users WHERE Doctor=true ORDER BY [First], [Last]"
Dim strSQL As String = "SELECT ID, [First]+' '+[Last] AS Display FROM Users WHERE Doctor=true ORDER BY [First], [Last]"

Dim da As New OleDbDataAdapter(strSQL, cnn)
Dim ds As New DataSet
da.Fill(ds, "Doctors")

Dim newRow As DataRow = ds.Tables("Doctors").NewRow()
newRow("ID") = -1
newRow("Display") = "<None Selected>"
ds.Tables("Doctors").Rows.InsertAt(newRow, 0)

With DoctorComboBox
.DisplayMember = "Display"
.ValueMember = "ID"
.DataSource = ds.Tables("Doctors")
.SelectedIndex = 0
End With

cnn.Close()

End Sub
[/CODE]

kpg

kpgraci
Yak Posting Veteran

68 Posts

Posted - 2011-10-17 : 09:49:31

Resloved.

Yes, it is a typo here in the forum, not in the code. I was going to post the code and I realized I was using an oledb connection (access), so I think
this is the problem - it will not work in access, but I'm sure it would work in sql server. Another doh! moment.

thx

kpg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:23:24

also
WHERE Doctor=true
doesnt make sense in sql server
it doesnt have boolean type. it should be either bit or varchar
if bit it should be
WHERE Doctor=1

if varchar it should be

WHERE Doctor='true'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources