Field grouping in Report Builder

Hi,

I build all my report with the report builder.

When I build a report that the first column is ID and I've several records with the same ID one after abnother the report is grouping the ID data (see 2 in the example below)

How can I cancel the grouping for report or for the entire environment?

example:

ID Date

1 1/1/1990

2 1/1/1990

1/1/2000

3 1/1/2005

Dear ,

Go to the data tab and remove the grouping.

from

sufian

|||

Hi,

I'm building the report in Report Builder not report designer.

There is no data tab in the report builder.

Anyone?

|||

You need to make sure you have an entity group, not a value group (i.e. grouping on a field). Check the label on the gray "tab" above the fields in the Report Builder design area to see what you are grouping by. If dragging in MyEntity.ID didn't give you an entity group on MyEntity, then you probably need to set MyEntity.DiscourageGrouping=True in your report model. Regardless, you can be sure to get an entity group if you actually drag the entity onto your report instead of a field.

Once you have an entity group, make sure you add any other fields to the same group. Usually they will go in by default, but if you specifically drop them on the far left edge, you will get a new group on that field.

Hope this helps!

field added to replicated tables?

Hi all,
i found a column "msrepl_synctran_ts" have been added to one of my replicated tables but not found in other replicated tables. I
guess this field may be added by the DB server, i would like to ask under what situation or configuration this field will be added
and the field is timestamp type (the replication is trasactional replication). thanks
Hi Cweb,
Thanks for your post.
From your descriptions, I understood that you would like to know why there
will be a column named "msrepl_synctran_ts" in one of your replication
tables. Have I understood you? Correct me if I was wrong.
Based on my knowledge, the column msrepl_synctran_ts is added to SQL Server
tables if you publish the table and enable it for 'Immediate updating
subscriptions'. If it was allowed, a Subscriber can modify subscription
data and have those changes replicated to the Publisher.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing ,
it is me again, the strange thing is that i have not enable the "Immediate updating subscriptions" option but the column have
been added. Can i remove it? or need to remove the subscription to truncate the coulumn and push subscription again? thanks
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:%23doEXJTrEHA.1344@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your post.
> From your descriptions, I understood that you would like to know why there
> will be a column named "msrepl_synctran_ts" in one of your replication
> tables. Have I understood you? Correct me if I was wrong.
> Based on my knowledge, the column msrepl_synctran_ts is added to SQL Server
> tables if you publish the table and enable it for 'Immediate updating
> subscriptions'. If it was allowed, a Subscriber can modify subscription
> data and have those changes replicated to the Publisher.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Cweb,
Nice to see you again and thanks for your prompt updates!
Based on my scope, if you do not want your tables to be replicated, you
could definitely delete this column, otherwise, it is not recommend to do
this. msrepl_synctan_ts is the timestamp column that replication uses to
determine which is the latest version of a given row accross the servers
participating in the replication. My suggest is let SQL Server handle the
column. If you have big concerns with this additional column, please send
me your replication script and I would love to see whether there is any
workaround for us to omit this.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
here is the script of table and publication
CREATE TABLE [dbo].[MisTransactionReply] (
[TransID] [int] IDENTITY (1, 1) NOT NULL ,
[RequestTransID] [int] NOT NULL ,
[CompanyID] [varchar] (15) NOT NULL ,
[City] [varchar] (3) NULL ,
[UserID] [varchar] (15) NOT NULL ,
[SenderPIMA] [varchar] (35) NULL ,
[RecipientPIMA] [varchar] (35) NULL ,
[CAR] [varchar] (35) NOT NULL ,
[MsgType] [varchar] (6) NOT NULL ,
[MsgID] [int] NOT NULL ,
[AWBPrefix] [varchar] (3) NULL ,
[AWBSuffix] [varchar] (8) NULL ,
[Origin] [varchar] (3) NULL ,
[Destination] [varchar] (3) NULL ,
[Remark] [varchar] (100) NULL ,
[CreateDT] [datetime] NOT NULL ,
[MsgDateTime] [varchar] (10) NULL ,
[Unread] [bit] NOT NULL ,
[Deleted] [bit] NOT NULL ,
[msrepl_synctran_ts] [timestamp] NOT NULL --original schema not have this column
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MisTransactionReply] ADD
CONSTRAINT [DF_MisTransactionReply_RequestTransID] DEFAULT (0) FOR [RequestTransID],
CONSTRAINT [DF_MisTransactionReply_CreateDT] DEFAULT (dateadd(hour,(-8),getdate())) FOR [CreateDT],
CONSTRAINT [DF_MisTransactionDetail_Unread] DEFAULT (1) FOR [Unread],
CONSTRAINT [DF_MisTransactionReply_Deleted] DEFAULT (0) FOR [Deleted],
CONSTRAINT [PK_MisTransactionReply] PRIMARY KEY NONCLUSTERED
(
[TransID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply] ON [dbo].[MisTransactionReply]([CompanyID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_1] ON [dbo].[MisTransactionReply]([MsgType]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_10] ON [dbo].[MisTransactionReply]([SenderPIMA], [CompanyID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_2] ON [dbo].[MisTransactionReply]([MsgID]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_3] ON [dbo].[MisTransactionReply]([Origin]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_4] ON [dbo].[MisTransactionReply]([Destination]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_5] ON [dbo].[MisTransactionReply]([AWBPrefix]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_6] ON [dbo].[MisTransactionReply]([AWBSuffix]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_7] ON [dbo].[MisTransactionReply]([CreateDT]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_8] ON [dbo].[MisTransactionReply]([SenderPIMA]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_9] ON [dbo].[MisTransactionReply]([RecipientPIMA]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_11] ON [dbo].[MisTransactionReply]([SenderPIMA], [CreateDT], [MsgType]) ON [PRIMARY]
GO
CREATE INDEX [IX_MisTransactionReply_12] ON [dbo].[MisTransactionReply]([RequestTransID]) ON [PRIMARY]
GO
--Publication.sql--
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'ACE_GLSP2', @.optname = N'publish', @.value = N'true'
GO
use [ACE_GLSP2]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.restricted = N'false', @.sync_method = N'native',
@.repl_freq = N'continuous', @.description = N'Transactional publication of ACE_GLSP2 database from Publisher DB02.TRAXON.NET.',
@.status = N'active', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.retention =
0
exec sp_addpublication_snapshot @.publication = N'ACE_GLSP2_UserData_Trans_Reply',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1,
@.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'TXNEDS\SQLAdmin'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.login = N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.source_owner = N'dbo',
@.source_object = N'MisTransactionReply', @.destination_table = N'MisTransactionReply', @.type = N'logbased', @.creation_script = null,
@.description = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x0000000000000063, @.status = 16, @.vertical_partition = N'false',
@.ins_cmd = N'CALL sp_MSins_MisTransactionReply', @.del_cmd = N'CALL sp_MSdel_MisTransactionReply', @.upd_cmd = N'MCALL
sp_MSupd_MisTransactionReply', @.filter = null, @.sync_object = null, @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the article filter
EXEC sp_articlefilter @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.filter_name =
N'dbo.FLTR_MisTransactionReply_1__36', @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the article synchronization object
EXEC sp_articleview @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'MisTransactionReply', @.view_name =
N'SYNC_ACE_GLSP2_UserData_Trans_Reply_MisTransacti onReply', @.filter_clause = N'datediff(day, CreateDT, getdate()) <= 90'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'ACE_GLSP2_UserData_Trans_Reply', @.article = N'all', @.subscriber = N'192.168.0.1',
@.destination_db = N'ACE_GLSP2', @.sync_type = N'automatic', @.update_mode = N'read only'
GO
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:tK1eXo2rEHA.2500@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Nice to see you again and thanks for your prompt updates!
> Based on my scope, if you do not want your tables to be replicated, you
> could definitely delete this column, otherwise, it is not recommend to do
> this. msrepl_synctan_ts is the timestamp column that replication uses to
> determine which is the latest version of a given row accross the servers
> participating in the replication. My suggest is let SQL Server handle the
> column. If you have big concerns with this additional column, please send
> me your replication script and I would love to see whether there is any
> workaround for us to omit this.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Cweb,
Thanks for your detailed script providing such detailed information.
I setup a transactional replication based on the scripts you provided and
it seems not adding that additional column "msrepl_synctran_ts" on Table
[MisTransactionReply]. You could pick up a test machine and have a try on
this.
Based on my scope, Published table will cause upgrade to fail so that
column msrepl_synctran_ts gets added to SQL Server tables, if you publish
the table and enable it for 'Immediate updating subscriptions'. This column
will not be removed even after unpublishing the table OR uninstalling
replication. During an SMS upgrade we add values to the table and fail
because of this new column that is created.
You could run the following script to update all the sysobjects.replinfo
column to 0
/***********************************/
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
sp_configure 'allow updates', 0
go
reconfigure with override
go
/***********************************/
Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
from the table.
If the you have multiple tables that need the timestamp column removed, the
following script will drop the timmestamp column for every table that has
one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
removal.
/*************************************/
DECLARE repl_remove CURSOR
FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
= 'u'
and a.id = b.id
and b.name = 'msrepl_synctran_ts'
OPEN repl_remove
DECLARE @.TableName sysname
FETCH NEXT FROM repl_remove into @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
FETCH NEXT FROM repl_remove into @.TableName
END
CLOSE repl_remove
DEALLOCATE repl_remove
GO
/***********************************************/
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||thx
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:3GGmGNDsEHA.4016@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your detailed script providing such detailed information.
> I setup a transactional replication based on the scripts you provided and
> it seems not adding that additional column "msrepl_synctran_ts" on Table
> [MisTransactionReply]. You could pick up a test machine and have a try on
> this.
> Based on my scope, Published table will cause upgrade to fail so that
> column msrepl_synctran_ts gets added to SQL Server tables, if you publish
> the table and enable it for 'Immediate updating subscriptions'. This column
> will not be removed even after unpublishing the table OR uninstalling
> replication. During an SMS upgrade we add values to the table and fail
> because of this new column that is created.
> You could run the following script to update all the sysobjects.replinfo
> column to 0
> /***********************************/
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> /***********************************/
> Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
> from the table.
> If the you have multiple tables that need the timestamp column removed, the
> following script will drop the timmestamp column for every table that has
> one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
> removal.
> /*************************************/
> DECLARE repl_remove CURSOR
> FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
> = 'u'
> and a.id = b.id
> and b.name = 'msrepl_synctran_ts'
> OPEN repl_remove
> DECLARE @.TableName sysname
> FETCH NEXT FROM repl_remove into @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
> FETCH NEXT FROM repl_remove into @.TableName
> END
> CLOSE repl_remove
> DEALLOCATE repl_remove
> GO
> /***********************************************/
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Mingqing,
i got this error when trying to delete the column, should i stop the publishing first and delete the column and re-publish
again?
'MisTransactionReply' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter the table 'MisTransactionReply' because it is being
published for replication.
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:3GGmGNDsEHA.4016@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your detailed script providing such detailed information.
> I setup a transactional replication based on the scripts you provided and
> it seems not adding that additional column "msrepl_synctran_ts" on Table
> [MisTransactionReply]. You could pick up a test machine and have a try on
> this.
> Based on my scope, Published table will cause upgrade to fail so that
> column msrepl_synctran_ts gets added to SQL Server tables, if you publish
> the table and enable it for 'Immediate updating subscriptions'. This column
> will not be removed even after unpublishing the table OR uninstalling
> replication. During an SMS upgrade we add values to the table and fail
> because of this new column that is created.
> You could run the following script to update all the sysobjects.replinfo
> column to 0
> /***********************************/
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE sysobjects SET replinfo = 0 WHERE replinfo = 1
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> /***********************************/
> Now, the timestamp (msrepl_synctran_ts column for example) can be deleted
> from the table.
> If the you have multiple tables that need the timestamp column removed, the
> following script will drop the timmestamp column for every table that has
> one. Simply replace <<msrepl_synctran_ts>> with the column name that needs
> removal.
> /*************************************/
> DECLARE repl_remove CURSOR
> FOR select a.name as TableName from sysobjects a, syscolumns b where a.type
> = 'u'
> and a.id = b.id
> and b.name = 'msrepl_synctran_ts'
> OPEN repl_remove
> DECLARE @.TableName sysname
> FETCH NEXT FROM repl_remove into @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE ' + @.TableName + ' DROP COLUMN msrepl_synctran_ts')
> FETCH NEXT FROM repl_remove into @.TableName
> END
> CLOSE repl_remove
> DEALLOCATE repl_remove
> GO
> /***********************************************/
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi CWeb,
Thanks for your prompt updates!
Yes, you will have to stop the replication if you want to delete this
column.
Note that make sure you have a full backup of the database if it has
critical data for you.
Tell me the result of your test and let me know whether you have any
questions or concerns, don't hesitate to let me know. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft
> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

field <long text>

I have a table with the column type [ntext].
I store in this column the contains of an xml file.
When I run the select for this table on "sqlserver enterprise manager", the
value of this column is <long text>.
how can I see the exact contains of this column ?
I have to use another tools ?
thanks
ft> I have a table with the column type [ntext].
> I store in this column the contains of an xml file.
> When I run the select for this table on "sqlserver enterprise manager",
the
> value of this column is <long text>.
> how can I see the exact contains of this column ?
Use Query Analyzer. Enterprise Manager is primarily for system management,
not data viewing/manipulation.
http://www.aspfaq.com/2455

Fetching sungle record from join

Hello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul JhaHello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul Jha

Are they 3 identical records, or is there something different about them ? If they are identical you can cheese it with a distinct or a group by.|||which one do you want?|||I've never heard of a sungle record|||Rudy asks the correct question here - which of the 3 corresponding records do you want to return? And the answer "it doesn't matter/any of them" doesn't cut it ;)|||And the answer "it doesn't matter/any of them" doesn't cut it ;)Why? He could simply using MAX() or MIN() to get only one record|||MAX or MIN will of course return only one value out of the joined row

what about "the row with the max value"|||Just checking in, pulling up a chair, putting my feet up on the ottoman, leaning back, opening a beer, putting my 3-D glasses on, and waiting for the show...|||BTW, Brett, a "sungle row" is simply a Single row from amongst a Jungle of rows.|||Or he's from New Zealand|||I just might take a stab at this one.

It sounds like rows from t2 are different in some way. If you had data in t2 having to do with say a person and all of the phone numbers they could possibly have, you would get a different row for every phone number.

This is of course, if I am understanding the question correctly.

Fetch last record in a table

HI ALL,
Suppose i did not use identity for generating sequence in a
table.Also there is no sequence no column or no primary key on a
column. Then how to find last record in a table. Suppose there are 10
rows. How can i fetch 10th rows record.
Define your SQL SELECT statement , and THEN use ORDER BY myCol DESC -
select TOP 1. That's assuming there are 10 records.
If there are more than 10 records , and you are using SQL 2005 , you could
do something like:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY Col2 DESC)AS RowFROm
myTableWHERE Row = 10
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
|||On Jan 16, 2:50Xpm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched, last
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
>
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.
|||MC
SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
> First you need to define 10th. What does it mean exactly? Last fetched,
> last by some value, last..... Without primary key you basically dont have
> a consistent approach, so some kind of definition is definitely needed
> here.
>
> MC
>
> "mohit" <goenka.mohit@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
|||On Jan 16, 3:22Xpm, mohit <goenka.mo...@.gmail.com> wrote:
> HI ALL,
> X X X Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
In a set there is no such thing as last record.
|||On Jan 16, 3:22Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
>
>
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong
|||On Jan 16, 3:28Xpm, SB <othell...@.yahoo.com> wrote:
> On Jan 16, 3:22Xpm, mohit <goenka.mo...@.gmail.com> wrote:
>
> In a set there is no such thing as last record.
Can you tell what set means. i am asking from table how to fetch last
record i.e last row from a table
|||On Jan 16, 4:45Xpm, mohit <goenka.mo...@.gmail.com> wrote:
> On Jan 16, 3:28Xpm, SB <othell...@.yahoo.com> wrote:
>
>
> Can you tell what set means. i am asking from table how to fetch last
> record i.e last row from a table
A table consists of a set of records. It is not a sequence so that you
have first or last record. A set is an unordered set of records.
|||> Sorry yaar but its not working. When we do order by col_name desc
> the rows gets shuffle due to which answer is coming wrong
How do you know it is not working if there is no data in the row to identify
the order of insertion?
An important relational database concept is that a table is an unordered set
of rows. Rows may be returned in an arbitrary sequence unless you specify
ORDER BY. If you want data returned in the sequence in which rows were
inserted, you'll need an incrementing column like inserted datetime or
identity for the ordering.
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:d4d2d6c5-c6e0-4612-9f57-13accf2ff8b1@.i7g2000prf.googlegroups.com...
On Jan 16, 3:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
>
>
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong
|||"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
As others have said, w/o a primary key, there's no such thing as a "last
record" defined.
And in fact, without an ORDER BY you can never guarantee the order you'll
return the data in.
SELECT * from FOO may in fact return a different order of results at
different times.
This could be due to what's in the memory cache at the time, if the engine
decides to parallize the query across different CPUs, etc.
Now, MOST LIKELY for 10 rows, a simple "select * from FOO" will return the
data in the order it was inserted but that's absolutely no guarantee this is
true.
You may want to google the definition of a "SET" or "TABLE" within SQL.
They have no inherent order.
So sorry to say, you probably can't get the answer to the question you seek
(at least not the way it's posed.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
sql

Fetch last record in a table

HI ALL,
Suppose i did not use identity for generating sequence in a
table.Also there is no sequence no column or no primary key on a
column. Then how to find last record in a table. Suppose there are 10
rows. How can i fetch 10th rows record.First you need to define 10th. What does it mean exactly? Last fetched, last
by some value, last..... Without primary key you basically dont have a
consistent approach, so some kind of definition is definitely needed here.
MC
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.|||Define your SQL SELECT statement , and THEN use ORDER BY myCol DESC -
select TOP 1. That's assuming there are 10 records.
If there are more than 10 records , and you are using SQL 2005 , you could
do something like:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY Col2 DESC)AS RowFROm
myTableWHERE Row = 10
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.|||On Jan 16, 2:50=A0pm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched, la=st
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.=
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
> > HI ALL,
> > =A0 =A0 =A0Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.|||MC
SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
> First you need to define 10th. What does it mean exactly? Last fetched,
> last by some value, last..... Without primary key you basically dont have
> a consistent approach, so some kind of definition is definitely needed
> here.
>
> MC
>
> "mohit" <goenka.mohit@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>> HI ALL,
>> Suppose i did not use identity for generating sequence in a
>> table.Also there is no sequence no column or no primary key on a
>> column. Then how to find last record in a table. Suppose there are 10
>> rows. How can i fetch 10th rows record.
>|||On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> HI ALL,
> =A0 =A0 =A0 Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
In a set there is no such thing as last record.|||On Jan 16, 3:22=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
> > First you need to define 10th. What does it mean exactly? Last fetched,
> > last by some value, last..... Without primary key you basically dont ha=ve
> > a consistent approach, so some kind of definition is definitely needed
> > here.
> > MC
> > "mohit" <goenka.mo...@.gmail.com> wrote in message
> >news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> >> HI ALL,
> >> =A0 =A0 =A0Suppose i did not use identity for generating sequence in a
> >> table.Also there is no sequence no column or no primary key on a
> >> column. Then how to find last record in a table. Suppose there are 10
> >> rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong|||On Jan 16, 3:28=A0pm, SB <othell...@.yahoo.com> wrote:
> On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> > HI ALL,
> > =A0 =A0 =A0 Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.
> In a set there is no such thing as last record.
Can you tell what set means. i am asking from table how to fetch last
record i.e last row from a table|||On Jan 16, 4:45=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> On Jan 16, 3:28=A0pm, SB <othell...@.yahoo.com> wrote:
> > On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> > > HI ALL,
> > > =A0 =A0 =A0 Suppose i did not use identity for generating sequence in =a
> > > table.Also there is no sequence no column or no primary key on a
> > > column. Then how to find last record in a table. Suppose there are 10
> > > rows. How can i fetch 10th rows record.
> > In a set there is no such thing as last record.
> Can you tell what set means. i am asking from table how to fetch last
> record i.e last row from a table
A table consists of a set of records. It is not a sequence so that you
have first or last record. A set is an unordered set of records.|||If the 'last' you mean last added then you need a column which will define
sequence of insertions. Something like 'DateAdded' or something like that.
Otherwise, you need to specify how do YOU know which record is 'last' when
you open sample data from table.
MC
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:a53c73e5-4685-4d12-8f94-5e8a1b94b5b8@.f10g2000hsf.googlegroups.com...
On Jan 16, 2:50 pm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched,
> last
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
> > HI ALL,
> > Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.|||> Sorry yaar but its not working. When we do order by col_name desc
> the rows gets shuffle due to which answer is coming wrong
How do you know it is not working if there is no data in the row to identify
the order of insertion?
An important relational database concept is that a table is an unordered set
of rows. Rows may be returned in an arbitrary sequence unless you specify
ORDER BY. If you want data returned in the sequence in which rows were
inserted, you'll need an incrementing column like inserted datetime or
identity for the ordering.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:d4d2d6c5-c6e0-4612-9f57-13accf2ff8b1@.i7g2000prf.googlegroups.com...
On Jan 16, 3:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
> > First you need to define 10th. What does it mean exactly? Last fetched,
> > last by some value, last..... Without primary key you basically dont
> > have
> > a consistent approach, so some kind of definition is definitely needed
> > here.
> > MC
> > "mohit" <goenka.mo...@.gmail.com> wrote in message
> >news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> >> HI ALL,
> >> Suppose i did not use identity for generating sequence in a
> >> table.Also there is no sequence no column or no primary key on a
> >> column. Then how to find last record in a table. Suppose there are 10
> >> rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong|||"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
As others have said, w/o a primary key, there's no such thing as a "last
record" defined.
And in fact, without an ORDER BY you can never guarantee the order you'll
return the data in.
SELECT * from FOO may in fact return a different order of results at
different times.
This could be due to what's in the memory cache at the time, if the engine
decides to parallize the query across different CPUs, etc.
Now, MOST LIKELY for 10 rows, a simple "select * from FOO" will return the
data in the order it was inserted but that's absolutely no guarantee this is
true.
You may want to google the definition of a "SET" or "TABLE" within SQL.
They have no inherent order.
So sorry to say, you probably can't get the answer to the question you seek
(at least not the way it's posed.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>>
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.
If you don't have a column that can tell you what "last" means, then you
could return ANY row and how would you know it wasn't the "last" row?
In SQL Server, a table is an unordered set of rows by definition. If you
say SELECT TOP 1 columns FROM table or SELECT TOP 10 columns FROM table and
you don't include an ORDER BY clause, the optimizer is free to return
whichever rows it wants... and it can change its mind when you run the query
again 5 minutes later, giving you a different set. So, if you want to
identify your "last" row, you will need to have a column that you can use to
tell SQL Server "use this column to determine last."
A

Fetch cursor help

Let's say i have 5 unique RRID's, column APID and ITID

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 700
5 15 700

If I run the stored procedure below, I get the results above however, I want my result to be

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 701
5 15 702

I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...

Any help is highly appreciated...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput
AS
Declare @.RRID int
Declare @.APID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null
open crReqRec
fetch next from crReqRec
into
@.RRID
set @.APID = (select APID from RequestRecords where REID = @.REID and RRID = @.RRID)
set @.intIMID = (SELECT ImplementationGroup.IMID
FROM ImplementationGroup_Location INNER JOIN
 ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
 Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID and ImplementationGroup_Location.LOID = @.LOID )
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.Identity
while @.@.fetch_status = 0
Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This is the newer version but still getting the same results. PLEASE HELP...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput

AS

Declare @.RRID int
Declare @.APID int
Declare @.APID2 int
Declare @.FS1 int
Declare @.FS2 int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)

Declare crReqRec cursor local for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null

open crReqRec
fetch next from crReqRec into @.RRID
set @.FS1 = @.@.fetch_status

Declare crAPID cursor local for
select APID from RequestRecords where REID = @.REID and RRID = @.RRID
open crAPID
fetch next from crAPID into @.APID2
set @.FS2 = @.@.fetch_status
set @.APID2 = (select APID from RequestRecords where APID = @.APID)
set @.intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID2 and ImplementationGroup_Location.LOID = @.LOID )

