Well,
I'm fairly new to SQL myself, but you could try using a cursor
within your stored procedure.
The way I'd try it in VB/VBA is to add a field to your table (this may
not be necessary with SQL as you can use #temp tables) this would be
used to flag your duplicate numbers. Then you'd take field one as a
recordset and cycle through the second field and flag your third field
when you found matches. After that you could filter on that third field.
I know people tend to look down on cursors in SQL but this looks like it
might be one of the times to use it. Unless someone more experienced has
some input?
Cheers
David Postlethwaite
The Bank of New York
London SecLend UK IT
Tel: (0)207 964 6855
Email: [Email Address Removed]
"Vallipuram,
Suganthan" To: "LazyDBA.com Discussion" <[Email Address Removed]
<[Email Address Removed] cc:
ix.com> Subject: RE: Need an urgent help in StoredProcedures
29/04/2004 10:44
Hi
Thanks, But my problem is nit that
Say as an example
Field1 field2
101 203
102 100
103 101
104 103
105 106
I want the numbers from field1 are 102,104,105 because 101 and 103 are
appeared in the field 2 please consider that if we write as you
mentioned
it will give all the values (from 101 to 105).
please suggest a solution for this problem
Thanks in advanced
With Best Regards,
Suganthan,V
Mobile : +94-77-7807639
> -----Original Message-----
> From: [Email Address Removed] Sent: Thursday, April 29, 2004 3:40 PM
> To: Vallipuram, Suganthan
> Subject: RE: Need an urgent help in StoredProcedures
>
> So as an example you have this data:
>
>
> Field1 Field2
> 001 256
> 002 458
> 003 003
> 004 357
>
>
> you want a view/query that would return:
>
> Field1 Field2
> 001 256
> 002 458
> 004 357
>
>
>
> As the below shows a number in field1 that is equal to field2
>
> Field1 Field2
> 003 003
>
>
> The query for this is simply (off the top of my head):
>
> SELECT dbo.Table1.*, field1 AS Expr1, field2 AS Expr2
> FROM dbo.Table1
> WHERE (field1 <> field2)
>
>
> Cheers
> David Postlethwaite
> The Bank of New York
> London SecLend UK IT
> Tel: (0)207 964 6855
> Email: [Email Address Removed] "Vallipuram,
>
> Suganthan" To: "LazyDBA.com
> Discussion" <[Email Address Removed] <[Email Address Removed] cc:
>
> ix.com> Subject: RE: Need an
> urgent help in StoredProcedures
>
>
> 29/04/2004 10:09
>
>
>
>
>
>
>
>
>
> Hi,
> The solution doesn't support me.
> Again I will say the problem.
>
> I have only one table and this table contains 2 fields and both fields
> are
> integer. The first one is a unique one (AutoNumber). The second field
> may
> contain any integer. Now I want the numbers from the
field1(AutoNumber)
> but
> the condition is these numbers should not be in field2 in the same
> table.
>
> Please suggest a solution which should run fast, because there a lot
of
> datas in this table.
>
> Thanks in advanced
>
>
> With Best Regards,
> Suganthan,V
> Mobile : +94-77-7807639
>
> > -----Original Message-----
> > From: O15_11_2002 [SMTP:[Email Address Removed] > Sent: Thursday, April 29, 2004 2:54 PM
> > To: Vallipuram, Suganthan
> > Subject: Re: Need an urgent help in StoredProcedures
> >
> > your welcome : )
> >
> > ----- Original Message -----
> > From: "Vallipuram, Suganthan" <[Email Address Removed] > To: "O15_11_2002" <[Email Address Removed] > Sent: Thursday, April 29, 2004 11:48 AM
> > Subject: RE: Need an urgent help in StoredProcedures
> >
> >
> > > HI,
> > > Thank you so much. I will try it now
> > >
> > > With Best Regards,
> > > Suganthan,V
> > >
> > >
> > > > -----Original Message-----
> > > > From: O15_11_2002 [SMTP:[Email Address Removed] > > > Sent: Thursday, April 29, 2004 2:15 PM
> > > > To: LazyDBA.com Discussion
> > > > Subject: Re: Need an urgent help in StoredProcedures
> > > >
> > > > yes, i think "not exists" better than "not in", may fastly
> > > > Suganthan, you should use "not exists"
> > > >
> > > > regards
> > > > ali
> > > >
> > > > ----- Original Message -----
> > > > From: "Bart Hayes" <[Email Address Removed] > > > To: "LazyDBA.com Discussion" <[Email Address Removed] > > > Sent: Thursday, April 29, 2004 11:04 AM
> > > > Subject: RE: Need an urgent help in StoredProcedures
> > > >
> > > >
> > > > > A self join should do it:
> > > > >
> > > > > select field1 from someTable
> > > > > where not exists(select * from someTable T2
> > > > > where field2 = someTable.field1)
> > > > >
> > > > > -----Original Message-----
> > > > > From: Vallipuram, Suganthan [mailto:[Email Address Removed] > > > > Sent: Thursday, 29 April 2004 3:42 PM
> > > > > To: LazyDBA.com Discussion
> > > > > Subject: Need an urgent help in StoredProcedures
> > > > >
> > > > >
> > > > > > Dear Friends,
> > > > > >
> > > > > > In MSSQL server-7, I want to write a stored procedure in
which
> I
> > would
> > > > > > like to search a field value which should not be in another
> field.
> > For
> > > > > > example
> > > > > >
> > > > > > Table1 and ite field are field1 and field 2 both are
integer,
> but
> > the
> > > > > > field1 is unique (AutoNumber).
> > > > > > I would like to get the values of field1, but these values
> should
> > not
> > > > be
> > > > > > in the field2.
> > > > > >
> > > > > > Please help me as soon as possible
> > > > > >
> > > > > >
> > > > > > Thanks in advanced
> > > > > >
> > > > > > With Best Regards,
> > > > > > Suganthan,V
> > > > > >
> > > > > >
> > > > >
> > > > >
> >
---------------------------------------------------------------------
> > > > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > > > > Get today's cartoon: http://www.LazyDBA.com
> > > > > To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
> > > > > then respond to the confirmation email you get
> > > > > For additional commands, e-mail: mssqldba-[Email Address Removed] > > > >
> > > >
> > > >
> ---------------------------------------------------------------------
> > > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > > > Get today's cartoon: http://www.LazyDBA.com
> > > > To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
> > > > then respond to the confirmation email you get
> > > > For additional commands, e-mail: mssqldba-[Email Address Removed] > >
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Get today's cartoon: http://www.LazyDBA.com
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
> then respond to the confirmation email you get
> For additional commands, e-mail: mssqldba-[Email Address Removed] The information in this e-mail, and any attachment therein, is
> confidential and for use by the addressee only. If you are not the
> intended recipient, please return the e-mail to the sender and delete
it
> from your computer. Although The Bank of New York attempts to sweep
e-mail
> and attachments for viruses, it does not guarantee that either are
> virus-free and accepts no liability for any damage sustained as a
result
> of viruses.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
then respond to the confirmation email you get
For additional commands, e-mail: mssqldba-[Email Address Removed] information in this e-mail, and any attachment therein, is confidential and for use by the addressee only. If you are not the intended recipient, please return the e-mail to the sender and delete it from your computer. Although The Bank of New York attempts to sweep e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses.
MS Sql Server LazyDBA home page