SQl quey problem

I have a sql query like this:

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)

This statement asks me to enter KDCODEKDW value to return results. However the value of this field is a string value so it does not return anything. How can I ask this value to the user as a string..
'?' is not working by the way...

Thanks
aozdamarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
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%'
0
aozdamarAuthor Commented:
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
0
momi_sabagCommented:
how do you do it with a numeric field ?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

tliottaCommented:
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
0
aozdamarAuthor Commented:
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
0
momi_sabagCommented:
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 ?
0
aozdamarAuthor Commented:
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...
0
tliottaCommented:
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
0
aozdamarAuthor Commented:
script for creating white list

0
aozdamarAuthor Commented:
Sorry for the last message please ignore it. I did not exactly understand what you are talking about. Can you explain a little more?
0
tliottaCommented:
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
0
aozdamarAuthor Commented:
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 ??
0
aozdamarAuthor Commented:
problem still exists guys
0
tliottaCommented:
aozdamar:

It's not quite clear what steps you've taken. If you're using DataDirect, then AFAIK, you have registered a DRDA wrapper for the DSN in order to access the AS/400 database. But that's still discussing the driver. We have no clue yet what tool you're using to run the query. Is this SQL SELECT statement in a C program? in a VB program? a ....whatever?

Exactly how are you executing the SELECT statement? If this SELECT is a prepared statement in a program, how did you declare the parameter definition for the parameter marker? And if there is no parameter marker definition, then the statement is perhaps being prepared dynamically by some program, but what program?

Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aozdamarAuthor Commented:
I ahve solved the problem but going to give you points for your care. thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.