cancel
Showing results for 
Search instead for 
Did you mean: 

select script returns no results

SOLVED
Go to solution
Ratzie
Super Advisor

select script returns no results

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

6 REPLIES
Sandman!
Honored Contributor
Solution

Re: select script returns no results

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
Honored Contributor

Re: select script returns no results

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

Volker
Sandman!
Honored Contributor

Re: select script returns no results

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
Honored Contributor

Re: select script returns no results

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
Honored Contributor

Re: select script returns no results

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
Never give up, Keep Trying
Ratzie
Super Advisor

Re: select script returns no results

You were absolutely right
$i instead of $1

I cant believe I did that!
Thanks!