Ok the problem. I have inherited an SQL Server 2005 database that is tied
to a website (Intranet) that is the front end for data entry. On the
screens a lot of data is scanned in via hand held barcode readers. The
reader software drops the data into <input type='text' /> textboxes based
on focus. But the textboxes can also have data keyed into them. Note
before we get much further I cannot alter the database and the data entry
screens at present I have limited access to the code to repair the data
validation for the problem I am about to tell you about.



There is a field in the database that is a varchar(50) I think (not in
front of it right now but sure of the varchar) that in fact is supposed to
hold only Integers. If scanning is done right with the proper focus ect,
only integers go into this field. The original developer appears to have
not considered this might not happen this way every time. Result other non
numeric data is in the column. Again I can't actually fix the root of the
problem so any solution that involves me writing code to prevent this
happening will not at present work -and I am not happy about this -.



Ok I'm looking for a query that will let me clean this field of non
integer values as it is killing a reporting program down the line.  I
worked my way though the data yesterday in 40,000+ only found about 50
entries that were bad. I am looking to set a proc into the database that
fix this column that the users can invoke without me.



I am OK with replacing non integers with zero or 999. Null won't work for
other reasons. I want the consumer of reports to be able to spot rows
where there was an issue. Columns in the table



OrderNo , cnt, batch, .



Cnt is the column the other two will be different for each cnt that is cnt
= 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
will have different OrderNo batch combinations so you can get to row one's
cnt =1 without hitting row 10's.



Cnt is the column to clean.  I thought about SELECT * FROM table WHERE
ISNUMERIC(cnt) = false; and then some sort of Update.



My question is how would you attack this. also note I encountered
yesterday .1 and .5 (yes decimals, but they are invalid values so need to
be reset, I suspect they are keying errors and should be 1 and 5
respectively)?



Thanks and I hope I'm more clear than mud.



John Warner





[Non-text portions of this message have been removed]

I am not 100% sure i understand what you want...so let me phrase it how i
understand it.

You want an update statement to set the 'cnt' field in your table to '-0' (-0
was used in COBAL as a numeric null), or some other value, when ever the current
text of 'cnt' is not numeric and does not have a '\period in it. 


Is that correct?
if so you can use this

UPDATE    a
SET            cnt = '-0'
FROM        Table1
WHERE    ISNUMERIC(cnt) = false
OR            cnt like '%.%'

HTH
Paul




________________________________
From: John Warner <john@...>
To: SQLQueriesNoCode@yahoogroups.com
Sent: Thu, January 6, 2011 5:56:26 AM
Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int

 
Ok the problem. I have inherited an SQL Server 2005 database that is tied
to a website (Intranet) that is the front end for data entry. On the
screens a lot of data is scanned in via hand held barcode readers. The
reader software drops the data into <input type='text' /> textboxes based
on focus. But the textboxes can also have data keyed into them. Note
before we get much further I cannot alter the database and the data entry
screens at present I have limited access to the code to repair the data
validation for the problem I am about to tell you about.

There is a field in the database that is a varchar(50) I think (not in
front of it right now but sure of the varchar) that in fact is supposed to
hold only Integers. If scanning is done right with the proper focus ect,
only integers go into this field. The original developer appears to have
not considered this might not happen this way every time. Result other non
numeric data is in the column. Again I can't actually fix the root of the
problem so any solution that involves me writing code to prevent this
happening will not at present work -and I am not happy about this -.

Ok I'm looking for a query that will let me clean this field of non
integer values as it is killing a reporting program down the line. I
worked my way though the data yesterday in 40,000+ only found about 50
entries that were bad. I am looking to set a proc into the database that
fix this column that the users can invoke without me.

I am OK with replacing non integers with zero or 999. Null won't work for
other reasons. I want the consumer of reports to be able to spot rows
where there was an issue. Columns in the table

OrderNo , cnt, batch, .

Cnt is the column the other two will be different for each cnt that is cnt
= 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
will have different OrderNo batch combinations so you can get to row one's
cnt =1 without hitting row 10's.

Cnt is the column to clean. I thought about SELECT * FROM table WHERE
ISNUMERIC(cnt) = false; and then some sort of Update.

My question is how would you attack this. also note I encountered
yesterday .1 and .5 (yes decimals, but they are invalid values so need to
be reset, I suspect they are keying errors and should be 1 and 5
respectively)?

Thanks and I hope I'm more clear than mud.

John Warner

[Non-text portions of this message have been removed]




[Non-text portions of this message have been removed]

sorry...correction

UPDATE    a
SET            cnt = '-0'
FROM        Table1 a
WHERE    ISNUMERIC(cnt) = false
OR            cnt like '%.%'





________________________________
From: Paul Livengood <p_livengood@...>
To: SQLQueriesNoCode@yahoogroups.com
Sent: Thu, January 6, 2011 8:44:19 AM
Subject: Re: [SQLQueriesNoCode] Fix a varchar column's data to Int

 
I am not 100% sure i understand what you want...so let me phrase it how i
understand it.

You want an update statement to set the 'cnt' field in your table to '-0' (-0
was used in COBAL as a numeric null), or some other value, when ever the current

text of 'cnt' is not numeric and does not have a '\period in it. 

Is that correct?
if so you can use this

UPDATE    a
SET            cnt = '-0'
FROM        Table1
WHERE    ISNUMERIC(cnt) = false
OR            cnt like '%.%'

HTH
Paul

________________________________
From: John Warner <john@...>
To: SQLQueriesNoCode@yahoogroups.com
Sent: Thu, January 6, 2011 5:56:26 AM
Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int

 
Ok the problem. I have inherited an SQL Server 2005 database that is tied
to a website (Intranet) that is the front end for data entry. On the
screens a lot of data is scanned in via hand held barcode readers. The
reader software drops the data into <input type='text' /> textboxes based
on focus. But the textboxes can also have data keyed into them. Note
before we get much further I cannot alter the database and the data entry
screens at present I have limited access to the code to repair the data
validation for the problem I am about to tell you about.

There is a field in the database that is a varchar(50) I think (not in
front of it right now but sure of the varchar) that in fact is supposed to
hold only Integers. If scanning is done right with the proper focus ect,
only integers go into this field. The original developer appears to have
not considered this might not happen this way every time. Result other non
numeric data is in the column. Again I can't actually fix the root of the
problem so any solution that involves me writing code to prevent this
happening will not at present work -and I am not happy about this -.

Ok I'm looking for a query that will let me clean this field of non
integer values as it is killing a reporting program down the line. I
worked my way though the data yesterday in 40,000+ only found about 50
entries that were bad. I am looking to set a proc into the database that
fix this column that the users can invoke without me.

I am OK with replacing non integers with zero or 999. Null won't work for
other reasons. I want the consumer of reports to be able to spot rows
where there was an issue. Columns in the table

OrderNo , cnt, batch, .

Cnt is the column the other two will be different for each cnt that is cnt
= 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
will have different OrderNo batch combinations so you can get to row one's
cnt =1 without hitting row 10's.

Cnt is the column to clean. I thought about SELECT * FROM table WHERE
ISNUMERIC(cnt) = false; and then some sort of Update.

My question is how would you attack this. also note I encountered
yesterday .1 and .5 (yes decimals, but they are invalid values so need to
be reset, I suspect they are keying errors and should be 1 and 5
respectively)?

Thanks and I hope I'm more clear than mud.

John Warner

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]




[Non-text portions of this message have been removed]

You understood correctly, I'll give that a try Paul. Thanks.

I'll have to check on the negative but yours looks better then the hoops I was
considering.

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
> Sent: Thursday, January 06, 2011 10:44 AM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: Re: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> I am not 100% sure i understand what you want...so let me phrase it how i
> understand it.
>
> You want an update statement to set the 'cnt' field in your table to '-0' (-0
> was used in COBAL as a numeric null), or some other value, when ever the
> current
> text of 'cnt' is not numeric and does not have a '\period in it.
>
>
> Is that correct?
> if so you can use this
>
> UPDATE    a
> SET            cnt = '-0'
> FROM        Table1
> WHERE    ISNUMERIC(cnt) = false
> OR            cnt like '%.%'
>
> HTH
> Paul
>
>
>
>
> ________________________________
> From: John Warner <john@...>
> To: SQLQueriesNoCode@yahoogroups.com
> Sent: Thu, January 6, 2011 5:56:26 AM
> Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
>
> Ok the problem. I have inherited an SQL Server 2005 database that is tied
> to a website (Intranet) that is the front end for data entry. On the
> screens a lot of data is scanned in via hand held barcode readers. The
> reader software drops the data into <input type='text' /> textboxes based
> on focus. But the textboxes can also have data keyed into them. Note
> before we get much further I cannot alter the database and the data entry
> screens at present I have limited access to the code to repair the data
> validation for the problem I am about to tell you about.
>
> There is a field in the database that is a varchar(50) I think (not in
> front of it right now but sure of the varchar) that in fact is supposed to
> hold only Integers. If scanning is done right with the proper focus ect,
> only integers go into this field. The original developer appears to have
> not considered this might not happen this way every time. Result other non
> numeric data is in the column. Again I can't actually fix the root of the
> problem so any solution that involves me writing code to prevent this
> happening will not at present work -and I am not happy about this -.
>
> Ok I'm looking for a query that will let me clean this field of non
> integer values as it is killing a reporting program down the line. I
> worked my way though the data yesterday in 40,000+ only found about 50
> entries that were bad. I am looking to set a proc into the database that
> fix this column that the users can invoke without me.
>
> I am OK with replacing non integers with zero or 999. Null won't work for
> other reasons. I want the consumer of reports to be able to spot rows
> where there was an issue. Columns in the table
>
> OrderNo , cnt, batch, .
>
> Cnt is the column the other two will be different for each cnt that is cnt
> = 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
> will have different OrderNo batch combinations so you can get to row one's
> cnt =1 without hitting row 10's.
>
> Cnt is the column to clean. I thought about SELECT * FROM table WHERE
> ISNUMERIC(cnt) = false; and then some sort of Update.
>
> My question is how would you attack this. also note I encountered
> yesterday .1 and .5 (yes decimals, but they are invalid values so need to
> be reset, I suspect they are keying errors and should be 1 and 5
> respectively)?
>
> Thanks and I hope I'm more clear than mud.
>
> John Warner
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

