Remove Duplicate value from One Cloumn Table

I am working SQL Server 2005 and One Table Which contain only one column without primary key
Now I want to remove all duplicate value from that table with only single query
Here are a couple options:
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
|||

Thank

Here happens like that

1) Select Distinct row from the original table and store it into tempory table

2) delete all rows from original table

3) copy the all rows from tempory table (where distinct rows inserted)

But I want to do this thing using single Delete Query
Can we do this by using only single delete query

sql

remove double value

Hy
Never use/practice SQL a lot, (vb... more, have free msde 2000) .
2 questions
A)is it simple to write a T-SQL query for having 2) at result starting
from 1) .
B)how to test dynamically sql with parmaeter ( using vb ADO)

1) before query
columA columB
d e <-same
e d <-same
e e
e d <-same

2)after query
columA columB
d e or e d
e e> A)is it simple to write a T-SQL query for having 2) at result starting
> from 1) .

SELECT DISTINCT
COALESCE(S2.a,S1.a) AS a,
COALESCE(S2.b,S1.b) AS b
FROM Sometable AS S1
LEFT JOIN Sometable AS S2
ON S1.a = S2.b AND S1.b = S2.a AND S2.a < S2.b

> B)how to test dynamically sql with parmaeter ( using vb ADO)

This may help:
http://msdn.microsoft.com/library/e...oprg02_6df7.asp
See also:
http://www.sommarskog.se/dyn-search.html

--
David Portas
----
Please reply only to the newsgroup
--|||David Portas wrote:
>>A)is it simple to write a T-SQL query for having 2) at result starting
>>from 1) .
>
> SELECT DISTINCT
> COALESCE(S2.a,S1.a) AS a,
> COALESCE(S2.b,S1.b) AS b
> FROM Sometable AS S1
> LEFT JOIN Sometable AS S2
> ON S1.a = S2.b AND S1.b = S2.a AND S2.a < S2.b
>
>>B)how to test dynamically sql with parmaeter ( using vb ADO)
>
> This may help:
> http://msdn.microsoft.com/library/e...oprg02_6df7.asp
> See also:
> http://www.sommarskog.se/dyn-search.html

hy
thank a lot for sharing your knowledge.
perhaps it was easy for you buit very hard for me
thank's for your quicky response

Remove default value from a table

Hi there.
I cannot figure this out, please help.
I have a table with an attribute with a default value. How do I erase this default value (with sql). I do not want to lose any data in my table.I didn't get that to work, it said I should use ALTER TABLE DROP CONSTRAINT. I have tried that before but without success.

But after a few minutes (almost an hour) I found a way to solve it:

ALTER TABLE voucher DROP CONSTRAINT DF__voucher__voucher__56D3D912;

The Constraint name I found by looking in the sysobjects table.

Thanks anyway!|||just do an alter table ... alter column ... and omit the default.

Remove default parameter

I have a report with default parameters set as ' ' i.e a blank value through a query.

when i deploy this report RS2005 adds a <select a value> as the text listed in the dropdown beacuase of which it does not take my default value and no display of report.

How can I remove this text being shown.

Thanks,

Kiran.

Do you have the "allow blank value" property set? Also, is your query set up to return rows if the parameter is nothing?

Simone

|||

Yes the parameter has allow blank value set. and it returns a different table with values if this parameter is kept blank.

Hence i want users to selcet blank if they do not want to consider this parameter and not just leave the parameter value as

<select a value>

Thanks,

Help appreciated.

Kiran

|||

It seems that if you have a list of available values you must also set a default value. I have a case where I allow no value to be selected but have a list of available values when necessary. To handle this I add an "All" option to my available values list and set this as the default. If this is the chosen value, then I return all records. If another value is filtered the recordset is chosen based on the selection.

Simone

|||

Hello,

So what i should do is if I ahve a list of values and a default value set to the parameter I remove teh allow blank value and that would remove the select a value.

would taht be correct?

thanks

|||

Hello,

I have defqault value for the parameter from the list of available values.

Also i have unchecked the allow blank and allow null text boxes. Still tehre is the <select a value> in the parameter drop-down.

What next.

Thanks

|||

In your query that selects the data, do you have anything such as this:

where column = case when @.variable = '' then column else @.variable end

This tells the query to pull all data when the variable value is blank, otherwise pull only the data matching the variable value. You then need to ensure you have '' as a valid choice in the "available values" section.

Simone

|||

Thanks Simone,

I do have conditions like AND @.Country=' ' AND @.Srv_Level=' '

but there is a value in the dropdown that is ' ' which is also set as default value for some of teh parameters.

the difference is that selecting ' ' does not mean that select all values as in the dropdown there is an all functionality too. Selecting a ' ' matches a specific case and gives values returning to that condition.

I am thinking this is a RS2005 addition as in the designer view do not see the <select a value> in teh parameter dropdowns

Thanks

|||

Maybe this will help you:

http://msdn2.microsoft.com/en-us/library/aa337234.aspx

