Ah so ! Subtle ! - or - Ahhhh ! So subtle !

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ah so ! Subtle ! - or - Ahhhh ! So subtle !

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-03 : 10:35:53

In this thread, I was stumped with a numeric conversion error.

The query causing the error was discussed in another thread. The query itself appears below

-- Process the Assignments
INSERT INTO dbo.Assignments (UserID, CourseID)
	SELECT	UC.UserID, UC.CourseID
		FROM	(
			SELECT	U2.UserID,  -- Rowset of UserID and CourseID from UserImport CSV string 'Assignments'
				CAST(SUBSTRING(' ' + Assignments + ' ', ID, CHARINDEX(' ', ' ' + Assignments + ' ', ID)-ID) AS INT) AS CourseID
				FROM dbo.UsersImport UI
				CROSS JOIN dbo.Tally
				INNER JOIN (SELECT UserID, Username FROM dbo.Users WHERE ClientID = @ClientID) U2 ON U2.Username = UI.Username
				WHERE	ID BETWEEN 2 AND Len(' ' + Assignments + ' ') -- Limit the rows used in the Tally table 
				AND	SUBSTRING( ' ' + Assignments + ' ' , ID - 1, 1) = ' '
				AND	ISNUMERIC(SUBSTRING(' ' + Assignments + ' ', ID, CHARINDEX(' ', ' ' + Assignments + ' ', ID)-ID)) = 1 -- Ensure this is a valid integer
			) UC
		LEFT OUTER JOIN dbo.Assignments AI ON AI.UserID = UC.UserID AND AI.CourseID = UC.CourseID
		WHERE	AI.UserID IS NULL

(PS: Anyone know how to make the code font larger?)

While debugging the numeric conversion error, I had eliminated all the conversions of CourseID to INT and it was still pulling the conversion error.

I found it, but it took time because I was *looking for bugs in all the wrong places* (sorry).

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 11:40:02

Reminds me of a song..

quote:

Keep on looking now
You gotta keep on looking now
Keep on looking now

You're looking for bugs
In all the wrong places

Where your walk it's always shadow
Conversation always shallow
When they talk they never look you in the eye
They look over your shoulder
To faces even colder
And you feel a little older
Every time

You're looking for [bugs]
In all the wrong places

When you're looking for reaction
When you're searching for direction
When you're scared of rejection
Or attack
You need the warmth of buging
When you're growing tired of seeing
A colourless reflection
Looking back

You're looking for bugs
In all the wrong places

Something real to fill those little empty spaces
So you're looking for bugs in all the wrong places

You need understanding
You need a home
All those people so alone
You need understanding
You need a home
All those people so alone

You're looking for bugs
In all the wrong places
You're looking for bugs
In all the wrong places

You better keep on looking now!

And so the evening shade will fall
Where nameless voices call and call
And think of all the friends you made
While you toast with Prozac and lemonade

You can hold me under septic skies
(You can dream to your own places)
Watch the sun set in my eyes
(You can't seem to put)
Could this be the time to die?
(A name to all the faces)
The wind upon your face
(Something real)

So you're looking for bugs in all the wrong places
You keep on looking for bugs in all the wrong places

Dream and take away the tears
(Keep on looking now)
Drift on to where your heart is numb
(You gotta keep on looking now)
It's bugly there where bugs becomes
A drug to fill your need
Just believe in you!
And learn to bug yourself
Before anyone else

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources