building a SET clause in shell script

Shell Programming and Scripting
1

Hi,

I have a comma delimited string, e.g. empno, ename, sal.

Using Korn Shell Script I want to build the SET clause for an UPDATE statement, and set clause should look like this:

empno=decode(:empno, '?', empno, :empno),
ename=decode(:ename, '?', empno, :ename),
sal=decode(:sal, '?', sal, :sal)

This comma seperated string can have any number of attributes in it seperated by commas.

Any suggestions will be appreciated.
Thanks
Shalu

2

/tmp/string.txt:

empno, ename, sal

#!/usr/bin/ksh
STRINGFILE=/tmp/string.txt

for i in $(sed 's/,//g' "$STRINGFILE")
 do
  echo "$i=decode(:$i, '?', $i, :$i),"
 done

output:

empno=decode(:empno, '?', empno, :empno),
ename=decode(:ename; '?', ename, :ename),
sal=decode(:sal, '?', sal, :sal),

second line differs from your first post, do you really want:

ename=decode(:ename, '?', empno, :ename)

?

and the third line has a komma at the end

if this is really wanted I 'll update the script

3

Thanks!! This will be a good learning for me. There are three things:

  1. empno was a typo, so what you did is correct.
  2. I don't want comma after the last line.
  3. also if any attribute in the delimited string is in uppercase I would like to convert it into lower case.
4 
#!/usr/bin/ksh
STRINGFILE=/tmp/string.txt

for i in $(sed 's/,//g' "$STRINGFILE")
 do
  echo "$i=decode(:$i, '?', $i, :$i),"
 done |  tr [:upper:] [:lower:] | sed '$ s/,$//'

should do the job

5

Basically this delimited string is the header of a file, and it is tab delimited not comma. So if I tweak your code like following, I believe I am doing something wrong, because I don't get anything in return:

STRINGFILE=head -1 /home/z1uals/src/pdsa/mydat/textDMR.txt | tr '\t' ','

for i in $(sed 's/,//g' "$STRINGFILE")
do
echo "$i=decode(:$i, '?', $i, :$i),"
done | tr [:upper:] [:lower:] | sed '$ s/,$//'

6 
#!/usr/bin/ksh
TEXTFILE=/home/z1uals/src/pdsa/mydat/textDMR.txt

for i in $(head -1 $TEXTFILE | tr '\t' ',' | sed 's/,//g')
 do
  echo "$i=decode(:$i, '?', $i, :$i),"
 done |  tr [:upper:] [:lower:] | sed '$ s/,$//'

can be optimized ^^

7

This returns a value, but it is not in the correct format as we are expecting.

8

post the output of:

head -1 /home/z1uals/src/pdsa/mydat/textDMR.txt | tr '\t' ','

9

Dmr_run_id,rma_server_id,instrument_id,portfolio_id,oas,price

10

yep because it has no spaces after komma as you wrote in your first post

use:

#!/usr/bin/ksh
TEXTFILE=/home/z1uals/src/pdsa/mydat/textDMR.txt

for i in $(head -1 $TEXTFILE | tr '\t' ',' | sed 's/,/ /g')
 do
  echo "$i=decode(:$i, '?', $i, :$i),"
 done |  tr [:upper:] [:lower:] | sed '$ s/,$//'
11

And this is the output of head -1 /home/z1uals/src/pdsa/mydat/textDMR.txt:
DMR_RUN_ID RMA_SERVER_ID INSTRUMENT_ID PORTFOLIO_ID OAS PRICE

12

Basically the header of the file is tab delimited.

13

then leave both tr and sed :slight_smile:

#!/usr/bin/ksh
TEXTFILE=/home/z1uals/src/pdsa/mydat/textDMR.txt

for i in $(head -1 $TEXTFILE)
 do
  echo "$i=decode(:$i, '?', $i, :$i),"
 done |  tr [:upper:] [:lower:] | sed '$ s/,$//'
14

Thanks much for all help. I am new to shell scripting, could you please point me to a good shell scripting tutorial.

15

no problem

KSH script BASICS seems to be ok