Solved

SQl quey problem

Posted on 2007-04-04
15
607 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:aozdamar
  • 8
  • 4
  • 3
15 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18848888
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
 

Author Comment

by:aozdamar
ID: 18849397
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18849477
how do you do it with a numeric field ?
0
 
LVL 27

Expert Comment

by:tliotta
ID: 18855216
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
 

Author Comment

by:aozdamar
ID: 18855659
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18855877
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
 

Author Comment

by:aozdamar
ID: 18857026
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 27

Expert Comment

by:tliotta
ID: 18860224
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
 

Author Comment

by:aozdamar
ID: 18880738
script for creating white list

0
 

Author Comment

by:aozdamar
ID: 18881276
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
 
LVL 27

Expert Comment

by:tliotta
ID: 18885567
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
 

Author Comment

by:aozdamar
ID: 18922460
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
 

Author Comment

by:aozdamar
ID: 19131798
problem still exists guys
0
 
LVL 27

Accepted Solution

by:
tliotta earned 125 total points
ID: 19131937
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
 

Author Comment

by:aozdamar
ID: 19652816
I ahve solved the problem but going to give you points for your care. thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Step by step guide to Clean and Sort your windows registry! Introduction: Always remember: A Clean registry = Better performance = Save your invaluable time In this article we're going to clear our registry manually! Yes, manually! The e…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now