Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: Alternate for ORA-01427

    Hello all

    I have a little problem to solve
    I have a EMPLOYER_NO which is a Varchar2 field
    Every field contains numbers and one letter
    Eg - 15897/A/
    ...... 54687/B/

    Then ZONE_CODE field for indicate the change of EMPLOYER_NO field letter

    Eg - EMPLOYER_NO... ZONE_CODE
    ......... 15897/A/ ......... B
    ......... 54687/B/ ......... F

    So i wrote the below code. I know it's wrong and Violate ORA-01427
    How can i take the changed ZONE_CODES count relevant to EMPLOYER_NO

    select count (*)
    from m394aforms
    where ZONE_CODE <> (select regexp_replace(EMPLOYER_NO,'[^A-Za-z]', '') from m394aforms)

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would
    Code:
    where zone_code not in (select regexp_replace ...)
    do any good? (i.e. substitute <> with NOT IN).

  3. #3
    Join Date
    Sep 2011
    Posts
    85

    Question

    No..

    I already tried that....

    Your answer will give Count = 0

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ai_zaviour View Post
    No..

    I already tried that....

    Your answer will give Count = 0
    In that case, you have different data than you posted in your initial post. For me, it is giving rows which ZONE_CODE column value is not present in any EMPLOYER_NO column. It would be really nice if you exactly described in words what is the result you want.
    Code:
    SQL> with m394aforms as ( select '15897/A/' EMPLOYER_NO, 'B' zone_code from dual
      2             union all select '54687/B/', 'F' from dual )
      3  -- your query with NOT IN displaying all result set rows
      4  select *
      5  from m394aforms
      6  where ZONE_CODE not in (
      7    select regexp_replace(EMPLOYER_NO,'[^A-Za-z]', '')
      8    from m394aforms
      9  );
    
    EMPLOYER Z
    -------- -
    54687/B/ F
    It may also return zero if there are no letters in EMPLOYER_NO column - see the explanation here: http://asktom.oracle.com/pls/asktom/...D:442029737684
    You should filter those values from the subquery or use NOT EXISTS clause.

  5. #5
    Join Date
    Sep 2011
    Posts
    85
    Sorry for the less information

    Attachment shows the data set

    In Red color box EMPLOYER_NO and in Blue ZONE_CODE

    so you can observe the different zone_codes

    When its not change then same EMPLOYER_NO letter will appear in ZONE_CODE

    What i need is the count of ZONE_CODE where is different than EMPLOYER_NO
    Attached Thumbnails Attached Thumbnails 1.JPG  

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ai_zaviour View Post
    When its not change then same EMPLOYER_NO letter will appear in ZONE_CODE
    I have no idea what is this sentence supposed to mean. Sorry.
    Quote Originally Posted by ai_zaviour View Post
    What i need is the count of ZONE_CODE where is different than EMPLOYER_NO
    And that is how many for those 15 rows? Which of them shall be counted and why?
    If you want to compare those two values from the same row for non-equality, simply do not use any subquery and do it directly.
    Code:
    select *
    from m394aforms
    where ZONE_CODE != regexp_replace(EMPLOYER_NO,'[^A-Za-z]', '');
    Maybe you will also need to treat the situation when no letters in EMPLOYER_NO column - just add an extra check whether the regular expression is NULL.

  7. #7
    Join Date
    Sep 2011
    Posts
    85

    Thumbs up

    Thanx..

    This will do..

    select count (*)
    from m394aforms
    where ZONE_CODE != regexp_replace(EMPLOYER_NO,'[^A-Za-z]', '')


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •