Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    27

    Unanswered: SUBSTR on an NUMBER incomplete results

    I'm creating a query which will be used with an auto-complete function on a web page. The table I'm using contains customer information, the important column here being the CUST_ID. It is an identity column, and the company has been in business long enough for it to grow to about 7 digits long. There are a few "test" customers that were entered fairly early whose CUST_IDs are only 5 digits long, and for some reason the query isn't seeing those. Here is the table (a whole lot of columns are left out here because they don't apply to this problem)
    Code:
    CREATE TABLE MyCustomerTable (
      CUST_ID NUMBER(10,0),
      FIRST_NAME varchar2(25),
      LAST_NAME varchar2(25)
    );
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (12345, 'FAKE', 'CUSTOMER');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234000, 'KATHLEEN', 'FEILDS');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234001, 'SHANDA', 'WILEY');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234002, 'ROSETTA S', 'KNOTTS');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234003, 'SANDRA', 'KRUGER');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234004, 'YUNG', 'SPANGLER');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234005, 'ELLROY', 'RAGNACCI');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234006, 'CLARINE', 'LUTZ');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234007, 'ZELINA', 'ABEL');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234008, 'DONNELL', 'VANVOLKINBURG');
    INSERT INTO MyCustomerTable (CUST_ID, FIRST_NAME, LAST_NAME) VALUES (1234009, 'EILEEN', 'GOODWIN');
    
    SELECT LAST_NAME || ', ' || FIRST_NAME || ' (' || CUST_ID || ')' AS FullName
    FROM MyCustomerTable 
    WHERE rownum <= 5 AND (SUBSTR(LTRIM(TO_CHAR(CUST_ID)), 1, 4) = '1234')
    ORDER BY LAST_NAME, FIRST_NAME, CUST_ID;
    
    --Alternatively I tried this with similar results
    SELECT LAST_NAME || ', ' || FIRST_NAME || ' (' || CUST_ID || ')' AS FullName
    FROM MyCustomerTable
    WHERE rownum <= 5 AND (LTRIM(TO_CHAR(CUST_ID)) LIKE '1234%')
    ORDER BY LAST_NAME, FIRST_NAME, CUST_ID;
    The problem that I'm having is that when I do a similar select on the actual table, it never brings up FAKE CUSTOMER. Unfortunately after testing this sample code here it doesn't seem to have the same problem. However, everything else appears to be exactly the same as my problem. Based on what you can see here, can you think of why it's not getting FAKE CUSTOMER from the real table? Let me know if there is more information that you need, especially since I can't seem to replicate the problem with my own created tables.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool What?

    What is your problem?:
    Code:
    SQL> With MyCustomerTable  As (
      2  SELECT 12345 CUST_ID, 'FAKE' FIRST_NAME, 'CUSTOMER' LAST_NAME FROM DUAL UNION
      3  SELECT 1234000, 'KATHLEEN', 'FEILDS' FROM DUAL UNION
      4  SELECT 1234001, 'SHANDA', 'WILEY' FROM DUAL UNION
      5  SELECT 1234002, 'ROSETTA S', 'KNOTTS' FROM DUAL UNION
      6  SELECT 1234003, 'SANDRA', 'KRUGER' FROM DUAL UNION
      7  SELECT 1234004, 'YUNG', 'SPANGLER' FROM DUAL UNION
      8  SELECT 1234005, 'ELLROY', 'RAGNACCI' FROM DUAL UNION
      9  SELECT 1234006, 'CLARINE', 'LUTZ' FROM DUAL UNION
     10  SELECT 1234007, 'ZELINA', 'ABEL' FROM DUAL UNION
     11  SELECT 1234008, 'DONNELL', 'VANVOLKINBURG' FROM DUAL UNION
     12  SELECT 1234009, 'EILEEN', 'GOODWIN' FROM DUAL
     13  )
     14  SELECT LAST_NAME || ', ' || FIRST_NAME || ' (' || CUST_ID || ')' AS FullName
     15  FROM MyCustomerTable
     16  WHERE SUBSTR(CUST_ID, 1, 4) = '1234'
     17  ORDER BY LAST_NAME, FIRST_NAME, CUST_ID
     18  /
    
    FULLNAME
    -------------------------------------------------------------------
    ABEL, ZELINA (1234007)
    CUSTOMER, FAKE (12345)
    FEILDS, KATHLEEN (1234000)
    GOODWIN, EILEEN (1234009)
    KNOTTS, ROSETTA S (1234002)
    KRUGER, SANDRA (1234003)
    LUTZ, CLARINE (1234006)
    RAGNACCI, ELLROY (1234005)
    SPANGLER, YUNG (1234004)
    VANVOLKINBURG, DONNELL (1234008)
    WILEY, SHANDA (1234001)
    
    11 rows selected.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2004
    Posts
    95
    have you tried to put the CUST_ID for the 'FAKE CUSTOMER' with 7 digits also?

    and the order condition is exactly as you put it on the example?

Posting Permissions

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