How to configur a column to be unique value from the Enterprize Ma

Hi, I want one of the column in a table to not have duplicate values(it's
Varchar) but it's not the primary key column which is a auto-generated id
column for the table.
Thanks,
AlphaCreate a unique constraint on the column
http://sqlservercode.blogspot.com/
"Alpha" wrote:
> Hi, I want one of the column in a table to not have duplicate values(it's
> Varchar) but it's not the primary key column which is a auto-generated id
> column for the table.
> Thanks,
> Alpha|||Sorry I wasn't clear on my question. I know I need to set the unique
constraint but just didn't know where is Enterprise Manager is the setting.
I just found it now in the design and right click on the column. thanks for
your help anyway.
"SQL" wrote:
> Create a unique constraint on the column
> http://sqlservercode.blogspot.com/
>
> "Alpha" wrote:
> > Hi, I want one of the column in a table to not have duplicate values(it's
> > Varchar) but it's not the primary key column which is a auto-generated id
> > column for the table.
> >
> > Thanks,
> > Alpha

How to configur a column to be unique value from the Enterprize Ma

Hi, I want one of the column in a table to not have duplicate values(it's
Varchar) but it's not the primary key column which is a auto-generated id
column for the table.
Thanks,
Alpha
Create a unique constraint on the column
http://sqlservercode.blogspot.com/
"Alpha" wrote:

> Hi, I want one of the column in a table to not have duplicate values(it's
> Varchar) but it's not the primary key column which is a auto-generated id
> column for the table.
> Thanks,
> Alpha

How to configur a column to be unique value from the Enterprize Ma

Hi, I want one of the column in a table to not have duplicate values(it's
Varchar) but it's not the primary key column which is a auto-generated id
column for the table.
Thanks,
AlphaCreate a unique constraint on the column
http://sqlservercode.blogspot.com/
"Alpha" wrote:

> Hi, I want one of the column in a table to not have duplicate values(it's
> Varchar) but it's not the primary key column which is a auto-generated id
> column for the table.
> Thanks,
> Alpha

How to Concatinate results to display results in a list from Left - Right instead of Top -

Hi,
I am creating a report using SQL Server Reporting Services 2000. My
report displays values in datatable. Say, the table looks like this
EmpName Emp# Address Phone#
..... ... ...... ...
I want all phone # for an employee to be displayed in a single row.
This can be acheived by displaying phone numbers in a group footer
(group by emp#). But I want all phone# to be displayed horizontally
(Left - Right) not vertically (Top - Bottom).
Instead of displaying results like this
EmpName Emp# Address
..... ... ......
Phones
###
###
###
###
I want to display results like below -
EmpName Emp# Address Phone#
..... ... ...... ...
Phones ### , ### , ####, ###, ###, ###
..... ... ...... ...
Phones ### , ###
..... ... ...... ...
Phones ### , ###, ### , ###
How can I concatinate the phone# ? I tried using a sub report in the
footer to retrive all phone# for an employee but how to concatinate
them ?
I tried to query like this
SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
But I'm not sure about the length of the return value (may be more than
8000 characters).
So is there any way in concatinating values in Report ? Or is there any
way in acheiving this in a Listbox or Table ?
Regards,
ChiroI think Chris Hays has something that will work for you.
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
Steve MunLeeuw
"Chiro" <chirangv@.hotmail.com> wrote in message
news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> Hi,
> I am creating a report using SQL Server Reporting Services 2000. My
> report displays values in datatable. Say, the table looks like this
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> I want all phone # for an employee to be displayed in a single row.
> This can be acheived by displaying phone numbers in a group footer
> (group by emp#). But I want all phone# to be displayed horizontally
> (Left - Right) not vertically (Top - Bottom).
> Instead of displaying results like this
> EmpName Emp# Address
> ..... ... ......
> Phones
> ###
> ###
> ###
> ###
> I want to display results like below -
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> Phones ### , ### , ####, ###, ###, ###
> ..... ... ...... ...
> Phones ### , ###
> ..... ... ...... ...
> Phones ### , ###, ### , ###
>
> How can I concatinate the phone# ? I tried using a sub report in the
> footer to retrive all phone# for an employee but how to concatinate
> them ?
> I tried to query like this
> SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> But I'm not sure about the length of the return value (may be more than
> 8000 characters).
> So is there any way in concatinating values in Report ? Or is there any
> way in acheiving this in a Listbox or Table ?
> Regards,
> Chiro
>|||Hi,
Thank You.
I was able to acheive this as follows -
1. Add a Matrix control. Add Phone# in column grouping of matrix.
2. Add a ListBox control. Include Matrix inside the Listbox.
3. Edit details of Listbox to add a group expression
=RowNumber(Nothing) / 15. (15 is number of columns to be displayed)
4. Add a Matrix column group expression as your Listbox group
expression.
=RowNumber("list1_Details_Group"). Now your matrix should contain 2
group expressions. (1 for Phone # and other for controling no. of
columns).
Regards,
Chiro
Steve MunLeeuw wrote:
> I think Chris Hays has something that will work for you.
> http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
> Steve MunLeeuw
> "Chiro" <chirangv@.hotmail.com> wrote in message
> news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> > Hi,
> > I am creating a report using SQL Server Reporting Services 2000. My
> > report displays values in datatable. Say, the table looks like this
> >
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > I want all phone # for an employee to be displayed in a single row.
> > This can be acheived by displaying phone numbers in a group footer
> > (group by emp#). But I want all phone# to be displayed horizontally
> > (Left - Right) not vertically (Top - Bottom).
> > Instead of displaying results like this
> > EmpName Emp# Address
> > ..... ... ......
> > Phones
> > ###
> > ###
> > ###
> > ###
> > I want to display results like below -
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > Phones ### , ### , ####, ###, ###, ###
> > ..... ... ...... ...
> > Phones ### , ###
> > ..... ... ...... ...
> > Phones ### , ###, ### , ###
> >
> >
> > How can I concatinate the phone# ? I tried using a sub report in the
> > footer to retrive all phone# for an employee but how to concatinate
> > them ?
> > I tried to query like this
> > SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> > But I'm not sure about the length of the return value (may be more than
> >
> > 8000 characters).
> > So is there any way in concatinating values in Report ? Or is there any
> >
> > way in acheiving this in a Listbox or Table ?
> > Regards,
> > Chiro
> >

