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

SQl logic inPerl

by Anonymous Monk
on Jul 14, 2010 at 17:18 UTC ( #849572=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi there Monks!
I don't know if should ask this question here but I might be lucky, maybe one of you had the same problem before.
Here is what I have, my issue is if I don't have the year 2009 in the database I would like to have another option, and doing all in the SQL query instead of checking afterwards in Perl. My code here is returning both years when what I want is to return 2009 if found only, otherwise get the year 2010, but not both.Has anyone encountered such issue before?

...code sql connect... my $year = param('year') || ''; # for testing only my $sql = "select name from mytable where acc = '12345' and ( (year='$year') or (year = $year + 1) )"; my $res= $dbh->exec_select( $sql ); ...code


I tried something like that, but its doesn't work:
...code my $year = param('year') || ''; # for testing only my $sql = "select name ,case year when '$year' then '$year' else '$year + 1' end from mytable where acc = '12345' and ( (year='$year') or (year = $year + 1) )"; ...code

Thanks for the help!!!

Comment on SQl logic inPerl
Select or Download Code
Re: SQl logic inPerl
by Corion (Pope) on Jul 14, 2010 at 17:46 UTC

    Maybe you want just

    my $sql = <<SQL; select name, year from mytable where acc = 12345 and (year =? or year =?+1) SQL; my $sth_year = $dbh->prepare($sql); $sth_year->execute( $year ); my $results = $sth_year->fetchall_arrayref(); ...
      That looks good on Perl but it doesn't fix the issue.

        So, what is the issue? Is the issue that you get two lines? Maybe you want to get the first line, when you ORDER BY some criterion? You haven't shown any example output and what result you prefer over what other result, so it's kinda hard to guess what you might mean.

Re: SQl logic inPerl
by brap (Pilgrim) on Jul 14, 2010 at 18:42 UTC
    How about something along the lines of:
    select name from mytable where acc = '12345' and year = (select max(year) from mytable where year in( $year, $year+1 +))
    The syntax is probably dependent on your database, though.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (7)
As of 2014-08-23 12:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (173 votes), past polls