Alan Humphrey wrote: >> -----Original Message----- >> From: Marc Mims [mailto:[email protected]] >> Sent: Friday, May 29, 2009 4:16 PM >> To: DBIx::Class user and developer list >> Cc: DBIx: >> Subject: Re: [Dbix-class] MSSQL/Page/Prefetch problem >> >> * Peter Rabbitson <[email protected]> [090529 15:07]: >>> Alan Humphrey wrote: >>>> Hi all - >>>> >>>> I just upgraded to version 0.08103 and have encountered a problem >> using page >>>> in conjunction with prefetch against a MSSQL database. >>>> >>>> Given: >>>> >>>> my $result = $schema->resultset('Surveys')->search( >>>> {}, >>>> { page => 1, >>>> rows => 25, >>>> order_by => ['survey_site_id', 'survey_date'], >>>> prefetch => 'site' >>>> } >>>> )->first(); >>>> >>>> Executing the code against MSSQL generates this error: >>>> >>>> DBI Exception: DBD::Sybase::st execute failed: Server message >> number=8156 >>>> severity=16 state=1 line=1 server=SQLA4 text=The column 'comments' >> was >>>> specified multiple times for 'foo'. [for Statement " SELECT * FROM >>>> ( >>>> SELECT TOP 25 * FROM >>>> ( >>>> SELECT TOP 25 me.id, me.survey_site_id, me.start_time, >> me.end_time, >>>> me.zero_ref_point, me.survey_date, me.weather, me.precipitation, >>>> me.sea_state, me.tide_movement, me.visibility_distance, >>>> me.poor_visibility_reason, me.poor_visibility_reason_other, >> me.equipment, >>>> me.binocular_magnification, me.scope_magnification, >> me.walker_count, >>>> me.dog_count, me.power_boat_count, me.unpowered_boat_count, >>>> me.other_activities_name, me.other_activities_count, me.comments, >>>> me.is_complete, me.raptor1_bird_id, me.raptor1_count, >> me.raptor1_affect, >>>> me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect, >> me.raptor3_bird_id, >>>> me.raptor3_count, me.raptor3_affect, site.site_code, >> site.site_name, >>>> site.position, site.elevation, site.comments FROM surveys me JOIN >>>> survey_sites site ON site.site_code = me.survey_site_id ORDER BY >>>> survey_site_id ASC, survey_date ASC >>>> ) AS foo >>>> ORDER BY survey_site_id DESC, survey_date DESC >>>> ) AS bar >>>> ORDER BY survey_site_id, survey_date >>>> >>>> >>>> This used to work. Any ideas? Thanks! >>>> >>>> - Alan >>>> >>> Can you debug the SQL for us, and figure out why it complains about >>> a column duplicate when there cleare isn't one? >> There are indeed 2: me.comments and sites.comments. In the outer >> selects, they conflict. >> >> -Marc > > Right. I don't understand why the outer selects are there. The inner > select works fine on its own. >
And what did a previous version generate as far as SQL goes (the one that used to work)? _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