How to compare values in different rows?

Hi

I’ve a table like this (in SQL Server 2000).

v1 v2
2 8
7 10
11 15

13 17

v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.

please advise

Thanks

In order to best get this to work you need another row to explicitly sequence the data -- such as ROW_ID or something. The otherwise, you can use Transact SQL extensions, but without an explicit sequence of some kind, the results will be volatile / unpredictable. It would probably be best to avoid the extensions and stick to a CTE here if you can.

Code Snippet

declare @.ex table(id int, v1 int, v2 int)
insert @.ex
select 1, 2, 8 union all
select 2, 7, 10 union all
select 3, 11,15 union all
select 4, 13,17
--select * from @.ex

select a.id, a.v1, a.v2
from @.ex a
join @.ex b
on a.id - 1 = b.id
and a.v1 <= b.v2

/*
id v1 v2
-- -- --
2 7 10
4 13 17
*/

|||

Do you have a field in the table that will indicate what is the 'previous row'? (Something like an IDENTITY field, or datetime of entry, etc.)

|||

The Table is

ID v1 v2
1 2 8
2 7 10
3 11 15

4 13 17

|||

Here it is,

Code Snippet

Create Table #data (

[ID] INT ,

[v1] INT ,

[v2] INT

);

Insert Into #data Values('1','2','8');

Insert Into #data Values('2','7','10');

Insert Into #data Values('3','11','15');

Insert Into #data Values('4','13','17');

Select

Down.*

from

#data Up

Join #data down On Up.Id = Down.Id-1

Where

Up.V2>=down.V1

|||

As long as you have that ID field, you can use the query

Code Snippet

CREATE TABLE prevRowTest (

ID INT,

v1 INT,

v2 INT

)

INSERT INTO prevRowTest VALUES (1,2,8)

INSERT INTO prevRowTest VALUES (2,7,10)

INSERT INTO prevRowTest VALUES (3,11,15)

INSERT INTO prevRowTest VALUES (4,13,17)

SELECT prt.ID, prt.v1, prt.v2

FROM prevRowTest prt JOIN

prevRowTest prev ON prt.id = (prev.ID + 1)

WHERE prt.v1 <= prev.v2

