Array perl issue

[Raul2005]

This is a partial result from a query...
EMAIL KEYTYPE MESSAGE CP_ID
@teck.com H msgSome 14019
@some1.com H TEST... 34481
@some1.com H TEST2.. 34481


In this PARTICULAR case two emails need to be fired the first email will be sent to

@teck.com and the "EMAIL body" should be :
H msgSome 14019

The second email will be sent to "@some1.com" and this should contain:
@some1.com H TEST... 34481
@some1.com H TEST2.. 34481

As you can see the body of the second email contains data from
two records which are related with the CP_ID = 34481

I need a help to breack up between blocks of data..

Raul

 

[TrojanWarBlade]

push @{$hash{$email}}, $record;

Then take a look at your data structure with Data:umper.


Trojan.

 

[Raul2005]

Thanks for your help

 

[Schoover]

Or just add a "group by" to your query, then as you loop through records, store the cp_id in a hold_cp_id and if cp = hold then concat. the build the msg bodies and send when cp_id changes.

 

[Raul2005]

I try GROUP BY in my query but ORTACLE DOESN'T support it.
If you have another approach let me know
Thanks
Any way

 

[Raul2005]

schoover would you give me a sample code.. I just start using perl not familiar with the syntax.

Thanks

 

[Schoover]

Well a sample group by would look something like this:

Select EMAIL, KEYTYPE, MESSAGE, CP_ID
from mytable
where <criteria>
group by cp_id (or would you want to group by email addy?)
(from your example either way would work)

Your Database should support Group By, I think its standard stuff for Oracle. Once you can get the group by query working then we can work on your perl logic.

 

[Schoover]

P.S. are you using DBIBD to do your database stuff in perl? (i'm assuming you are at this point)

 

[Raul2005]

yes
I am connecting to databse like this:


use DBD::ODBC;


my $dbh = DBI->connect( 'DBI:ODBC:TEST',
'user',
'PASS',
) || die "Database connection not made: $DBI::errstr";

 

[Schoover]

Ok....so let us know if you can get a Group By query to work, if not, then Trojan's idea will work better for you.

 

[Raul2005]

Select EMAIL, KEYTYPE, MESSAGE, CP_ID
from mytable
where <criteria>
group by cp_id,EMAIL, KEYTYPE, MESSAGE

This is my grouping
Thanks for any help

 

[Schoover]

if your grouping your emails by recipient and cp_id, then why not just group by those 2 fields...and drop the keytype and message from the groupby statement?

if I were writing this, I would probably use hashes like Trojan said but that would require much more explanation here. So from simple coding standpoint, look at it this way: As you loop through your results, you want to send <some email> any time the cp_id changes, correct?

So just loop through your results, storing the email_addr and message text in some arrays, ...in each loop hold the cp_id in a different variable, and also in each loop compare the current cp_id to the one you are holding. If they are different, go get all the junk from your stored arrays and send the email. ...I know that's not exactly ...but pretty close to the logic you'll need.

Its either that or get familiar with Hashes.... and use the cp_id as your Hash key, imagine you have a bunch of buckets and you use one bucket for each cp_id, ....sort it all out then for each bucket you'll send an email with the info inside it.