insert into ImplementationTasks
(IMID, ITStatus,ITStatusDate) VALUES (@.intIMID,'2',GetDate())
SET @.RetVal = @.@.Identity

while @.FS2 = 0
while @.FS1 = 0

Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID

fetch next from crAPID into @.APID2
FETCH NEXT FROM crReqRec into @.RRID
end

close crReqRec
deallocate crReqRec
close crAPID
deallocate crAPID

--EXEC TrigRetReqRecIDP2 @.REID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Fetch a certain row

Hi.
I have a dataset with a two column table:
name | value
What I'm trying to do is to make a textbox in the report designer to fetch a
certain field (value) from the table based on the name field.
How to fetch that certain cell from the dataset.
Any help appreciated.
AlekWhere do you select the name for which you want the value
You could do this with a parameter which displays the name and returns the
value. Then in the Text box
=Parameters!myparam.value
You could maybe filter the where clause in the sql or in the data set..
You could write a funcion which accepts some value and returns the name of
the field in the data set... Then use
=Fields(Code.GetFieldname("mypassedinvalue")).Value in the textbox
Hope this helps... If I do not understand your question, please send more
details
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alek Lynge" <alekl@.penguin.dk> wrote in message
news:OUSV$MI2FHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi.
> I have a dataset with a two column table:
> name | value
> What I'm trying to do is to make a textbox in the report designer to fetch
> a certain field (value) from the table based on the name field.
> How to fetch that certain cell from the dataset.
> Any help appreciated.
> Alek
>|||Hi, Wayne.
Thanx for your quick answer. However I'm still a bit lost in the VBScript
world, as I'm usually coding in c#.
It is easiest to explain with a sample data example:
I have a dataset with multiple tables. The one I need is called
InvoiceTemplate and has its tableadapter implemented to fetch data from the
sql server backend with a stored proc, which accepts a single parameter: an
integer determining the country.
When the dataset is filled I get the following data in my InvoiceTemplate
table:
Columns: name, value
Sample data (ntext, ntext):
'iName', 'Lene Espersen'
'iNumber', 'Faktura nr.'
'iUnits', 'Antal'
'iPrice', 'Pris'
Etc.
So I've got a textbox in the report designer which I need to fill with the
value from the row containing 'iUnits' (get the data 'Antal')?
How to make an elegant expression solution to this? Essentially I'm trying
to escape from table pivoting, as sql server 2000 has poor support for that.
Best regards
Alek
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:O7y8AmI2FHA.2616@.tk2msftngp13.phx.gbl...
> Where do you select the name for which you want the value
> You could do this with a parameter which displays the name and returns
> the value. Then in the Text box
> =Parameters!myparam.value
> You could maybe filter the where clause in the sql or in the data set..
> You could write a funcion which accepts some value and returns the name of
> the field in the data set... Then use
> =Fields(Code.GetFieldname("mypassedinvalue")).Value in the textbox
> Hope this helps... If I do not understand your question, please send more
> details
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Alek Lynge" <alekl@.penguin.dk> wrote in message
> news:OUSV$MI2FHA.3712@.TK2MSFTNGP09.phx.gbl...
>> Hi.
>> I have a dataset with a two column table:
>> name | value
>> What I'm trying to do is to make a textbox in the report designer to
>> fetch a certain field (value) from the table based on the name field.
>> How to fetch that certain cell from the dataset.
>> Any help appreciated.
>> Alek
>

