Results 1 to 2 of 2

Pl sql case select

  1. #1
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78

    Pl sql case select

    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

  2. #2
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    I seem to have found the answer at long last.... pretty sure I can get it out of the sub query now..... I had to use substr() not right()

    Code:
    SELECT CASE WHEN Length(Q1.NoParentheses) = 11 THEN SubStr(Q1.NoParentheses, 2, 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) Q1

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Select case help
    By killermonkey in forum Programming
    Replies: 7
    Last Post: 10-25-2013, 05:09 PM
  2. VBA for SELECT CASE logic
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 12-04-2012, 09:09 PM
  3. select case or else if for unhiding
    By nichmeg in forum Programming
    Replies: 3
    Last Post: 10-30-2011, 09:30 AM
  4. Select Case vs Dlookup
    By BRV in forum Programming
    Replies: 1
    Last Post: 10-28-2011, 03:18 PM
  5. Help with Select Case statement
    By focosi in forum Access
    Replies: 4
    Last Post: 08-09-2011, 12:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums