lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jonathan Hendler <jonathan.hend...@gmail.com>
Subject Re: Simple problem with a nested entity and it's SQL
Date Wed, 28 Oct 2009 18:03:35 GMT
Thanks - that solution still causes an error.

But it helped me think of an SQL solution like so :
CONVERT ( '${table1.somethin_like_a_foreign_key}' , UNSIGNED INTEGER )

Convert the integer or NULL to a string, then back again. (ugly but it  
works)




On Oct 28, 2009, at 1:48 PM, Avlesh Singh wrote:

> Assuming this to be MySQL, will this work -
> SELECT *  FROM table2 WHERE id =
> IF(ISNULL(${table1.somethin_like_a_foreign_key}), 0,
> ${table1.somethin_like_a_foreign_key});
>
> Cheers
> Avlesh
>
> On Wed, Oct 28, 2009 at 11:12 PM, Jonathan Hendler <
> jonathan.hendler@gmail.com> wrote:
>
>> No - the SQL will fail to validate because at runtime it will look  
>> like
>>
>>
>> SELECT *  FROM table2 WHERE
>>> IS NOT NULL table1.somethin_like_a_foreign_key
>>> AND table1.somethin_like_a_foreign_key > 0
>>> AND id =
>>>
>>>
>>
>> Note the "id = "
>>
>>
>> On Oct 28, 2009, at 1:38 PM, Avlesh Singh wrote:
>>
>> Shouldn't this work too?
>>> SELECT *  FROM table2 WHERE IS NOT NULL
>>> ${table1.somethin_like_a_foreign_key} AND
>>> ${table1.somethin_like_a_foreign_key} > 0 AND id =
>>> ${table1.somethin_like_a_foreign_key}
>>>
>>> Cheers
>>> Avlesh
>>>
>>> On Wed, Oct 28, 2009 at 11:03 PM, Jonathan Hendler <
>>> jonathan.hendler@gmail.com> wrote:
>>>
>>> I have a nested entity on a jdbc data import handler that is  
>>> causing an
>>>> SQL
>>>> error because the second key is either NULL (blank when  
>>>> generating the
>>>> sql)
>>>> or non-zero INT.
>>>> The query is in the following form:
>>>>
>>>> <document name="content">
>>>>             <entity name="bl_lessonfiles"
>>>> transformer="TemplateTransformer" query="SELECT * FROM table1 ">
>>>>                   ...
>>>>                     <entity name="user_index" query="SELECT *  FROM
>>>> table2 WHERE  id = ${table1.somethin_like_a_foreign_key} ">
>>>>                         ....
>>>>                     </entity>
>>>>             </entity>
>>>>     </document>
>>>>
>>>> Is the only way to avoid this to modify the source DB schema to  
>>>> be NOT
>>>> NULL
>>>> so it always returns at least a 0?
>>>>
>>>> - Jonathan
>>>>
>>>>
>>


Mime
View raw message