byterock

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.