No problem I understood the (-0 was an 'error'

Thanks again!

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
> Sent: Thursday, January 06, 2011 10:45 AM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: Re: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> sorry...correction
>
> UPDATE    a
> SET            cnt = '-0'
> FROM        Table1 a
> WHERE    ISNUMERIC(cnt) = false
> OR            cnt like '%.%'
>
>
>
>
>
> ________________________________
> From: Paul Livengood <p_livengood@...>
> To: SQLQueriesNoCode@yahoogroups.com
> Sent: Thu, January 6, 2011 8:44:19 AM
> Subject: Re: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
>
> I am not 100% sure i understand what you want...so let me phrase it how i
> understand it.
>
> You want an update statement to set the 'cnt' field in your table to '-0' (-0
> was used in COBAL as a numeric null), or some other value, when ever the
> current
>
> text of 'cnt' is not numeric and does not have a '\period in it.
>
> Is that correct?
> if so you can use this
>
> UPDATE    a
> SET            cnt = '-0'
> FROM        Table1
> WHERE    ISNUMERIC(cnt) = false
> OR            cnt like '%.%'
>
> HTH
> Paul
>
> ________________________________
> From: John Warner <john@...>
> To: SQLQueriesNoCode@yahoogroups.com
> Sent: Thu, January 6, 2011 5:56:26 AM
> Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
>
> Ok the problem. I have inherited an SQL Server 2005 database that is tied
> to a website (Intranet) that is the front end for data entry. On the
> screens a lot of data is scanned in via hand held barcode readers. The
> reader software drops the data into <input type='text' /> textboxes based
> on focus. But the textboxes can also have data keyed into them. Note
> before we get much further I cannot alter the database and the data entry
> screens at present I have limited access to the code to repair the data
> validation for the problem I am about to tell you about.
>
> There is a field in the database that is a varchar(50) I think (not in
> front of it right now but sure of the varchar) that in fact is supposed to
> hold only Integers. If scanning is done right with the proper focus ect,
> only integers go into this field. The original developer appears to have
> not considered this might not happen this way every time. Result other non
> numeric data is in the column. Again I can't actually fix the root of the
> problem so any solution that involves me writing code to prevent this
> happening will not at present work -and I am not happy about this -.
>
> Ok I'm looking for a query that will let me clean this field of non
> integer values as it is killing a reporting program down the line. I
> worked my way though the data yesterday in 40,000+ only found about 50
> entries that were bad. I am looking to set a proc into the database that
> fix this column that the users can invoke without me.
>
> I am OK with replacing non integers with zero or 999. Null won't work for
> other reasons. I want the consumer of reports to be able to spot rows
> where there was an issue. Columns in the table
>
> OrderNo , cnt, batch, .
>
> Cnt is the column the other two will be different for each cnt that is cnt
> = 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
> will have different OrderNo batch combinations so you can get to row one's
> cnt =1 without hitting row 10's.
>
> Cnt is the column to clean. I thought about SELECT * FROM table WHERE
> ISNUMERIC(cnt) = false; and then some sort of Update.
>
> My question is how would you attack this. also note I encountered
> yesterday .1 and .5 (yes decimals, but they are invalid values so need to
> be reset, I suspect they are keying errors and should be 1 and 5
> respectively)?
>
> Thanks and I hope I'm more clear than mud.
>
> John Warner
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

John,

Would it help to remove the non-integer characters and keep any integer
characters -or do you wish to just clear out the entire value replacing
with a marker?

The sorting is not an issue, it is easily accomplished by using a cast()
in the ORDER BY clause.


Regards,

Arnie Rowland, MVP (SQL Server)

"You cannot do a kindness too soon because you never know how soon it
will be too late."
-Ralph Waldo Emerson




-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
Sent: Thursday, January 06, 2011 4:59 AM
To: SQLQueriesNoCode@yahoogroups.com
Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int

Ok the problem. I have inherited an SQL Server 2005 database that is
tied to a website (Intranet) that is the front end for data entry. On
the screens a lot of data is scanned in via hand held barcode readers.
The reader software drops the data into <input type='text' /> textboxes
based on focus. But the textboxes can also have data keyed into them.
Note before we get much further I cannot alter the database and the data
entry screens at present I have limited access to the code to repair the
data validation for the problem I am about to tell you about.



There is a field in the database that is a varchar(50) I think (not in
front of it right now but sure of the varchar) that in fact is supposed
to hold only Integers. If scanning is done right with the proper focus
ect, only integers go into this field. The original developer appears to
have not considered this might not happen this way every time. Result
other non numeric data is in the column. Again I can't actually fix the
root of the problem so any solution that involves me writing code to
prevent this happening will not at present work -and I am not happy
about this -.



Ok I'm looking for a query that will let me clean this field of non
integer values as it is killing a reporting program down the line.  I
worked my way though the data yesterday in 40,000+ only found about 50
entries that were bad. I am looking to set a proc into the database that
fix this column that the users can invoke without me.



I am OK with replacing non integers with zero or 999. Null won't work
for other reasons. I want the consumer of reports to be able to spot
rows where there was an issue. Columns in the table



OrderNo , cnt, batch, .



Cnt is the column the other two will be different for each cnt that is
cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
column) will have different OrderNo batch combinations so you can get to
row one's cnt =1 without hitting row 10's.




Cnt is the column to clean.  I thought about SELECT * FROM table WHERE
ISNUMERIC(cnt) = false; and then some sort of Update.



My question is how would you attack this. also note I encountered
yesterday .1 and .5 (yes decimals, but they are invalid values so need
to be reset, I suspect they are keying errors and should be 1 and 5
respectively)?



Thanks and I hope I'm more clear than mud.



John Warner





[Non-text portions of this message have been removed]



------------------------------------

Yahoo! Groups Links







Disclaimer - January 6, 2011
This email and any files transmitted with it are confidential and intended
solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee
you should not disseminate, distribute, copy or alter this email. Any views or
opinions presented in this email are solely those of the author and might not
represent those of Westwood Consulting, Inc. Warning: Although Westwood
Consulting, Inc has taken reasonable precautions to ensure no viruses are
present in this email, the company cannot accept responsibility for any loss or
damage arising from the use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

Oh yes, that would be even better.

CAST() further down the road in the process is what is causing me to need
to 'scrub' the field. What am I missing?


Thanks.

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
> Sent: Thursday, January 06, 2011 12:00 PM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> John,
>
> Would it help to remove the non-integer characters and keep any integer
> characters -or do you wish to just clear out the entire value replacing
> with a marker?
>
> The sorting is not an issue, it is easily accomplished by using a cast()
> in the ORDER BY clause.
>
>
> Regards,
>
> Arnie Rowland, MVP (SQL Server)
>
> "You cannot do a kindness too soon because you never know how soon it
> will be too late."
> -Ralph Waldo Emerson
>
>
>
>
> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
> Sent: Thursday, January 06, 2011 4:59 AM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> Ok the problem. I have inherited an SQL Server 2005 database that is
> tied to a website (Intranet) that is the front end for data entry. On
> the screens a lot of data is scanned in via hand held barcode readers.
> The reader software drops the data into <input type='text' /> textboxes
> based on focus. But the textboxes can also have data keyed into them.
> Note before we get much further I cannot alter the database and the data
> entry screens at present I have limited access to the code to repair the
> data validation for the problem I am about to tell you about.
>
>
>
> There is a field in the database that is a varchar(50) I think (not in
> front of it right now but sure of the varchar) that in fact is supposed
> to hold only Integers. If scanning is done right with the proper focus
> ect, only integers go into this field. The original developer appears to
> have not considered this might not happen this way every time. Result
> other non numeric data is in the column. Again I can't actually fix the
> root of the problem so any solution that involves me writing code to
> prevent this happening will not at present work -and I am not happy
> about this -.
>
>
>
> Ok I'm looking for a query that will let me clean this field of non
> integer values as it is killing a reporting program down the line.  I
> worked my way though the data yesterday in 40,000+ only found about 50
> entries that were bad. I am looking to set a proc into the database that
> fix this column that the users can invoke without me.
>
>
>
> I am OK with replacing non integers with zero or 999. Null won't work
> for other reasons. I want the consumer of reports to be able to spot
> rows where there was an issue. Columns in the table
>
>
>
> OrderNo , cnt, batch, .
>
>
>
> Cnt is the column the other two will be different for each cnt that is
> cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
> column) will have different OrderNo batch combinations so you can get to
> row one's cnt =1 without hitting row 10's.
>
>
>
>
> Cnt is the column to clean.  I thought about SELECT * FROM table WHERE
> ISNUMERIC(cnt) = false; and then some sort of Update.
>
>
>
> My question is how would you attack this. also note I encountered
> yesterday .1 and .5 (yes decimals, but they are invalid values so need
> to be reset, I suspect they are keying errors and should be 1 and 5
> respectively)?
>
>
>
> Thanks and I hope I'm more clear than mud.
>
>
>
> John Warner
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
> Disclaimer - January 6, 2011
> This email and any files transmitted with it are confidential and
intended solely
> for SQLQueriesNoCode@yahoogroups.com. If you are not the named
> addressee you should not disseminate, distribute, copy or alter this
email. Any
> views or opinions presented in this email are solely those of the author
and
> might not represent those of Westwood Consulting, Inc. Warning: Although
> Westwood Consulting, Inc has taken reasonable precautions to ensure no
> viruses are present in this email, the company cannot accept
responsibility for
> any loss or damage arising from the use of this email or attachments.
> This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

John,

Here is a UDF that I created a long time ago. You may be able to use it
to 'clean' the data, then your queries can be sorted properly.

		 USE tempdb;
		 GO

		 CREATE FUNCTION dbo.fnNumericOnly
		    ( @InParam varchar(50) )
		    RETURNS varchar(50)
		 AS
		    BEGIN
		       IF patindex( '%[^0-9]%', @InParam ) > 0
		          BEGIN
		             WHILE patindex( '%[^0-9]%', @InParam ) > 0
		                BEGIN
		                   SET @InParam = Stuff( @InParam,
patindex( '%[^0-9]%', @InParam), 1, '' )
		                END
		          END
		       RETURN @InParam
		    END;
		 GO

		 CREATE TABLE #TestTable
		    (  RowId int IDENTITY
		    ,  SomeValue varchar(20)
		    );

		 INSERT INTO #TestTable
		    (  SomeValue  )
		    VALUES
		       ( '1' ),
		       ( '25x' ),
		       ( '.5' ),
		       ( 'a2x5m' ),
		       ( 'a05' ),
		       ( 'b1x5m' );

		 UPDATE #TestTable
		    SET SomeValue = dbo.fnNumericOnly( SomeValue );

		 SELECT SomeValue
		 FROM #TestTable
		 ORDER BY cast(SomeValue AS int);

		 DROP TABLE #TestTable;
		 DROP FUNCTION dbo.fnNumericOnly;
		 GO

The results will order like this. NOTE: If you wish to eliminate leading
zeros, that is another small issue but can be easily done.
		 SomeValue
		 1
		 5
		 05
		 15
		 25
		 25

Regards,

Arnie Rowland, MVP (SQL Server)

"You cannot do a kindness too soon because you never know how soon it
will be too late."
-Ralph Waldo Emerson




-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
Sent: Thursday, January 06, 2011 9:08 AM
To: SQLQueriesNoCode@yahoogroups.com
Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int

Oh yes, that would be even better.

CAST() further down the road in the process is what is causing me to
need to 'scrub' the field. What am I missing?


Thanks.

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
> Sent: Thursday, January 06, 2011 12:00 PM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> John,
>
> Would it help to remove the non-integer characters and keep any
> integer characters -or do you wish to just clear out the entire value
> replacing with a marker?
>
> The sorting is not an issue, it is easily accomplished by using a
> cast() in the ORDER BY clause.
>
>
> Regards,
>
> Arnie Rowland, MVP (SQL Server)
>
> "You cannot do a kindness too soon because you never know how soon it
> will be too late."
> -Ralph Waldo Emerson
>
>
>
>
> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
> Sent: Thursday, January 06, 2011 4:59 AM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> Ok the problem. I have inherited an SQL Server 2005 database that is
> tied to a website (Intranet) that is the front end for data entry. On
> the screens a lot of data is scanned in via hand held barcode readers.
> The reader software drops the data into <input type='text' />
> textboxes based on focus. But the textboxes can also have data keyed
into them.
> Note before we get much further I cannot alter the database and the
> data entry screens at present I have limited access to the code to
> repair the data validation for the problem I am about to tell you
about.
>
>
>
> There is a field in the database that is a varchar(50) I think (not in

> front of it right now but sure of the varchar) that in fact is
> supposed to hold only Integers. If scanning is done right with the
> proper focus ect, only integers go into this field. The original
> developer appears to have not considered this might not happen this
> way every time. Result other non numeric data is in the column. Again
> I can't actually fix the root of the problem so any solution that
> involves me writing code to prevent this happening will not at present

> work -and I am not happy about this -.
>
>
>
> Ok I'm looking for a query that will let me clean this field of non
> integer values as it is killing a reporting program down the line.  I
> worked my way though the data yesterday in 40,000+ only found about 50

> entries that were bad. I am looking to set a proc into the database
> that fix this column that the users can invoke without me.
>
>
>
> I am OK with replacing non integers with zero or 999. Null won't work
> for other reasons. I want the consumer of reports to be able to spot
> rows where there was an issue. Columns in the table
>
>
>
> OrderNo , cnt, batch, .
>
>
>
> Cnt is the column the other two will be different for each cnt that is

> cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
> column) will have different OrderNo batch combinations so you can get
> to row one's cnt =1 without hitting row 10's.
>
>
>
>
> Cnt is the column to clean.  I thought about SELECT * FROM table WHERE
> ISNUMERIC(cnt) = false; and then some sort of Update.
>
>
>
> My question is how would you attack this. also note I encountered
> yesterday .1 and .5 (yes decimals, but they are invalid values so need

> to be reset, I suspect they are keying errors and should be 1 and 5
> respectively)?
>
>
>
> Thanks and I hope I'm more clear than mud.
>
>
>
> John Warner
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
> Disclaimer - January 6, 2011
> This email and any files transmitted with it are confidential and
intended solely
> for SQLQueriesNoCode@yahoogroups.com. If you are not the named
> addressee you should not disseminate, distribute, copy or alter this
email. Any
> views or opinions presented in this email are solely those of the
> author
and
> might not represent those of Westwood Consulting, Inc. Warning:
> Although Westwood Consulting, Inc has taken reasonable precautions to
> ensure no viruses are present in this email, the company cannot accept
responsibility for
> any loss or damage arising from the use of this email or attachments.
> This disclaimer was added by Policy Patrol:
> http://www.policypatrol.com/
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>



------------------------------------

Yahoo! Groups Links







[Non-text portions of this message have been removed]

Oh, you have misunderstood, it isn't a sorting issue. But let me play with
this as it still may well work just fine.

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
> Sent: Thursday, January 06, 2011 12:53 PM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> John,
>
> Here is a UDF that I created a long time ago. You may be able to use it
> to 'clean' the data, then your queries can be sorted properly.
>
> 	 USE tempdb;
> 	 GO
>
> 	 CREATE FUNCTION dbo.fnNumericOnly
> 		   ( @InParam varchar(50) )
> 		   RETURNS varchar(50)
> 	 AS
> 		   BEGIN
> 		      IF patindex( '%[^0-9]%', @InParam ) > 0
> 		         BEGIN
> 		            WHILE patindex( '%[^0-9]%', @InParam ) > 0
> 		               BEGIN
> 		                  SET @InParam = Stuff( @InParam,
> patindex( '%[^0-9]%', @InParam), 1, '' )
> 		               END
> 		         END
> 		      RETURN @InParam
> 		   END;
> 	 GO
>
> 	 CREATE TABLE #TestTable
> 		   (  RowId int IDENTITY
> 		   ,  SomeValue varchar(20)
> 		   );
>
> 	 INSERT INTO #TestTable
> 		   (  SomeValue  )
> 		   VALUES
> 		      ( '1' ),
> 		      ( '25x' ),
> 		      ( '.5' ),
> 		      ( 'a2x5m' ),
> 		      ( 'a05' ),
> 		      ( 'b1x5m' );
>
> 	 UPDATE #TestTable
> 		   SET SomeValue = dbo.fnNumericOnly( SomeValue );
>
> 	 SELECT SomeValue
> 	 FROM #TestTable
> 	 ORDER BY cast(SomeValue AS int);
>
> 	 DROP TABLE #TestTable;
> 	 DROP FUNCTION dbo.fnNumericOnly;
> 	 GO
>
> The results will order like this. NOTE: If you wish to eliminate leading
> zeros, that is another small issue but can be easily done.
> 	 SomeValue
> 	 1
> 	 5
> 	 05
> 	 15
> 	 25
> 	 25
>
> Regards,
>
> Arnie Rowland, MVP (SQL Server)
>
> "You cannot do a kindness too soon because you never know how soon it
> will be too late."
> -Ralph Waldo Emerson
>
>
>
>
> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
> Sent: Thursday, January 06, 2011 9:08 AM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> Oh yes, that would be even better.
>
> CAST() further down the road in the process is what is causing me to
> need to 'scrub' the field. What am I missing?
>
>
> Thanks.
>
> John Warner
>
>
> > -----Original Message-----
> > From: SQLQueriesNoCode@yahoogroups.com
> > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie
> Rowland
> > Sent: Thursday, January 06, 2011 12:00 PM
> > To: SQLQueriesNoCode@yahoogroups.com
> > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
> >
> > John,
> >
> > Would it help to remove the non-integer characters and keep any
> > integer characters -or do you wish to just clear out the entire value
> > replacing with a marker?
> >
> > The sorting is not an issue, it is easily accomplished by using a
> > cast() in the ORDER BY clause.
> >
> >
> > Regards,
> >
> > Arnie Rowland, MVP (SQL Server)
> >
> > "You cannot do a kindness too soon because you never know how soon it
> > will be too late."
> > -Ralph Waldo Emerson
> >
> >
> >
> >
> > -----Original Message-----
> > From: SQLQueriesNoCode@yahoogroups.com
> > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
> > Sent: Thursday, January 06, 2011 4:59 AM
> > To: SQLQueriesNoCode@yahoogroups.com
> > Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
> >
> > Ok the problem. I have inherited an SQL Server 2005 database that is
> > tied to a website (Intranet) that is the front end for data entry. On
> > the screens a lot of data is scanned in via hand held barcode readers.
> > The reader software drops the data into <input type='text' />
> > textboxes based on focus. But the textboxes can also have data keyed
> into them.
> > Note before we get much further I cannot alter the database and the
> > data entry screens at present I have limited access to the code to
> > repair the data validation for the problem I am about to tell you
> about.
> >
> >
> >
> > There is a field in the database that is a varchar(50) I think (not in
>
> > front of it right now but sure of the varchar) that in fact is
> > supposed to hold only Integers. If scanning is done right with the
> > proper focus ect, only integers go into this field. The original
> > developer appears to have not considered this might not happen this
> > way every time. Result other non numeric data is in the column. Again
> > I can't actually fix the root of the problem so any solution that
> > involves me writing code to prevent this happening will not at present
>
> > work -and I am not happy about this -.
> >
> >
> >
> > Ok I'm looking for a query that will let me clean this field of non
> > integer values as it is killing a reporting program down the line.  I
> > worked my way though the data yesterday in 40,000+ only found about 50
>
> > entries that were bad. I am looking to set a proc into the database
> > that fix this column that the users can invoke without me.
> >
> >
> >
> > I am OK with replacing non integers with zero or 999. Null won't work
> > for other reasons. I want the consumer of reports to be able to spot
> > rows where there was an issue. Columns in the table
> >
> >
> >
> > OrderNo , cnt, batch, .
> >
> >
> >
> > Cnt is the column the other two will be different for each cnt that is
>
> > cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
> > column) will have different OrderNo batch combinations so you can get
> > to row one's cnt =1 without hitting row 10's.
> >
> >
> >
> >
> > Cnt is the column to clean.  I thought about SELECT * FROM table WHERE
> > ISNUMERIC(cnt) = false; and then some sort of Update.
> >
> >
> >
> > My question is how would you attack this. also note I encountered
> > yesterday .1 and .5 (yes decimals, but they are invalid values so need
>
> > to be reset, I suspect they are keying errors and should be 1 and 5
> > respectively)?
> >
> >
> >
> > Thanks and I hope I'm more clear than mud.
> >
> >
> >
> > John Warner
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> > Disclaimer - January 6, 2011
> > This email and any files transmitted with it are confidential and
> intended solely
> > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
> > addressee you should not disseminate, distribute, copy or alter this
> email. Any
> > views or opinions presented in this email are solely those of the
> > author
> and
> > might not represent those of Westwood Consulting, Inc. Warning:
> > Although Westwood Consulting, Inc has taken reasonable precautions to
> > ensure no viruses are present in this email, the company cannot accept
> responsibility for
> > any loss or damage arising from the use of this email or attachments.
> > This disclaimer was added by Policy Patrol:
> > http://www.policypatrol.com/
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

I understood it to be primarily a 'bad character' issue. Sorting was
secondary.



Regards,

Arnie Rowland, MVP (SQL Server)

"You cannot do a kindness too soon because you never know how soon it
will be too late."
-Ralph Waldo Emerson




