CODEWITHSUNDEEP
sqldb2ibm-midrange
ThatGuyYouKnow
ThatGuyYouKnow

Reputation: 227

DB2 Query Functions Not Working With Parameters

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?

Upvotes: 2

Views: 1359

Answers (1)

ThatGuyYouKnow
ThatGuyYouKnow

Reputation: 227

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.

Upvotes: 1

Related Questions