|||

Thanks a lot.

I got my Answer

With Regards

Vijay

How to compare value pair of one table with value pair of another table

Here is the problem:
--------
create table A( a1 int, a2 int)
create table B( b1 int, b2 int)

insert A values(1,1)
insert A values(1,2)
insert A values(2,1)
insert A values(2,3)
insert A values(3,1)
insert A values(3,3)

insert B values(1,1)
insert B values(1,2)
insert B values(3,2)
insert B values(2,2)

What is the SQL query to find out the pairs existing in B, but not in A ?

I can solve this by using string functions, but my problem is I have to write a query that can execute on MSSQL, DB2 and ORACLE, and as you know they have differnt syntax for string functions.

Thanks in advance.

Regards,
s99shah.pairs in B but not in A? left outer join, check for unmatched rows --
select b1, b2
from B
left outer
join A
on b1 = a1
and b2 = a2
where a1 is null

rudy
http://r937.com/|||Hi,

TRY THIS

Select B.* from a,b
where (+) a1 = b1;

How to compare dynamic variable in proc

I have a table with 52 columns named 'Week1', 'Week2' etc. with values 1, 0 etc. I want to check values in each column. I have following lines in my procedure.

Declare @.l_str varchar(50),
@.l_count int

Select @.l_count = 1
Select @.l_str = 'Week' + Convert(varchar, @.l_count)
Now how do I compare the value stored in the @.l_str which should be wither 0 or 1 and not 'Week1'?

Is there any better method to compare read these 52 table variables?

Thanks in advanceCompare with what?|||Compare to check what does Week1 holds 1 or 0?|||I have a table with 52 columns named 'Week1', 'Week2' etc. with values 1, 0 etc.

It may be the liquid lunch, but

BBBBBBBBBWWWWWWWWWWWWWWWWWAAAAAAAAAAAAHHHAAHAHAHA

How to compare Binary and Int colns

I need to compare and update a Binary(8) coln with values from an
Int(4) coln. Could someone please explain how to do this..??

thanks
Sunitsjoshi (sjoshi@.ingr.com) writes:
> I need to compare and update a Binary(8) coln with values from an
> Int(4) coln. Could someone please explain how to do this..??

No, because I don't know how you want to compare four bytes with eight.

The standard recommendation is to include:

o CREATE TABLE statemens for your table(s)
o INSERT statements with sample data.
o The desired result given the sample data.

That permits people who tackle your problem to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE TABLE [dbo].[COREBaseClass] (
[oid] [uniqueidentifier] NOT NULL ,
[ConditionID] [int] NOT NULL ,
[persistentFlag] [int] NOT NULL ,
[UIDCreator] [uniqueidentifier] NOT NULL ,
[UIDLastModifier] [uniqueidentifier] NOT NULL ,
[DateCreated] [smalldatetime] NOT NULL ,
[DateLastModified] [smalldatetime] NOT NULL ,
[ApprovalStatus] [int] NOT NULL ,
[ApprovalReason] [int] NOT NULL ,
[tsRelations] [int] NOT NULL ,
[ts] [int] NOT NULL
)
CREATE TABLE [dbo].[COREProxy] (
[oid] [uniqueidentifier] NOT NULL ,
[dwProperties] [int] NOT NULL ,
[sourceTimeStamp] [binary] (8) NOT NULL ,
[COREPersistentFlags] [int] NOT NULL ,
[ts] [int] NOT NULL ,
[tsRelations] [int] NOT NULL
)
These are the 2 tables and I need to update COREProxy.sourceTimeStamp
with the value of COREBaseClass.ts wherever they are not the same.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Sunit Joshi (sjoshi@.ingr.com) writes:
> CREATE TABLE [dbo].[COREBaseClass] (
> [oid] [uniqueidentifier] NOT NULL ,
> [ConditionID] [int] NOT NULL ,
> [persistentFlag] [int] NOT NULL ,
> [UIDCreator] [uniqueidentifier] NOT NULL ,
> [UIDLastModifier] [uniqueidentifier] NOT NULL ,
> [DateCreated] [smalldatetime] NOT NULL ,
> [DateLastModified] [smalldatetime] NOT NULL ,
> [ApprovalStatus] [int] NOT NULL ,
> [ApprovalReason] [int] NOT NULL ,
> [tsRelations] [int] NOT NULL ,
> [ts] [int] NOT NULL
> )
> CREATE TABLE [dbo].[COREProxy] (
> [oid] [uniqueidentifier] NOT NULL ,
> [dwProperties] [int] NOT NULL ,
> [sourceTimeStamp] [binary] (8) NOT NULL ,
> [COREPersistentFlags] [int] NOT NULL ,
> [ts] [int] NOT NULL ,
> [tsRelations] [int] NOT NULL
> )
> These are the 2 tables and I need to update COREProxy.sourceTimeStamp
> with the value of COREBaseClass.ts wherever they are not the same.