-----Original Message-----
From: SQLQueriesNoCode@yahoogroups.com
[mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
Sent: Thursday, January 06, 2011 10:36 AM
To: SQLQueriesNoCode@yahoogroups.com
Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int

Oh, you have misunderstood, it isn't a sorting issue. But let me play
with this as it still may well work just fine.

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
> Sent: Thursday, January 06, 2011 12:53 PM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> John,
>
> Here is a UDF that I created a long time ago. You may be able to use
> it to 'clean' the data, then your queries can be sorted properly.
>
> 	 USE tempdb;
> 	 GO
>
> 	 CREATE FUNCTION dbo.fnNumericOnly
> 		   ( @InParam varchar(50) )
> 		   RETURNS varchar(50)
> 	 AS
> 		   BEGIN
> 		      IF patindex( '%[^0-9]%', @InParam ) > 0
> 		         BEGIN
> 		            WHILE patindex( '%[^0-9]%', @InParam ) > 0
> 		               BEGIN
> 		                  SET @InParam = Stuff( @InParam,
patindex(
> '%[^0-9]%', @InParam), 1, '' )
> 		               END
> 		         END
> 		      RETURN @InParam
> 		   END;
> 	 GO
>
> 	 CREATE TABLE #TestTable
> 		   (  RowId int IDENTITY
> 		   ,  SomeValue varchar(20)
> 		   );
>
> 	 INSERT INTO #TestTable
> 		   (  SomeValue  )
> 		   VALUES
> 		      ( '1' ),
> 		      ( '25x' ),
> 		      ( '.5' ),
> 		      ( 'a2x5m' ),
> 		      ( 'a05' ),
> 		      ( 'b1x5m' );
>
> 	 UPDATE #TestTable
> 		   SET SomeValue = dbo.fnNumericOnly( SomeValue );
>
> 	 SELECT SomeValue
> 	 FROM #TestTable
> 	 ORDER BY cast(SomeValue AS int);
>
> 	 DROP TABLE #TestTable;
> 	 DROP FUNCTION dbo.fnNumericOnly;
> 	 GO
>
> The results will order like this. NOTE: If you wish to eliminate
> leading zeros, that is another small issue but can be easily done.
> 	 SomeValue
> 	 1
> 	 5
> 	 05
> 	 15
> 	 25
> 	 25
>
> Regards,
>
> Arnie Rowland, MVP (SQL Server)
>
> "You cannot do a kindness too soon because you never know how soon it
> will be too late."
> -Ralph Waldo Emerson
>
>
>
>
> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
> Sent: Thursday, January 06, 2011 9:08 AM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
>
> Oh yes, that would be even better.
>
> CAST() further down the road in the process is what is causing me to
> need to 'scrub' the field. What am I missing?
>
>
> Thanks.
>
> John Warner
>
>
> > -----Original Message-----
> > From: SQLQueriesNoCode@yahoogroups.com
> > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie
> Rowland
> > Sent: Thursday, January 06, 2011 12:00 PM
> > To: SQLQueriesNoCode@yahoogroups.com
> > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
> >
> > John,
> >
> > Would it help to remove the non-integer characters and keep any
> > integer characters -or do you wish to just clear out the entire
> > value replacing with a marker?
> >
> > The sorting is not an issue, it is easily accomplished by using a
> > cast() in the ORDER BY clause.
> >
> >
> > Regards,
> >
> > Arnie Rowland, MVP (SQL Server)
> >
> > "You cannot do a kindness too soon because you never know how soon
> > it will be too late."
> > -Ralph Waldo Emerson
> >
> >
> >
> >
> > -----Original Message-----
> > From: SQLQueriesNoCode@yahoogroups.com
> > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
> > Sent: Thursday, January 06, 2011 4:59 AM
> > To: SQLQueriesNoCode@yahoogroups.com
> > Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
> >
> > Ok the problem. I have inherited an SQL Server 2005 database that is

> > tied to a website (Intranet) that is the front end for data entry.
> > On the screens a lot of data is scanned in via hand held barcode
readers.
> > The reader software drops the data into <input type='text' />
> > textboxes based on focus. But the textboxes can also have data keyed
> into them.
> > Note before we get much further I cannot alter the database and the
> > data entry screens at present I have limited access to the code to
> > repair the data validation for the problem I am about to tell you
> about.
> >
> >
> >
> > There is a field in the database that is a varchar(50) I think (not
> > in
>
> > front of it right now but sure of the varchar) that in fact is
> > supposed to hold only Integers. If scanning is done right with the
> > proper focus ect, only integers go into this field. The original
> > developer appears to have not considered this might not happen this
> > way every time. Result other non numeric data is in the column.
> > Again I can't actually fix the root of the problem so any solution
> > that involves me writing code to prevent this happening will not at
> > present
>
> > work -and I am not happy about this -.
> >
> >
> >
> > Ok I'm looking for a query that will let me clean this field of non
> > integer values as it is killing a reporting program down the line.
> > I worked my way though the data yesterday in 40,000+ only found
> > about 50
>
> > entries that were bad. I am looking to set a proc into the database
> > that fix this column that the users can invoke without me.
> >
> >
> >
> > I am OK with replacing non integers with zero or 999. Null won't
> > work for other reasons. I want the consumer of reports to be able to

> > spot rows where there was an issue. Columns in the table
> >
> >
> >
> > OrderNo , cnt, batch, .
> >
> >
> >
> > Cnt is the column the other two will be different for each cnt that
> > is
>
> > cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
> > column) will have different OrderNo batch combinations so you can
> > get to row one's cnt =1 without hitting row 10's.
> >
> >
> >
> >
> > Cnt is the column to clean.  I thought about SELECT * FROM table
> > WHERE
> > ISNUMERIC(cnt) = false; and then some sort of Update.
> >
> >
> >
> > My question is how would you attack this. also note I encountered
> > yesterday .1 and .5 (yes decimals, but they are invalid values so
> > need
>
> > to be reset, I suspect they are keying errors and should be 1 and 5
> > respectively)?
> >
> >
> >
> > Thanks and I hope I'm more clear than mud.
> >
> >
> >
> > John Warner
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> > Disclaimer - January 6, 2011
> > This email and any files transmitted with it are confidential and
> intended solely
> > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
> > addressee you should not disseminate, distribute, copy or alter this
> email. Any
> > views or opinions presented in this email are solely those of the
> > author
> and
> > might not represent those of Westwood Consulting, Inc. Warning:
> > Although Westwood Consulting, Inc has taken reasonable precautions
> > to ensure no viruses are present in this email, the company cannot
> > accept
> responsibility for
> > any loss or damage arising from the use of this email or
attachments.
> > This disclaimer was added by Policy Patrol:
> > http://www.policypatrol.com/
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>



------------------------------------

Yahoo! Groups Links

Dear Friends

I am working with a huge database which has a table named table2 with
4,63,22,505 records and this has only two columns namely ID1 and ID2

I have one more table named USD which has 20 thousand data in it .

So the structure of Table2 goes like this
ID1 Nvarchar
ID2 nvarchar


USD Table

Field3    nvarchar(50)
Field1    nvarchar(50)
Field2    text
Field4    text
Field5    datetime
Field6    numeric(29, 0)
Field7    numeric(29, 0)
Field8    numeric(29, 0)
NoOf     numeric(29, 0)
Desc     text

my query goes like this

select ID2,(Select count (ID2) from table2 where ID2=Field1)
,Field5,Field3,Desc
From  USD
join table2 on ID2=Field1


As the query taking more time i.e., more than 2 hours .

Please help me out solve this .
--
*Thanks & Regards
Avinash Desai
Save Our Environment...  Save Paper.. Reuse Materials..*


Think Before you print: Please consider our environment before printing this
e-mail


[Non-text portions of this message have been removed]

Do you have any indexes in these two tables ?? If not try to create on ID2 of
table2 and Field1 of USD table.... may be this will reduce time

Also try to use group by clause instead of inner query which is returning the
count of ID2


________________________________________________________________________________\
___________________________________________________
Regards,

Farhan Ahmed
Programmer Analyst
The Shams Group
Karachi, Pakistan
(+92) 345 2523688

Do not blame Allah for having created the tiger, but thank him for not having
given it wings.







To: SQLQueriesNoCode@yahoogroups.com
From: avinashdesai10@...
Date: Sun, 16 Jan 2011 11:20:51 +0530
Subject: [SQLQueriesNoCode] Need help in to get data fast






Dear Friends

I am working with a huge database which has a table named table2 with
4,63,22,505 records and this has only two columns namely ID1 and ID2

I have one more table named USD which has 20 thousand data in it .

So the structure of Table2 goes like this
ID1 Nvarchar
ID2 nvarchar

USD Table

Field3 nvarchar(50)
Field1 nvarchar(50)
Field2 text
Field4 text
Field5 datetime
Field6 numeric(29, 0)
Field7 numeric(29, 0)
Field8 numeric(29, 0)
NoOf numeric(29, 0)
Desc text

my query goes like this

select ID2,(Select count (ID2) from table2 where ID2=Field1)
,Field5,Field3,Desc
From USD
join table2 on ID2=Field1

As the query taking more time i.e., more than 2 hours .

Please help me out solve this .
--
*Thanks & Regards
Avinash Desai
Save Our Environment... Save Paper.. Reuse Materials..*

Think Before you print: Please consider our environment before printing this
e-mail

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]

What columns are indexed? Also what RDBMS are we dealing with?

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of ?A???a??
> ???a?T?
> Sent: Sunday, January 16, 2011 12:51 AM
> To: SQL Code group
> Subject: [SQLQueriesNoCode] Need help in to get data fast
>
> Dear Friends
>
> I am working with a huge database which has a table named table2 with
> 4,63,22,505 records and this has only two columns namely ID1 and ID2
>
> I have one more table named USD which has 20 thousand data in it .
>
> So the structure of Table2 goes like this
> ID1 Nvarchar
> ID2 nvarchar
>
>
> USD Table
>
> Field3    nvarchar(50)
> Field1    nvarchar(50)
> Field2    text
> Field4    text
> Field5    datetime
> Field6    numeric(29, 0)
> Field7    numeric(29, 0)
> Field8    numeric(29, 0)
> NoOf     numeric(29, 0)
> Desc     text
>
> my query goes like this
>
> select ID2,(Select count (ID2) from table2 where ID2=Field1)
> ,Field5,Field3,Desc
> From  USD
> join table2 on ID2=Field1
>
>
> As the query taking more time i.e., more than 2 hours .
>
> Please help me out solve this .
> --
> *Thanks & Regards
> Avinash Desai
> Save Our Environment...  Save Paper.. Reuse Materials..*
>
>
> Think Before you print: Please consider our environment before printing
this
> e-mail
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

Please create an index on ID2 for table2 and another on USD table for field1. I
have tables with 9 million rows but return data in less than a miinute because I
use unique primary keys.
Melvyn
Sent from my Verizon Wireless BlackBerry

-----Original Message-----
From: A ™ avinashdesai10@...
Sender: SQLQueriesNoCode@yahoogroups.com
Date: Sun, 16 Jan 2011 11:20:51
To: SQL Code group<SQLQueriesNoCode@yahoogroups.com>
Reply-To: SQLQueriesNoCode@yahoogroups.com
Subject: [SQLQueriesNoCode] Need help in to get data fast

Dear Friends

I am working with a huge database which has a table named table2 with
4,63,22,505 records and this has only two columns namely ID1 and ID2

I have one more table named USD which has 20 thousand data in it .

So the structure of Table2 goes like this
ID1 Nvarchar
ID2 nvarchar


USD Table

