Stuck on the Moose Case

Another Case Day here in the Moose-pen

So I decided to do some more case work today specificity their rather odd little SQL to start;


SELECT CASE WHEN Price < 100 THEN 'under 100$'
WHEN Price in (105, 110,120) THEN Price 105,110 or 120$'
ELSE 'Over 120$' END
FROM Products

and the above comes out as this test;

{
caption => 'Retrieve with case using In clause',
key => 'elements',
elements => [
{
whens => [
{
left => { name => 'Price', },
right => { value => '100' },
operator => '<',
statement => { value => 'under 100$' }
},
{
left => { name => 'Price' },
right => [
{ value => '105' },
{ value => '110' },
{ value => '120' },
],
operator => 'in',
statement => { value => 'Price 105,110 or 120$' }
},
{ statement => { value => 'Over 120$' } },
]
}
],
retrieve => {
sql =>
"SELECT CASE WHEN Price < ? THEN ? WHEN Price IN (?,?,?) THEN ? ELSE ? END FROM Products",
params => [
100, 'under 100$', 105, 110,120, 'Price 105,110 or 120$','Over 120$'
]
},

}


and on my first run I get a fail;

Expected SELECT CASE WHEN Price < ? THEN ? WHEN Price IN (?,?,?) THEN ? ELSE ? END FROM Products
Generated SELECT CASE WHEN Price < ? THEN ? WHEN Price in ?,?,? THEN ? ELSE ? END FROM Products

So two quick things I am missing my parentheses for my 'IN' an my 'IN' is in lower case. Now I have looked at the second problem before in my '32_where_operators.t' test case but the way I call that code the items has to be a 'Predicate' class in this case I am dealing with an “When' class.

Fortunately a 'when' is just an extended version of a 'predicate' class so I think I can get away with this little change to the '_field_sql' sub;


...
elsif (ref($element) eq "Database::Accessor::Case::When"){
++ return $self->_predicate_sql($element);
-- return join(" ",$self->_field_sql($element->left(),$use_view)
-- ,$element->operator
-- ,$self->_field_sql($element->right(),$use_view));
}
elsif (ref($element) eq "Database::Accessor::Expression"){


and this time I get;

Expected--> SELECT CASE WHEN Price < ? THEN ? WHEN Price IN (?,?,?) THEN ?
ELSE ? END FROM Products
Genrated-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN Products.Price
IN (?,?,?) THEN ? ELSE ? END FROM Products

ok I got the correct parentheses in place and even my IN is in UC but I see that now I have the 'table/view' name appearing in my generated SQL. So I solved one problem but I see I will have to fix up that 'table/view' issue I believe my default on that is to always show the 'table/view' So I have some changes to do.

After changing my expected results for my first test I reran and got


Expected-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN Products.Price >= ? AND Products.Price <= ? THEN ? WHEN Products.Price > ?
AND Products.Price <= ? THEN ? ELSE ? END price_group FROM Products
Gennerated-> SELECT CASE WHEN Products.Price < ? THEN ? WHEN Products.Price >= ? AND AND Products.Price <= ? THEN ? WHEN Products.Price >
? AND AND Products.Price <= ? THEN ? ELSE ? END

I am getting an extra and in there I think I can get rid of that by taking out a little code in the '_field_sql' sub where I check for a condition on the when

...
foreach my $condition (@{$when}){
-- $condition_sql .= " ".$condition->condition." "
-- if ($condition->condition);
$condition_sql .= $self->_field_sql($condition,0);
...

and on this run I get;

ok 1 - Retrieve with case statement in elements retrieve SQL correct
ok 2 - Retrieve with case statement in elements retrieve params correct
ok 3 - Retrieve with case using In clause retrieve SQL correct
ok 4 - Retrieve with case using In clause retrieve params correct

so things are working out for me.

IMG_6641a.jpg

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations