| View previous topic :: View next topic |
| Author |
Message |
SAHANA
Beginner
Joined: 18 Jul 2007
Posts: 18
Topics: 5
Location: United Kingdom
|
 Posted: Sun Nov 29, 2009 9:44 am Post subject: Select Query Doubt |
 |
|
Hi All,
I have an input field I_PNO which comes from the Web and is stored in a table TAB1. I_PNO is a 10 digit alphanumeric field.
I have another host variable :PNO which is from a input flat file which is of the same length.
This is my select query in the code.
select * from tab1 where I_PNO =:PNO
This select query is coded assuming that only 10 digit part numbers would be entered from the web. But now the I_PNO can be 5 or 8 or 10 digit.
For ex: I_PNO = 12345. I will have to select all the records from the input file like this 12345%...I have to change the Select query now.
The :PNO can have values like 12345, 12345678ab, 1234567890.
So if the entered I_PNO from the web is 12345, i will have to select all the records which starts with 12345 from the flat file using the field :PNO.
Could you please let me know how should I change the select query now?
Thanks
Sahana |
|
| Back to top |
|
 |
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Sun Nov 29, 2009 4:49 pm Post subject: |
|
|
| Quote: | | I will have to select all the records from the input file like this 12345% |
I believe you have answered your question. . .
Modify the value in the host variable before issuing the SELECT.
_________________
All the best,
di |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Nov 30, 2009 9:07 am Post subject: |
|
|
| Quote: | | 10 digit alphanumeric field. |
well part of your problem is that you have such a poor foundation of DB2 vocabulary,
you can't ask questions properly or look up the answers yourself (if you are ever inclined to do so)
there is no such DB2 datatype as alphanumeric
now, are you saying that the trigger value will always need wildcards
or, is the data value in the qsam field an exact match to the DB2 column value?
Also, how is the datavalue captured during the web event stored?
as 0012345678, bb12345678, or 12345678bb? (where b = space)
if the former, then as di said, you have your answer. WHERE I_PNO LIKE :host_variable_padded_with_%
if the latter, then you want an exact match. WHERE I_PNO = :host_variable
so, what is the definition of the value of the field?
you will require different WHERE clause constructs.
As well as, since you are reading a qsam file,
this must be in BATCH,
what is your disposition of the SELECTed values?
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Nov 30, 2009 11:20 am Post subject: |
|
|
| dbzTHEdinosauer wrote: | if the former, then as di said, you have your answer. WHERE I_PNO LIKE :host_variable_padded_with_%
if the latter, then you want an exact match. WHERE I_PNO = :host_variable
so, what is the definition of the value of the field?
you will require different WHERE clause constructs.
As well as, since you are reading a qsam file,
this must be in BATCH,
what is your disposition of the SELECTed values? |
dbz,
FWIW, I don't think you need different sql queries. It can be done in a single query itself. Untested code
Suhana,
Use this code
| Code: |
select * from tab1 where I_PNO LIKE :PNO
|
If your input has indeed leading spaces as well as trailing spaces and you would like to select if the string occurred any where you need to modify the host variable.
| Code: |
MOVE SPACES TO PNO
INSPECT WEB-VAL TALLYING WS-TALLY FOR ALL SPACES
EVALUATE TRUE
WHEN WS-TALLY = 1
STRING WEB-VAL DELIMITED BY SPACE
'%' DELIMITED BY SIZE
INTO PNO
WHEN WS-TALLY > 1
STRING '%' DELIMITED BY SIZE
WEB-VAL DELIMITED BY SPACE
'%' DELIMITED BY SIZE
INTO PNO
WHEN OTHER
MOVE WEB-VAL TO PNO
END-EVALUATE
|
Kolusu |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Nov 30, 2009 12:44 pm Post subject: |
|
|
If the requirements are as one can assume based on what the TS has writen, I agree.
that means the qsam file is a fuzzy trigger/key.
but, what if the qsam file is an exact trigger/key? and the TS has not communicated that properly? Only his proposed solution?
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Nov 30, 2009 1:16 pm Post subject: |
|
|
| dbzTHEdinosauer wrote: | | but, what if the qsam file is an exact trigger/key? and the TS has not communicated that properly? Only his proposed solution? |
AFAIK, I think DB2 will work fine even when the key is full 10 bytes.
select * from tab1 where I_PNO LIKE '1234567890' will only fetch the record with 1234567890 as the PNO
kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