Field3    nvarchar(50)
Field1    nvarchar(50)
Field2    text
Field4    text
Field5    datetime
Field6    numeric(29, 0)
Field7    numeric(29, 0)
Field8    numeric(29, 0)
NoOf     numeric(29, 0)
Desc     text

my query goes like this

select ID2,(Select count (ID2) from table2 where ID2=Field1)
,Field5,Field3,Desc
From  USD
join table2 on ID2=Field1


As the query taking more time i.e., more than 2 hours .

Please help me out solve this .
--
*Thanks & Regards
Avinash Desai
Save Our Environment...  Save Paper.. Reuse Materials..*


Think Before you print: Please consider our environment before printing this
e-mail


[Non-text portions of this message have been removed]




[Non-text portions of this message have been removed]

Hi Everyone,

I am having difficulty writing a query or set of queries to do what I need. I
have complete control over this portion of the DB and can change the schema if
needed.

I have these tables:

item_groups
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+

items
+----+----------+------------+
| ID | item_key | item_value |
+----+----------+------------+
|  1 |      201 |          1 |
|  2 |      301 |          4 |
|  3 |      302 |          2 |
|  4 |      305 |          6 |
|  5 |      307 |          8 |
|  6 |      301 |          2 |
|  7 |      302 |          4 |
|  8 |      307 |          6 |
|  9 |      201 |          2 |
| 10 |      205 |          4 |
| 11 |      205 |          2 |
+----+----------+------------+

item_type_A with
CONSTRAINT `FK_GROUPS` FOREIGN KEY (`group`) REFERENCES `item_groups` (`ID`),
   CONSTRAINT `FK_ITEMS` FOREIGN KEY (`item`) REFERENCES `items` (`ID`)
+-------+------+
| group | item |
+-------+------+
|     1 |    1 |
|     2 |    1 |
|     3 |    9 |
|     4 |    1 |
|     5 |    1 |
|     6 |    1 |
+-------+------+

item_type_B with
CONSTRAINT `FK_GROUPS` FOREIGN KEY (`group`) REFERENCES `item_groups` (`ID`),
   CONSTRAINT `FK_ITEMS` FOREIGN KEY (`item`) REFERENCES `items` (`ID`)
+-------+------+
| group | item |
+-------+------+
|     1 |    5 |
|     2 |    2 |
|     2 |    3 |
|     3 |    3 |
|     3 |    2 |
|     4 |    8 |
|     5 |    3 |
|     6 |    2 |
|     6 |    3 |
|     6 |    8 |
+-------+------+

What I need to know is which groups are the same but differ by the count of just
one item, which groups are the same but differ only by the inclusion (or
exclusion) of one item, and which groups are exactly the same. The examples that
follows are hard-coded with values but this would have to be done dynamically.

I can create this view which produces most of the info that I want:
CREATE OR REPLACE VIEW groups AS (
   SELECT ig.id, i.item_key AS A_KEY, i.item_value AS A_VAL, i2.item_key AS
B_KEY, i2.item_value AS B_VAL
   FROM  item_groups ig JOIN item_type_A a ON (a.group = ig.ID)
	       JOIN item_type_B b ON (b.group = ig.ID)
	       JOIN items i ON (i.ID = a.item)
         JOIN items i2 ON (i2.ID = b.item)
);
+----+-------+-------+-------+-------+
| id | A_KEY | A_VAL | B_KEY | B_VAL |
+----+-------+-------+-------+-------+
|  1 |   201 |     1 |   307 |     8 |
|  2 |   201 |     1 |   301 |     4 |
|  2 |   201 |     1 |   302 |     2 |
|  3 |   201 |     2 |   302 |     2 |
|  3 |   201 |     2 |   301 |     4 |
|  4 |   201 |     1 |   307 |     6 |
|  5 |   201 |     1 |   302 |     2 |
|  6 |   201 |     1 |   301 |     4 |
|  6 |   201 |     1 |   302 |     2 |
|  6 |   201 |     1 |   307 |     6 |
+----+-------+-------+-------+-------+

I have written this query which tells me that group 2 is equal to itself which I
want to know, but also returns group 6 which has one more element. So 2 and 6
are NOT a match:

SELECT t1.REF_ID AS ID_X, t2.REF_ID AS ID_Y
FROM  (SELECT g1.ID AS REF_ID, g2.id AS ID2, g1.A_KEY, g1.A_VAL
        FROM groups g1, groups g2
        WHERE g1.B_KEY = 302 AND g1.B_VAL = 2 AND
              g2.B_KEY = 301 AND g2.B_VAL = 4 AND
              g1.ID = g2.ID) t1,
       (SELECT g1.ID AS REF_ID, g2.id AS ID2, g1.A_KEY, g1.A_VAL
        FROM groups g1, groups g2
        WHERE g1.B_KEY = 302 AND g1.B_VAL = 2 AND
              g2.B_KEY = 301 AND g2.B_VAL = 4 AND
              g1.ID = g2.ID) t2
WHERE t1.A_KEY = 201 AND t1.A_VAL = 1 AND
       t2.A_KEY = 201 AND t2.A_VAL = 1;
+------+------+
| ID_X | ID_Y |
+------+------+
|    2 |    2 |
|    6 |    2 |
|    2 |    6 |
|    6 |    6 |
+------+------+

And I have the same problem with this query that tells me correctly that groups
2 & 3 differ by the count of item 201, but also includes group 6 which has too
many elements:

SELECT t1.REF_ID AS ID_X, t2.REF_ID AS ID_Y
FROM  (SELECT g1.ID AS REF_ID, g2.id AS ID2, g1.A_KEY, g1.A_VAL
        FROM groups g1, groups g2
        WHERE g1.B_KEY = 302 AND g1.B_VAL = 2 AND
              g2.B_KEY = 301 AND g2.B_VAL = 4 AND
              g1.ID = g2.ID) t1,
       (SELECT g1.ID AS REF_ID, g2.id AS ID2, g1.A_KEY, g1.A_VAL
        FROM groups g1, groups g2
        WHERE g1.B_KEY = 302 AND g1.B_VAL = 2 AND
              g2.B_KEY = 301 AND g2.B_VAL = 4 AND
              g1.ID = g2.ID) t2
WHERE t1.A_KEY = 201 AND t1.A_VAL = 1 AND
       t2.A_KEY = 201 AND t2.A_VAL <> 1;
+------+------+
| ID_X | ID_Y |
+------+------+
|    2 |    3 |
|    6 |    3 |
+------+------+

I just want to point out again that I will be building these queries and
sub-queries dynamically at run time so I do not know ahead of time the size of
the list. And I have not figured out a way to write a query that returns either
group 2 or 6 because group 2 is the same as 6 except that it is missing one
element.

Is there a better way to write these queries or structure the tables? Am I stuck
with having to re-check the final result set for accuracy?

Thanks in advance!

- Mike.

Off course ur query will take a lot of resource, if like this :

select ID2,(Select count (ID2) from table2 where ID2=Field1)
,Field5,Field3,Desc
From USD
join table2 on ID2=Field1

You've got to change the query
more or less like this
Select count(id2), field5, field3
From usd
Left join table2 on
Table2.id2=usd.field1
Group by table2.id2, field5,field3

Try that TSQL, and has ur table already had primary key and indexes ?

Sent from my BlackBerry®
powered by Sinyal Kuat INDOSAT

-----Original Message-----
From: ☺Aνιηαѕн ∂єѕαι™☺
	 <avinashdesai10@...>
Sender: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
Date: Sun, 16 Jan 2011 12:50:51
To: SQL Code group<SQLQueriesNoCode@yahoogroups.com>
Reply-To: "SQLQueriesNoCode@yahoogroups.com"
	 <SQLQueriesNoCode@yahoogroups.com>
Subject: [SQLQueriesNoCode] Need help in to get data fast



Dear Friends

I am working with a huge database which has a table named table2 with
4,63,22,505 records and this has only two columns namely ID1 and ID2

I have one more table named USD which has 20 thousand data in it .

So the structure of Table2 goes like this
ID1 Nvarchar
ID2 nvarchar

USD Table

Field3 nvarchar(50)
Field1 nvarchar(50)
Field2 text
Field4 text
Field5 datetime
Field6 numeric(29, 0)
Field7 numeric(29, 0)
Field8 numeric(29, 0)
NoOf numeric(29, 0)
Desc text

my query goes like this

select ID2,(Select count (ID2) from table2 where ID2=Field1)
,Field5,Field3,Desc
From USD
join table2 on ID2=Field1

As the query taking more time i.e., more than 2 hours .

Please help me out solve this .
--
*Thanks & Regards
Avinash Desai
Save Our Environment... Save Paper.. Reuse Materials..*

Think Before you print: Please consider our environment before printing this
e-mail

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]

Hello,
 
i am not able to connect local sql server which was getting connected properly
before i installed VS2010 on my machine, now its giving the following error
 
TITLE: Connect to Server
------------------------------
Cannot connect to ETG1\SQLEXPRESS.
------------------------------
ADDITIONAL INFORMATION:
This version of Microsoft SQL Server Management Studio Express can only be used
to connect to SQL Server 2000 and SQL Server 2005 servers.
(Microsoft.SqlServer.Express.ConnectionDlg)
------------------------------
BUTTONS:
OK
------------------------------

though i am connecting to same 2005 instance only, i think some default setting
has been updated during the vs2010 installation.
 
can any one help me please ? 

-Thanks



 
Arindam Chakraborty
Mumbai, India
http://e-tigers.net/
http://etgconsultancy.com/
 



[Non-text portions of this message have been removed]

i don't have seen this error before but it sounds to me like the vs2010
  replaced your sqlexpress2005 with the 2008 version, why don't you try to
verify the version of your sqlserver express

2011/2/16 Arindam <e_arindam@...>

>
>
> Hello,
>
> i am not able to connect local sql server which was getting connected
> properly before i installed VS2010 on my machine, now its giving the
> following error
>
> TITLE: Connect to Server
> ------------------------------
> Cannot connect to ETG1\SQLEXPRESS.
> ------------------------------
> ADDITIONAL INFORMATION:
> This version of Microsoft SQL Server Management Studio Express can only be
> used to connect to SQL Server 2000 and SQL Server 2005 servers.
> (Microsoft.SqlServer.Express.ConnectionDlg)
> ------------------------------
> BUTTONS:
> OK
> ------------------------------
>
> though i am connecting to same 2005 instance only, i think some default
> setting has been updated during the vs2010 installation.
>
> can any one help me please ?
>
> -Thanks
>
>
> Arindam Chakraborty
> Mumbai, India
> http://e-tigers.net/
> http://etgconsultancy.com/
>
>
> [Non-text portions of this message have been removed]
>
>
>


[Non-text portions of this message have been removed]

The SProc takes 3 minutes to do a half million records but I would like much
more speed.


Basically it pulls out data from a Table that has Values


