August 12, 2014 at 11:17 am
I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error. Any pointers or links of how to accomplish this would be great. I am also including example of how the account_numbers are formatted.
R222209
R222220
R222222
R222212
R221123
F707768
F707769
F707771
F707772
F707773
F707774
F707765
SELECT LTRIM(RTRIM(account_number)) ,
ACTIVATE ,
(CASE
WHEN COMMERCIAL_RESIDENTL = 'C' THEN 'Commercial'
ELSE 'Residential'
END) AS Acct_Type ,
CASE
WHEN left(LTRIM(RTRIM(account_number)),2) = 18 THEN 'AlarmNet'
WHEN left(LTRIM(RTRIM(account_number)),2) = 26 THEN 'AES'
WHEN left(LTRIM(RTRIM(account_number)),2) = 28 THEN 'AES'
WHEN ISNUMERIC(left(LTRIM(RTRIM(account_number)),2)) = 0 THEN 'Other'
ELSE 'OTHER'
END AS Acct_Vend ,
COMMERCIAL_RESIDENTL ,
B_BRANCH
FROM DICE_ALSUBSCR AS ALSUBSCR
WHERE (ACCOUNT_NUMBER >= ' 100000')
AND (ACCOUNT_NUMBER <= '710000000')
AND (ACTIVATE = 'Y')
AND ACCOUNT_NUMBER <> 'ACCOUNT_NUMBER'
August 12, 2014 at 11:25 am
cbrammer1219 (8/12/2014)
I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error. Any pointers or links of how to accomplish this would be great. I am also including example of how the account_numbers are formatted.R222209
R222220
R222222
R222212
R221123
F707768
F707769
F707771
F707772
F707773
F707774
F707765
SELECT LTRIM(RTRIM(account_number)) ,
ACTIVATE ,
(CASE
WHEN COMMERCIAL_RESIDENTL = 'C' THEN 'Commercial'
ELSE 'Residential'
END) AS Acct_Type ,
CASE
WHEN left(LTRIM(RTRIM(account_number)),2) = 18 THEN 'AlarmNet'
WHEN left(LTRIM(RTRIM(account_number)),2) = 26 THEN 'AES'
WHEN left(LTRIM(RTRIM(account_number)),2) = 28 THEN 'AES'
WHEN ISNUMERIC(left(LTRIM(RTRIM(account_number)),2)) = 0 THEN 'Other'
ELSE 'OTHER'
END AS Acct_Vend ,
COMMERCIAL_RESIDENTL ,
B_BRANCH
FROM DICE_ALSUBSCR AS ALSUBSCR
WHERE (ACCOUNT_NUMBER >= ' 100000')
AND (ACCOUNT_NUMBER <= '710000000')
AND (ACTIVATE = 'Y')
AND ACCOUNT_NUMBER <> 'ACCOUNT_NUMBER'
Quick thought, be careful with the ISNUMERIC function!
😎
Edit: typo.
August 12, 2014 at 11:44 am
Add quotes around "18", "26" and "28" when you check for those values:
CASE
WHEN left(LTRIM(RTRIM(account_number)),2) = '18' THEN 'AlarmNet'
WHEN left(LTRIM(RTRIM(account_number)),2) = '26' THEN 'AES'
WHEN left(LTRIM(RTRIM(account_number)),2) = '28' THEN 'AES'
WHEN ISNUMERIC(left(LTRIM(RTRIM(account_number)),2)) = 0 THEN 'Other'
ELSE 'OTHER'
END AS Acct_Vend ,
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 12, 2014 at 11:44 am
It is throwing the error trying to compare the first two characters of your varchar column to integer values. Try putting 18, 26, and 28 in single quotes.
CREATE TABLE #temp (col1 VARCHAR(10))
INSERT INTO #temp VALUES
('R222209'),
('R222220'),
('R222222'),
('R222212'),
('R221123'),
('F707768'),
('F707769'),
('F707771'),
('F707772'),
('F707773'),
('F707774'),
('F707765'),
('1807765') --added a good value
SELECT col1 ,
CASE
WHEN left(col1,2) = '18' THEN 'AlarmNet'
WHEN left(col1,2) = '26' THEN 'AES'
WHEN left(col1,2) = '28' THEN 'AES'
WHEN ISNUMERIC(left(col1,2)) = 0 THEN 'Other'
ELSE 'OTHER'
END AS Acct_Vend
FROM #temp AS ALSUBSCR
Results:
col1 Acct_Vend
---------- ---------
R222209 Other
R222220 Other
R222222 Other
R222212 Other
R221123 Other
F707768 Other
F707769 Other
F707771 Other
F707772 Other
F707773 Other
F707774 Other
F707765 Other
1807765 AlarmNet
August 12, 2014 at 11:44 am
You're comparing strings (LEFT result) with integers. Due to implicit conversion, the strings are being converted to integers and generating an error. Just add some quotes to convert your integers to strings.
SELECT LTRIM(RTRIM(account_number)) ,
ACTIVATE ,
(CASE
WHEN COMMERCIAL_RESIDENTL = 'C' THEN 'Commercial'
ELSE 'Residential'
END) AS Acct_Type ,
CASE
WHEN left(LTRIM(RTRIM(account_number)),2) = '18' THEN 'AlarmNet'
WHEN left(LTRIM(RTRIM(account_number)),2) = '26' THEN 'AES'
WHEN left(LTRIM(RTRIM(account_number)),2) = '28' THEN 'AES'
WHEN left(LTRIM(RTRIM(account_number)),2) LIKE '%[^0-9]%' THEN 'Other'
ELSE 'OTHER'
END AS Acct_Vend ,
COMMERCIAL_RESIDENTL ,
B_BRANCH
FROM DICE_ALSUBSCR AS ALSUBSCR
WHERE (ACCOUNT_NUMBER >= ' 100000')
AND (ACCOUNT_NUMBER <= '710000000')
AND (ACTIVATE = 'Y')
AND ACCOUNT_NUMBER <> 'ACCOUNT_NUMBER'
August 12, 2014 at 12:06 pm
That did the trick, putting the single ticks around the number.
Thank You
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply