Unwated data in Dim Table

JunaidKhan

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,

Jim_Knicely

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)

JunaidKhan

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

Jim_Knicely

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

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