select script returns no results

[Ratzie]

I have a flat file that I need to see if these records are in the data base, the script runs but returns 0 in database and I know there are records in the db. I can manually copy the one record from the flat file and run simple sql and it returns 1.

#!/usr/bin/sh
>GSG.not.in.ac; >GsG.in.ac
cat GSG.class |while read i
do echo $i
var=`sqlplus -s admin/admin <set echo off head off
SELECT count (cct)
FROM table
WHERE CCT = '$1';
exit
EOF`

if [ $var -eq 0 ]; then
echo $i >>GSG.not.in.ac
else
echo $i>>GSG.in.ac
fi
done

Flat file looks like:
GSG44U104186000TSYS000
GSG44U105141000TSYS000
GSG44U105142000TSYS000
GSG44U105143000TSYS000
GSG44U105144000TSYS000
GSG44U105169000TSYS000
GSG44U107608000TSYS000
...
...

[Sandman! · Accepted solution]

Instead of single quoting the variable use double quotes otherwise the shell won't expand it i.e.

> WHERE CCT = '$1';<
...should be...
WHERE CCT = "$1";

cheers!

[Volker Borowski]

Shouldn't it be $i in position of $1 ?

Volker

[Sandman!]

Thanks Volker...it should be $i instead of $1 and moreover if the table column is of type number then don't quote it at all.

cheers!

[Volker Borowski]

Hmm,

GSG44U104186000TSYS000 lets me assume to be very much a sting. So single quoutes seem to me a proper solution, which might interfere with the reverse quotes.
May be they need to be escaped.

What did you try when you included a record directly ? Just the sqlplus or the entire assignment to "var" ?

so may be either

WHERE CCT = '$i';

or

WHERE CCT = \'$1\';

could be of help ?!!?

Volker

[Indira Aramandla]

Hi Hradowy,

Your script is fine whether single or double quotes.

Only change is WHERE CCT = '$1'; the comparing vaue is in $i and not $1.

And also make sure that your text on the flat file does not have any special chanracters towards the end. In whcih case you will have to use the 'like' instead of '='

Like '%$i%' instead of = '$i'.

I tested with the same flat file that you gave and it works fine with the = '$i'


Indira A

[Ratzie]

You were absolutely right
$i instead of $1

I cant believe I did that!
Thanks!