Solved

SQl quey problem

Posted on 2007-04-04
15
614 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL trigger 5 32
SQL group by query to return records with highest value 6 31
removing unwanted rows from an sql server ranked table 13 36
T-SQL: Stored Procedure Syntax 3 32
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…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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