Problem with this script, need help!

Question

Problem with this script, need help!

leo.medina

Valued Member

Points: 57
More actions
October 14, 2008 at 8:25 pm

#199775

Hi,
I'm receiving this error
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
when I run he script below. The weird thing is that when I remove the last where criteria it works just fine. Can you advise if this is a syntax error or what? Any help is appreaciated!
Select top 10 det.*, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(ISNULL(EMP_NAME, ''))), '.', ''),' ',''),CHAR(39), ''),CHAR(96),''),'-',''), REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(hr.F_NAME, CHARINDEX(CHAR(32), hr.F_NAME) - 1) + hr.L_NAME, '.',''),' ',''), CHAR(39), ''),CHAR(96),''),'-','')
FROM cg_emsp.dbo.GICS_WWDS_08 DET
JOIN CG_STORE.DBO.GLOBAL_HR hr ON right('0000000000' + DET.GEID_SAID, 10) = hr.GEID
WHERE --DET.EMP_GEID IS NULL
ISNULL(EMP_NAME, '') <> ''
AND ISNULL(hr.f_name, '')<> ''
AND CHARINDEX(CHAR(32), hr.F_NAME)-1 > 0
AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(ISNULL(EMP_NAME, ''))), '.', ''),' ',''),CHAR(39), ''),CHAR(96),''),'-','') = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(hr.F_NAME, CHARINDEX(CHAR(32), hr.F_NAME) - 1) + hr.L_NAME, '.',''),' ',''), CHAR(39), ''),CHAR(96),''),'-','')
thanks!
Leo

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

If FName has no spaces, the CHARINDEX will return 0 and then you subtract 1 to make the operand of LEFT a negative number. You need to check for FNAME+' '

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

leo.medina Valued Member Points: 57 More actions · Answer 2

Hi

thanks for your reply! We have a criteria (second to the last) to check for this. Is this not correct?

thanks!

Leo

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Heh.. yeah... you're right. Still looking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Guess I don't see it since you have the same thing in the SELECT list... should fail there, too. Maybe, I've just not had enough coffee today. Sorry, but without some actual test tables and data, I'm just not seeing it.

DECLARE @F_Name VARCHAR(20)

DECLARE @L_Name VARCHAR(20)

SET @F_Name = ''

SET @L_Name = 'moden'

SELECT CHARINDEX(CHAR(32), @F_Name)-1

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(@F_NAME, CHARINDEX(CHAR(32), @F_NAME) - 1) + @L_NAME, '.',''),' ',''), CHAR(39), ''),CHAR(96),''),'-','')

WHERE CHARINDEX(CHAR(32), @F_Name)-1 >0

The fault must be somewhere else of you've discovered a predicate bug like the great "IS NOT NULL" bug of 2004.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

leo.medina Valued Member Points: 57 More actions · Answer 5

You're right we're also asking ourselves why the select does not give us any error and here we see actual results with ten records. It's just weird!!!

Anyways, thanks for your help man! By the way, where are you from? We're from Manila, Philippines.

thanks!

Leo

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Perhaps the problem is that SQL isn't quite Lisp?

Heh. Just kidding 😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

leo.medina (10/14/2008)
You're right we're also asking ourselves why the select does not give us any error and here we see actual results with ten records. It's just weird!!!
Anyways, thanks for your help man! By the way, where are you from? We're from Manila, Philippines.
thanks!
Leo

Deeeeeeeetroit, Michigan. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

leo.medina (10/14/2008)
We have a criteria (second to the last) to check for this. Is this not correct?

Actually, no.

The problem is that the order of AND sub-clauses in the WHERE clause does not determine their order of evaluation (the optimizer does that). So what is happening is that your last WHERE criterion is being evaluated before your next to last criterion can short-circuit it out.

The reason that you do not get an error in your SELECT column list is that it is always evaluated after the WHERE section, so your next to last criterion is effective in cutting out the error cases there.

Heh. So it turns out that you were right after all, Jeff.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 9

Heh... I gotta get it through my head, someday... order of predicates is not guaranteed to be in same order listed. I keep treating the bloody things like a CASE statement and they just don't work that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

It's a naturally seductive trap, Jeff. I find myself making the same mistake all the time. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]