topic Re: select script returns no results in Operating System - HP-UX

select script returns no results

Ratzie — Thu, 09 Mar 2006 17:39:01 GMT

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
...
...

Re: select script returns no results

Sandman! — Thu, 09 Mar 2006 17:47:55 GMT

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!

Re: select script returns no results

Volker Borowski — Thu, 09 Mar 2006 17:51:34 GMT

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

Volker

Re: select script returns no results

Sandman! — Thu, 09 Mar 2006 17:56:50 GMT

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!

Re: select script returns no results

Volker Borowski — Thu, 09 Mar 2006 18:09:34 GMT

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

Re: select script returns no results

Indira Aramandla — Thu, 09 Mar 2006 19:46:52 GMT

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

Re: select script returns no results

Ratzie — Thu, 09 Mar 2006 21:26:20 GMT

You were absolutely right
$i instead of $1

I cant believe I did that!
Thanks!