ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,676 through 9,690 (of 10,143 total)

RE: Sorting value in a column

Christopher Stobbs (8/7/2008)
HI Chris,
Your solution does not work.
The TOP 100 PERCENT is messing with your results.
If you make TOP 100000 then it works
Interesting...it works with the (albeit limited) set 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

August 7, 2008 at 6:07 am

#854378
RE: Sorting value in a column

-- Create a function
CREATE FUNCTION [dbo].[uftSortString]
(
@String NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @ReturnValue NVARCHAR(20)
SET @ReturnValue = ''
SELECT @ReturnValue = @ReturnValue + d.TheValue
FROM (SELECT TOP 100 PERCENT CAST(SUBSTRING(@String,number,1) AS CHAR(1)) AS...
^{“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

August 7, 2008 at 5:27 am

#854359
RE: Sorting value in a column

Hello
You want to sort the data in the string, as opposed to sorting the column.
So the value '132' becomes '123' etc
What is the maximum length of string values found 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

August 7, 2008 at 4:27 am

#854335
RE: if-else

It's still really difficult to tell what's supposed to be happening here because the use of variables doesn't necessarily make any sense. It would help people a great deal if...
^{“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

August 6, 2008 at 1:57 am

#853657
RE: IF-ELSE Syntax error

You need a BEGIN / END block between IF and ELSE if the IF is followed by more than one statement.
^{“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

August 5, 2008 at 9:15 am

#853254
RE: Inserting Top 1 Record

jeordonez (8/4/2008)
Gianluca,
Almost there, unfortunately we are not on SQL 2005 yet so ROW_NUMBER() doesn't work.. :(. is there an equivalent to this for 2000???
Yep:
SELECT i.*
FROM #InventoryStage i
INNER JOIN (SELECT...
^{“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

August 4, 2008 at 12:55 pm

#852863
RE: Inserting Top 1 Record

Hi Jeordonez
A picture tells a thousand words...
CREATE TABLE #InventoryStage(
[SerialNumber] [varchar](20) NULL,
[TagNumber] [varchar](8) NULL,
...
^{“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

August 4, 2008 at 10:35 am

#852785
RE: Date Range - Weeks

DROP TABLE #Dates
CREATE TABLE #Dates (CreateDate DATETIME)
INSERT INTO #Dates (CreateDate)
SELECT '01/01/2008' UNION ALL
SELECT '02/01/2008' UNION ALL
SELECT '03/01/2008' UNION ALL
SELECT '04/01/2008' UNION ALL
SELECT '05/01/2008' UNION ALL
SELECT '06/01/2008' UNION ALL
SELECT '07/01/2008' UNION...
^{“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

August 4, 2008 at 10:10 am

#852776
RE: function with alphabet

You're welcome :unsure::ermm:
^{“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

August 4, 2008 at 9:38 am

#852757
RE: function with alphabet

declare @Return varchar(30), @String varchar(30)
set @Return = ''
set @String = 'CHARLES H#L6CAL JOEL4'
SELECT @Return = @Return + SUBSTRING(@String,number,1)
FROM dbo.Numbers WITH (NOLOCK)
WHERE number <= LEN(@String)
AND UPPER(SUBSTRING(@String,number,1)) LIKE '%[^A-Z]%'
SELECT CASE WHEN...
^{“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

August 4, 2008 at 9:16 am

#852742
RE: function with alphabet

How about a little more information? What do you want to do if the string contains something other than a letter? Such as
report an error
not import the row into 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

August 4, 2008 at 9:00 am

#852733
RE: insert - help!

You're welcome Jude, 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

August 4, 2008 at 8:38 am

#852720
RE: insert - help!

Test the SELECT on its own:
SELECT c.academic_year, c.faculty, c.faculty_description, c.department_code,
c.department_description, c.course_level_code, c.location_of_study, c.location_of_study_description,
c.franchise_flag, c.source_of_funding_hesa, c.Residential_Status_Code, c.residential_status_description,
c.Course_Code, c.course_mode_of_study_hesa
FROM tblStudent_Course c
INNER JOIN tblNew_Entrants n
...
^{“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

August 4, 2008 at 8:03 am

#852697
RE: insert - help!

Hi Jude
Can you please post the code you ran, with the error message?
Please also post:
The list of columns you want populated in the registration table
The list of columns from 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

August 4, 2008 at 7:45 am

#852681
RE: The Daily Commute

torpkev (8/4/2008)
selling my kidney for gas money.. i think i may go bug em about it
:hehe: Admirably laid-back attitude Kev!
^{“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

August 4, 2008 at 7:17 am

#852657

Viewing 15 posts - 9,676 through 9,690 (of 10,143 total)