But how? If ts is 4711, what do you want in sourceTimeStamp?

0x126700000000000, 0x0000126700000000 or 0x000000000001267?

If the number is 47114711, do you want:

0xE9D702CE00000000, 0x02CEE9D700000000 or 0x0000000002CEE9D7?

In my previous post, I said:

> The standard recommendation is to include:
> o CREATE TABLE statemens for your table(s)
> o INSERT statements with sample data.
> o The desired result given the sample data.
> That permits people who tackle your problem to post a tested solution.

I did not say this only for fun. If you cannot give a good specification
to the problem you have, you will either get no solution at all or

You can say things like:

convert(binary(8), ts) or convert(int, sourceTimeStamp)

But whether this gives you the values you are looking for, I can't
say, because I don't know what you are looking for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I would need 0x126700000000000 as below:

thanks
Sunit
---------------
But how? If ts is 4711, what do you want in sourceTimeStamp?

0x126700000000000, 0x0000126700000000 or 0x000000000001267?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Sunit Joshi (sjoshi@.ingr.com) writes:
> I would need 0x126700000000000 as below:

And what about 47114711?

0xE9D702CE00000000, 0x02CEE9D700000000 or 0x0000000002CEE9D7?

And why do you refuse to post:

> The standard recommendation is to include:
> o INSERT statements with sample data.
> o The desired result given the sample data.

If you are not prepared to put any effort into your problem, why do
you expect someone else to do it?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to compare 2 values in different table and sources?

I got 1 xml source and 1 sql DB table..

Eg: Contact table in xml contains

Email

--

bbb@.yaho.com

aaa@.yahoo.com

ccc@.yahoo.com

then COntact table in SQL server contains

aaa@.yahoo.com

ccc@.yahoo.com

zzz@.yahoo.com

i want to compare the two table if got same value update the row..

how to compare it in SSIS

thx

Can you provide more details?

What would be the filed(s) to be compared and which the ones to be updated?

See if the 'Checking to see if a record exists and if so update else insert ' thread in the 1st page of this forum helps you.

|||

Contact1

--

email ID

abc@.yah.com

bbf@.ya.com

-

Contact2

-

email ID

abc@.yah.com

zzz@.yaho.com

i want to check whether email column in two table match or not?

if got matching case, update the ID column in table Contact 1 with ID in Contact2

(Expected result is abc@.yah.com matching only)

is it lookup component can compare two column from different table?

thx.

|||

toongyang wrote:

is it lookup component can compare two column from different table?

In certain way, yes.

The lookup transformation will allow you to join 2 tables based on a set of columns, in your case email field, then bring additional columns to the data pipeline from the lookup table. I recommend you to check Books on line.

Notice that lookup can only based in tables that comes from an OLE DB source. If this is a problem for you, you could have 2 source components and then use a merge join. Merge join requires the 2 data sets to be sorted.

how to combine fromdate and todate values in one field

Hi All,

I have to fetch FromDate and Todate values from the table like this.Suppose Fromdate value is 02-Feb-2007 and Todate Value is 04-Feb-2007,then my need is to get the date value like this.....Feb 2-4,2007or 2-4 Feb,2007.Can anybody know the syntax or code?.I am using sql Server and fromdate and todate values are stored in two different feilds in table.

Thanks and Regards

This seems to be a duplicate post? I posted an answer in the other post, here it is again. This assumes two columns: from_date and to_date. If it fixes your problem mark one or both as answered!

Selectconvert(varchar(2),DatePart(day, from_date)) +'-' +convert(varchar(2),DatePart(day, to_date))+' '+substring(convert(varchar(12), to_date, 106), 4, 8)from [yourtable]