I misunderstood your intentions with the blank value. The same should be true with having the blank value as your default. If it is a valid selection in the list and selected as your default value, so long as all other parameters also have a default value, your report should run. I am using both "ALL" and "" as defaults and do not have the <select a value> in my lists. I have either the "" or "All". Good luck.

Simone

|||I have used 'ALL' and NULL to accomplish this task on reports I have written|||Hi,

Could you elaborate how using Null u manged to remove <select a value> I need a ' ' as a possible selection....
thanks
|||

I am using a SQL statement to return my list of available values. In the statement I add a UNION to include the '' default.

ex.

Code Snippet

Select FieldA

From TableA

UNION

Select '' as FieldA

Order by 1

I then set '' as the default value.

I hope this helps.

Simone

Remove default parameter

I have a report with default parameters set as ' ' i.e a blank value through a query.

when i deploy this report RS2005 adds a <select a value> as the text listed in the dropdown beacuase of which it does not take my default value and no display of report.

How can I remove this text being shown.

Thanks,

Kiran.

Do you have the "allow blank value" property set? Also, is your query set up to return rows if the parameter is nothing?

Simone

|||

Yes the parameter has allow blank value set. and it returns a different table with values if this parameter is kept blank.

Hence i want users to selcet blank if they do not want to consider this parameter and not just leave the parameter value as

<select a value>

Thanks,

Help appreciated.

Kiran

|||

It seems that if you have a list of available values you must also set a default value. I have a case where I allow no value to be selected but have a list of available values when necessary. To handle this I add an "All" option to my available values list and set this as the default. If this is the chosen value, then I return all records. If another value is filtered the recordset is chosen based on the selection.

Simone

|||

Hello,

So what i should do is if I ahve a list of values and a default value set to the parameter I remove teh allow blank value and that would remove the select a value.

would taht be correct?

thanks

|||

Hello,

I have defqault value for the parameter from the list of available values.

Also i have unchecked the allow blank and allow null text boxes. Still tehre is the <select a value> in the parameter drop-down.

What next.

Thanks

|||

In your query that selects the data, do you have anything such as this:

where column = case when @.variable = '' then column else @.variable end

This tells the query to pull all data when the variable value is blank, otherwise pull only the data matching the variable value. You then need to ensure you have '' as a valid choice in the "available values" section.

Simone

|||

Thanks Simone,

I do have conditions like AND @.Country=' ' AND @.Srv_Level=' '

but there is a value in the dropdown that is ' ' which is also set as default value for some of teh parameters.

the difference is that selecting ' ' does not mean that select all values as in the dropdown there is an all functionality too. Selecting a ' ' matches a specific case and gives values returning to that condition.

I am thinking this is a RS2005 addition as in the designer view do not see the <select a value> in teh parameter dropdowns

Thanks

|||

Maybe this will help you:

http://msdn2.microsoft.com/en-us/library/aa337234.aspx

I misunderstood your intentions with the blank value. The same should be true with having the blank value as your default. If it is a valid selection in the list and selected as your default value, so long as all other parameters also have a default value, your report should run. I am using both "ALL" and "" as defaults and do not have the <select a value> in my lists. I have either the "" or "All". Good luck.

Simone

|||I have used 'ALL' and NULL to accomplish this task on reports I have written|||Hi,

Could you elaborate how using Null u manged to remove <select a value> I need a ' ' as a possible selection....
thanks
|||

I am using a SQL statement to return my list of available values. In the statement I add a UNION to include the '' default.

ex.

Code Snippet

Select FieldA

From TableA

UNION

Select '' as FieldA

Order by 1

I then set '' as the default value.

I hope this helps.

Simone

sql

Remove Decimal Points

I have the following summary string used in a Group Footer
=Count(Fields!Title.Value) &" Opportunities worth: " &
Format(Sum(Fields!Estimated_Value.Value),"C")
it gives the proper results
7 Opportunities worth $750.60
How can I remove the decimal points from the formatting?How about Format(Sum(Fields!Estimated_Value.Value),"C0")
On Mar 5, 1:28=A0pm, BLAW <brad...@.gmail.com> wrote:
> I have the following summary string used in a Group Footer
> =3DCount(Fields!Title.Value) &" Opportunities worth: " &
> Format(Sum(Fields!Estimated_Value.Value),"C")
> it gives the proper results
> 7 Opportunities worth $750.60
> How can I remove the decimal points from the formatting?|||Thanks! Easy fix. Any help on
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/2be09ac8fcf5e49a/5238a4bc8bd90c98#5238a4bc8bd90c98
?

Remove currency symbol

Hi!
Does anybody know how a currency value can be formated so that the
currency symbol (=A3 or $) is not shown?
/erikShould be able to right-click the textbox/cell and choose properties.
Then click on the Format tab. Under format code choose to format as a
number rather than currency (code will be N, I believe).
On Feb 16, 6:47 am, "inga2005" <e...@.liffner.se> wrote:
> Hi!
> Does anybody know how a currency value can be formated so that the
> currency symbol (=A3 or $) is not shown?
> /erik

Remove Comma from an integer value

