SQl quey problem

hi
when you put a ? in a statement that tells db2 that a value will be provided in run time
if you want to provide an actual string you should use something like
'your string'

the like predicate allows you to enter a pattern in which '_' represents one character and '%' represents zero or more characters, so to find all strings that have A as the second character, you will do
where column like '_A%'

I need user to enter the value. User takes a report with this query and every time he/she runs the KDCODEKDW value(as search criteria) is different. If the field was a numeric field there was not a problem it works. The problem is the value that provided in runtime is a string and I don't want to make user to work with sql code. Thats too much to ask

how do you do it with a numeric field ?

Minor note... It's not clear exactly _how_ this query is being run. A parameter marker (i.e., "?") generally will be linked to a host variable that has been typed as numeric or character. But it's _possible_ that the marker is essentially not typed.

A fundamental difference between numeric and character string values in SQL is that numeric values don't need quotes. When you enter your character string, are you entering quotes around it?

Tom

What really happens when I put ? for the value in the sql statement is that it asks me the value for the KDCODEKDW field in the query which is what I want. However, It expects a numeric value from me. The value I amtrting to query is (for example) 1234. But the type of this field is string in the database. So I need to enter a string value to come up with a result. I also tried to enter '1234', "1234", [1234] and same problem occured. I receive an error saying "invalid hexadecimal character found during conversion. Error in parameter 1."  I don't understand what is the problem

ok
now it's clear
i have 2 suggestions:
first, you can try to use this query
SELECT LSMB.MBLBUD, LSMB.MACODEVAW, LSMB.KDCODEKDW, LSMB.MBLBLN, LSMB.MBLMGE, LSMB.MBLSFR, LSMB.MBLSZS, LSMB.MBLELP, LSMB.ECCODEEIW, LSMB.MBLVKP, LSMB.ECCODEVPW, LSMB.LSLCODUWW, LSMB.LFCODELFW, LSMB.GECODEGEW
FROM "DC881DTA".LSMB LSMB
WHERE (LSMB.KDCODEKDW Like char(?)) AND (LSMB.BWCODEBWW='601') AND (LSMB.MBLBUD Between 20061101 And 20061231)

second, you can try to use
SELECT LSMB.MBLBUD, LSMB.MACODEVAW, LSMB.KDCODEKDW, LSMB.MBLBLN, LSMB.MBLMGE, LSMB.MBLSFR, LSMB.MBLSZS, LSMB.MBLELP, LSMB.ECCODEEIW, LSMB.MBLVKP, LSMB.ECCODEVPW, LSMB.LSLCODUWW, LSMB.LFCODELFW, LSMB.GECODEGEW
FROM "DC881DTA".LSMB LSMB
WHERE (LSMB.KDCODEKDW Like '?') AND (LSMB.BWCODEBWW='601') AND (LSMB.MBLBUD Between 20061101 And 20061231)
although i don't think the second one will work

what is the datatype of the column LSMB.KDCODEKDW  ?
are you sure it's a character datatype ?

Yes its char although we put number for it :)

The first option you have offered gives an error saying "STRING TO BE PREPARED CONTAINS INVALID USE OF PARAMETER MARKERS and second option is searching for ? character so returns no value...

I _hate_ to even mention this possibility, but it _might_ be worth testing to see if a hex-representation of the string can be entered. That way, SQL _might_ not try to insert the character digits as numerics. If nothing else, the result will give us clues.

Tom

script for creating white list

Sorry for the last message please ignore it. I did not exactly understand what you are talking about. Can you explain a little more?

aozdamar:

(Assuming you mean me...)

I'm not sure what tool you're using, but it's not a good tool for entering values for LIKE. LIKE expects a quoted string because it _always_ is a character string. But your tool isn't preserving quote marks when you type numerics. Your tool seems to assume that it can strip the quotes off of what you type.

Therefore, try to trick it.

Instead of typing '1234', type X'273132333427'. That's the hexadecimal equivalent of [quote-1-2-3-4-quote] in ASCII. A quote mark is X'27', a character '1' is X'31', etc.

I have no idea if it will work because I don't know what tool you're using to enter the value. However, we might at least see an error that's useful to us.

Also, have you tried entering two quote marks? I.e., instead of entering '1234', type ''1234'' where there are four single-quote marks in that value. That is, those aren't double-quote marks; they are two single-quote marks at the beginning and two more at the end. In an earlier response from you, you showed different ways that you tried to type your value; but you only showed single-quote marks, double-quote marks and square-brackets. You didn't say that you tried doubling the single-quote marks.

Overall, I'm not yet clear on why you're using LIKE. That's a difficult option to type into a parameter marker area.

Tom

The problem is I am making a BD2 connection to a AS400 server from windows xp pro with DSN..
I am using an external driver called Data direct. Do you think that might be the problem ??

problem still exists guys

I ahve solved the problem but going to give you points for your care. thanks