Failed searching records by using SQLCommand Parameter "LIKE %@keywords%"

I use SQL Server 2005 Developer. I failed to search the records by using SQLCommand Paramater. Please find the code below.

However, when I hardcode like LIKE '%sudoku%' , it works. Could aynone help?

Thanks,

Andy.

privatebool searchProducts(string keywords)

{

......

command.CommandText ="SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE '%@.keywords%' ";

command.Parameters.Add("@.keywords",SqlDbType.VarChar, 100);

command.Parameters["@.keywords"].Value = keywords;

..........

}

Your keyword parameter will be treated as literal text as it is within quotes and hence it will not be treated as parameter.

The following shoud work:

command.CommandText ="SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE @.keywords";

command.Parameters.Add("@.keywords",SqlDbType.VarChar, 100);

command.Parameters["@.keywords"].Value = "%" + keywords + "%";

|||

Many thanks. It works now.

Andy.

Failed Replication

I use SQL2000 and Transactional Replication from server A to B. It is
success to replicate the changes in "Add" and "Deleted" records. But, if I
change the record in server A and then find that fails to replicate. In "Log
Reader" in server A, I can see 1 log is delivered but no any changes in
target. It hasn't any error in log.
Then I tried to rename both tables in server A and B and build the
replication again. It's in normal operation. i.e. Add/Change/Delete are also
ok in replication. Once use back the original table name, it fails to do
"update" replication.
Please advise. Thanks.
Tommy,
can you do an update and check the MSrepl_commands table by using
sp_browsereplcmds. Also, have a look at the article properties (elipsis
button) and check the commands section. It might be that somehow NONE has
been set for the updates. Finally, check that there are no triggers set on
the table which are preventing updates.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Extremely slow median measures

Hi

I'm having a problem with extremely slow median measures.

I've created a named set of all record IDs and wrote the measure as Median( [All Records], [Measures].[Age] ). When I drop a dimension into one of the axes, it takes a very long time to calculate the median even at the top level of the hierarchy, and I suspect it's computing the median for all the members of that dimension, even before I've drilled down into them.

Anyone know a better method for this?

Your formula computes Median for all records always, regardless of the selection in Records dimension.|||

Sorry Mosha, I don't quite follow. I need this to be a generic measure that will return the median of any cell in the client browser. Since median cannot be preaggregated, I thought the only way to do this was to take the median of the set of all records in the current cell. The other option I had explored went something like this:

Median (

{ ( Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.All, [All Records].[ ID ].[ ID ] ) },

[Measures].[Age]

)

But this didn't work at all.

|||Sorry, this time I don't quite follow. What exactly do you mean by the following: "I need this to be a generic measure that will return the median of any cell in the client browser". Median of what ? Perha[s you could illustrate with couple of examples.|||

My mistake... Should have said median of a measure (e.g. age) within any cell in the spreadsheet. For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells. Other times, users would be interested in the median age for other combinations of factors, say Gender and Cancer, or Cancer and Socioeconomic Status, but they shouldn't have to select a different median measure for each combination. Sort of like a percentage/proportion against any dimension selected on the row/column axis, as discussed in the following post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720160&SiteID=1

|||

If you are guaranteed to always have two axes, then something like that may work

Median(CrossJoin(Axis(0), Axis(1)), Measures.[Age])

|||

Thanks Mosha, but no luck with that one either. There's probably no other way around this; that is, other than using the Fact Table primary key (Record ID) to select the set of all individual age values.

|||

Then I again don't understand your requirements :( Based on what you wrote before:

> For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells.

The formula that I wrote computes then median of these 4 cells and places it into each one of these 4 cells. I have verified it with AdventureWorks which has Gender and Marital Status attributes...

Extremely long processing time

I have a historical table that is over 100 million records in size.
Processing this table in AS takes an extremly long time (about 2 hours). Is
there any way to speed this up in AS?
is it 2000 or 2005?
As for the processing, are your cubes optimized (keys from dimensions read
from fact?). In another words, if you look at the select query does it have
any join statements?
MC
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>
|||do you have a distinct count measure in the cube?
(this cause the SQL statement to be sorted by this column)
have you created partitions?
have you optimized the schema in the cube (AS2000)? (the select statement
used use less inner join commands between your fact table and your
dimensions)
does your SQL Server is on another server or the same server?
how many aggregation have you in your cube?
what is your server? (CPU, Memory...)
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>
|||The general rule of thumb is that a common server-class machine with a
reasonable I/O subsystem will do about 1 million rows per minute. That
varies based on many issues, such as the number of aggregates, storage type.
Your experience is a bit low, but not unreasonable.
For hints on how to improve, look at the AS Performance Guide at:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>

Extremely long processing time

I have a historical table that is over 100 million records in size.
Processing this table in AS takes an extremly long time (about 2 hours). Is
there any way to speed this up in AS?is it 2000 or 2005?
As for the processing, are your cubes optimized (keys from dimensions read
from fact?). In another words, if you look at the select query does it have
any join statements?
MC
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>|||do you have a distinct count measure in the cube?
(this cause the SQL statement to be sorted by this column)
have you created partitions?
have you optimized the schema in the cube (AS2000)? (the select statement
used use less inner join commands between your fact table and your
dimensions)
does your SQL Server is on another server or the same server?
how many aggregation have you in your cube?
what is your server? (CPU, Memory...)
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>|||The general rule of thumb is that a common server-class machine with a
reasonable I/O subsystem will do about 1 million rows per minute. That
varies based on many issues, such as the number of aggregates, storage type.
Your experience is a bit low, but not unreasonable.
For hints on how to improve, look at the AS Performance Guide at:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||

