Whether to perl or oracle

tradez has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monks, I come to you with limited sql power and mediocre perl knowledge asking you for your help in finishing this question. I am attempting to get a count of users in a database that fall into one of three categories. It is a decibel recording DB that keeps track at the levels at which modems are speaking on a RF network, in dB of course. The 3 groups are less then 30, 30-40, and 40 plus dB.

The current way I am doing it is with a mixture of the following sql:


my $sql = "select power_level, decode(power_level,null,'NA',decode(sig
+n(power_level-30),-1,'1',
                        decode(sign(power_level-40),1,'2','3')))
                 from wbr_power
                 where host_name in (
                       select host_name
                       from hosts
                       where market_id = '$market'
                       )";
[download]

And the following perl doing simple iterations on the array returned :

my ($lessThan, $inBetween, $greaterThan) = 0;
       foreach my $code (@powerCodes) {
         if ($code->[1] eq '1'){
           $lessThan++;
         }elsif ($code->[1] eq '2'){
           $inBetween++;
         }elsif ($code->[1] eq '3'){
           $greaterThan++;
         }
       }
[download]

There must be someway through joins and count() or sum() to do this all on the oracle end. But like I said, still pretty wet in the SQL relm. Please help.

Tradez
"Never underestimate the predicability of stupidity"
- Bullet Tooth Tony, Snatch (2001)
Comment on Whether to perl or oracle
Select or Download Code

Replies are listed 'Best First'.
Re: Whether to perl or oracle by VSarkiss (Monsignor) on Feb 27, 2002 at 18:17 UTC
Personally, I wouldn't put any of the binning logic in the SQL at all. In other words, retrieve the power levels unchanged from the database, then calculate the subtotals in Perl. You already have code in your Perl program to count the number of occurrences, except now it's much harder to see what's going on, because the logic behind the calculation is split between the Perl and the SQL (and is rather obscure in the SQL, IMHO). It's hard to say whether you could improve the SQL without knowing more about the database design. My first impulse was to recommend joining `wbr_power` and `hosts` rather than using a subquery, but that may get you multiple rows if the `hosts` table is not unique on `host_name`. More information about the data model would help to tune up your SQL without introducing errors. ;-) HTH	[reply] [d/l] [select]
Re: Re: Whether to perl or oracle by tradez (Pilgrim) on Feb 27, 2002 at 19:16 UTC
Sorry about not giving more info on the particular normilization, I know what it is like to try and answer these obscure questions without all the info. It is infact unique on the host_name, but I think the subquery is a simple and efficient way of doing what I need. Thanks for the input though! Tradez "Never underestimate the predicability of stupidity" - Bullet Tooth Tony, Snatch (2001)	[reply]
Re: Whether to perl or oracle by mr.dunstan (Monk) on Feb 27, 2002 at 18:46 UTC
I end up getting stuck on these all the time. The people at my work who are SQL gurus tell me to use SQL, to "leverage the power of the database" - but the perl folk say "use perl, it's way easier, plus you can do offline analysis of the data and take some of the work -off- the database." I say use perl! -mr.dunstan	[reply]
Re: Re: Whether to perl or oracle by tradez (Pilgrim) on Feb 27, 2002 at 19:15 UTC
Yeah, I am always a big fan of simple perl. Thanks for your thought. Tradez "Never underestimate the predicability of stupidity" - Bullet Tooth Tony, Snatch (2001)	[reply]