Showing posts with label distinct. Show all posts
Showing posts with label distinct. Show all posts

Wednesday, March 21, 2012

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

Friday, March 9, 2012

How to use iif in Filter expression

I am want to do a count distinct of an ID where fieldInt is not null in a table. This is how I am doing it... In Layout view, click the table, then right click Properties on Filters tab, for filter I do the following:

In Expression column: = Fields.fieldInt.Value

In Operator column: = !=

In Value: 'null'

But this is not working. How do I solve this problem.

try this.

IsDBNull(Fields.fieldInt.Value ) = false

|||

You can write this in the expression

= iif( Fields.Fieldint.value is nothing, 0, CountDistinct(Fields.Fieldint.value))

This should get you your count distinct where values are not null.

Hammer

How to use Distinct in XML Column

We have used XML Column in our Table.

I want to select a distict values from the xml colunm.

is there any way to use distinct?

Regards

Vasanth Thangasamy

Do you want to get XML out, or a single row rowset or multiple rows?

Can you paste your xml structure?

|||

This is my XML Structure. I have stored this XML in a colunm. Here i want to fetch the disticnt of XML/Directors/Dir/Name........

<xml>

<MovieTitle>Vettaiyadu Vilaiyadu</MovieTitle>

<ImgName>KamalJyo.jpg</ImgName>

<Directors>

<Dir>

<Name>DirYou</Name>

<Status>N/A</Status>

</Dir>

<Dir>

<Name>DireMe</Name>

<Status>Pending</Status>

</Dir>

<Dir>

<Name>Gowtham</Name>

<Status>Killed</Status>

</Dir>

<Dir>

<Name>Manirat</Name>

<Status>N/A</Status>

</Dir>

</Directors>

<CastMem>

<CM>

<CM_Name>Kamal</CM_Name>

<Status>N/A</Status>

</CM>

<CM>

<CM_Name>Jyothika</CM_Name>

<Status>Aprvd</Status>

</CM>

<CM>

<CM_Name>PrakshRaj</CM_Name>

<Status>Pending</Status>

</CM>

<CM>

<CM_Name>Asai</CM_Name>

<Status>Killed</Status>

</CM>

</CastMem>

<ImgNum>2128</ImgNum>

<Tags>

<Tag>

<value>FIR</value>

</Tag>

<Tag>

<value>SEC</value>

</Tag>

<Tag>

<value>THI</value>

</Tag>

<Tag>

<value>FOR</value>

</Tag>

</Tags>

<ImgSize>

<Ht>21.2</Ht>

<Wt>22.2</Wt>

<Si>222.2</Si>

</ImgSize>

<WebAccess>True</WebAccess>

</xml>

|||

You can use the nodes method to extract the values you are interesting in and then use DISTINCT in the projection.

For example:

declare @.x xml
set @.x = N'<you xml here/>'

select distinct ref.a.value('text()[1]', 'nvarchar(25)') as name
from @.x.nodes('/xml/Directors/Dir/Name') ref(a)

If, on the other hand, you intend to use the distinct values inside of XQuery, you can use the function fn:distinct-values:

select @.x.query('fn:distinct-values(/xml/Directors/Dir/Name)')

Regards,

Galex