CASE /IF-ELSE help - SQL Server Forums

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

Transact-SQL (2000)

CASE /IF-ELSE help

Author

Topic

matticusfinch
Starting Member

9 Posts

Posted - 2005-07-14 : 18:12:30

I have a field, STAY.s_arrefer in which there are several member numbers that, in order to join with my ARACCT table, I have to modify depending on the number of characters.

I have tried CASE statements and IF/ELSE statements and nothing seems to work.

In Access, I was able to do this with the following code.

SELECT (IIf(Len([S_ARREFER])=6,
(IIf(Mid([dbo_stay].[s_arrefer],2,1)=0,Right([dbo_stay].[s_arrefer],4),Right([dbo_stay].[s_arrefer],5))),[s_arrefer])) AS ARCODELEN, dbo_STAY.S_ARREFER
FROM dbo_STAY
WHERE (((dbo_STAY.S_ARREFER)<>"" And (dbo_STAY.S_ARREFER) Is Not Null))

I know I cannot use the IIF statement in SQL, however everything I try is erroring out. Even to make it simple, I can't get LEN to work properly with a less convoluted query as below.

SELECT CASE
WHEN LEN(s_arrefer) = 6 then '6'
WHEN LEN(s_arrefer) < 6 then 'NA'
FROM STAY

Let alone, trying to imbed a second CASE/WHEN inside the above statement incorporating SUBSTRING and RIGHT operators.

I can try and put down all my feeble attempts, but I thought I'd start with this. It's driving me nuts, and I know there's probably a better way, so if anyone has any ideas, you will be my SQL god/goddess.

While trying not to go postal on my pc -

Matt