If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:

select *

from (select *, row_number() over(order by CustomerId) as rownum

from customers

) as c

where c.rownum between 1000 and 10000;

If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).

Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

set rowcount @.n

while(1=1)

begin

insert into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

set rowcount 0

-- using TOP

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

while(1=1)

begin

insert top(@.n) into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...

for extract customers betweeen 10000 and 11000 based on customer_id
|||

if you use sql2005

you also can use the function ROW_NUMBER() to

select rownum between 1000 and 10000 like oracle

example:

with customers _temp as

(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||

If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:

select *

from (select *, row_number() over(order by CustomerId) as rownum

from customers

) as c

where c.rownum between 1000 and 10000;

If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).

Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

set rowcount @.n

while(1=1)

begin

insert into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

set rowcount 0

-- using TOP

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

while(1=1)

begin

insert top(@.n) into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...

for extract customers betweeen 10000 and 11000 based on customer_id|||

if you use sql2005

you also can use the function ROW_NUMBER() to

select rownum between 1000 and 10000 like oracle

example:

with customers _temp as

(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000

Extracting from linked tables into pivot

Hi there,
I've managed to come this far and I need a bit of help to finalise. I'm
extracting records correctly using the following:
select substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Jan,
sum(case month(D.MLineShipDate)
when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Feb,
sum(case month(D.MLineShipDate)
when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Mar,
sum(case month(D.MLineShipDate)
when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Apr,
sum(case month(D.MLineShipDate)
when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as May,
sum(case month(D.MLineShipDate)
when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
end) as Jun
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)
What I need to add is the quantity on hand from the InvWarehouse table. My
stock codes look something like this: 002-0300-10-WW-01 where the first 3
digits indicate the style (as in my code). In the InvWarehouse table there
are the same codes with a quantity on hand, and what I need is to sum the
total quantity on hand per style and add this field to the pivot (per style)
.
Thanking you in advance.
Kind regards,Hi
You are alredy joining to the InvWarehouse table therefore (if I understand
your problem) your summation of the quantity should be an extension of what
you have!
select substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) else 0
end) as Jan,
sum(case month(D.MLineShipDate)
when 1 then w.quantity else 0
end) as JanQuantity,
...
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)
If this is not the case, Posting DDL and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
the sample data would be beneficial.
John
"CyberFox" wrote:

> Hi there,
> I've managed to come this far and I need a bit of help to finalise. I'm
> extracting records correctly using the following:
> select substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Jan,
> sum(case month(D.MLineShipDate)
> when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Feb,
> sum(case month(D.MLineShipDate)
> when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Mar,
> sum(case month(D.MLineShipDate)
> when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Apr,
> sum(case month(D.MLineShipDate)
> when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as May,
> sum(case month(D.MLineShipDate)
> when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> end) as Jun
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
> What I need to add is the quantity on hand from the InvWarehouse table. My
> stock codes look something like this: 002-0300-10-WW-01 where the first 3
> digits indicate the style (as in my code). In the InvWarehouse table there
> are the same codes with a quantity on hand, and what I need is to sum the
> total quantity on hand per style and add this field to the pivot (per styl
e).
> Thanking you in advance.
> Kind regards,|||Hi John,
The summation of the quantities is not date-dependent. Let me explain
exactly what I want:
The InvWarehouse table has a quantity on hand per stock item (this is the
quantity in stock at the current time, and is not date-dependent at all).
What I want to show is the stock item (actually the style number, which is
the first 3 digits of the stock item), it's quantity on hand (the stock
item's), and the outstanding sales orders per style (date-dependent).
Hope this clarifies.
Rgds,
"John Bell" wrote:
> Hi
> You are alredy joining to the InvWarehouse table therefore (if I understan
d
> your problem) your summation of the quantity should be an extension of wha
t
> you have!
> select substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) el
se 0
> end) as Jan,
> sum(case month(D.MLineShipDate)
> when 1 then w.quantity else 0
> end) as JanQuantity,
> ...
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
> If this is not the case, Posting DDL and example data would help see
> http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
> the sample data would be beneficial.
> John
>
> "CyberFox" wrote:
>|||Hi
DDL, Example data and expected output would have eliminated any ambiguity
when you post. These are untested:
If you just want to sum the QuantityInHand values using the same where
clause as your main query then you can do that with
SELECT substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
end) as Jan,
...
SUM(D.QuantityInHand) AS InHand
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)
If you don't want that restriction then a subquery may be needed:
SELECT substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
end) as Jan,
...
( SELECT SUM(I.QuantityInHand) FROM InvWarehouse I WHERE
substring(D.MStockCode,1,3) = substring(I.MStockCode,1,3) ) AS InHand
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)
or possibly using derived tables and joining them
SELECT A.Style, A.Jan, A.Feb,... B.Total
FROM
( SELECT substring(D.MStockCode,1,3) as Style,
sum(case month(D.MLineShipDate)
when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
end) as Jan,
...
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3) ) A
JOIN
( SELECT substring(D.MStockCode,1,3) as Style,
SUM ( D.QuantityInHand ) AS InHand
from SorDetail D LEFT JOIN
SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3) ) B ON A.Style = B.Style
ORDER BY A.Style
John
"CyberFox" wrote:
> Hi John,
> The summation of the quantities is not date-dependent. Let me explain
> exactly what I want:
> The InvWarehouse table has a quantity on hand per stock item (this is the
> quantity in stock at the current time, and is not date-dependent at all).
> What I want to show is the stock item (actually the style number, which is
> the first 3 digits of the stock item), it's quantity on hand (the stock
> item's), and the outstanding sales orders per style (date-dependent).
> Hope this clarifies.
> Rgds,
> "John Bell" wrote:
>|||John,
I've tried the sub-query option, but it didn't do what I was hoping for. Let
me simplify and give you some examples of my data, if you don't mind:
SorDetail table:
Itemcode BackOrderQty OrderDate
002-0200-10-WW-02 100 01/01/06
002-0200-11-WW-02 150 02/01/06
002-0200-12-WW-02 150 01/02/06
010-0300-16-ED-03 100 01/01/06
010-0300-16-MK-01 200 01/01/06
010-0300-16-TR-02 100 01/03/06
InvWarehouse table
ItemCode QtyOnHand
002-0200-10-WW-02 2000
002-0200-11-WW-02 1400
002-0200-12-WW-02 1500
010-0300-16-ED-03 1000
010-0300-16-MK-01 1000
010-0300-16-TR-02 1000
I need the following (considering that the style = first 3 digits of the
item codes)
Style QtyOnHand JanOrders FebOrders Mar
002 4900 250 150
0
010 3000 300 0
100
Thank you very much for your help so far...
Rgds,
"John Bell" wrote:
> Hi
> DDL, Example data and expected output would have eliminated any ambiguity
> when you post. These are untested:
> If you just want to sum the QuantityInHand values using the same where
> clause as your main query then you can do that with
> SELECT substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
> end) as Jan,
> ...
> SUM(D.QuantityInHand) AS InHand
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
> If you don't want that restriction then a subquery may be needed:
> SELECT substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
> end) as Jan,
> ...
> ( SELECT SUM(I.QuantityInHand) FROM InvWarehouse I WHERE
> substring(D.MStockCode,1,3) = substring(I.MStockCode,1,3) ) AS InHand
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
> or possibly using derived tables and joining them
> SELECT A.Style, A.Jan, A.Feb,... B.Total
> FROM
> ( SELECT substring(D.MStockCode,1,3) as Style,
> sum(case month(D.MLineShipDate)
> when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
> end) as Jan,
> ...
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3) ) A
> JOIN
> ( SELECT substring(D.MStockCode,1,3) as Style,
> SUM ( D.QuantityInHand ) AS InHand
> from SorDetail D LEFT JOIN
> SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3) ) B ON A.Style = B.Style
> ORDER BY A.Style
> John
> "CyberFox" wrote:
>|||Hi
This post is inconsistent with the tables/columns that you have posted
previously so it is even more confusing, make sure that you read
http://www.aspfaq.com/etiquette.asp?id=5006 and post something usable.
With:
CREATE TABLE SorDetail ( Itemcode char(17), BackOrderQty
int, OrderDate datetime)
CREATE TABLE InvWarehouse ( Itemcode char(17), QtyOnHand
int )
INSERT INTO SorDetail ( Itemcode, BackOrderQty, OrderDate)
SELECT '002-0200-10-WW-02', 100, '20060101'
UNION ALL SELECT '002-0200-11-WW-02', 150, '20060102'
UNION ALL SELECT '002-0200-12-WW-02', 150, '20060201'
UNION ALL SELECT '010-0300-16-ED-03', 100, '20060101'
UNION ALL SELECT '010-0300-16-MK-01', 200, '20060101'
UNION ALL SELECT '010-0300-16-TR-02', 100, '20060103
'
INSERT INTO InvWarehouse ( Itemcode, QtyOnHand )
SELECT '002-0200-10-WW-02', 2000
UNION ALL SELECT '002-0200-11-WW-02', 1400
UNION ALL SELECT '002-0200-12-WW-02', 1500
UNION ALL SELECT '010-0300-16-ED-03', 1000
UNION ALL SELECT '010-0300-16-MK-01', 1000
UNION ALL SELECT '010-0300-16-TR-02', 1000
My query:
SELECT substring(D.Itemcode,1,3) as Style,
sum(case month(D.OrderDate)
when 1 then D.BackOrderQty
else 0
end) as Jan,
sum(case month(D.OrderDate)
when 2 then D.BackOrderQty
else 0
end) as Feb,
sum(case month(D.OrderDate)
when 3 then D.BackOrderQty
else 0
end) as Mar,
( SELECT SUM(I.QtyOnHand) FROM InvWarehouse I WHERE
substring(D.Itemcode,1,3) = substring(I.Itemcode,1,3) ) AS InHand
from SorDetail D
LEFT JOIN InvWarehouse W ON D.Itemcode = W.Itemcode
WHERE D.BackOrderQty > 0
group by substring(D.Itemcode,1,3)
order by substring(D.Itemcode,1,3)
seems to give exaclty what you required, although it gives me an error if I
change the column order, so using:
SELECT A.Style, B.QtyOnHand, A.Jan, A.Feb, A.Mar
FROM
( SELECT SUBSTRING(D.Itemcode,1,3) as Style,
SUM(CASE MONTH(D.OrderDate)
WHEN 1 THEN D.BackOrderQty
ELSE 0
END) AS Jan,
SUM(CASE MONTH(D.OrderDate)
WHEN 2 THEN D.BackOrderQty
ELSE 0
END) AS Feb,
SUM(CASE MONTH(D.OrderDate)
WHEN 3 THEN D.BackOrderQty
ELSE 0
END) AS Mar
FROM SorDetail D
LEFT JOIN InvWarehouse W ON D.Itemcode = W.Itemcode
WHERE D.BackOrderQty > 0
GROUP BY SUBSTRING(D.Itemcode,1,3) ) A
LEFT JOIN ( SELECT SUBSTRING(i.Itemcode,1,3) as Style,
SUM(I.QtyOnHand) AS QtyOnHand
FROM InvWarehouse I
GROUP BY SUBSTRING(I.Itemcode,1,3) ) B ON A.Style = B.Style
ORDER BY A.Style
may be a better option.
John
"CyberFox" wrote:
> John,
> I've tried the sub-query option, but it didn't do what I was hoping for. L
et
> me simplify and give you some examples of my data, if you don't mind:
> SorDetail table:
> Itemcode BackOrderQty OrderDate
> 002-0200-10-WW-02 100 01/01/06
> 002-0200-11-WW-02 150 02/01/06
> 002-0200-12-WW-02 150 01/02/06
> 010-0300-16-ED-03 100 01/01/06
> 010-0300-16-MK-01 200 01/01/06
> 010-0300-16-TR-02 100 01/03/06
> InvWarehouse table
> ItemCode QtyOnHand
> 002-0200-10-WW-02 2000
> 002-0200-11-WW-02 1400
> 002-0200-12-WW-02 1500
> 010-0300-16-ED-03 1000
> 010-0300-16-MK-01 1000
> 010-0300-16-TR-02 1000
> I need the following (considering that the style = first 3 digits of the
> item codes)
> Style QtyOnHand JanOrders FebOrders Mar
> 002 4900 250 150
0
> 010 3000 300 0
> 100
> Thank you very much for your help so far...
> Rgds,
> "John Bell" wrote:
>