Test Value 1
Test Value 2
Test Value 3
Test Value 1
Test Value 1
Test Value 3
Test Value 1

and turns it into a summary table with count and first and last date and the
last row that fed that summary
                       Count   First                 Last
RawID
Test Value 1   4          mm/dd/yy tttt    mm/dd/yy ttt     bigint
Test Value 2   1
Test Value 3   2


Table LogRaw
==========================
LogRaw_ID     bigint
OccuredWhen  datetime2(7)
LogValue          nvarchar(max,null)
XMLFile            (XML(.),null)

Indexes on LogRaw_ID  of course

Table LogDeDuped
===========================
LogDeDuped_ID   bigint
LogValue (nvarchar(max),not null)
OccuredLast datetime2(7)
OccuredFirst datetime2(7)
LogRaw_ID  bigint
LogValueChecksum (int, not null)

Indexes on LogDeDuped_ID and LogValueChecksum of course.

I use the checksum to get past the nvarchar(max) not being indexable....

The main long running Sproc:
ALTER PROCEDURE [dbo].[LogRawTransferDeduped]
AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;

     DECLARE @HowManyRows bigint;
     SET @HowManyRows =0;

     DECLARE @LogValue nvarchar(max);
     DECLARE @LogValue_ID bigint;

     DECLARE @Deduped_ID bigint;

     DECLARE @Start_LogRaw_Id bigint;

     set @Start_LogRaw_ID=(select max(LogRaw_ID) from LogDeDuped);
     If @Start_LogRaw_ID IS NULL
         BEGIN
             set @Start_LogRaw_ID=1
         END



     DECLARE CursorRaw CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw]
WHERE [LogRaw_ID]>@Start_LogRaw_ID;

     OPEN CursorRaw;

     FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
     PRINT 'Starting at LogRaw_ID=' + CAST (@Start_LogRaw_ID  AS
nvarchar(max));
     WHILE @@FETCH_STATUS = 0
         BEGIN
             SET @HowManyRows=@HowManyRows+1;
             /*PRINT 'LogValue Before=' + @LogValue */
             If @LogValue IS NULL
                 BEGIN
                     SET @LogValue=CAST((Select XMLFile from LogRaw where
LogRaw_ID=@LogValue_ID) AS nvarchar(max))
                 END
             EXEC [dbo].[LogRawTransferRowDedupe]
                 @LogValue = @LogValue,
                 @LogRaw_ID = @LogValue_ID
             FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;

         END
     PRINT 'Processed ' + CAST (@HowManyRows  AS nvarchar(max)) + ' rows';
     CLOSE CursorRaw;
     DEALLOCATE CursorRaw;
END




The Sproc it calls for each summary row:

ALTER PROCEDURE [dbo].[LogRawTransferRowDeDupe]
     @LogValue as nvarchar(max)
     ,@LogRaw_ID as bigint
     /*,@OccuredWhen as datetime2(7) */
AS
BEGIN
     SET NOCOUNT ON;

     DECLARE @LogDeDuped_ID bigint;
     DECLARE @LogValueChecksum int;

     SET @LogValueChecksum = CHECKSUM(@LogValue);

         /*select @LogDeDuped_ID=[LogDeDuped_ID]  FROM [LogDeDuped] where
rtrim(ltrim(@LogValue)) = rtrim(ltrim([LogValue])); */
         select @LogDeDuped_ID=[LogDeDuped_ID]  FROM [LogDeDuped] where
@LogValueChecksum = [LogValueChecksum];
             If @LogDeDuped_ID IS NULL
                 BEGIN
                    INSERT INTO [LogDeDuped]
([LogValue],[OccuredLast],[Frequency],[OccuredFirst],[LogRaw_ID],[LogValueChecks\
um])
VALUES (@LogValue,SYSDATETIME(),1,(select [OccuredWhen] from [LogRaw] where
[LogRaw_ID]=@LogRaw_ID),@LogRaw_ID,CHECKSUM(@LogValue));
                 END
             ELSE
                 BEGIN
                     UPDATE [LogDeDuped] SET [LogValue]=@LogValue,
                          [OccuredLast]= (select [OccuredWhen] from [LogRaw]
where [LogRaw_ID]=@LogRaw_ID),
                          [LogRaw_ID]=@LogRaw_ID, [Frequency]=[Frequency]+1,
[LogValueChecksum]=@LogValueChecksum
                          /* WHERE rtrim(ltrim(@LogValue)) =
rtrim(ltrim([LogValue])); */
                           where @LogValueChecksum = [LogValueChecksum];
                 END

END


[Non-text portions of this message have been removed]

Not sure I follow XMLFile 's purpose but XML inside a cursor loop can be
expensive

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
> Sent: Wednesday, February 23, 2011 1:34 PM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
>
> The SProc takes 3 minutes to do a half million records but I would like
much
> more speed.
>
>
> Basically it pulls out data from a Table that has Values
>
>
> Test Value 1
> Test Value 2
> Test Value 3
> Test Value 1
> Test Value 1
> Test Value 3
> Test Value 1
>
> and turns it into a summary table with count and first and last date and
the
> last row that fed that summary
>                       Count   First                 Last
> RawID
> Test Value 1   4          mm/dd/yy tttt    mm/dd/yy ttt     bigint
> Test Value 2   1
> Test Value 3   2
>
>
> Table LogRaw
> ==========================
> LogRaw_ID     bigint
> OccuredWhen  datetime2(7)
> LogValue          nvarchar(max,null)
> XMLFile            (XML(.),null)
>
> Indexes on LogRaw_ID  of course
>
> Table LogDeDuped
> ===========================
> LogDeDuped_ID   bigint
> LogValue (nvarchar(max),not null)
> OccuredLast datetime2(7)
> OccuredFirst datetime2(7)
> LogRaw_ID  bigint
> LogValueChecksum (int, not null)
>
> Indexes on LogDeDuped_ID and LogValueChecksum of course.
>
> I use the checksum to get past the nvarchar(max) not being indexable....
>
> The main long running Sproc:
> ALTER PROCEDURE [dbo].[LogRawTransferDeduped]
> AS
> BEGIN
>     -- SET NOCOUNT ON added to prevent extra result sets from
>     -- interfering with SELECT statements.
>     SET NOCOUNT ON;
>
>     DECLARE @HowManyRows bigint;
>     SET @HowManyRows =0;
>
>     DECLARE @LogValue nvarchar(max);
>     DECLARE @LogValue_ID bigint;
>
>     DECLARE @Deduped_ID bigint;
>
>     DECLARE @Start_LogRaw_Id bigint;
>
>     set @Start_LogRaw_ID=(select max(LogRaw_ID) from LogDeDuped);
>     If @Start_LogRaw_ID IS NULL
>         BEGIN
>             set @Start_LogRaw_ID=1
>         END
>
>
>
>     DECLARE CursorRaw CURSOR FOR SELECT [LogRaw_ID],[LogValue]
> from [LogRaw]
> WHERE [LogRaw_ID]>@Start_LogRaw_ID;
>
>     OPEN CursorRaw;
>
>     FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
>     PRINT 'Starting at LogRaw_ID=' + CAST (@Start_LogRaw_ID  AS
> nvarchar(max));
>     WHILE @@FETCH_STATUS = 0
>         BEGIN
>             SET @HowManyRows=@HowManyRows+1;
>             /*PRINT 'LogValue Before=' + @LogValue */
>             If @LogValue IS NULL
>                 BEGIN
>                     SET @LogValue=CAST((Select XMLFile from LogRaw where
> LogRaw_ID=@LogValue_ID) AS nvarchar(max))
>                 END
>             EXEC [dbo].[LogRawTransferRowDedupe]
>                 @LogValue = @LogValue,
>                 @LogRaw_ID = @LogValue_ID
>             FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
>
>         END
>     PRINT 'Processed ' + CAST (@HowManyRows  AS nvarchar(max)) + '
> rows';
>     CLOSE CursorRaw;
>     DEALLOCATE CursorRaw;
> END
>
>
>
>
> The Sproc it calls for each summary row:
>
> ALTER PROCEDURE [dbo].[LogRawTransferRowDeDupe]
>     @LogValue as nvarchar(max)
>     ,@LogRaw_ID as bigint
>     /*,@OccuredWhen as datetime2(7) */
> AS
> BEGIN
>     SET NOCOUNT ON;
>
>     DECLARE @LogDeDuped_ID bigint;
>     DECLARE @LogValueChecksum int;
>
>     SET @LogValueChecksum = CHECKSUM(@LogValue);
>
>         /*select @LogDeDuped_ID=[LogDeDuped_ID]  FROM [LogDeDuped]
> where
> rtrim(ltrim(@LogValue)) = rtrim(ltrim([LogValue])); */
>         select @LogDeDuped_ID=[LogDeDuped_ID]  FROM [LogDeDuped]
> where
> @LogValueChecksum = [LogValueChecksum];
>             If @LogDeDuped_ID IS NULL
>                 BEGIN
>                    INSERT INTO [LogDeDuped]
>
([LogValue],[OccuredLast],[Frequency],[OccuredFirst],[LogRaw_ID],[LogValue
> Checksum])
> VALUES (@LogValue,SYSDATETIME(),1,(select [OccuredWhen] from
> [LogRaw] where
> [LogRaw_ID]=@LogRaw_ID),@LogRaw_ID,CHECKSUM(@LogValue));
>                 END
>             ELSE
>                 BEGIN
>                     UPDATE [LogDeDuped] SET [LogValue]=@LogValue,
>                          [OccuredLast]= (select [OccuredWhen] from
[LogRaw]
> where [LogRaw_ID]=@LogRaw_ID),
>                          [LogRaw_ID]=@LogRaw_ID,
[Frequency]=[Frequency]+1,
> [LogValueChecksum]=@LogValueChecksum
>                          /* WHERE rtrim(ltrim(@LogValue)) =
> rtrim(ltrim([LogValue])); */
>                           where @LogValueChecksum = [LogValueChecksum];
>                 END
>
> END
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

Can't you do this (or at least most of it) in a single statement using
GROUP BY?

In my database I've got 49,000 coils in my inventory table.  Each coil
will be processed using one of a small set of cycles.  Among the data
for the coil is its width.  The following query gives me all of the
cycles in my inventory, along with number of coils processed using that
cycle and the smallest and largest width coil that was processed using
each cycle:

select cycle, count(*), min(width), max(width) from inventory
group by cycle

This query works on a PostgreSQL database.  You may have to tweak the
syntax for yours.  Also, I don't know what you mean by "the last row
that fed the summary".

I hope this helps!

RobR

Values are either simple or XMLFile

Test 1   null
Test 2   null
null       XMLFile
null       XMLFile

probably about 1% of the values are XMLFile 99% of the time XMLFile=null and
the other value is used.

So inside the LOOP if I see a NULL value I just fetch the XMLFile field for
that row instead.

