[Dbix-class] Help with Arbitrary SQL through a custom ResultSource

[Steve Kirkup]

Matt S Trout wrote:
> On Mon, Mar 10, 2008 at 12:36:22PM -0700, Steve Kirkup wrote:
>   
>> I followed the cookbook for writing a custom ResultSource for executing 
>> custom sql.
>>
>> However when I run the code, it I get SQL errors, it looks like it 
>> concats my customer after the FROM keyword.
>>
>> My code,
>>
>>    my $new_source = $source->new( $source );
>>       $new_source->source_name( $source_name );
>>       $new_source->name( \'some stuff' );
>>   
>>    $oracle->register_source( $source_name => $new_source );
>>
>>    return [ $oracle->resultset( $source_name )->search( {},
>>        { bind => [ $cmd_options->{'start_date'}->mdy(q{/}),
>>                    $cmd_options->{'end_date'  }->mdy(q{/})  ] }
>>    )];
>>
>>
>> The error I get is,
>>
>> DBIx::Class::ResultSet::search(): DBI Exception: DBD::Oracle::db 
>> prepare_cached failed: ORA-00903: invalid table name (DBD ERROR: error 
>> possibly near <*> indicator at char 235 in 'SELECT me.currency_tax, 
>> me.royalty, me.product_info, me.quantity, me.invoice_id, me.tax, 
>> me.currency_price, me.item_id, me.created, me.amount, 
>> me.currency_amount, me.media_id, me.product_id, me.customer, me.price, 
>> me.promotion_id FROM <*>some stuff
>>
>> I would just like to run my SQL as a standalone rather than try and 
>> force intergration with SQL::Abstract, at least in this case.  Could 
>> someone help me out?
>>     
>
> The custom resultsource trick expects you to provide a full (SELECT ...)
> subquery. Do that and it'll work.
>
>   
I guess, I don't understand what the subquery is supposed to encompass.  
My SQL is basically

SELECT * FROM Table INNER JOIN Sub_Table ON ( Table.id = Sub_Table.id ) 
WHERE Table.id = '1'

Does this mean I should drop the 'SELECT * FROM ' from the SQL in order 
to work?

Steve K