ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,601 through 9,615 (of 10,143 total)

RE: Populating a sort order column based on string data

something like this?
DROP TABLE #buildings
CREATE TABLE #buildings (building int, unit varchar(4), sort_order int)
INSERT INTO #buildings (building, unit, sort_order)
SELECT 1, '101', 1 UNION ALL
SELECT 1, '102', 2 UNION ALL
SELECT 1, '103',...
^{“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

September 9, 2008 at 8:15 am

#869069
RE: Conditional Select

You're welcome Chris, thanks for the feedback!
^{“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

September 9, 2008 at 4:21 am

#868927
RE: Conditional Select

Hi Chris
IF is for conditional execution of statements within a batch. Use CASE for conditions within a statement. Check out CASE in BOL, there are two types, simple and searched....
^{“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

September 9, 2008 at 4:04 am

#868918
RE: patindex function

Hey Matt, see also
http://www.sqlservercentral.com/Forums/Topic565548-8-1.aspx?Update=1
Cheers
ChrisM
^{“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

September 8, 2008 at 10:27 am

#868585
RE: Use String Function

Hi Chris
It's a kinda cross-post, Matt is dealing with it too:
http://www.sqlservercentral.com/Forums/Topic565481-8-1.aspx
Cheers
ChrisM
^{“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

September 8, 2008 at 10:26 am

#868583
RE: patindex function

Could be significant with OP's data too:
SELECT note,
PATINDEX('%,[0-9][0-9],',note) AS A,
PATINDEX('%[0-9][0-9]',note) AS B,
...
^{“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

September 8, 2008 at 10:18 am

#868574
RE: patindex function

Does it work? What's it supposed to do? It doesn't return results where ',[0-9][0-9],' is in the rightmost part of the column...
SELECT note,
PATINDEX('%,[0-9][0-9],',note) AS A,
PATINDEX('%[0-9][0-9]',note) AS B,
SUBSTRING(note, PATINDEX('%[0-9][0-9]',note),...
^{“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

September 8, 2008 at 9:38 am

#868537
RE: Randomly Assigning values to a column in table

That's because you're expecting this section...
(SELECT TOP 1 TargetID
FROM ##T2 where TargetID not in...
^{“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

September 8, 2008 at 9:24 am

#868515
RE: Test SQL version in a stored procedure?

You could account for the spaces as follows. There's a system proc too:
[font="Courier New"]DECLARE @cVersion VARCHAR(5)
SELECT @cVersion = CASE WHEN UPPER(REPLACE(@@version, ' ', ' ')) LIKE 'MICROSOFT SQL SERVER 2000 %'...
^{“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

September 8, 2008 at 9:07 am

#868503
RE: Test SQL version in a stored procedure?

Hi David
@@VERSION:
DECLARE @cVersion VARCHAR(200)
SELECT @cVersion = @@version
SELECT SUBSTRING(@cVersion, 23, 4)
...you could use conditional processing on the result.
Cheers
ChrisM
^{“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

September 8, 2008 at 8:27 am

#868476
RE: Randomly Assigning values to a column in table

[font="Courier New"]-- Set up some test data
CREATE TABLE ##T2 (TargetID smallint)
INSERT INTO ##T2
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6...
^{“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

September 8, 2008 at 7:22 am

#868406
RE: Randomly Assigning values to a column in table

bhuvnesh.dogra (9/8/2008)
On what basis you want to update targetid in T2 from T1
there shud be some criteria or condition 😉
Random - that's the point.
It looks very like 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

September 8, 2008 at 6:48 am

#868387
RE: Giving "Invalid column Name " error

Bhuvnesh,
SQL Server detects that the columns are missing before running the code, but doesn't see that the new columns are added to the table in step 2.
However, you don't need...
^{“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

September 8, 2008 at 2:33 am

#868324
RE: Query

Something like this?
[font="Courier New"]SELECT CASE WHEN LEFT(strLinesDate, 2) = 'PC'
   THEN REPLACE(strLinesDate,
       LEFT(strLinesDate, 11),
       'P  ' + SUBSTRING(strLinesDate,4,8) + ', ' + 'C  ' + SUBSTRING(strLinesDate,4,8))
   ELSE strLinesDate END...
^{“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

September 5, 2008 at 8:52 am

#867831
RE: Ping David Jackson

David Jackson (9/5/2008)
I know this is nearly two years late, :w00t: but I've just spotted this. What an excellent addition. I'll test this avro and post it with due...
^{“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

September 5, 2008 at 7:27 am

#867744

Viewing 15 posts - 9,601 through 9,615 (of 10,143 total)