I have a MSSQL 2019 database with table `[objs]` with a column `city`.
When indexing I want to use that value OR if a match in table `cities` is found
that value instead.
When I run this on my database (it has just 5 records for now) it returns the
desired output:
SELECT IIF(citygeonameid=0,city,c.name) as city FROM objs o
LEFT JOIN cities c on c.geonameid=o.citygeonameid
In my solr data config I want to index the value from `objs.city` or if a match
is found in `cities` the value from there and store it in a field `city`. I
want to use the same name since my entire application is already using this
field and I don't want to rename it everywhere. Also `objs.city` serves another
purpose in my application logic.
I tried these two options in my data-config, but both solutions still show the
`objs.city` value from the database rather than the `normalizedgeo.city` value
from my data-config. I commented out the original field name, but still that
value shows.
I've tried restarting Solr, reloading the data-config, and have done a
succesful full import each time to test. All to no avail.
How is this possible?
**data-config.xml**
<!--<field name="city" column="city" />-->
<entity name="normalizedgeo" query="SELECT
IIF(citygeonameid=0,city,c.name) as city FROM objs o
LEFT JOIN cities c on c.geonameid=o.citygeonameid
WHERE o.id=${obj.id}">
<field name="city" column="city" />
</entity>
<!--<field name="city" column="city" />-->
<entity name="normalizedgeo" query="SELECT
IIF(citygeonameid=0,city,c.name) as city FROM objs o
LEFT JOIN cities c on c.geonameid=o.citygeonameid
WHERE o.id=${obj.id}">
<!--<field name="city" column="city" />-->
</entity>
