SQl logic inPerl

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
[download]

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
[download]

Thanks for the help!!!

Comment on SQl logic inPerl Select or Download Code

Replies are listed 'Best First'.
Re: SQl logic inPerl by Corion (Patriarch) 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(); ...` [download]	[reply] [d/l]
Re^2: SQl logic inPerl by Anonymous Monk on Jul 14, 2010 at 18:24 UTC
That looks good on Perl but it doesn't fix the issue.	[reply]
Re^3: SQl logic inPerl by Corion (Patriarch) on Jul 14, 2010 at 18:29 UTC
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.	[reply] [d/l]
Re^4: SQl logic inPerl by Anonymous Monk on Jul 14, 2010 at 19:49 UTC
Re^5: SQl logic inPerl by Corion (Patriarch) on Jul 14, 2010 at 19:53 UTC
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 +))` [download] The syntax is probably dependent on your database, though.	[reply] [d/l]