Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re: s/Perl/SQL/ ?

by Neighbour (Friar)
on Sep 11, 2012 at 08:37 UTC ( #992954=note: print w/ replies, xml ) Need Help??


in reply to s/Perl/SQL/ ?

Let's see if I can still do this:

my $ar_data = $dbh->selectall_arrayref(qq ( SELECT noot.Reference, aap.Comparitor, aap.entity_name FROM ( SELECT ? AS Comparitor, entity_name FROM e_annotation_090812.annotation AS A JOIN e_annotation_090812.Temp_table AS T ON A.user = T.Line WHERE IFNULL(A.entity_name, '') <> '' AND A.evidence_code NOT LIKE '%_________8__' AND A.centre = ? ) AS aap JOIN ( SELECT ? AS Reference, entity_name FROM e_annotation_090812.annotation AS B JOIN e_annotation_090812.Temp_table AS U ON B.user = U.Line WHERE IFNULL(B.entity_name, '') <> '' AND B.evidence_code NOT LIKE '%_________8__' AND B.centre = ? ) AS noot ON aap.entity_name = noot.entity_name ), { Slice => {} }, ".${Comparitor}.", ".${Comparitor}.", ".${Referenc +e}.", ".${Reference}.") or die("Error executing query: " . $dbh->errs +tr);
Note that this is untested :)
Feedback is appreciated

Edit: Could you provide a small portion of (dummy) testdata to work with, as well as the DDL (the result of SHOW CREATE TABLE e_annotation_090812.annotation) of both tables?
This is handy to find further optimizations (for example, if evidence_code is a numerical field, using LIKE is very bad as it needs to typecast and thus can't use an index if present).

Edit2: Changed centre LIKE ? to centre = ? which should be better unless you're using wildcards (_ or %) in $Reference or $Comparitor


Comment on Re: s/Perl/SQL/ ?
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://992954]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (19)
As of 2015-07-06 13:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (75 votes), past polls