REPLACE first character when...?

Ron Howerton · January 26th, 2010, 05:27 PM

Having trouble writing a complex select. This is what I have at this point:

RIGHTOUTERJOIN
Common.dbo.T_pin ON OfficeInfo.dbo.vwTestS8DDS.OfficeCode = Common.dbo.T_pin.FOC

In brief, I want to check the value Common.T_Pin.FOC, and if the first character='R' I want to change it to 'F'. I then want to use that altered value in the join. It doesn't seem to like a reference to a CASE function in the JOIN. Nor can I create a variable, because the result of this is a view.

Clues?

Old Pedant · January 26th, 2010, 09:34 PM

So why not create a VIEW of Common.dob.T_pin that has the FOC already fixed up as you want it??

That is:

Code:

CREATE VIEW whatever 
AS
SELECT a,b,c,
       (CASE WHEN LEFT(FOC,1) = 'R'
                  THEN 'F' + SUBSTRING(FOC,2,LEN(FOC)-1)
                  ELSE FOC
        END) AS FOC
FROM Common.dbo.T_pin

And then use that in your JOIN??

Ron Howerton · January 27th, 2010, 04:49 PM

Thanks for your response, Old Pedant! I'd considered the idea before posting my question, but the view I'm creating is being consumed by yet another view, and that view is already occasionally timing out with simple selects in the Management Studio. I was concerned another level of nested views could degrade performance significantly. But as this appears to be the only solution, I will give it a shot.

Thanks again!

PS - If anybody from P2P is following this, email notification of responses to this thread appears to have failed.