How to append database column to a delimited file

Hi,

I have the below flat filewith ~ as delimiter
emp.no~dept.name

I need to append corresponding emp.name column which will come from database based on emp.no in the flat file.
I need the output as
dept.name~emp.name
Can anyone please help me in resolving this issue..

I tried the below code..

cat input_file | gawk -f awk_file > outputfile

awk file will be:

BEGIN 
{
print $0 "~" emp.name
while IFS="~" read f1 f2
do
emp.name=` db2 "select emp.name from table where emp.id=$f1
cut -d'~' -f1 input_file
done < input_file
             
}

Thanks in advance

You cannot put a shell script inside an awk program.

assuming your flat file is called file.txt and output file is outfile.txt

cat file.txt |  sed 's/~/ /g' | while read empNum depName
do
 
empName=$(Your code to get the name from your database using empNum  )
echo "${depName}~${empName}" >> outfile.txt
done