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:
- param1 --> 19 EDGEWOOD BLVD
- param2 --> 19 EDGEWOOD BLVD
- param3 --> %68046%
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?
param1andparam2in your code. Are you passing the same string for both?param2to beparam1to ensure the same string is passed, the resultset is empty.CHARis equivalent toCHAR(1), so all your input values are truncated. Use the actual data type when casting.param3is defined asCHARvs.VARCHARis that aCHAR' variable is always the defined length, andc pads on the left with blanks. So if you put%68046%` in a CHAR(10) field, it is as if you used'%68046% 'and will only find rows wherepstcodcontains 68046andhas three blanks at the end.