Thank you for the in-depth analysis and apologies for the confusion. When I mentioned CL (Command Line) I was referring to Perl running in a shell window. Here is the actual SQL statement that I run:
<code>
sqlite> select count(*) as NUM,keyword from keywords group by keyword order by count(keyword) desc limit 5;
NUM keyword
--- -----------
67 Programming
47 Mathematics
24 Perl
22 Knots
13 Reference
sqlite>
</code>
That output is correct. I only want the top five keywords used in a library DB. There is obviously no Perl hash involved here.
Note that the results are within the query loop. But when I try to access the hash %KeyWords from a perl loop, I have problems. I am trying create a donut chart with SVG of the top 5 keywords. For that I need certain calculations, one of which is the percentage of each keyword to the total number of keywords.
But notice that in the SQL query I use an alias for the count(*). I have to use an alias because I don't know the keyword before hand - the library DB changes all the time. I could say count(keyword) as NUM but that would not make any difference - the key to the whole issue is using an alias.
Apparently in SQLite an alias is only available during the query. So when I try to access the hash outside of the query loop, it fails.
The line you refer to
`
why do you say "$records[1]" but "records[0]" without the dollar sign?
`
is wrong. I had pasted code in from a previous version. Many apologies.
I'm not sure if showing the insert statements would be any help to solving the issue. As mentioned the DB grows as I add / delete books etc. and that is all done in another Perl script. But if you're interested:
`
$sth=$dbh->prepare(qq{insert into keywords(authorid,bookid,keyword) values ($AuthorID,$TitleID,"$kw")});
`
As further explanation, I am trying to create a dynamic Perl hash from an SQLite query using an alias.
So not sure whether this is an SQLite issue or a Perl issue. I've posted on a Perl forum and still no joy.