Sorted Moose Case
Its Sort the Case day here in the Moose-Pen
It appears that one of the more common uses for the CASE statement in SQL is in a 'GROUP BY' or at least a good 80% of the tutorials for SQL I have looked at have it as an example so let see how well the present Driver::DBI handles it.
To start I just add this test to the '60_order_by.t' test case;
{
key => 'sorts',
sorts => [{whens=>[{ left => { name => 'Price', },
right => { value => '10' },
operator => '<',
statement=>{value=>'under 10$'}},
[{left => { name =>'Price'},
right => { value => '10' },
operator => '>=',
},
{ condition => 'and',
left => {name=>'Price'},
right => { value => '30' },
operator => '<=',
statement=>{value=>'10~30$'}}
],
[{left => {name => 'Price'},
right => { value => '30' },
operator => '>',
},
{ condition => 'and',
left => {name=>'Price'},
right => { value => '100' },
operator => '<=',
statement=>{value=>'30~100$'}}
],
{ statement=>{value=>'Over 100$'}},
],}],
caption => "Order by with Case ",
retrieve => {
params => [10,'under 10$',10,30,'10~30$',30,100,'30~100$','Over 100$'],
sql =>
"SELECT people.first_name, people.last_name, people.user_id FROM people ORDER BY CASE WHEN Price < ? THEN ? WHEN Price >= ? AND Price <= ? THEN ? WHEN Price > ? AND Price <= ? THEN ? ELSE ? END",
},
},
a simple enough test no reason to add in the other three CUD actions as I have proven they are unaffected by a 'Sort' in earlier tests; Now on my first run of the above I get
...
ok 21 - Order by with Case retrieve SQL correct
ok 22 - Order by with Case retrieve params correct
Bonus.
I also see that is it use in a conditional clause in a 'WHERE' so I am going to get cocky and add this test;
{
caption => 'One Case left field conditions',
key => 'conditions',
conditions => [
{
left => {whens=>[{ left => { name => 'Price', },
right => { value => '10' },
operator => '<',
statement=>{value=>'under 10$'}},
[{left => { name =>'Price'},
right => { value => '10' },
operator => '>=',
},
{ condition => 'and',
left => {name=>'Price'},
right => { value => '30' },
operator => '<=',
statement=>{value=>'10~30$'}}
],
[{left => {name => 'Price'},
right => { value => '30' },
operator => '>',
},
{ condition => 'and',
left => {name=>'Price'},
right => { value => '100' },
operator => '<=',
statement=>{value=>'30~100$'}}
],
{ statement=>{value=>'Over 100$'}},
],},
right => { value => 'test1' },
operator => '=',
condition => 'AND'
},
],
retrieve => {
sql =>
"SELECT people.first_name, people.last_name, people.user_id FROM people WHERE CASE WHEN Price < ? THEN ? WHEN Price >= ? AND Price <= ? THEN ? WHEN Price > ? AND Price <= ? THEN ? ELSE ? END = ?",
params => [10,'under 10$',10,30,'10~30$',30,100,'30~100$','Over 100$','test1']
},
update => {
container => $container,
sql =>
"UPDATE people SET first_name = ?, last_name = ? WHERE CASE WHEN Price < ? THEN ? WHEN Price >= ? AND Price <= ? THEN ? WHEN Price > ? AND Price <= ? THEN ? ELSE ? END = ?",
params => [ 'Bill', 'Bloggings', 10,'under 10$',10,30,'10~30$',30,100,'30~100$','Over 100$','test1' ]
},
delete => {
sql => "DELETE FROM people WHERE CASE WHEN Price < ? THEN ? WHEN Price >= ? AND Price <= ? THEN ? WHEN Price > ? AND Price <= ? THEN ? ELSE ? END = ?",
params => [10,'under 10$',10,30,'10~30$',30,100,'30~100$','Over 100$','test1']
},
},
to the '30_where_basic.t' and see if I come up roses.
Here is my result of my first run;
...
not ok 81 - One Case left field conditions retrieve SQL correct
ok 82 - One Case left field conditions retrieve params correct
ok 83 - One Case left field conditions update SQL correct
ok 84 - One Case left field conditions update params correct
ok 85 - One Case left field conditions delete SQL correct
ok 86 - One Case left field conditions delete params correct
and the only real problem was my expected SQL was off. I expected this;
SELECT people.first_name, people.last_name, people.user_id
but the generated was;
SELECT people.first_name First, people.last_name Last, people.user_id "User ID"
and that is only a problem with the content of my test.
-- sql =>
"SELECT people.first_name, people.last_name, people.user_id FROM people WHERE CASE WHEN Price < ? THEN ? WHEN Price >= ? AND Price <= ? THEN ? WHEN Price > ? AND Price <= ? THEN ? ELSE ? END = ?",
++ sql =>
'SELECT people.first_name First, people.last_name Last, people.user_id "User ID" FROM people WHERE CASE WHEN Price < ? THEN ? WHEN Price >= ? AND Price <= ? THEN ? WHEN Price > ? AND Price <= ? THEN ? ELSE ? END = ?',
and now I get that to pass
...
ok 81 - One Case left field conditions retrieve SQL correct
ok 82 - One Case left field conditions retrieve params correct
Not too bad.
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
Leave a comment