How to execute a no of SELECT COUNT(*) statements using a loop

Shell Programming and Scripting
1

HI Unix Gurus,

I have a number of SELECT count(*) statements in an input file and I want to execute it using a shell script but one by one using loop in script....
How can I do this.....

2 
cat input.file |sh
3

Hi Ustechie,
Can you please post how your file looks like.
We have very few information.
If it's written in SQL then I'd do:

while true; do
    mysql < input.file
done
4

My requirement is....

I can have a number of Select count(*) statements in an input file....it may be only 1 more more than 1, number of statements is not fixed...

So I want to use a loop thru which i can check the count of 1 statement and if it is greater than 100,000 then i will stop processing....this is requirement

I can grep the number of SQLs present in input file but I want to run 1 sql at a time instead of running them all...

5

You're really NOT making it easy by not copying an example of what your file could look like! What is your problem with that? If I understand well, the Select count(*) statements are buried in the middle of other statements. Right? Do they always occupy a full line? Can they be split between several lines? Do they have the trailing semicolon?

What if there are 150,000 statements? Do you want to process the first 100,000 statements? or nothing at all?

Why do you want to use a loop?

while read -r; do
    mysql -e "$REPLY"
done < input.file

is exactly the same thing as

mysql < input.file

If the idea is to process the first 100,000 statements, and considering one statement = one line, I would just do the following:

egrep '^Select count(*)' input.file | head -100000 | mysql

If you want more help, provide more information.

1 Like
6

I am sorry but cannot paste my code here due to some compliance issues...hope you understand.....

to make it simple, i'll give you an example.....

input file would contain...

Select count() from employee where dept=10;
Select count() from employee where dept=20;
Select count() from employee where dept=30;
Select count() from employee where dept=40;
Select count() from employee where dept=50;
Select count() from employee where dept=60;
Select count() from employee where dept=70;
Select count() from employee where dept=80;
Select count(*) from employee where dept=90;

Number of SQLs may vary from 1 to any number.....here i took it randomly 9 SQLs.....

Now I want to execute this input file thru a unix script but I want to execute first SQL first and if the result of first SQL is greater than 100,000 then stop processing...

7

That's better. Of course I was not expecting you to paste the full or real code. But this template helps a lot!
In this case, I think that's what you want to do:

limit=100000
while read -r; do
    [[ $(mysql -Ne "$REPLY") -gt $limit ]] && break
done < input.file

Be careful, if one of the statement outputs more than one line, an error will occur. For example if you insert another field or a GROUP BY.

8

thanks a lot.....
i'll try this and will post the updates on this....
thanks again....

---------- Post updated at 01:08 PM ---------- Previous update was at 12:58 PM ----------

Hi Chebarbudo.......

I tried this code but it throws an error:

ksh: mysql: not found.
ksh: mysql: not found.
ksh: mysql: not found.
ksh: mysql: not found.
ksh: mysql: not found.
ksh: mysql: not found.
ksh: mysql: not found.
ksh: mysql: not found.
ksh: mysql: not found.

can you explain this code.......Does it execute 1 sql in 1 iteration and check the count, if greater than 100,000 then break??

I am a newb and hence not much familer with the functions and syntax.

Thanks for your help.....