ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,656 through 5,670 (of 10,143 total)

RE: Order by with case

rajawat.niranjan (9/13/2012)
Thanks @chrism-2@Work
It worked 🙂
But what was wrong was is null sort or something else?
Each option in the CASE construct, including the ELSE, must have the same datatype - otherwise...
^{“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 14, 2012 at 2:01 am

#1537666
RE: Temp table - table scan

BeginnerBug (9/13/2012)
Yes.. But First name or MiddleNamee or Lastname would differ... I simulated my actual table into this one.. Thanks brown.. i will try to create clustered index on empno...
^{“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 14, 2012 at 1:45 am

#1537659
RE: Temp table - table scan

BeginnerBug (9/13/2012)
Thanks Brown!!!.. but i cant make column empno as autoincreament as per requirement....but i can additionally add one more column and make it auto increment. will that avoid table...
^{“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 13, 2012 at 9:56 am

#1537308
RE: encrypt password in sql server database

aaron.reese (9/13/2012)
...
Why do you need to be able to unencrypt the encoded data?
Interesting point - if you can't decrypt it, is there any argument for keeping it?
^{“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 13, 2012 at 9:48 am

#1537304
RE: Order by with case

Try this:
declare @var int =2
select ClientInternalID, ClientName, NULL ParentInternalID
from TB_Client cl
CROSS APPLY (
SELECT OrderBy = CASE @var
WHEN 1 THEN CAST(ClientInternalID AS VARCHAR(10))
WHEN 2 THEN CAST(ClientName AS VARCHAR(10))
WHEN 4 THEN...
^{“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 13, 2012 at 9:09 am

#1537276
RE: UniqueIdentifier as a Primary Key

ScottPletcher (9/12/2012)
There is NO case where I would ever want to use a GUID as a clustered key column.
It would have to be THE ONLY WAY POSSIBLE for me 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

September 13, 2012 at 8:49 am

#1537260
RE: Blank the data in row from Position 50-74

Lynn Pettis (9/13/2012)
ChrisM@Work (9/13/2012)
STUFF() is perfect for this:
SELECT
Data,
ChangedData = STUFF(Data,50,25,SPACE(25))
FROM ( -- note: '[' is position 49 and ']' is position 75
SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'
) d
WHERE LEFT(Data,8)...
^{“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 13, 2012 at 7:54 am

#1537209
RE: Slow return of data to program

m.henly (9/13/2012)
Thanks for your time, will get those on and have a play around with it.
Cheers,
...
^{“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 13, 2012 at 7:21 am

#1537182
RE: Blank the data in row from Position 50-74

STUFF() is perfect for this:
SELECT
Data,
ChangedData = STUFF(Data,50,25,SPACE(25))
FROM ( -- note: '[' is position 49 and ']' is position 75
SELECT Data = 'ZY0503400000234567891234567890110000000000088975[_5668550000000XXXXXXXXXXX]'
) d
WHERE LEFT(Data,8) = 'ZY050340'
^{“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 13, 2012 at 7:13 am

#1537179
RE: Minimum Value 3 Fields > 0

dwain.c (9/13/2012)
ChrisM@Work (9/13/2012)
SELECT Id, A, B, C, MinVal FROM #MinAmt CROSS APPLY ( SELECT MIN(a) FROM (VALUES (A),(B),(C) ) v (a) WHERE a > 0
) x (MinVal)
😉
And the referee throws down...
^{“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 13, 2012 at 5:11 am

#1537132
RE: Order by with case

Eugene Elutin (9/13/2012)
Man, you do really need to read the previous posts.
It's only going to work if you use dynamic sql.
Please note, if you run the following type of...
^{“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 13, 2012 at 4:29 am

#1537118
RE: Update using Max() value on multi join query

UPDATE tr SET
FV_Flag = 'N',
EX16 = ao.EX16
FROM Results tr
CROSS APPLY (
SELECT TOP 1
ao.EX16,
ao.Date_Effective
FROM AddOns ao
WHERE ao.Customer = tr.CustBookId
AND ao.FmNo =...
^{“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 13, 2012 at 3:02 am

#1537081
RE: Filling Buckets

dwain.c (9/11/2012)
ChrisM@Work (9/11/2012)
Using the sample data provided, a simple calculation rCTE works fine with both positive and negative numbers.
DECLARE @AmountToAllocate INT = 21
;WITH Calculator AS (
SELECT
BucketID, TotalSize, Amount,
AmountLeftToAllocate...
^{“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 13, 2012 at 2:41 am

#1537070
RE: Minimum Value 3 Fields > 0

SELECT
Id, A, B, C,
MinVal
FROM #MinAmt
CROSS APPLY (
SELECT MIN(a)
FROM (VALUES (A),(B),(C) ) v (a)
WHERE a > 0
) x (MinVal)
😉
^{“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 13, 2012 at 2:31 am

#1537065
RE: Today's Random Word!

SQLRNNR (9/12/2012)
premeditated
Premedicated
^{“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 13, 2012 at 1:50 am

#1537044

Viewing 15 posts - 5,656 through 5,670 (of 10,143 total)