I have a query that I can't get to work in DB2/AS400.
select integer(score(ADRLIN1, :param1)*100) as RELEVANCEADR,
ADRLIN1, PSTCOD from MYSCHEMA.MYTABLE
where contains(ADRLIN1, :param2) = 1
and pstcod like :param3
order by RELEVANCEADR desc
When I try to run the query above, and entering values in my parameters, I get [SQL0418] Use of parameter marker not valid.
This isn't a big issue. A google search told me to CAST the parameters prior to using them. So then I change the query to this:
select integer(score(ADRLIN1, CAST(:param1 AS CHAR))*100) as RELEVANCEADR,
ADRLIN1, PSTCOD from MYSCHEMA.MYTABLE
where contains(ADRLIN1, CAST(:param2 AS CHAR)) = 1
and pstcod like :param3 order by RELEVANCEADR desc
With the following values:
And I get an empty resultset. However, if I actually fill in the query with literals, the query works.
select integer(score(ADRLIN1, '19 EDGEWOOD BLVD')*100) as RELEVANCEADR,
ADRLIN1, PSTCOD from MYSCHEMA.MYTABLE
where contains(ADRLIN1, '19 EDGEWOOD BLVD') = 1
and pstcod like '%68046%'
order by RELEVANCEADR desc
The query above returns a valid record.
My question is, how can I get the score and contains functions to work with passed in parameters instead of using hard-coded strings?
As @Mustaccio pointed out in the comments, casting as a CHAR is the equivalent to CHAR(1). I corrected this to declare an actual length and the query is working.