I am using crystal report 8.
I have a data base field called batch_number of type integer.
I am grouping it by batch_number.
In the report group footer i am using the Format Field and setting the
format to (1123) for the batch_number.
When the report is executed ,on the left hand side we get a Group Option which shows value that are in a group.
My problem is that the batch_number always comes with a comma
for example 10,001.In the report i am able to eliminate the comma
by using format filed dialog.
Does any one has idea on how to remove comma ?

Thanks and RegardsIs the problem with the group value? if so this also you can go to format field and make the same to eliminate the comma.|||yes this value i am using to group it.I am able to remove comma in the report footer by format field.
how can i remove comma in the top left area where the group value gets
populated when the report is executed.
How can i use format field for that value?|||i also have the same problem?help me help me

examlpe my code:

"LMB/"&ToText(truncate(tonumber({dsKertasLMB_B.BilMesyuarat}),0),0)&"/"& totext((year({dsKertasLMB_B.Tahun})),0)&"(B)"

the result:
LMB/101/2,007(B)

i need to remove the saparete thousand so that the result like this:LMB/101/2007(B)

tq

Remove alpha chars & all spaces in field [UPDATED]

Hello,

I need to strip out all alpha chars and spaces in a given field and return only the numbers.

I've tried =CInt(Fields!Info.Value) and get an unexplained error. If the data was formatted consitantly I could simply do a RTrim or Right, but the number strings are not the same, some have spaces as in phone numbers (1 800 555 1212) or don't have a leading 1. Most instances are correct for my purpose (8005551212).

Any help would be appreciated.

UPDATE: Using the Replace function =Replace(Fields!Info.Value, " ","") gets me almost there. Now I should be able to use a Right, 10 function to return my desired value. Is it possible to combine these two funtions together?

Hi,

If it is possible to call the SQLServer function Please write the following function:

create FUNCTION [dbo].[GetNumberFromData]

(@.data VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @.pos BIGINT,@.char CHAR(1),@.rightside VARCHAR(MAX)

/*Set @.data = rtrim(ltrim(@.data))

While CharIndex(' ',@.data)>0

Select @.data = Replace(@.data, ' ', '')*/

SET @.pos = 1

WHILE (@.data like '%[^0-9]%') AND @.pos <= LEN(@.data)

BEGIN

SET @.char = SUBSTRING(@.data, @.pos, 1)

IF @.char like '%[0-9]%'SET @.pos = @.pos + 1

ELSE

BEGIN

SELECT @.rightside = SUBSTRING(@.data, @.pos + 1, LEN(@.data)),@.data = CASE WHEN @.rightside like '%[0-9]%' THEN

LEFT(@.data, @.pos - 1) + @.rightside

ELSE

LEFT(@.data, @.pos - 1)END

END

END

IF @.data like '%[^0-9]%' OR LEN(@.data) = 0

SET @.data = NULL

RETURN LTRIM(RTRIM(@.data))

End

and call the function like this:

If you can't able to call the function from report put the below statement in the stored procedure.and call that SP in your report.

select [dbo].[GetNumberFromData](' 1 800 , dd 798 hjjhjj kal 8976 asdf ')

Hope this helps.

Thanks

|||

Hello,

Here's a custom function I wrote that will strip all the non-numeric characters from a string. Go to Report Properties from the report menu, then in the Code tab, enter this:

Public Function GetNumbers(s as string) as Integer

Dim str as String

Dim i as Integer

str = ""

i = 0

While i < Len(s)

If (Asc(Mid(s, i + 1, 1)) >= 48 and Asc(Mid(s, i + 1, 1)) <= 57)

str = str + Mid(s, i + 1, 1)

End If

i = i + 1

End While

return cInt(str)

End Function

Then, in your textbox for the numbers, use this as your expression:

=Code.GetNumbers(Fields@.!Info.Value)

As for your Update... yes, you can use them both at the same time. =Right(Replace(Fields!Info.Value, " ", ""), 10)

Hope this helps.

Jarret

|||Thanks a BUNCH!

Remove <select a value> option

I have a parameter that get his values from a query.
his defualt value is Null.
In the report page, The first value is "<select a value>".
even if I give the parameter another defualt value Then it open with
this value selected but still the first value is "<select a value>".
Can I remove this?
Thanks.On Feb 25, 12:23 am, "nicknack" <roezo...@.gmail.com> wrote:
> I have a parameter that get his values from a query.
> his defualt value is Null.
> In the report page, The first value is "<select a value>".
> even if I give the parameter another defualt value Then it open with
> this value selected but still the first value is "<select a value>".
> Can I remove this?
> Thanks.
Please refer to the response I posted on the other Reporting Services
group.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Where is the other reporting services group..I can't find it.
Cheers
D
"EMartinez" wrote:
> On Feb 25, 12:23 am, "nicknack" <roezo...@.gmail.com> wrote:
> > I have a parameter that get his values from a query.
> > his defualt value is Null.
> >
> > In the report page, The first value is "<select a value>".
> > even if I give the parameter another defualt value Then it open with
> > this value selected but still the first value is "<select a value>".
> >
> > Can I remove this?
> >
> > Thanks.
> Please refer to the response I posted on the other Reporting Services
> group.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>