Different Char behaviour on Case statement

[robert030975]

HI All

Have an issue which has taken me a while to figure out where its going wrong however I'm interested to know why. As my black arts ERP team claim to know nothing I pretty sure there has to be a behavioural change or something!

My table is i_st_stock which is an import from the EPR system with the columns in question

fullcode Char(60)null
identifier Char (1) null

Values
'P3W3065/P157/10 ', 'P'
'P2P1160/P100/12 ', 'W'

On this basis if I select

Select distinct identifier, LEFT(i_st_stock.fullcode, 1)FP , CASE WHEN i_st_stock.identifier IN ('L', 'M', 'W', 'P', 'R') AND LEFT(i_st_stock.fullcode, 1) = i_st_stock.identifier
THEN substring(i_st_stock.fullcode, 2, 1) + LEFT(i_st_stock.fullcode, 1)
ELSE LEFT(i_st_stock.fullcode, 2) END FPS
FROM i_st_stock where
fullcode like 'P2%'

Results

W P P2


Select distinct identifier, LEFT(i_st_stock.fullcode, 1)FP , CASE WHEN i_st_stock.identifier IN ('L', 'M', 'W', 'P', 'R') AND LEFT(i_st_stock.fullcode, 1) = i_st_stock.identifier
THEN substring(i_st_stock.fullcode, 2, 1) + LEFT(i_st_stock.fullcode, 1)
ELSE LEFT(i_st_stock.fullcode, 2) END FPS
FROM i_st_stock where
fullcode like 'P3%'

Results
W P 3P


First question is why the difference P2 and 3P?

This case statement forms part of a join so I could remove the case statment and just use (i_st_stock.fullcode, 2) but I need to prove why its not working

Many Thanks


Robert

 

[bborissov]

Because [W] is not EQUAL to [P]

CASE WHEN identifier IN ('L', 'M', 'W', 'P', 'R') AND
 -- Here in the first SELECT  [P] = [P]
 -- in the second you compare [W] = [P]
          LEFT(fullcode, 1) = identifier
     THEN substring(fullcode, 2, 1) + LEFT(fullcode, 1)
     ELSE LEFT(fullcode, 2) END FPS

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[robert030975]

Thanks!!

Its been a while since I've picked up SQL in anger and could not see the wood for the trees. What makes me interested is why neither my dba nor the consultancy team who built it could spot this fundamental flaw. Lessons to be learned me thinks.

 

[gmmastros]

What makes me interested is why neither my dba nor the consultancy team who built it could spot this fundamental flaw.

You shouldn't try to compare Boris to any member of your team. Boris truly is a SQL Superstar.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

O!
George, thank you!
I'm really flattered. But if I am a Superstar, then I have no words to describe you!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.