Comparing the data in a 2 files

i150371485 · November 19, 2012, 6:56am

Hi Friends,

I have a file 1

CREATE MULTISET TABLE TEYT_Q9_T.TEST ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
     XYZ DECIMAL(10,0),
      ABC VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      PQR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
     IST CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
      EFF_END_DT DATE FORMAT 'yyyy-mm-dd',
      EFF_STRT_DT DATE FORMAT 'yyyy-mm-dd',
      CRE_DT DATE FORMAT 'yyyy-mm-dd',
      CRE_BY VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      ETL_PKG_ID DECIMAL(15,0) NOT NULL,
      SRC_SYS_CD VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      CRE_RUN_ID INTEGER NOT NULL,
      ETL_SRC_FILE_NB DECIMAL(15,0) NOT NULL,
      ETL_ROW_NB INTEGER NOT NULL,
      ETL_FILE_TYPE_CD VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'I',
      ETL_LD_DT DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      ETL_ACTN_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'I' COMPRESS ('D','I','P','U'),
      ICDW_ISRT_TS TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
      BAD_RCRD_IND VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX NUPI_CST_MKEY_ORGN_MSTR ( ETL_PKG_ID ,CRE_RUN_ID ,
ETL_ROW_NB ,ETL_LD_DT );

File 2

 
record    
  decimal('�') xyz;         DECIMAL(10)  
  string('�') abc; VARCHAR(10)  
  string('�') pqr; VARCHAR(20)
  string("\307") ist; CHAR(4)  
  string('�') eff_end_dt; CHAR(8) YYYYMMDD
  string("\307") eff_strt_dt = NULL(""); CHAR(8) YYYYMMDD
  string("\307") cre_dt; CHAR(8) YYYYMMDD
  string("\n") cre_by;  VARCHAR(5)  
end

I need to validate the 2 files structures.Please find the green colour which i highlited where comparision sholud be done. Plz help

Output should Some thing like

 
Columnname | SOURCE_FILE1| TARGET_FILE2
abc             | varchar(5)     | varchar(10)

danielos · November 19, 2012, 8:13am

I propose you a solution, maybe it is not the best one but you have to correct a bit of code but it works.

awk '$0 ~ /\(/, $0 ~ /PRIMARY/ {print $1,$2}' kk | while read var tipo
do 
otro=$(egrep -i "$var" kkk | cut -d";" -f2 | awk '{print $1}')
[[ ! -z $otro ]] && 
(
echo "VAR:$var, TIPO:$tipo, OTRO:$otro"
if [[ $tipo = $otro ]]
then 
echo "OK: var: $var, ok: $tipo vs $otro"
else 
echo "FAIL var: $var, ok: $tipo vs $otro"
fi
)
done

I hope It's usefull

---------- Post updated at 02:13 PM ---------- Previous update was at 02:06 PM ----------

I'm sorry, but file1 = kk and file2 = kkk, I was checking and I forget to correct it.

i150371485 · November 23, 2012, 5:25am

@danielos : Thanks for the reply. . I have tried the above code and will change accroding to my requirement.it would be great if you Could explain the code ?

danielos · November 23, 2012, 8:09am

Hi!
Ok, I have fixed a bit of code:

#!/usr/bin/ksh

awk '$0 ~ /\(/, $0 ~ /PRIMARY/ {print $1,$2}' file1 | while read variable type_file1
do
   [[ "${variable}" = *[A-Za-z_] ]] &&
   (
      type_file2=$(egrep -i "$variable" file2 | cut -d";" -f2 | awk '{print $1}')
      [[ ! -z $type_file2 ]] &&
        (
        if [[ $type_file1 = $type_file2  ]]
        then
                echo "OK  : var: $variable,| FILE1: $type_file1 | FILE2: $type_file2"
        else
                echo "FAIL: var: $variable,| FILE1: $type_file1 | FILE2: $type_file2"
        fi
        )
    )
done