I will run the cursor without fetching XMLFile and see how much faster it
goes and it made no difference.

I kind of thought the cursor only involves 2 field fetches i.e.
CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw] WHERE
[LogRaw_ID]>@Start_LogRaw_ID;

But I spend 99% of my time in C# (and occasionally C++) and am rarely in SQL
Server.

On Wed, Feb 23, 2011 at 1:43 PM, John Warner <john@...> wrote:

>
>
> Not sure I follow XMLFile 's purpose but XML inside a cursor loop can be
> expensive
>
> John Warner
>
>
> > -----Original Message-----
> > From: SQLQueriesNoCode@yahoogroups.com
> > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
> > Sent: Wednesday, February 23, 2011 1:34 PM
> > To: SQLQueriesNoCode@yahoogroups.com
> > Subject: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
> >
> > The SProc takes 3 minutes to do a half million records but I would like
> much
> > more speed.
> >
> >
> > Basically it pulls out data from a Table that has Values
> >
> >
> > Test Value 1
> > Test Value 2
> > Test Value 3
> > Test Value 1
> > Test Value 1
> > Test Value 3
> > Test Value 1
> >
> > and turns it into a summary table with count and first and last date and
> the
> > last row that fed that summary
> > Count First Last
> > RawID
> > Test Value 1 4 mm/dd/yy tttt mm/dd/yy ttt bigint
> > Test Value 2 1
> > Test Value 3 2
> >
> >
> > Table LogRaw
> > ==========================
> > LogRaw_ID bigint
> > OccuredWhen datetime2(7)
> > LogValue nvarchar(max,null)
> > XMLFile (XML(.),null)
> >
> > Indexes on LogRaw_ID of course
> >
> > Table LogDeDuped
> > ===========================
> > LogDeDuped_ID bigint
> > LogValue (nvarchar(max),not null)
> > OccuredLast datetime2(7)
> > OccuredFirst datetime2(7)
> > LogRaw_ID bigint
> > LogValueChecksum (int, not null)
> >
> > Indexes on LogDeDuped_ID and LogValueChecksum of course.
> >
> > I use the checksum to get past the nvarchar(max) not being indexable....
> >
> > The main long running Sproc:
> > ALTER PROCEDURE [dbo].[LogRawTransferDeduped]
> > AS
> > BEGIN
> > -- SET NOCOUNT ON added to prevent extra result sets from
> > -- interfering with SELECT statements.
> > SET NOCOUNT ON;
> >
> > DECLARE @HowManyRows bigint;
> > SET @HowManyRows =0;
> >
> > DECLARE @LogValue nvarchar(max);
> > DECLARE @LogValue_ID bigint;
> >
> > DECLARE @Deduped_ID bigint;
> >
> > DECLARE @Start_LogRaw_Id bigint;
> >
> > set @Start_LogRaw_ID=(select max(LogRaw_ID) from LogDeDuped);
> > If @Start_LogRaw_ID IS NULL
> > BEGIN
> > set @Start_LogRaw_ID=1
> > END
> >
> >
> >
> > DECLARE CursorRaw CURSOR FOR SELECT [LogRaw_ID],[LogValue]
> > from [LogRaw]
> > WHERE [LogRaw_ID]>@Start_LogRaw_ID;
> >
> > OPEN CursorRaw;
> >
> > FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
> > PRINT 'Starting at LogRaw_ID=' + CAST (@Start_LogRaw_ID AS
> > nvarchar(max));
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
> > SET @HowManyRows=@HowManyRows+1;
> > /*PRINT 'LogValue Before=' + @LogValue */
> > If @LogValue IS NULL
> > BEGIN
> > SET @LogValue=CAST((Select XMLFile from LogRaw where
> > LogRaw_ID=@LogValue_ID) AS nvarchar(max))
> > END
> > EXEC [dbo].[LogRawTransferRowDedupe]
> > @LogValue = @LogValue,
> > @LogRaw_ID = @LogValue_ID
> > FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
> >
> > END
> > PRINT 'Processed ' + CAST (@HowManyRows AS nvarchar(max)) + '
> > rows';
> > CLOSE CursorRaw;
> > DEALLOCATE CursorRaw;
> > END
> >
> >
> >
> >
> > The Sproc it calls for each summary row:
> >
> > ALTER PROCEDURE [dbo].[LogRawTransferRowDeDupe]
> > @LogValue as nvarchar(max)
> > ,@LogRaw_ID as bigint
> > /*,@OccuredWhen as datetime2(7) */
> > AS
> > BEGIN
> > SET NOCOUNT ON;
> >
> > DECLARE @LogDeDuped_ID bigint;
> > DECLARE @LogValueChecksum int;
> >
> > SET @LogValueChecksum = CHECKSUM(@LogValue);
> >
> > /*select @LogDeDuped_ID=[LogDeDuped_ID] FROM [LogDeDuped]
> > where
> > rtrim(ltrim(@LogValue)) = rtrim(ltrim([LogValue])); */
> > select @LogDeDuped_ID=[LogDeDuped_ID] FROM [LogDeDuped]
> > where
> > @LogValueChecksum = [LogValueChecksum];
> > If @LogDeDuped_ID IS NULL
> > BEGIN
> > INSERT INTO [LogDeDuped]
> >
> ([LogValue],[OccuredLast],[Frequency],[OccuredFirst],[LogRaw_ID],[LogValue
> > Checksum])
> > VALUES (@LogValue,SYSDATETIME(),1,(select [OccuredWhen] from
> > [LogRaw] where
> > [LogRaw_ID]=@LogRaw_ID),@LogRaw_ID,CHECKSUM(@LogValue));
> > END
> > ELSE
> > BEGIN
> > UPDATE [LogDeDuped] SET [LogValue]=@LogValue,
> > [OccuredLast]= (select [OccuredWhen] from
> [LogRaw]
> > where [LogRaw_ID]=@LogRaw_ID),
> > [LogRaw_ID]=@LogRaw_ID,
> [Frequency]=[Frequency]+1,
> > [LogValueChecksum]=@LogValueChecksum
> > /* WHERE rtrim(ltrim(@LogValue)) =
> > rtrim(ltrim([LogValue])); */
> > where @LogValueChecksum = [LogValueChecksum];
> > END
> >
> > END
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
>


[Non-text portions of this message have been removed]

select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First,
MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last  from
TestAutoStoreRPC.dbo.LogRaw
group by LogValue

order by frequency desc

works very well and fast was not sure if it would group by the varchar(max)

Can I use coalesce or some null trick to use XMLFile for LogValue when
LogValue is null?

The GroupBy makes that unlikely although I could just process the XML
differently...

On Wed, Feb 23, 2011 at 1:57 PM, Rob Richardson
<Rob.Richardson@...>wrote:

>
>
> Can't you do this (or at least most of it) in a single statement using
> GROUP BY?
>
> In my database I've got 49,000 coils in my inventory table. Each coil
> will be processed using one of a small set of cycles. Among the data
> for the coil is its width. The following query gives me all of the
> cycles in my inventory, along with number of coils processed using that
> cycle and the smallest and largest width coil that was processed using
> each cycle:
>
> select cycle, count(*), min(width), max(width) from inventory
> group by cycle
>
> This query works on a PostgreSQL database. You may have to tweak the
> syntax for yours. Also, I don't know what you mean by "the last row
> that fed the summary".
>
> I hope this helps!
>
> RobR
>
>


[Non-text portions of this message have been removed]

Well, just a guess and my knee jerk reaction when I see XML within SQL
procs.

John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
> Sent: Wednesday, February 23, 2011 1:58 PM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: Re: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
>
> Values are either simple or XMLFile
>
> Test 1   null
> Test 2   null
> null       XMLFile
> null       XMLFile
>
> probably about 1% of the values are XMLFile 99% of the time XMLFile=null
> and
> the other value is used.
>
> So inside the LOOP if I see a NULL value I just fetch the XMLFile field
for
> that row instead.
>
> I will run the cursor without fetching XMLFile and see how much faster
it
> goes and it made no difference.
>
> I kind of thought the cursor only involves 2 field fetches i.e.
> CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw] WHERE
> [LogRaw_ID]>@Start_LogRaw_ID;
>
> But I spend 99% of my time in C# (and occasionally C++) and am rarely in
> SQL
> Server.
>
> On Wed, Feb 23, 2011 at 1:43 PM, John Warner <john@...> wrote:
>
> >
> >
> > Not sure I follow XMLFile 's purpose but XML inside a cursor loop can
be
> > expensive
> >
> > John Warner
> >

The XMLFile did not make any difference in speed/the cursor almost identical
times. The query:

select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First,
MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last  from
TestAutoStoreRPC.dbo.LogRaw
where LogValue IS NOT NULL
group by LogValue

order by frequency desc

achieves the same thing very fast which makes me happy.

select XMLFILE, count(*) as frequency, MIN(OccuredWhen) AS First,
MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last  from
TestAutoStoreRPC.dbo.LogRaw
where LogValue IS NULL
group by XMLFile

does not work as a UNION candidate but I have some ideas and could always
process the XML separately or actually include the XML file duped in the
nvarchar(max).

Of course I get:
The XML data type cannot be compared or sorted, except when using the IS
NULL operator.


On Wed, Feb 23, 2011 at 2:16 PM, John Warner <john@...> wrote:

>
>
> Well, just a guess and my knee jerk reaction when I see XML within SQL
> procs.
>
>
> John Warner
>
> > -----Original Message-----
> > From: SQLQueriesNoCode@yahoogroups.com
> > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
> > Sent: Wednesday, February 23, 2011 1:58 PM
> > To: SQLQueriesNoCode@yahoogroups.com
> > Subject: Re: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
> >
> > Values are either simple or XMLFile
> >
> > Test 1 null
> > Test 2 null
> > null XMLFile
> > null XMLFile
> >
> > probably about 1% of the values are XMLFile 99% of the time XMLFile=null
> > and
> > the other value is used.
> >
> > So inside the LOOP if I see a NULL value I just fetch the XMLFile field
> for
> > that row instead.
> >
> > I will run the cursor without fetching XMLFile and see how much faster
> it
> > goes and it made no difference.
> >
> > I kind of thought the cursor only involves 2 field fetches i.e.
> > CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw] WHERE
> > [LogRaw_ID]>@Start_LogRaw_ID;
> >
> > But I spend 99% of my time in C# (and occasionally C++) and am rarely in
> > SQL
> > Server.
> >
> > On Wed, Feb 23, 2011 at 1:43 PM, John Warner <john@...> wrote:
> >
> > >
> > >
> > > Not sure I follow XMLFile 's purpose but XML inside a cursor loop can
> be
> > > expensive
> > >
> > > John Warner
> > >
>
>
>


[Non-text portions of this message have been removed]

update TestAutoStoreRPC.dbo.LogRaw Set LogValue=CAST (XMLFile As
nvarchar(max)) where LogValue IS NULL;


