Non Ascii data Oracle

-- Oracle Query to identify non ascii data

SELECT 'PRICING','PROD_CHAN','PROD_CHAN_TYP_NM',PROD_CHAN_TYP_NM

FROM  PRICING.PROD_CHAN  WHERE LENGTH(ASCIISTR( PROD_CHAN_TYP_NM)) <> LENGTH (PROD_CHAN_TYP_NM )
;


-- Oracle Query to identify non ascii data 
SELECT 'PRICING','PROD_CHAN','PROD_CHAN_TYP_NM',PROD_CHAN_TYP_NM
FROM  PRICING.PROD_CHAN  WHERE
REPLACE(TRANSLATE(LOWER(PROD_CHAN_TYP_NM) ,'abcdefghijklmnopqrstuvwxyz1234567890_-+=()*&^%$#@!~`:}{][|\/?.,><"'''||chr( 10)||chr(13)||chr(9),'X'),'X','')

;

Comments

Post a Comment

Popular posts from this blog

The Secret behind A B I N I T I O.

Background on Abinitio : Abinitio is considered one of the major ETL players  in line with informatica ,datastage ,SSIS ( if it can be considered major ETL player ;-) ) .In fact, in most respects it is considered better than all of the above . It has user interface and debugging simpler than Informatica  ( one of the positive points about informatica ) ,It has parallelism which data stage may still take years to reach . Superiority : (HearSay ) It has the ability to convert a 6 people ,6 month ETL project to 2 month 4 people ETL project . (Sounds hyperbole ,But yet it has been established in many cases that it takes half the development time compared to INFA with much better quality of code ) . Other statistics suggest that if you call Abinitio ,there is 75% chance you will be talking to a PhD holder.In other words it is considered to be ingenously simple. Secrecy : In spite of all this there is very little you would know about it (If at all ) . Reason : It is so because Abi
Read more

Finding Skewed Tables in Teradata

       Skewed Tables in Teradata                                                                  Teradata distributes its data for a given table based on its Primary Index and Primary Index alone .If this Primary Index is not selected appropriately it can cause performance bottle necks. Following are general parameters we should consider while creating Primary Indexes       1:Access Path       2:Volatility       3: Data Distribution        Volatility is usually not an issue in a well designed database .That is to say we do not expect update clauses updating the primary index itself. This usually leaves us with data distribution and Access Path.       Access Path implies particular column ( set of columns ) are always used in join conditions .Advantage of using these columns as PI is that it will avoid redistribution of data during join .( One of the most expensive operation for teradata ) and therefore can reduce usage of Spool files.        Data distribution implies
Read more

Macro to generate Mload Script

DROP MACRO MLOAD_EXPORT REPLACE MACRO MLOAD_EXPORT ( DB VARCHAR(100), TB VARCHAR(100), PATH VARCHAR(100), FS VARCHAR(100) ) AS ( SEL 1,CAST(1 AS BIGINT), CAST('.DATEFORM ANSIDATE; .LOGON server/username,password; .LOGTABLE USR_WORK.ML_UMS_' || :TB || '_1; DROP TABLE USR_WORK.WT_UMS_' || :TB || '_1; DROP TABLE USR_WORK.ET_UMS_' || :TB || '_1; DROP TABLE USR_WORK.UV_UMS_' || :TB || '_1; .ROUTE MESSAGES WITH ECHO TO FILE ''' || :PATH || '.ldrlog'' ; .BEGIN IMPORT MLOAD TABLES ' || :DB || '.' || :TB || ' WORKTABLES USR_WORK.WT_UMS_' || :TB || '_1 ERRORTABLES USR_WORK.ET_UMS_' || :TB || '_1 USR_WORK.UV_UMS_' || :TB || '_1 ERRLIMIT          0 CHECKPOINT     100000 TENACITY 10000 SESSIONS        10 SLEEP 6 ; .LAYOUT InputFileLayout; ' AS VARCHAR(6000)) FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=DATE UNION SEL 2,CAST(COLUMNID AS BIGINT), CAST('.FI
Read more