Korn shell script - SQL statement challenges

Shell Programming and Scripting
1

Hi scripting experts.

I have some coding challenges that I'm hoping you can help me out.

I have one file#1 that contains the following sql statement that spans over multiple lines:

sql Select /*+ use_has(a,b) */ *
    from customer a,
         customer_address b
    where a.id = b.id
    -- and a.id is not null
    and b.address is not null

First step - Cleanup the file to remove blank lines, comments (#) and convert sql statement to span over 1 line only

cat file#1 | sed '/^$/d' | sed '/^#/d' | tr '\n\r' ' ' > tmp_file#1
echo >>tmp_file#1

tmp_file#1 now contains:

sql Select /*+ use_has(a,b) */ *     from customer a,          customer_address b     where a.id = b.id -- and a.id is not null and b.address is not null

Challenge#1:
The condition "and b.address is not null" should not be commented out.
I'm thinking the comment part "-- and a.id is not null" should become "/* and a.id is not null */ in order to comment that condition only
How can I easily handle this?
Note: Comments can appear anywhere in the sql statement

Next, I have a second file#2 as follows which I need to replace $$SQL with the sql statement above:

Existing
...
$$SQL=select count(*) from dual
....

New
...
$$SQL=sql Select /*+ use_has(a,b) */ *     from customer a,          customer_address b     where a.id = b.id -- and a.id is not null and b.address is not null
....

sSqlStmt="sql Select /*+ use_has(a,b) */ *     from customer a,          customer_address b     where a.id = b.id -- and a.id is not null and b.address is not null"


sed -e "s/\$\$SQL=.*/\$\$SQL=$sSqlStmt/"

Challenge#2:
The "/" and "*" in the sql statment is not producing the desired results by the sed command

I get an error such as "sed function cannot be parsed".

Thank you for any help or insight that you can provide

2

This is one example:

$ cat sql_file.sql
sql Select /*+ use_has(a,b) */ *
    from customer a,
         customer_address b
    where a.id = b.id
    -- and a.id is not null
    and b.address is not null

$ cat sql_file.sql | tr -d '\n' | sed 's/ \{2,\}/ /g' | sed 's/-- //g'
sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id and a.id is not null and b.address is not null

$ cat file2
$$SQL=select count(*) from dual

$ sql='sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id and a.id is not null and b.address is not null'

# This just replaces the $$SQL with the contents of the variable($sql) in the file as you requested not the rest of the line.
$ sed "s#\$\$SQL#$sql#g" file2
sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id and a.id is not null and b.address is not null=select count(*) from dual