ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,421 through 6,435 (of 10,143 total)

RE: Update with Inner Join

How many rows are in the update target table [MOHG_Processing_TestData].[dbo].[Stage2test]?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 8:34 am

#1416731
RE: Evaluating PIN codes

The alphanumeric bit sounds interesting. Here's my take on generating a list of insecure PINs:
SELECT
x.PIN, y.InValidCode
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 6:48 am

#1416656
RE: Update with Inner Join

martin.kerr 34088 (12/5/2011)
yes i have and there are 75k that have not been updated
If you run the original SELECT...
select *
FROM MOHG_Processing_TestData.dbo.Stage2test
INNER JOIN MOHG_Processing_TestData.dbo.Country_Transform
ON MOHG_Processing_TestData.dbo.Stage2test.Address1_City = MOHG_Processing_TestData.dbo.Country_Transform.DQCorrection COLLATE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 5:42 am

#1416617
RE: Update with Inner Join

Have you checked the target table after the update to see if any values remain unchanged?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 5:33 am

#1416611
RE: Evaluating PIN codes

okbangas (12/5/2011)
As being only 4 digits, any pin code is rather unsecure. If you want to eliminate "unsecure" pin codes, you'll eliminate quite a few pin codes, and thus making...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 5:31 am

#1416610
RE: Update with Inner Join

martin.kerr 34088 (12/5/2011)
but even with the joining table de-duped it still produces the same count. Does it matter if the original table i am trying to update to NULL has...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 5:21 am

#1416600
RE: Update with Inner Join

martin.kerr 34088 (12/5/2011)
so are you saying my update statement will never update what i want to (i.e. the select statement)
It will update just fine, but it's important for you to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 4:33 am

#1416570
RE: Update with Inner Join

It doesn't matter if the staging table (the update target) has multiple rows which match the search predicate of the update.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 4:21 am

#1416564
RE: Update with Inner Join

-- This query will show you which of the cities (which have a match in the staging table)
-- are duped in the source table Country_Transform
-- Query uses WHERE EXISTS to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 4:12 am

#1416559
RE: Update with Inner Join

martin.kerr 34088 (12/5/2011)
thanks for this, but both of the statement run fine but i am gaining different results when i am expecting the same number of records
Did you run the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 3:54 am

#1416544
RE: Update with Inner Join

Looks like you have multiple matching rows in the country_transform table. Test with this:
SELECT *
FROM MOHG_Processing_TestData.dbo.Stage2test t
INNER JOIN (SELECT DISTINCT DQCorrection FROM MOHG_Processing_TestData.dbo.Country_Transform) s
ON t.Address1_City = s.DQCorrection...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 5, 2011 at 3:49 am

#1416542
RE: Indexing queries

kyle.doouss (12/2/2011)
Thanks for that. Something you might be able to help me with is if you look at a view in design it messes up the format.
Is there a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 2, 2011 at 4:51 am

#1415803
RE: Indexing queries

kyle.doouss (12/2/2011)
Sorry to be a while coming back. I have had a problem with my web.
Execution plan attached. If you walk me through this one then it will...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 2, 2011 at 3:32 am

#1415781
RE: Assign a string to a variable that contains a Quote '

DECLARE @Suffix VARCHAR(12)
SET @Suffix = ')'') AS DB2'
SELECT @Suffix
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2011 at 8:39 am

#1415317
RE: how to join two tables with non equal conditions?

Slightly tested:
SELECT aYr, bYr, aCol, bCol
FROM #A a
CROSS APPLY (
SELECT MAX_bYr = MAX(bYr) FROM #b) x
INNER JOIN #b b
ON b.bYr = CASE WHEN a.aYr <= MAX_bYr THEN a.aYr ELSE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

December 1, 2011 at 5:22 am

#1415175

Viewing 15 posts - 6,421 through 6,435 (of 10,143 total)