You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by "keshari.prerna" <ke...@gmail.com> on 2013/10/11 23:02:19 UTC
Replace NULL with 0 while Indexing
Hello,
One of my indexing field have NULL values and i want it to be replaces with
0 while indexing itself. So that when i search after indexing it gives me 0
instead of NULL.
This is my data-config.xml and duration is the field which has null values.
<dataConfig>
<dataSource type="JdbcDataSource"
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://trdbadhoc/test_results"
responseBuffering="adaptive"
batchSize="-1"
user="results"
password="resultsloader"/>
<document>
<entity name="Test_Syndrome"
pk="id"
query="SELECT TS.id AS id, TET.type AS error_type, TS.syndrome AS
syndrome, S.start_date, SE.session_id AS sessionid,
S.duration, TL.logfile, J.job_number AS job, cluster,
S.hostname, platform FROM Test_Syndrome AS TS
STRAIGHT_JOIN Session_Errors AS SE ON (SE.test_syndrome_id = TS.id)
STRAIGHT_JOIN Session AS S ON (S.id = SE.session_id)
STRAIGHT_JOIN Test_Run AS TR ON (TR.session_id = SE.session_id)
STRAIGHT_JOIN Test_Log AS TL ON (TL.id = TR.test_log_id)
STRAIGHT_JOIN Job AS J ON (J.id = TL.job_id)
STRAIGHT_JOIN Cluster AS C ON (C.id = J.cluster_id)
STRAIGHT_JOIN Platform ON (TR.platform_id = Platform.id)
STRAIGHT_JOIN Test_Error_Type TET ON (SE.test_error_type_id =
TET.id)">
<Field column="id" name="id"/>
<Field column="error_type" name="error_type"/>
<Field column="syndrome" name="syndrome"/>
<Field column="sessionid" name="sessionid"/>
<Field column="duration" name="duration"/>
<Field column="logfile" name="logfile"/>
<Field column="job" name="job"/>
<Field column="cluster" name="cluster"/>
<Field column="hostname" name="hostname"/>
<Field column="platform" name="platform"/>
</entity>
</document>
</dataConfig>
Please help.
Thanks & Regards,
Prerna
--
View this message in context: http://lucene.472066.n3.nabble.com/Replace-NULL-with-0-while-Indexing-tp4095059.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: Replace NULL with 0 while Indexing
Posted by Arcadius Ahouansou <ar...@menelic.com>.
What about using COALESCE in SQL?
like:
select COALESCE(duration, 0) as duration from mytable
On 11 October 2013 22:02, keshari.prerna <ke...@gmail.com> wrote:
> Hello,
>
> One of my indexing field have NULL values and i want it to be replaces with
> 0 while indexing itself. So that when i search after indexing it gives me 0
> instead of NULL.
>
> This is my data-config.xml and duration is the field which has null values.
>
> <dataConfig>
> <dataSource type="JdbcDataSource"
> driver="com.mysql.jdbc.Driver"
> url="jdbc:mysql://trdbadhoc/test_results"
> responseBuffering="adaptive"
> batchSize="-1"
> user="results"
> password="resultsloader"/>
> <document>
> <entity name="Test_Syndrome"
> pk="id"
> query="SELECT TS.id AS id, TET.type AS error_type, TS.syndrome AS
> syndrome, S.start_date, SE.session_id AS sessionid,
> S.duration, TL.logfile, J.job_number AS job, cluster,
> S.hostname, platform FROM Test_Syndrome AS TS
> STRAIGHT_JOIN Session_Errors AS SE ON (SE.test_syndrome_id = TS.id)
> STRAIGHT_JOIN Session AS S ON (S.id = SE.session_id)
> STRAIGHT_JOIN Test_Run AS TR ON (TR.session_id = SE.session_id)
> STRAIGHT_JOIN Test_Log AS TL ON (TL.id = TR.test_log_id)
> STRAIGHT_JOIN Job AS J ON (J.id = TL.job_id)
> STRAIGHT_JOIN Cluster AS C ON (C.id = J.cluster_id)
> STRAIGHT_JOIN Platform ON (TR.platform_id = Platform.id)
> STRAIGHT_JOIN Test_Error_Type TET ON (SE.test_error_type_id =
> TET.id)">
>
> <Field column="id" name="id"/>
> <Field column="error_type" name="error_type"/>
> <Field column="syndrome" name="syndrome"/>
> <Field column="sessionid" name="sessionid"/>
> <Field column="duration" name="duration"/>
> <Field column="logfile" name="logfile"/>
> <Field column="job" name="job"/>
> <Field column="cluster" name="cluster"/>
> <Field column="hostname" name="hostname"/>
> <Field column="platform" name="platform"/>
>
> </entity>
> </document>
> </dataConfig>
>
> Please help.
>
> Thanks & Regards,
> Prerna
>
>
>
>
>
> --
> View this message in context:
> http://lucene.472066.n3.nabble.com/Replace-NULL-with-0-while-Indexing-tp4095059.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>
Re: Replace NULL with 0 while Indexing
Posted by Karol Sikora <ka...@laboratorium.ee>.
Or you can use custom update preprocessor.
W dniu 11.10.2013 23:02, keshari.prerna pisze:
> Hello,
>
> One of my indexing field have NULL values and i want it to be replaces with
> 0 while indexing itself. So that when i search after indexing it gives me 0
> instead of NULL.
>
> This is my data-config.xml and duration is the field which has null values.
>
> <dataConfig>
> <dataSource type="JdbcDataSource"
> driver="com.mysql.jdbc.Driver"
> url="jdbc:mysql://trdbadhoc/test_results"
> responseBuffering="adaptive"
> batchSize="-1"
> user="results"
> password="resultsloader"/>
> <document>
> <entity name="Test_Syndrome"
> pk="id"
> query="SELECT TS.id AS id, TET.type AS error_type, TS.syndrome AS
> syndrome, S.start_date, SE.session_id AS sessionid,
> S.duration, TL.logfile, J.job_number AS job, cluster,
> S.hostname, platform FROM Test_Syndrome AS TS
> STRAIGHT_JOIN Session_Errors AS SE ON (SE.test_syndrome_id = TS.id)
> STRAIGHT_JOIN Session AS S ON (S.id = SE.session_id)
> STRAIGHT_JOIN Test_Run AS TR ON (TR.session_id = SE.session_id)
> STRAIGHT_JOIN Test_Log AS TL ON (TL.id = TR.test_log_id)
> STRAIGHT_JOIN Job AS J ON (J.id = TL.job_id)
> STRAIGHT_JOIN Cluster AS C ON (C.id = J.cluster_id)
> STRAIGHT_JOIN Platform ON (TR.platform_id = Platform.id)
> STRAIGHT_JOIN Test_Error_Type TET ON (SE.test_error_type_id =
> TET.id)">
>
> <Field column="id" name="id"/>
> <Field column="error_type" name="error_type"/>
> <Field column="syndrome" name="syndrome"/>
> <Field column="sessionid" name="sessionid"/>
> <Field column="duration" name="duration"/>
> <Field column="logfile" name="logfile"/>
> <Field column="job" name="job"/>
> <Field column="cluster" name="cluster"/>
> <Field column="hostname" name="hostname"/>
> <Field column="platform" name="platform"/>
>
> </entity>
> </document>
> </dataConfig>
>
> Please help.
>
> Thanks & Regards,
> Prerna
>
>
>
>
>
> --
> View this message in context: http://lucene.472066.n3.nabble.com/Replace-NULL-with-0-while-Indexing-tp4095059.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>
--
Karol Sikora
Kierownik Informatyczny Projektu CBN - Interfejs 2.0
+48 781 493 788
Laboratorium EE
ul. Mokotowska 46A/23 | 00-543 Warszawa |
www.laboratorium.ee | www.laboratorium.ee/facebook
Re: Replace NULL with 0 while Indexing
Posted by Developer <bb...@gmail.com>.
You can also use
SELECT ISNULL(myColumn, 0 ) FROM myTable
Reference: http://www.w3schools.com/sql/sql_isnull.asp
--
View this message in context: http://lucene.472066.n3.nabble.com/Replace-NULL-with-0-while-Indexing-tp4095059p4095550.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: Replace NULL with 0 while Indexing
Posted by Shawn Heisey <so...@elyograg.org>.
On 10/11/2013 3:02 PM, keshari.prerna wrote:
> One of my indexing field have NULL values and i want it to be replaces with
> 0 while indexing itself. So that when i search after indexing it gives me 0
> instead of NULL.
Most of your other replies have concentrated on the SQL side of things.
One mentioned custom update processors. If you want a solution in SQL,
or you are comfortable writing custom code, that's no problem, but I
think there's another way.
NULL values in SQL, when imported with DIH, result in the Solr field
simply not existing for that document. If you add a default value to
your schema, then anytime there is no value, the default will be used.
This should be much simpler than the other solutions.
<field name="myField" type="long" default="0" indexed="true"
stored="true"/>
http://wiki.apache.org/solr/SchemaXml#Common_field_options
Thanks,
Shawn
Re: Replace NULL with 0 while Indexing
Posted by "keshari.prerna" <ke...@gmail.com>.
Thank you everyone, I think COALESCE(duration, 0) and ISNULL will solve my
problem.
--
View this message in context: http://lucene.472066.n3.nabble.com/Replace-NULL-with-0-while-Indexing-tp4095059p4095678.html
Sent from the Solr - User mailing list archive at Nabble.com.