Generate Join clause based on key data

Shell Programming and Scripting
1

Hi,

I have a file pk.txt which has pk data in following format

TableName | PK
Employee | id
Contact|name,country

My Output should be

Employee | t1.id=s.id
Contact| t1.name=s.name AND t1.country=s.country

I started of like this:

for LIST in `cat pk.txt`        do
						TABLE_NAME=`cut -d '|' -f1 ${LIST}`
						P_KEY=`cut -d '|' -f2 ${LIST}`
						P_KEYCNT=`echo $P_KEY |awk -F\, '{print NF-1}'`
        
        		START=0     

 
						for (( c=$START; c<=$P_KEY_CNT; c++ ))
						do

           ##Need some Help Here##            
done

I need some sort of dynamic variable to be generated based on number of keys and then build a string

Thanks
Wah

2

Can't help you with your own approach, but following Perl code does exactly what you want:

genjoin.pl

#!/usr/bin/perl

use strict;
use warnings;

while (<>) {
    chomp;
    next if $_ =~ /TableName/;
    $_ =~ s/\s+//g;
    my ($tab, $cols) = split(/\|/, $_, 2);
    my @cols = split(/,/, $cols);

    for my $col (@cols) {
        $col="t1.$col=s.$col";
    }
    print "$tab | " . join(" AND ", @cols) . "\n";
}

Run it like so:

$ perl genjoin.pl pk.txt

If you like the result, then simply redirect the output to a file:

$ perl genjoin.pl pk.txt >outfile.txt

Hope this helps.

3

You already use awk to get your KEYCNT so why not use awk to do all the processing:

awk -F "[ |]*" '
NR>1{ 
  printf $1 " | "
  flds=split($2, F, ",")
  for(i=0;i<flds;i++)
     printf "%s%s.%s=%s.%s", i?" AND ":"", S, F[i+1], D, F[i+1]
  printf "\n"
}' S=t1 D=s pk.txt

Note NR>1 is to skip the "Table| PK" header line. I set variables S (source) and D(Dest) so you could change them easily

1 Like
4

Another approach

awk '{gsub(/[^,]+/, "t1.&=s1.&", $2); gsub(/,/," AND ", $2)} NR>1' FS=' *[|] *' OFS=\| file
1 Like
5 
perl -F'\|' -wlap -e 'if($#F+1 && $.>1){$F[1] = join(" AND ", map{ s/\s*(\S+)\s*/t1.$1=s.$1/;  $_ } split(",",$F[1]) );$_ = join("|",@F)}' file

---------- Post updated at 10:35 PM ---------- Previous update was at 08:35 PM ----------

OR like this

perl -F'\|' -lape 'BEGIN{$,="|"}next if $. < 2 || !($#F+1); $F[1] = join(" AND ",map{ s/^\s+|\s+$//g; "t.".$_."=s.".$_ } split(/,/,$F[1])); $_=join($,,@F)' file
6

Another way using just substitution:

perl -wlne 'print if /Employee/ and s/(\|\s*)(\w+)$/$1tl.$2=s.$2/ or /Contact/ and s/(\|\s*)(\w+),(\w+)$/$1t1.$2=s.$2 AND t1.$3=s.$3/;' file