Fast way to find similar posts

Hello!

I have several posts and one column looks like this:

"0001.11010.101101.00111000.000000000000.011"

I want to find all posts where there are zero or at least one matching 1 in the group.
Each group i separated by .

If we find similar posts to the one above we can say that it will find those for example:

0001 - true since 1 is on the right spot
01000 - true since at least one 1 is on the right spot
000100 - true since at least one 1 is on the right spot
00001000 - true since at least one 1 is on the right spot
000010000000 - true since the whole group is 0 in the string above.
001 - true since at least one 1 is on the right spot

Right now I am creating my query dynamic and use substring() to find the right posts. for the string above my query right now would look like this:

select
t1.rum_profil_id
from
rum_profil t1
where
(substring(t1.nmask,4,1)='1')
and
(substring(t1.nmask,6,1)='1' or substring(t1.nmask,7,1)='1' or substring(t1.nmask,9,1)='1')
and
(substring(t1.nmask,12,1)='1' or substring(t1.nmask,14,1)='1' or substring(t1.nmask,15,1)='1' or substring(t1.nmask,17,1)='1')
and
(substring(t1.nmask,21,1)='1' or substring(t1.nmask,22,1)='1' or substring(t1.nmask,23,1)='1')
and
(substring(t1.nmask,42,1)='1' or substring(t1.nmask,43,1)='1')

