I am trying to work with this statement in PL SQL. I can do this in access using IIF() but am having trouble converting it.... I keep getting an error that says: Unexpected Token Right at 1, 53.....
I have a varchar2(40) field that is named GC_CLINIC.GC_CL_TEL1. THe issue is that people have put in these characters "(", ")", "-" and "_".... I have used the replace function to remove them. Once these are removed I am left with a calculated field called NoParentheses that is either 10 or 11 characters in length. The records with 11 characters have a preceding 1 i.e 12223334444 .... I would like to trim this additional 1 off by using right....
I have got this statement to work successfully....:
Code:
SELECT CASE WHEN Length(Q1.NoParentheses) = 11 THEN 'NEED TO USE RIGHT FUNCTION HERE' ELSE Q1.NoParentheses END AS NEW
FROM
(
SELECT Replace(Replace(Replace(Replace(GC_CLINIC.GC_CL_TEL1, '(', ''), ')', ''), '_', ''), '-', ''
) AS NoParentheses
FROM GC_CLINIC
WHERE GC_CLINIC.GC_CL_ACTIVE = 'Y'
ORDER BY GC_CLINIC.GC_CL_CODE) AS Q1
However I need the Red portion to actually contain the Right Function... i.e.
Code:
SELECT CASE WHEN Length(Q1.NoParentheses) = 11 THEN Right(Q1.NoParentheses,10) ELSE Q1.NoParentheses END AS NEW
FROM
(
SELECT Replace(Replace(Replace(Replace(GC_CLINIC.GC_CL_TEL1, '(', ''), ')', ''), '_', ''), '-', ''
) AS NoParentheses
FROM GC_CLINIC
WHERE GC_CLINIC.GC_CL_ACTIVE = 'Y'
ORDER BY GC_CLINIC.GC_CL_CODE) AS Q1
But I get an error when I run it with the Right() Function it seems that I have an unexpected token Right
Does anyone know how I can fix this error? Any help would be very much appreciated. Not familiar with Oracle SQL and have been beating my head up against the wall for hours