How to use SELECT UPPER

Hi

I want to return distinct values from a table in uppercase

Have tried

SELECT UPPER DISTINCT fieldname FROM tablename

But returns an error, what is the correct syntax.

ThanksNearly, upper is a function so you need to provide a column as an argument.
Select distinct upper(fieldname) from tablename|||Thanks,

However that now causes an error, my select statement is now:

SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479

This is used in a datareader i.e.

Dim MySQL As String = "SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479"
Dim MyConnection As New SqlConnection(ConnectionString)
Dim DataReader As SqlDataReader
Dim SQLCommand As New SqlCommand(MySQL, MyConnection)
MyConnection.Open()
DataReader = SQLCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
DropDownList1.DataSource = DataReader
DropDownList1.DataTextField = ("maketext")
DropDownList1.DataBind()

The error message is:

DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a property with the name maketext.

Thanks

Ben|||it doesnt seem to be the problem with the upper word. check if the col name is correct..

hth|||When you run a column through a function, you need to provide the result with an alias:

SELECT DISTINCT UPPER (maketext) AS maketext from VsVehicles WHERE dealerrefnum=8479

SELECT SUM(Volume) AS Volume FROM MilkBottles|||nope an alias is not necessary...unless you want to parse through the loop ( if the query returns one ) or get the value into a variable...you dont need an alias when you use a function...alias is only a way to identify the column...

hth|||Well, I could be wrong, but he is databinding to a datareader, and setting the DataTextField to a column named "maketext."

His query:

SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479

returns no columns named "maketext." It does include a column (with no column name) derived from the column named "maketext", but no actual column named "maketext".

My guess is that aliasing the column name will solve his problem...|||i see what you mean...i was looking at the select stmt all the while...

::nope an alias is not necessary...unless you want to parse through the loop ( if the query
::returns one ) or get the value into a variable...you dont need an alias when you use a
::function...alias is only a way to identify the column...

from my stmt, i meant the same thing by saying "get the value into a variable"

i was prbly not clear...good you clarified it out..

dinakar|||Yes that worked, thankyou

SELECT DISTINCT UPPER (maketext) AS maketext FROM VsVehicles

Thanks

Bensql

how to use if...else

i have 4 columns that hold phone numbers. at least one has to be filled in.
how could i select the first one with a value in it
i tried:
SELECT (IF PhoneWork != '' BEGIN (SELECT CAST(PhoneWork AS bigint)) END ELSE
(SELECT CAST('0' AS bigint))) AS PhoneWork FROM Customers
but that isnt working. its not checking the other 3 columns: PhoneHome,
PhoneFax, PhoneOther, because i got an error: Incorrect syntax near ')'
thanks for your help.have u tried CASE
SELECT
CASE
WHEN PhoneWork1 IS NOT NULL THEN PhoneWork1
WHEN PhoneWork2 IS NOT NULL THEN PhoneWork2
WHEN PhoneWork3 IS NOT NULL THEN PhoneWork3
WHEN PhoneWork4 IS NOT NULL THEN PhoneWork4
END
FROM Customers|||Abraham Luna,
See function COALESCE in BOL.
Example:
select coalesce(c1, c2, c3, c4) as c
from
(
select 1, cast(null as int), cast(null as int), cast(null as int)
union all
select cast(null as int), 2, cast(null as int), cast(null as int)
union all
select cast(null as int), cast(null as int), 3, cast(null as int)
union all
select cast(null as int), cast(null as int), cast(null as int), 4
) as t1(c1, c2, c3, c4)
go
AMB
"Abraham Luna" wrote:

> i have 4 columns that hold phone numbers. at least one has to be filled in
.
> how could i select the first one with a value in it
> i tried:
> SELECT (IF PhoneWork != '' BEGIN (SELECT CAST(PhoneWork AS bigint)) END EL
SE
> (SELECT CAST('0' AS bigint))) AS PhoneWork FROM Customers
> but that isnt working. its not checking the other 3 columns: PhoneHome,
> PhoneFax, PhoneOther, because i got an error: Incorrect syntax near ')'
> thanks for your help.
>
>|||>i have 4 columns that hold phone numbers. at least one has to be filled in.
>how could i select the first one with a value in it
CREATE TABLE dbo.foo
(
PhoneWork VARCHAR(32),
PhoneHome VARCHAR(32),
PhoneFax VARCHAR(32),
PhoneOther VARCHAR(32)
)
GO
SET NOCOUNT ON
INSERT dbo.foo SELECT '555',NULL,NULL,NULL
INSERT dbo.foo SELECT '555',NULL,'222',NULL
INSERT dbo.foo SELECT NULL,'333',NULL,NULL
INSERT dbo.foo SELECT NULL,NULL,NULL,'444'
INSERT dbo.foo SELECT NULL,NULL,'666',NULL
INSERT dbo.foo SELECT NULL,NULL,NULL,NULL
INSERT dbo.foo SELECT NULL,NULL,NULL,'x'
GO
SELECT PhoneWork = COALESCE
(
NULLIF(PhoneWork, ''),
NULLIF(PhoneHome, ''),
NULLIF(PhoneFax, ''),
NULLIF(PhoneOther,''),
'0'
)
FROM dbo.foo
GO
DROP TABLE dbo.foo
Why are you casting this as a BIGINT? How are you preventing non-numeric
values from being entered into any of these columns? What on earth is a
PhoneFax?|||thank you for your answer
"Manshu" <upadhyay.himanshu@.gmail.com> wrote in message
news:1126012613.765505.60260@.g49g2000cwa.googlegroups.com...
> have u tried CASE
> SELECT
> CASE
> WHEN PhoneWork1 IS NOT NULL THEN PhoneWork1
> WHEN PhoneWork2 IS NOT NULL THEN PhoneWork2
> WHEN PhoneWork3 IS NOT NULL THEN PhoneWork3
> WHEN PhoneWork4 IS NOT NULL THEN PhoneWork4
> END
> FROM Customers
>|||LOL,
thank you for your answer. i cast it as a bigint because the gridview
boundfield dataformatstring is set to {0:(###) ###-####} and it only works
on integer columns. i can't figure out how to format strings using the
dataformatstring property (if you know how let me know).
phonefax is there fax number :)
they tried to keep the naming convention of PhoneXXX where XXX is the type
of phone number.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23SR4WXusFHA.260@.TK2MSFTNGP11.phx.gbl...
>
> CREATE TABLE dbo.foo
> (
> PhoneWork VARCHAR(32),
> PhoneHome VARCHAR(32),
> PhoneFax VARCHAR(32),
> PhoneOther VARCHAR(32)
> )
> GO
> SET NOCOUNT ON
> INSERT dbo.foo SELECT '555',NULL,NULL,NULL
> INSERT dbo.foo SELECT '555',NULL,'222',NULL
> INSERT dbo.foo SELECT NULL,'333',NULL,NULL
> INSERT dbo.foo SELECT NULL,NULL,NULL,'444'
> INSERT dbo.foo SELECT NULL,NULL,'666',NULL
> INSERT dbo.foo SELECT NULL,NULL,NULL,NULL
> INSERT dbo.foo SELECT NULL,NULL,NULL,'x'
> GO
> SELECT PhoneWork = COALESCE
> (
> NULLIF(PhoneWork, ''),
> NULLIF(PhoneHome, ''),
> NULLIF(PhoneFax, ''),
> NULLIF(PhoneOther,''),
> '0'
> )
> FROM dbo.foo
> GO
> DROP TABLE dbo.foo
>
> Why are you casting this as a BIGINT? How are you preventing non-numeric
> values from being entered into any of these columns? What on earth is a
> PhoneFax?
>