ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,586 through 9,600 (of 10,143 total)

RE: Traversing Rows

karthikeyan (9/10/2008)
Hi All,
I have one senario.
ID Value
1MAGFX
150
1Comment1
2MALOX
250
2Comment2
3MAGFX
350
3Comment3
I want to display like
1 MAGFX 50 Comments1
2 ...
^{“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 10, 2008 at 8:17 am

#869680
RE: Report # of characters for an integer value

gpeters (9/10/2008)
I was hoping I could convert the float value to string in order use len in my select stmt:
i.e. select len ('-500.55') ...
^{“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 10, 2008 at 8:12 am

#869671
RE: Report # of characters for an integer value

Oh, ok...
[font="Courier New"]SELECT FloatValue, LEN(FloatValue) AS Length
FROM (
   SELECT CAST(1 AS FLOAT) AS FloatValue UNION ALL
   SELECT 22 UNION ALL
   SELECT 333 UNION ALL
   SELECT 4444 UNION ALL
   SELECT -500.55
)...
^{“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 10, 2008 at 7:56 am

#869649
RE: Report # of characters for an integer value

-500.55 isn't an integer value. Can you be more specific?
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 10, 2008 at 7:26 am

#869611
RE: Report # of characters for an integer value

You mean, the length? Like this?
SELECT IntegerValue, LEN(IntegerValue) AS Length
FROM (
SELECT CAST(1 AS INT) AS IntegerValue UNION ALL
SELECT 22 UNION ALL
SELECT 333 UNION ALL
SELECT 4444 ) d
^{“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 10, 2008 at 7:00 am

#869588
RE: String function

Hi Khushbu
You're welcome, thanks for the feedback.
The number of rows will probably matter if there are hundreds of thousands. If the query runs too slowly (and it runs in minutes...
^{“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 10, 2008 at 6:00 am

#869547
RE: String function

Thanks Jeff, and thanks also for posting up your tally table article. One of these days I'll take the time to put them in my sig
INSERT INTO sig SELECT Tally...
^{“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 10, 2008 at 5:50 am

#869541
RE: Date Conversion

If it's varchar then this would do the trick:
[font="Courier New"]DROP TABLE #Test
CREATE TABLE #Test (mmddyyyy VARCHAR(10))
INSERT INTO #Test
SELECT '01/01/2008' UNION ALL
SELECT '31/01/2008' UNION ALL
SELECT '01/02/2008' UNION ALL
SELECT '28/02/2008'
SELECT mmddyyyy,...
^{“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 10, 2008 at 5:32 am

#869532
RE: String function

Yep, you need a tally table:
[font="Courier New"]DROP TABLE #Temp
-- make some sample data
CREATE TABLE #Temp (col1 CHAR(1), col2 VARCHAR(20), col3 VARCHAR(20))
INSERT INTO #Temp (col1, col2, col3)
SELECT 'a', '6-10,4,5', 'hjkj..............' 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

September 10, 2008 at 5:30 am

#869530
RE: Odd Construct in a WHERE clause

Richard Fryar (9/10/2008)
I've just tried this on SQL Server 2005 Standard
select 'x' where 1=0 and 1/0=0
select 'x' where 1/0=0 and 1=0
select 'x' where 1=1 and 1/0=0
select 'x' where 1/0=0...
^{“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 10, 2008 at 5:13 am

#869521
RE: Date Conversion

chad_hainline (9/10/2008)
I have at a table whihc for export purposes to an accounting software needs to have the invoice date in a dd/mm/yyyy format, which is not a problem....
^{“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 10, 2008 at 5:07 am

#869519
RE: Beginning to hate dynamic sql and variables

You're welcome Simon, thanks for the feedback. Have another coffee, it helps 🙂
^{“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 10, 2008 at 4:43 am

#869508
RE: If you can read this...

Christian Buettner (9/9/2008)
..., then we are still alive!
Save the date: 10 September 2008, 07:30 GMT
http://en.wikipedia.org/wiki/Large_Hadron_Collider
http://www.youtube.com/watch?v=s9XotvwgnaY
I just hope that they know what they are doing 😉
They know what they're doing Chris,...
^{“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 10, 2008 at 4:27 am

#869501
RE: Beginning to hate dynamic sql and variables

Hi Simon
@TableName is out of scope of the statement which is supposed to be using it. You need to change your line 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 10, 2008 at 4:23 am

#869498
RE: Use String Function

shuzi (9/9/2008)
so how to retrieve the middle value, e.g. A104 and A101
thanks
The middle value of how many values? Will it always be three?
Looks like this is going 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 9, 2008 at 8:44 am

#869090

Viewing 15 posts - 9,586 through 9,600 (of 10,143 total)