Does anyone know of a better way to do this?about the only alternative I could think of would be to store each part of the mask on it's own attribute as an int. you could then use or "|" to test for a pattern inclusion.

you could also use a function to test for a pattern...

drop table #Tmp
CREATE FUNCTION CompareCharPattern (
@.TestPattern varchar(255)
, @.PatternToTest varchar(255))
RETURNS int
AS
BEGIN
declare @.len int, @.Ans int, @.pos int
select @.Ans = 0
, @.len = len(@.TestPattern)
, @.pos = 1
while @.pos <= @.len begin
if substring(@.TestPattern,@.pos,1) = substring(@.PatternToTest,@.pos,1)
set @.Ans = 1
set @.pos = @.pos + 1
end
return @.Ans
END
GO

create table #tmp(RowID int identity,nmask varchar(50))
insert into #tmp (nmask) values('0001.00001.000001.00000001.000000000000.01 1')
insert into #tmp (nmask) values('0001.00100.000101.00000010.000000010000.01 0')
insert into #tmp (nmask) values('0001.00010.001000.00000100.000000001000.10 0')
insert into #tmp (nmask) values('0001.11010.101101.00111000.000000000000.01 1')
go

declare @.TestValue varchar(50)
set @.TestValue = '0001.11010.101101.00111000.000000000000.011'
select *
From #Tmp t1
where dbo.CompareCharPattern(substring(t1.nmask, 1, 4),substring(@.TestValue, 1, 4)) = 1
and dbo.CompareCharPattern(substring(t1.nmask, 6, 5),substring(@.TestValue, 6, 5)) = 1
and dbo.CompareCharPattern(substring(t1.nmask,12, 6),substring(@.TestValue,12, 6)) = 1
and dbo.CompareCharPattern(substring(t1.nmask,21,12),s ubstring(@.TestValue,21,12)) = 1
and dbo.CompareCharPattern(substring(t1.nmask,42, 3),substring(@.TestValue,42, 3)) = 1
go|||How could I use the bitwise OR?

Lets say I make columns of each group. Then the first column would look like this:

0001 = 1

But the first column could also look like this.

0101 = 5

To compare there can be either

0101
0001
0100

anyone would be accepted.

but

0011 = 3 shold not be accepted.|||I don't follow your logic.

In your first post you stated that a matching 1 in any position would = a match. In addition you stated that if the matched pattern were all 0s that would also be a match, I neglected to have this test in my posted function but it would be easy to add.|||Not really in any position.

It has to be on the same spot. Let me show you.

If out pattern is: "0101"

then it will find those:

0100 - Matches at least one 1 on the correct positions
0001 - Matches at least one 1 on the correct positions
0101 - Matches two 1 on the correct positions
1111 - Matches two 1 on the correct, but fails two.

These one will fail:

1000 - Doesnt match at all
1010 - Doesnt match at all
0000 - Doesnt match at all

Following now?|||Yup clear.

CREATE FUNCTION CompareCharPattern (
@.TestPattern varchar(255)
, @.PatternToTest varchar(255))
RETURNS int
AS
BEGIN
declare @.len int, @.Ans int, @.pos int
select @.Ans = 1
, @.len = len(@.TestPattern)
, @.pos = 1
while @.pos <= @.len begin
if substring(@.TestPattern,@.pos,1) = 0 and substring(@.PatternToTest,@.pos,1) = 1
select @.Ans = 0
, @.pos = @.len

set @.pos = @.pos + 1
end
return @.Ans
END
GO

create table #tmp(RowID int identity,nmask varchar(50))
insert into #tmp (nmask) values('0001.00001.000001.00000001.000000000000.01 1')
insert into #tmp (nmask) values('0001.00100.000101.00000010.000000010000.01 0')
insert into #tmp (nmask) values('0001.00010.001000.00000100.000000001000.10 0')
insert into #tmp (nmask) values('0001.11010.101101.00111000.000000000000.01 1')
insert into #tmp (nmask) values('1001.11010.101101.00111001.100000000001.11 1')
go

declare @.TestValue varchar(50)
-- 1111111111222222222233333333334444
-- 1234567890123456789012345678901234567890123
set @.TestValue = '0001.11010.101101.00111000.000000000000.011'
select *
From #Tmp t1
where dbo.CompareCharPattern(substring(t1.nmask, 1, 4),substring(@.TestValue, 1, 4)) = 1
and dbo.CompareCharPattern(substring(t1.nmask, 6, 5),substring(@.TestValue, 6, 5)) = 1
and dbo.CompareCharPattern(substring(t1.nmask,12, 6),substring(@.TestValue,12, 6)) = 1
and dbo.CompareCharPattern(substring(t1.nmask,19, 8),substring(@.TestValue,19, 8)) = 1
and dbo.CompareCharPattern(substring(t1.nmask,28,12),s ubstring(@.TestValue,28,12)) = 1
and dbo.CompareCharPattern(substring(t1.nmask,41, 3),substring(@.TestValue,41, 3)) = 1
go


