[Dbix-class] subquery insert for MSSQL

[Peter Rabbitson]

On Tue, Jul 21, 2009 at 05:11:22PM -0500, fREW Schmidt wrote:
> Hey guys,
> 
> I am trying to autoincrement the last part of a composite primary key and
> it's not working like I hoped and dreamed that it would.  It's close though!
> 
> Here's the code in my result class:
> 
> sub new {
> >    my $class = shift;
> >    my (@args) = @_;
> >    my $self = $class->next::method(@args);
> >    $self->id($self->_generate_id);
> >    return $self;
> > }
> >
> > method _generate_id {
> >    return $self->result_source->resultset->search({
> >          work_order_id => $self->work_order_id,
> >          work_scope_id => $self->work_scope_id
> >       },{
> >          columns  => { new_id => \'MAX(id) + 1' },
> >       })->get_column('new_id')->as_query;
> > }
> >
> 
> Here is the generated SQL:
> 
> INSERT INTO WorkScopeOperations (
> >    department,
> >    description,
> >    id,
> >    signature_required,
> >    work_order_id,
> >    work_scope_id
> > ) VALUES (
> >    ?,
> >    ?, (
> >       SELECT MAX(id) + 1 FROM WorkScopeOperations me
> >       WHERE ( ( work_order_id = ? AND work_scope_id = ? ) )
> >     ),
> >     ?,
> >     ?,
> >     ?
> 
> );
> >
> 
> Here is the error message:
> 
> [error] DBIx::Class::ResultSet::create(): DBI Exception: DBD::ODBC::st
> > execute failed: [Microsoft][SQL Native Client][SQL Server]Subqueries are not
> > allowed in this context. Only scalar expressions are allowed. (SQL-42000)
> >
> 
> I read online about the syntax to do what I want with SQL Server, which
> leads me to believe that I probably can't do this with DBIC at this point,
> but hopefully I'm wrong.
> 
> Any tips?
> 

Here is tip#1 - how about showing us the SQL which will make MSSQL happy? :)