Extracting duplicate records

I am working on an inherited database and trying to add indexes on existing
tables. A few of the tables have duplicate entries that cause the
application of the indices to fail. I know that on some of the tables I can
simply copy the table, delete the records apply the index import from old
table and only unique records will make it into the table. ( I am correct
here aren't I?) However...
On a couple of tables I need to actually "see" the duplicate records so that
I can choose which records to keep and do a manual deletion of the duplicate
records.
There are no PK's on the tables in question and the indices I need to put in
place are, for example, on 6 of 15 columns. I need to find the dupes and
view the information in non-indexed columns to make my decision on which to
keep and which to delete.
Does this make any sense? Can anyone help me out with how I would go about
identifying the duplicate records?
Thanks in advance.
daniel
daniel wrote:
> I am working on an inherited database and trying to add indexes on
> existing tables. A few of the tables have duplicate entries that
> cause the application of the indices to fail. I know that on some of
> the tables I can simply copy the table, delete the records apply the
> index import from old table and only unique records will make it into
> the table. ( I am correct here aren't I?) However...
> On a couple of tables I need to actually "see" the duplicate records
> so that I can choose which records to keep and do a manual deletion
> of the duplicate records.
> There are no PK's on the tables in question and the indices I need to
> put in place are, for example, on 6 of 15 columns. I need to find the
> dupes and view the information in non-indexed columns to make my
> decision on which to keep and which to delete.
> Does this make any sense? Can anyone help me out with how I would go
> about identifying the duplicate records?
> Thanks in advance.
> daniel
You can group by the columns to be indexed and use a having clause to
return the dupes. Not sure of your table size, so the query may be CPU
and tiem consuming:
For example:
Select a, b, c, d
From MyTable
Group By a, b, c, d
Having COUNT(*) > 1
To get rid of the dupes, you could simply create another table and add a
clustered index with the ignore dup keys flag and insert the rows. But
you wouldn't have much control over which dupes were removed.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OrwxbzgCFHA.444@.TK2MSFTNGP15.phx.gbl...
> daniel wrote:
> You can group by the columns to be indexed and use a having clause to
> return the dupes. Not sure of your table size, so the query may be CPU
> and tiem consuming:
> For example:
> Select a, b, c, d
> From MyTable
> Group By a, b, c, d
> Having COUNT(*) > 1
> To get rid of the dupes, you could simply create another table and add a
> clustered index with the ignore dup keys flag and insert the rows. But
> you wouldn't have much control over which dupes were removed.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks David but I guess maybe I've misunderstood the error message from
SQL.(?) I queried one of the tables on which I receive the indexing error,
with the query you provided, and it returned 0 records. Perhaps providing
the error message will allow someone more knowledgeable than I to tell me
I'm interpreting it wrong. The message is as follows:
"Server: Msg 1505, Level 16, State 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
id 9. Most significant primary key is '115040'"
Does this not mean there are duplicate row entries based on the attempted
index columns? If so why do I get a return of 0 records with the query
provided below?
Any ideas anyone?
|||daniel wrote:
> Thanks David but I guess maybe I've misunderstood the error message
> from SQL.(?) I queried one of the tables on which I receive the
> indexing error, with the query you provided, and it returned 0
> records. Perhaps providing the error message will allow someone more
> knowledgeable than I to tell me I'm interpreting it wrong. The
> message is as follows:
> "Server: Msg 1505, Level 16, State 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for
> index id 9. Most significant primary key is '115040'"
> Does this not mean there are duplicate row entries based on the
> attempted index columns? If so why do I get a return of 0 records
> with the query provided below?
> Any ideas anyone?
You'll need to provide the query, the table ddl, and the index statement
in order to tell what's going on.
David Gugick
Imceda Software
www.imceda.com
|||You might find this interesting.
http://www.15seconds.com/issue/011009.htm
Ben Miller
"daniel" <dhagwood@.hotmail.com> wrote in message
news:OVAIxhgCFHA.560@.TK2MSFTNGP15.phx.gbl...
>I am working on an inherited database and trying to add indexes on existing
> tables. A few of the tables have duplicate entries that cause the
> application of the indices to fail. I know that on some of the tables I
> can
> simply copy the table, delete the records apply the index import from old
> table and only unique records will make it into the table. ( I am correct
> here aren't I?) However...
> On a couple of tables I need to actually "see" the duplicate records so
> that
> I can choose which records to keep and do a manual deletion of the
> duplicate
> records.
> There are no PK's on the tables in question and the indices I need to put
> in
> place are, for example, on 6 of 15 columns. I need to find the dupes and
> view the information in non-indexed columns to make my decision on which
> to
> keep and which to delete.
> Does this make any sense? Can anyone help me out with how I would go about
> identifying the duplicate records?
> Thanks in advance.
> daniel
>
|||daniel wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OrwxbzgCFHA.444@.TK2MSFTNGP15.phx.gbl...
> Thanks David but I guess maybe I've misunderstood the error message
> from SQL.(?) I queried one of the tables on which I receive the
> indexing error, with the query you provided, and it returned 0
> records. Perhaps providing the error message will allow someone more
> knowledgeable than I to tell me I'm interpreting it wrong. The
> message is as follows:
> "Server: Msg 1505, Level 16, State 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for
> index id 9. Most significant primary key is '115040'"
> Does this not mean there are duplicate row entries based on the
> attempted index columns? If so why do I get a return of 0 records
> with the query provided below?
> Any ideas anyone?
This works for me:
create table #a (col1 int, col2 int, col3 int)
insert into #a values (1, 2, 3)
insert into #a values (1, 2, 3)
insert into #a values (2, 2, 3)
insert into #a values (3, 2, 1)
insert into #a values (3, 2, 1)
insert into #a values (3, 2, 1)
Select count(*) as "# Dupes", col1, col2, col3
From #a
Group By col1, col2, col3
Having count(*) > 1
# Dupes col1 col2 col3
-- -- -- --
2 1 2 3
3 3 2 1
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ea8zyRlCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> daniel wrote:
> This works for me:
> create table #a (col1 int, col2 int, col3 int)
>
> insert into #a values (1, 2, 3)
> insert into #a values (1, 2, 3)
> insert into #a values (2, 2, 3)
> insert into #a values (3, 2, 1)
> insert into #a values (3, 2, 1)
> insert into #a values (3, 2, 1)
>
> Select count(*) as "# Dupes", col1, col2, col3
> From #a
> Group By col1, col2, col3
> Having count(*) > 1
> # Dupes col1 col2 col3
> -- -- -- --
> 2 1 2 3
> 3 3 2 1
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks again David. I finally got the query to work. Knowing that there were
duplicates I did some intensive eye straining work to figure out the
problem. Appears as though some of the "duplicate" records also contained
unexpected data, empty string vs. NULL value and this was thowing off the
query. I updated the fields as necessary and everything worked out fine.
Thanks again for your assistance.
daniel

Extracting duplicate records

I am working on an inherited database and trying to add indexes on existing
tables. A few of the tables have duplicate entries that cause the
application of the indices to fail. I know that on some of the tables I can
simply copy the table, delete the records apply the index import from old
table and only unique records will make it into the table. ( I am correct
here aren't I?) However...
On a couple of tables I need to actually "see" the duplicate records so that
I can choose which records to keep and do a manual deletion of the duplicate
records.
There are no PK's on the tables in question and the indices I need to put in
place are, for example, on 6 of 15 columns. I need to find the dupes and
view the information in non-indexed columns to make my decision on which to
keep and which to delete.
Does this make any sense? Can anyone help me out with how I would go about
identifying the duplicate records?
Thanks in advance.
danieldaniel wrote:
> I am working on an inherited database and trying to add indexes on
> existing tables. A few of the tables have duplicate entries that
> cause the application of the indices to fail. I know that on some of
> the tables I can simply copy the table, delete the records apply the
> index import from old table and only unique records will make it into
> the table. ( I am correct here aren't I?) However...
> On a couple of tables I need to actually "see" the duplicate records
> so that I can choose which records to keep and do a manual deletion
> of the duplicate records.
> There are no PK's on the tables in question and the indices I need to
> put in place are, for example, on 6 of 15 columns. I need to find the
> dupes and view the information in non-indexed columns to make my
> decision on which to keep and which to delete.
> Does this make any sense? Can anyone help me out with how I would go
> about identifying the duplicate records?
> Thanks in advance.
> daniel
You can group by the columns to be indexed and use a having clause to
return the dupes. Not sure of your table size, so the query may be CPU
and tiem consuming:
For example:
Select a, b, c, d
From MyTable
Group By a, b, c, d
Having COUNT(*) > 1
To get rid of the dupes, you could simply create another table and add a
clustered index with the ignore dup keys flag and insert the rows. But
you wouldn't have much control over which dupes were removed.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OrwxbzgCFHA.444@.TK2MSFTNGP15.phx.gbl...
> daniel wrote:
> You can group by the columns to be indexed and use a having clause to
> return the dupes. Not sure of your table size, so the query may be CPU
> and tiem consuming:
> For example:
> Select a, b, c, d
> From MyTable
> Group By a, b, c, d
> Having COUNT(*) > 1
> To get rid of the dupes, you could simply create another table and add a
> clustered index with the ignore dup keys flag and insert the rows. But
> you wouldn't have much control over which dupes were removed.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks David but I guess maybe I've misunderstood the error message from
SQL.(?) I queried one of the tables on which I receive the indexing error,
with the query you provided, and it returned 0 records. Perhaps providing
the error message will allow someone more knowledgeable than I to tell me
I'm interpreting it wrong. The message is as follows:
"Server: Msg 1505, Level 16, State 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
id 9. Most significant primary key is '115040'"
Does this not mean there are duplicate row entries based on the attempted
index columns? If so why do I get a return of 0 records with the query
provided below?
Any ideas anyone?|||daniel wrote:
> Thanks David but I guess maybe I've misunderstood the error message
> from SQL.(?) I queried one of the tables on which I receive the
> indexing error, with the query you provided, and it returned 0
> records. Perhaps providing the error message will allow someone more
> knowledgeable than I to tell me I'm interpreting it wrong. The
> message is as follows:
> "Server: Msg 1505, Level 16, State 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for
> index id 9. Most significant primary key is '115040'"
> Does this not mean there are duplicate row entries based on the
> attempted index columns? If so why do I get a return of 0 records
> with the query provided below?
> Any ideas anyone?
You'll need to provide the query, the table ddl, and the index statement
in order to tell what's going on.
David Gugick
Imceda Software
www.imceda.com|||You might find this interesting.
http://www.15seconds.com/issue/011009.htm
Ben Miller
"daniel" <dhagwood@.hotmail.com> wrote in message
news:OVAIxhgCFHA.560@.TK2MSFTNGP15.phx.gbl...
>I am working on an inherited database and trying to add indexes on existing
> tables. A few of the tables have duplicate entries that cause the
> application of the indices to fail. I know that on some of the tables I
> can
> simply copy the table, delete the records apply the index import from old
> table and only unique records will make it into the table. ( I am correct
> here aren't I?) However...
> On a couple of tables I need to actually "see" the duplicate records so
> that
> I can choose which records to keep and do a manual deletion of the
> duplicate
> records.
> There are no PK's on the tables in question and the indices I need to put
> in
> place are, for example, on 6 of 15 columns. I need to find the dupes and
> view the information in non-indexed columns to make my decision on which
> to
> keep and which to delete.
> Does this make any sense? Can anyone help me out with how I would go about
> identifying the duplicate records?
> Thanks in advance.
> daniel
>|||daniel wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OrwxbzgCFHA.444@.TK2MSFTNGP15.phx.gbl...
> Thanks David but I guess maybe I've misunderstood the error message
> from SQL.(?) I queried one of the tables on which I receive the
> indexing error, with the query you provided, and it returned 0
> records. Perhaps providing the error message will allow someone more
> knowledgeable than I to tell me I'm interpreting it wrong. The
> message is as follows:
> "Server: Msg 1505, Level 16, State 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for
> index id 9. Most significant primary key is '115040'"
> Does this not mean there are duplicate row entries based on the
> attempted index columns? If so why do I get a return of 0 records
> with the query provided below?
> Any ideas anyone?
This works for me:
create table #a (col1 int, col2 int, col3 int)
insert into #a values (1, 2, 3)
insert into #a values (1, 2, 3)
insert into #a values (2, 2, 3)
insert into #a values (3, 2, 1)
insert into #a values (3, 2, 1)
insert into #a values (3, 2, 1)
Select count(*) as "# Dupes", col1, col2, col3
From #a
Group By col1, col2, col3
Having count(*) > 1
# Dupes col1 col2 col3
-- -- -- --
2 1 2 3
3 3 2 1
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ea8zyRlCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> daniel wrote:
> This works for me:
> create table #a (col1 int, col2 int, col3 int)
>
> insert into #a values (1, 2, 3)
> insert into #a values (1, 2, 3)
> insert into #a values (2, 2, 3)
> insert into #a values (3, 2, 1)
> insert into #a values (3, 2, 1)
> insert into #a values (3, 2, 1)
>
> Select count(*) as "# Dupes", col1, col2, col3
> From #a
> Group By col1, col2, col3
> Having count(*) > 1
> # Dupes col1 col2 col3
> -- -- -- --
> 2 1 2 3
> 3 3 2 1
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks again David. I finally got the query to work. Knowing that there were
duplicates I did some intensive eye straining work to figure out the
problem. Appears as though some of the "duplicate" records also contained
unexpected data, empty string vs. NULL value and this was thowing off the
query. I updated the fields as necessary and everything worked out fine.
Thanks again for your assistance.
daniel

Extracting duplicate records

I am working on an inherited database and trying to add indexes on existing
tables. A few of the tables have duplicate entries that cause the
application of the indices to fail. I know that on some of the tables I can
simply copy the table, delete the records apply the index import from old
table and only unique records will make it into the table. ( I am correct
here aren't I?) However...
On a couple of tables I need to actually "see" the duplicate records so that
I can choose which records to keep and do a manual deletion of the duplicate
records.
There are no PK's on the tables in question and the indices I need to put in
place are, for example, on 6 of 15 columns. I need to find the dupes and
view the information in non-indexed columns to make my decision on which to
keep and which to delete.
Does this make any sense? Can anyone help me out with how I would go about
identifying the duplicate records?
Thanks in advance.
danieldaniel wrote:
> I am working on an inherited database and trying to add indexes on
> existing tables. A few of the tables have duplicate entries that
> cause the application of the indices to fail. I know that on some of
> the tables I can simply copy the table, delete the records apply the
> index import from old table and only unique records will make it into
> the table. ( I am correct here aren't I?) However...
> On a couple of tables I need to actually "see" the duplicate records
> so that I can choose which records to keep and do a manual deletion
> of the duplicate records.
> There are no PK's on the tables in question and the indices I need to
> put in place are, for example, on 6 of 15 columns. I need to find the
> dupes and view the information in non-indexed columns to make my
> decision on which to keep and which to delete.
> Does this make any sense? Can anyone help me out with how I would go
> about identifying the duplicate records?
> Thanks in advance.
> daniel
You can group by the columns to be indexed and use a having clause to
return the dupes. Not sure of your table size, so the query may be CPU
and tiem consuming:
For example:
Select a, b, c, d
From MyTable
Group By a, b, c, d
Having COUNT(*) > 1
To get rid of the dupes, you could simply create another table and add a
clustered index with the ignore dup keys flag and insert the rows. But
you wouldn't have much control over which dupes were removed.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OrwxbzgCFHA.444@.TK2MSFTNGP15.phx.gbl...
> daniel wrote:
> > I am working on an inherited database and trying to add indexes on
> > existing tables. A few of the tables have duplicate entries that
> > cause the application of the indices to fail. I know that on some of
> > the tables I can simply copy the table, delete the records apply the
> > index import from old table and only unique records will make it into
> > the table. ( I am correct here aren't I?) However...
> >
> > On a couple of tables I need to actually "see" the duplicate records
> > so that I can choose which records to keep and do a manual deletion
> > of the duplicate records.
> >
> > There are no PK's on the tables in question and the indices I need to
> > put in place are, for example, on 6 of 15 columns. I need to find the
> > dupes and view the information in non-indexed columns to make my
> > decision on which to keep and which to delete.
> >
> > Does this make any sense? Can anyone help me out with how I would go
> > about identifying the duplicate records?
> >
> > Thanks in advance.
> >
> > daniel
> You can group by the columns to be indexed and use a having clause to
> return the dupes. Not sure of your table size, so the query may be CPU
> and tiem consuming:
> For example:
> Select a, b, c, d
> From MyTable
> Group By a, b, c, d
> Having COUNT(*) > 1
> To get rid of the dupes, you could simply create another table and add a
> clustered index with the ignore dup keys flag and insert the rows. But
> you wouldn't have much control over which dupes were removed.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks David but I guess maybe I've misunderstood the error message from
SQL.(?) I queried one of the tables on which I receive the indexing error,
with the query you provided, and it returned 0 records. Perhaps providing
the error message will allow someone more knowledgeable than I to tell me
I'm interpreting it wrong. The message is as follows:
"Server: Msg 1505, Level 16, State 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
id 9. Most significant primary key is '115040'"
Does this not mean there are duplicate row entries based on the attempted
index columns? If so why do I get a return of 0 records with the query
provided below?
Any ideas anyone?|||daniel wrote:
> Thanks David but I guess maybe I've misunderstood the error message
> from SQL.(?) I queried one of the tables on which I receive the
> indexing error, with the query you provided, and it returned 0
> records. Perhaps providing the error message will allow someone more
> knowledgeable than I to tell me I'm interpreting it wrong. The
> message is as follows:
> "Server: Msg 1505, Level 16, State 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for
> index id 9. Most significant primary key is '115040'"
> Does this not mean there are duplicate row entries based on the
> attempted index columns? If so why do I get a return of 0 records
> with the query provided below?
> Any ideas anyone?
You'll need to provide the query, the table ddl, and the index statement
in order to tell what's going on.
David Gugick
Imceda Software
www.imceda.com|||You might find this interesting.
http://www.15seconds.com/issue/011009.htm
Ben Miller
"daniel" <dhagwood@.hotmail.com> wrote in message
news:OVAIxhgCFHA.560@.TK2MSFTNGP15.phx.gbl...
>I am working on an inherited database and trying to add indexes on existing
> tables. A few of the tables have duplicate entries that cause the
> application of the indices to fail. I know that on some of the tables I
> can
> simply copy the table, delete the records apply the index import from old
> table and only unique records will make it into the table. ( I am correct
> here aren't I?) However...
> On a couple of tables I need to actually "see" the duplicate records so
> that
> I can choose which records to keep and do a manual deletion of the
> duplicate
> records.
> There are no PK's on the tables in question and the indices I need to put
> in
> place are, for example, on 6 of 15 columns. I need to find the dupes and
> view the information in non-indexed columns to make my decision on which
> to
> keep and which to delete.
> Does this make any sense? Can anyone help me out with how I would go about
> identifying the duplicate records?
> Thanks in advance.
> daniel
>|||daniel wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OrwxbzgCFHA.444@.TK2MSFTNGP15.phx.gbl...
>> daniel wrote:
>> I am working on an inherited database and trying to add indexes on
>> existing tables. A few of the tables have duplicate entries that
>> cause the application of the indices to fail. I know that on some of
>> the tables I can simply copy the table, delete the records apply the
>> index import from old table and only unique records will make it
>> into the table. ( I am correct here aren't I?) However...
>> On a couple of tables I need to actually "see" the duplicate records
>> so that I can choose which records to keep and do a manual deletion
>> of the duplicate records.
>> There are no PK's on the tables in question and the indices I need
>> to put in place are, for example, on 6 of 15 columns. I need to
>> find the dupes and view the information in non-indexed columns to
>> make my decision on which to keep and which to delete.
>> Does this make any sense? Can anyone help me out with how I would go
>> about identifying the duplicate records?
>> Thanks in advance.
>> daniel
>> You can group by the columns to be indexed and use a having clause to
>> return the dupes. Not sure of your table size, so the query may be
>> CPU and tiem consuming:
>> For example:
>> Select a, b, c, d
>> From MyTable
>> Group By a, b, c, d
>> Having COUNT(*) > 1
>> To get rid of the dupes, you could simply create another table and
>> add a clustered index with the ignore dup keys flag and insert the
>> rows. But you wouldn't have much control over which dupes were
>> removed.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
> Thanks David but I guess maybe I've misunderstood the error message
> from SQL.(?) I queried one of the tables on which I receive the
> indexing error, with the query you provided, and it returned 0
> records. Perhaps providing the error message will allow someone more
> knowledgeable than I to tell me I'm interpreting it wrong. The
> message is as follows:
> "Server: Msg 1505, Level 16, State 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for
> index id 9. Most significant primary key is '115040'"
> Does this not mean there are duplicate row entries based on the
> attempted index columns? If so why do I get a return of 0 records
> with the query provided below?
> Any ideas anyone?
This works for me:
create table #a (col1 int, col2 int, col3 int)
insert into #a values (1, 2, 3)
insert into #a values (1, 2, 3)
insert into #a values (2, 2, 3)
insert into #a values (3, 2, 1)
insert into #a values (3, 2, 1)
insert into #a values (3, 2, 1)
Select count(*) as "# Dupes", col1, col2, col3
From #a
Group By col1, col2, col3
Having count(*) > 1
# Dupes col1 col2 col3
-- -- -- --
2 1 2 3
3 3 2 1
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ea8zyRlCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> daniel wrote:
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:OrwxbzgCFHA.444@.TK2MSFTNGP15.phx.gbl...
> >> daniel wrote:
> >> I am working on an inherited database and trying to add indexes on
> >> existing tables. A few of the tables have duplicate entries that
> >> cause the application of the indices to fail. I know that on some of
> >> the tables I can simply copy the table, delete the records apply the
> >> index import from old table and only unique records will make it
> >> into the table. ( I am correct here aren't I?) However...
> >>
> >> On a couple of tables I need to actually "see" the duplicate records
> >> so that I can choose which records to keep and do a manual deletion
> >> of the duplicate records.
> >>
> >> There are no PK's on the tables in question and the indices I need
> >> to put in place are, for example, on 6 of 15 columns. I need to
> >> find the dupes and view the information in non-indexed columns to
> >> make my decision on which to keep and which to delete.
> >>
> >> Does this make any sense? Can anyone help me out with how I would go
> >> about identifying the duplicate records?
> >>
> >> Thanks in advance.
> >>
> >> daniel
> >>
> >> You can group by the columns to be indexed and use a having clause to
> >> return the dupes. Not sure of your table size, so the query may be
> >> CPU and tiem consuming:
> >>
> >> For example:
> >>
> >> Select a, b, c, d
> >> From MyTable
> >> Group By a, b, c, d
> >> Having COUNT(*) > 1
> >>
> >> To get rid of the dupes, you could simply create another table and
> >> add a clustered index with the ignore dup keys flag and insert the
> >> rows. But you wouldn't have much control over which dupes were
> >> removed.
> >>
> >>
> >> --
> >> David Gugick
> >> Imceda Software
> >> www.imceda.com
> >>
> >
> > Thanks David but I guess maybe I've misunderstood the error message
> > from SQL.(?) I queried one of the tables on which I receive the
> > indexing error, with the query you provided, and it returned 0
> > records. Perhaps providing the error message will allow someone more
> > knowledgeable than I to tell me I'm interpreting it wrong. The
> > message is as follows:
> >
> > "Server: Msg 1505, Level 16, State 1
> > CREATE UNIQUE INDEX terminated because a duplicate key was found for
> > index id 9. Most significant primary key is '115040'"
> >
> > Does this not mean there are duplicate row entries based on the
> > attempted index columns? If so why do I get a return of 0 records
> > with the query provided below?
> >
> > Any ideas anyone?
> This works for me:
> create table #a (col1 int, col2 int, col3 int)
>
> insert into #a values (1, 2, 3)
> insert into #a values (1, 2, 3)
> insert into #a values (2, 2, 3)
> insert into #a values (3, 2, 1)
> insert into #a values (3, 2, 1)
> insert into #a values (3, 2, 1)
>
> Select count(*) as "# Dupes", col1, col2, col3
> From #a
> Group By col1, col2, col3
> Having count(*) > 1
> # Dupes col1 col2 col3
> -- -- -- --
> 2 1 2 3
> 3 3 2 1
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks again David. I finally got the query to work. Knowing that there were
duplicates I did some intensive eye straining work to figure out the
problem. Appears as though some of the "duplicate" records also contained
unexpected data, empty string vs. NULL value and this was thowing off the
query. I updated the fields as necessary and everything worked out fine.
Thanks again for your assistance.
daniel