Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Unwated data in Dim Table

Hello,

Can some one help me in the below query.

" I have dim table dim_User i am getting some useless entries in the username table like Actionid-xxxxxxxx which is causing some of the statistical issues can some once guide me to get rid of these entries by some trigger,procedure or something else"

Br,

Comments

  • Jim_KnicelyJim_Knicely Administrator

    Do the useless entries follow some sort of pattern? That is, do they all have that hyphen in them? If so, wouldn't a simple DELETE work?

    dbadmin=> SELECT * FROM dim_user;
         user_name
    -------------------
     JIM
     JANE
     Actionid-xxxxxxxx
     JOSH
     HELEN-AHGHGD
    (5 rows)
    
    dbadmin=> DELETE FROM dim_user WHERE INSTR(user_name, '-') > 1;
     OUTPUT
    --------
          2
    (1 row)
    
    dbadmin=> SELECT * FROM dim_user;
     user_name
    -----------
     JIM
     JANE
     JOSH
    (3 rows)
    
  • Yes Jim the pattern is like UCA EBC - ActionId: -

  • Jim_KnicelyJim_Knicely Administrator
    edited November 2018

    If you don't want bad values to be inserted into a table column, add a "Check Constraint". For the constraint's Boolean expression, you use a Regular Expression to make sure the correct format is being used.

    Below is a general example showing how to only permit valid 10-digit phone numbers with hyphens to be added to a table... I'd try to give you a better example using your pattern, but I'm not sure what the hyphens are supposed to represent :p

    dbadmin=> CREATE TABLE only_usa_phone_number (phone_number VARCHAR(15), CONSTRAINT phone_number_check CHECK (REGEXP_LIKE(phone_number, '^\d{3}-\d{3}-\d{4}$')));    
    CREATE TABLE
    
    dbadmin=> INSERT INTO only_usa_phone_number SELECT '123-456-7890';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> INSERT INTO only_usa_phone_number SELECT '1234567890';
    ERROR 7230:  Check constraint 'public.only_usa_phone_number.phone_number_check' regexp_like(only_usa_phone_number.phone_number, E'^\\d{3}-\\d{3}-\\d{4}$') violation: 'phone_number=1234567890'
    

    See:
    https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Constraints/ConstraintTypes/CheckConstraints.htm

    https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/RegularExpressions/REGEXP_LIKE.htm

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.