in reply to DBI efficient loop?

I don't understand your data manipulation requirements. It would be better if you edit your post to put your code in between <code>...</code> tags.

I think you could save a lot of work by pushing the data manipulation into the database instead of running a loop in Perl to do the data manipulation. If you need the count of FIELD1 that are equal, that's just some basic SQL:

select field1, count(*)
from mytable
group by field1

[download]

Your other query seems to have some more convoluted logic, but if you can reformulate it in a more descriptive than prescriptive way, it will be translatable to SQL far more easy. You use vocabulary like "loop until find a valid entry", which I guess means "not equal to '-'". My guess at it is the following, but I'm unclear on whether you want to actually manipulate the data or just return a result:

select field1
from mytable l
where field2 <> '-'
order by field1, field2

[download]

or, if you only want all the "first" rows of field1 where field2 is not '-', then maybe the following returns what you need:

select field1, min(field2)
from mytable l
where field2 <> '-'
group by field1
order by field1, field2

[download]

Replies are listed 'Best First'.
Re^2: DBI efficient loop?
by lorenzov (Novice) on Jun 11, 2008 at 11:04 UTC
    hi guys, thanks for the replies. i still haven't solved the issue, but you made me think about using an hybrid solution.
    i thought about using mysql directly to manipulate fields, however it is possible that my knowledge/experience of mysql is just not good enough!
    i can't take a blanket query like the one suggested as records are chronological and depend on each other.
    filtering out all rows with a field2 containing '-' is not good enough as i need to substitute the value with a 'real' string which is extracted comparing field1 in either the previous or next row if the values in field 1 are the same.(if anyone has suggestions please pass them on!)
    however it was very useful to consider your suggestion to consider the query with min(fieldX) as i queried all the single entries matching entries with a 'real' value. 
    select pKey, field1, min(field2)
from myTable
where field2 <> '-' and pKey<20000
group by field1
order by pKey,field2

    [download]
    what i thought after this is that i can pass the values to a much smaller 'reference' array and use another pass to check rows with the reference and substitute where necessary.
    something i still have issues in grasping where is ideal to prepare and execute the queries in the loop, therefore suggestions will be much appreciated!
[reply]
[d/l]

In Section Seekers of Perl Wisdom