I think this accounts for everything.|||You have rescued my day :D

I had to do some changes in the logic of CompareCharPattern, but your idea with using select and a function in the wherestatement is super!

Thanks alot!!

Fast copy of data from one table to another

I have to copy the contents of a table (6million rows) into another table on
the same instance. The target table has an identity column(PK). What is the
fastest way to do this? The instance has to remain up for the web site, and
yes, the target table is read by the web site. I was thinking a bcp out/in.
Thanks,
MichaelUse SET IDENTITY_INSERT ON/OFF to keep the identity column. There's
also a switch in the bcp utility to keep identities (-E)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp
Snake wrote:
> I have to copy the contents of a table (6million rows) into another table on
> the same instance. The target table has an identity column(PK). What is the
> fastest way to do this? The instance has to remain up for the web site, and
> yes, the target table is read by the web site. I was thinking a bcp out/in.
> Thanks,
> Michael

Fast copy of data from one table to another

I have to copy the contents of a table (6million rows) into another table on
the same instance. The target table has an identity column(PK). What is the
fastest way to do this? The instance has to remain up for the web site, and
yes, the target table is read by the web site. I was thinking a bcp out/in.
Thanks,
Michael
Use SET IDENTITY_INSERT ON/OFF to keep the identity column. There's
also a switch in the bcp utility to keep identities (-E)
http://msdn.microsoft.com/library/de...t-set_7zas.asp
http://msdn.microsoft.com/library/de...p_bcp_61et.asp
Snake wrote:
> I have to copy the contents of a table (6million rows) into another table on
> the same instance. The target table has an identity column(PK). What is the
> fastest way to do this? The instance has to remain up for the web site, and
> yes, the target table is read by the web site. I was thinking a bcp out/in.
> Thanks,
> Michael

Fast copy of data from one table to another

I have to copy the contents of a table (6million rows) into another table on
the same instance. The target table has an identity column(PK). What is th
e
fastest way to do this? The instance has to remain up for the web site, and
yes, the target table is read by the web site. I was thinking a bcp out/in.
Thanks,
MichaelUse SET IDENTITY_INSERT ON/OFF to keep the identity column. There's
also a switch in the bcp utility to keep identities (-E)
http://msdn.microsoft.com/library/d... />
t_7zas.asp
http://msdn.microsoft.com/library/d...
1et.asp
Snake wrote:
> I have to copy the contents of a table (6million rows) into another table
on
> the same instance. The target table has an identity column(PK). What is
the
> fastest way to do this? The instance has to remain up for the web site, a
nd
> yes, the target table is read by the web site. I was thinking a bcp out/in
.
> Thanks,
> Michael