Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

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!!!

Replies are listed 'Best First'.
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?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://849572]
Approved by moritz
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (3)
As of 2018-03-24 17:07 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (299 votes). Check out past polls.