Unwated data in Dim Table

JunaidKhanJunaidKhan Registered User

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, Moderator, Employee, Registered User, VerticaExpert

    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)
    
  • JunaidKhanJunaidKhan Registered User

    Yes Jim the pattern is like UCA EBC - ActionId: -

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited November 21

    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