Beefy Boxes and Bandwidth Generously Provided by pair Networks Joe
Don't ask to ask, just ask
 
PerlMonks  

Re^2: s/Perl/SQL/ ?

by Freezer (Sexton)
on Sep 10, 2012 at 14:37 UTC ( #992773=note: print w/ replies, xml ) Need Help??


in reply to Re: s/Perl/SQL/ ?
in thread s/Perl/SQL/ ?

This bit of code (update: shown above) looks very interesting. Can anyone convert the SQL easily into MySQL speak? Am I right in thinking that the syntax shown is Oracle?

my $ST = $DB->prepare(<<EOSQL); SELECT ?, ?, A.entity_name FROM e_annotation_090812.annotation A join e_annotation_090812.Temp_table T on T.Line=A.user where length(A.entity_name)>0 and ( length(A.evidence_code)<12 or substr(A.evidence_code,length(A.evidence_code)-2,1)!='8') ) and (A.centre=? or A.centre=?) EOSQL $ST->execute($Reference, $Comparitor, ".$Reference.", ".$Comparitor.")


Comment on Re^2: s/Perl/SQL/ ?
Download Code
Re^3: s/Perl/SQL/ ?
by Corion (Pope) on Sep 10, 2012 at 14:39 UTC

    What makes you think that the SQL as shown is specific to Oracle?

    Also, why are you posting nonsensical syntax errors in what looks like it could be meant as Perl code?

    Maybe you want to tell us what syntax errors you actually get?

Re^3: s/Perl/SQL/ ?
by roboticus (Canon) on Sep 10, 2012 at 15:47 UTC

    Freezer:

    I used commonly-used[1] SQL constructs, so[2] it should be just fine. I used MySQL about 10 years ago, and I seem to recall that it was fairly standard SQL, so I would expect it to work. (Most of the things I remember as lacking (such as nested queries) were added to MySQL years ago.)

    I only mentioned Oracle to let you know that I'm not current on MySQL and that you *might* need to tweak it.

    However, as Corion mentioned in his initial response, there's no substitute for using indexes, benchmarking and any statement analysis feedback your database offers. After all, how the database decides to execute your SQL statement may be markedly different than what you might imagine. For example, I *expect* that the comparison / length / substring may be faster than like. But I have no idea what MySQL's opinion may turn out to be.

    Update: Updated in response to AM's reply. Thanks! [1] I changed "standard SQL" to "commonly-used SQL", and [2] removed "if MySQL uses standard SQL".

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

      To nit-pick, != is not standard SQL. <> is. And ANSI SQL has substring(string FROM idx FOR len), as opposed to substr(string, idx, len)

      Anyway, good work on the rewrite. I tried it myself and ended up with a similar statement (albeit with a few extra tables).

        Anonymous Monk:

        Nice catches. I've never read the SQL standard, so I should've phrased it something more like "commonly used/implemented". The substr is just a bobble on my side. I'm always using substring in perl and substr in SQL, so you'd think I would be sensitized to checking that, at least.

        Sigh.

        Updated both nodes.

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (12)
As of 2014-04-17 19:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (454 votes), past polls