select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First,
MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last  from
TestAutoStoreRPC.dbo.LogRaw
where LogValue IS NOT NULL
group by LogValue

order by frequency desc


works GREAT.

Thannnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnks

Sprocs go away and its super fast.

On Wed, Feb 23, 2011 at 2:25 PM, Charles Carroll <911@...> wrote:

> The XMLFile did not make any difference in speed/the cursor almost
> identical times. The query:
>
>
> select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First,
> MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last  from
> TestAutoStoreRPC.dbo.LogRaw
> where LogValue IS NOT NULL
>
> group by LogValue
>
> order by frequency desc
>
> achieves the same thing very fast which makes me happy.
>
> select XMLFILE, count(*) as frequency, MIN(OccuredWhen) AS First,
> MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last  from
> TestAutoStoreRPC.dbo.LogRaw
> where LogValue IS NULL
> group by XMLFile
>
> does not work as a UNION candidate but I have some ideas and could always
> process the XML separately or actually include the XML file duped in the
> nvarchar(max).
>
> Of course I get:
> The XML data type cannot be compared or sorted, except when using the IS
> NULL operator.
>
>
>
> On Wed, Feb 23, 2011 at 2:16 PM, John Warner <john@...> wrote:
>
>>
>>
>> Well, just a guess and my knee jerk reaction when I see XML within SQL
>> procs.
>>
>>
>> John Warner
>>
>> > -----Original Message-----
>> > From: SQLQueriesNoCode@yahoogroups.com
>> > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
>> > Sent: Wednesday, February 23, 2011 1:58 PM
>> > To: SQLQueriesNoCode@yahoogroups.com
>> > Subject: Re: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
>> >
>> > Values are either simple or XMLFile
>> >
>> > Test 1 null
>> > Test 2 null
>> > null XMLFile
>> > null XMLFile
>> >
>> > probably about 1% of the values are XMLFile 99% of the time XMLFile=null
>> > and
>> > the other value is used.
>> >
>> > So inside the LOOP if I see a NULL value I just fetch the XMLFile field
>> for
>> > that row instead.
>> >
>> > I will run the cursor without fetching XMLFile and see how much faster
>> it
>> > goes and it made no difference.
>> >
>> > I kind of thought the cursor only involves 2 field fetches i.e.
>> > CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw] WHERE
>> > [LogRaw_ID]>@Start_LogRaw_ID;
>> >
>> > But I spend 99% of my time in C# (and occasionally C++) and am rarely in
>> > SQL
>> > Server.
>> >
>> > On Wed, Feb 23, 2011 at 1:43 PM, John Warner <john@...> wrote:
>> >
>> > >
>> > >
>> > > Not sure I follow XMLFile 's purpose but XML inside a cursor loop can
>> be
>> > > expensive
>> > >
>> > > John Warner
>> > >
>>
>>
>>
>
>


[Non-text portions of this message have been removed]

ID   KeyName     KeyValue

1     Test1           100
2     Test2            Blah
3     Test3            200
4     Test1            300
5     UserName     Ted Smith
6     DeviceName   Hp 900x
7     DeviceName   Rico 107ex


Need it made into

        Test1      Test2    Test3    UserName    DeviceName
1        100      null       null       null              null
2       null       Blah      null       null              null
3       null       null       200       null              null
4       300      null        null       null              null
5        null       null       null      Ted Smith    null
6        null       null       null       null             Hp 900x
7        null       null       null       null             Rico 107ex


I of course looked hard at Pivot operator googled a lot of samples and read
up on the fact you must build a dynamic PIVOT query since my columns vary a
LOT and there are some more complicated things under the covers....

Something like the Sproc below is close to what I want it gives me the
structure but values are still on left i.e.:

        TestValue           Test1      Test2    Test3    UserName
DeviceName
1              100             0            1           2
1                   0
2             Blah             0            1           2
1                   0
3              200             0            1           2
1                   0
4              300             0            1           2
1                   0
5      Ted Smith            0            1           2
1                  0
6       Hp 900x              0            1           2
1                   0
7       Rico 107ex          0            1           2
1                   0

But the values are not where I want them to be and the 2 dozen samples I
looked at are all over the place and do not show what I am looking for. It
seems like I must use Count(), MAX() or an agrregate but it does not seem
relevant in this situation. Once I solve it this way I know I will have to
go to dynamic SQL because I have some complexities of what pairs to include,
etc.




     SELECT
         *
         FROM [TestPivot]



     PIVOT
     (
         COUNT(KeyName)
         FOR [KeyName] IN
         ([Test1],[Test2],[Test3],[UserName],[DeviceName])
     ) pvt

order by [KeyValue]


[Non-text portions of this message have been removed]

You may need to do the pivot yourself what I mean is build a CASE WHEN
structure. Sort of the pivot table we made in the SQL Server 2000 and
lower days.


John Warner


> -----Original Message-----
> From: SQLQueriesNoCode@yahoogroups.com
> [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
> Sent: Thursday, February 24, 2011 3:51 PM
> To: SQLQueriesNoCode@yahoogroups.com
> Subject: [SQLQueriesNoCode] Pivot Help Enitity Attribute Value style
scenario
>
> ID   KeyName     KeyValue
>
> 1     Test1           100
> 2     Test2            Blah
> 3     Test3            200
> 4     Test1            300
> 5     UserName     Ted Smith
> 6     DeviceName   Hp 900x
> 7     DeviceName   Rico 107ex
>
>
> Need it made into
>
>        Test1      Test2    Test3    UserName    DeviceName
> 1        100      null       null       null              null
> 2       null       Blah      null       null              null
> 3       null       null       200       null              null
> 4       300      null        null       null              null
> 5        null       null       null      Ted Smith    null
> 6        null       null       null       null             Hp 900x
> 7        null       null       null       null             Rico 107ex
>
>
> I of course looked hard at Pivot operator googled a lot of samples and
read
> up on the fact you must build a dynamic PIVOT query since my columns
vary a
> LOT and there are some more complicated things under the covers....
>
> Something like the Sproc below is close to what I want it gives me the
> structure but values are still on left i.e.:
>
>        TestValue           Test1      Test2    Test3    UserName
> DeviceName
> 1              100             0            1           2
> 1                   0
> 2             Blah             0            1           2
> 1                   0
> 3              200             0            1           2
> 1                   0
> 4              300             0            1           2
> 1                   0
> 5      Ted Smith            0            1           2
> 1                  0
> 6       Hp 900x              0            1           2
> 1                   0
> 7       Rico 107ex          0            1           2
> 1                   0
>
> But the values are not where I want them to be and the 2 dozen samples I
> looked at are all over the place and do not show what I am looking for.
It
> seems like I must use Count(), MAX() or an agrregate but it does not
seem
> relevant in this situation. Once I solve it this way I know I will have
to
> go to dynamic SQL because I have some complexities of what pairs to
include,
> etc.
>
>
>
>
>     SELECT
>         *
>         FROM [TestPivot]
>
>
>
>     PIVOT
>     (
>         COUNT(KeyName)
>         FOR [KeyName] IN
>         ([Test1],[Test2],[Test3],[UserName],[DeviceName])
>     ) pvt
>
> order by [KeyValue]
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

So you meant like the one for months at:
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-ex\
amples.aspx
i.e.

SELECT *
FROM (
  SELECT
    YEAR(OrderDate) [Year],
    CASE MONTH(OrderDate)
      WHEN 1 THEN 'January'
      WHEN 2 THEN 'February'
      WHEN 3 THEN 'March'
      WHEN 4 THEN 'April'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'June'
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 8 THEN 'September'
      WHEN 8 THEN 'October'
      WHEN 8 THEN 'November'
      WHEN 8 THEN 'December'
    END as [Month],
    SubTotal
  FROM Sales.SalesOrderHeader
) TableDate
PIVOT (
  SUM(SubTotal)
  FOR [Month] IN (
    [January],[February],[March],[April],
    [May],[June],[July],[August],
    [September],[October],[November],[December]
  )
) PivotTable

What amazes me is this very simple scenario conceptually has not been
solved elegantly in god knows how many versions of SQL Server.... Plus
it seems to me I will be building that SQL and executing it
dynamically not only do my KeyPairs vary they even vary for the report
application.

Some people will want some of the keypairs others will not, so it ends
up being a very dynamic situation. I may have to go with dynamic
CREATE TABLES since I will want to cast the values once not leave them
in nvarchar(n) form in the case of numeric/date/etc. other strongly
typed data.

On Thu, Feb 24, 2011 at 5:30 PM, John Warner <john@...> wrote:
>
>
>
> You may need to do the pivot yourself what I mean is build a CASE WHEN
> structure. Sort of the pivot table we made in the SQL Server 2000 and
> lower days.

ID    TestName     TestValue           StartDate   EndDate
===========================================
1     Test1           100
2     Test2            Blah
3     Test3            200
4     Test1            300
5     UserName     Ted Smith
6     DeviceName   Hp 900x
7     DeviceName   Rico 107ex


Need it made into

ID    Test1      Test2    Test3    UserName    DeviceName  StartDate   EndDate
========================================================
1        100      null       null       null              null
2       null       Blah      null       null              null
3       null       null       200       null              null
4       300      null        null       null              null
5        null       null       null      Ted Smith    null
6        null       null       null       null             Hp 900x
7        null       null       null       null             Rico 107ex

My first instinct is to dynamically build a create table i.e.

REATE TABLE [dbo].[ReportPivot_1](
	 [ReportPivot1_ID] [bigint] IDENTITY(1,1) NOT NULL,
	 [Test1]  int NULL,
	 [Test2]  int NULL,
	 [Test3]  int NULL,
	 [UserName] varchar(31) NULL,
	 [DeviceName] varchar(31) NULL,
	 [StartDate] [datetime2](7) NOT NULL,
	 [EndDate] [datetime2](7) NOT NULL
)


But what is most efficient way to shuttle in data I don't want to use
a CURSOR and billions of INSERT statements though I obviously could
because not only would perf suck it would not be very set oriented but
if I could construct a set based / subquery INSERT great where I just
did in this example n queries in this example 5 i.e.
	 [Test1]
	 [Test2]
	 [Test3]
	 [UserName]
	 [DeviceName]

My assumption is I will be distinct'ing the [TestName] column and for
the sakes of this group lets just assume using that to build 1 create
table and 1 or more INSERTS/SELECT INTO (I assume I need as many as I
have distinct values) .,,,,

What would the correct elegant INSERT / SELECT INTO whatever look like?

(The real application goes through some additional steps because the
Users of app have tables about ignoring KeyPairs, synonyms i.e.
'User', 'Username' could resolve to same keypair even if rows are
labelled differently and the KeyPairs are associated with the correct
data types i.e. dates, ints, nvarchars, currency, etc. to power the
create table data type assignments and casts on the inserts but those
steps are not interesting to solving the main issue.)