|
View previous topic :: View next topic
|
| Author |
Message |
Mukesh Pandey
Active User
Joined: 11 Nov 2008
Posts: 143
Location: India
|
|
|
|
hi all please find the below db2 query.
| Code: |
SELECT
A.CLM_NUM_CD,
A.UPDT_TS,
M.SITUS_ST
FROM
F3696DB.TUDCLINF A,
F3696DB.TUDEMPLR M
WHERE
A.RPRT_NUM = M.RPRT_NUM
AND A.CLM_NUM_CD = '200910138950'
-- AND (M.SITUS_ST IN ('NY',' ') OR (M.SITUS_ST IS NULL))
WITH UR; |
o/p
| Code: |
CLM
NUM UPDT SITUS
CD TS ST
------------ ------------------------ -----
222222222222 2009-11-02-14.25.24.555555 |
| Code: |
SELECT
A.CLM_NUM_CD,
A.UPDT_TS,
M.SITUS_ST
FROM
F3696DB.TUDCLINF A,
F3696DB.TUDEMPLR M
WHERE
A.RPRT_NUM = M.RPRT_NUM
AND A.CLM_NUM_CD = '200910138950'
AND (M.SITUS_ST IN ('NY',' ') OR (M.SITUS_ST IS NULL))
WITH UR; |
o/p
| Code: |
CLM
NUM UPDT SITUS
CD TS ST
------------ -------------------------- ----- |
Can i know why no row is fetched in the second query.
Thanks in advance |
|
| Back to top |
|
 |
Mukesh Pandey
Active User
Joined: 11 Nov 2008
Posts: 143
Location: India
|
|
|
|
| The STS_ST is blank in first o/p . the second query gives 0 rows when sts_st is queried for spaces or blank. |
|
| Back to top |
|
|
Mukesh Pandey
Active User
Joined: 11 Nov 2008
Posts: 143
Location: India
|
|
|
|
i am getting the hex value of of SITUS code as 00
00
Can i know what is the value of 00
00 |
|
| Back to top |
|
|
Mukesh Pandey
Active User
Joined: 11 Nov 2008
Posts: 143
Location: India
|
|
|
|
| the field in STITUS_ST is neither NULL nor SPACES. what query will fetch these values keeping in mind i need to give suggestion for the time being. i cant change data. |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
"the field in STITUS_ST is neither NULL nor SPACES. what query will fetch these values keeping in mind i need to give suggestion for the time being. i cant change data."
The column is not nullable but it contains a x'00' which is a valid character but it is not a space (x'40'). Suggest you look at the application that loaded the data in the first place. |
|
| Back to top |
|
|
Mukesh Pandey
Active User
Joined: 11 Nov 2008
Posts: 143
Location: India
|
|
|
|
| Thanks Craq. I will do that. |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Mukesh,
Are you sure that you are getting followng output of First query?
| Code: |
CLM
NUM UPDT SITUS
CD TS ST
------------ ------------------------ -----
[b]222222222222[/b] 2009-11-02-14.25.24.555555 |
As in the query you have specified A.CLM_NUM_CD = '200910138950'
in the predicate then output record should have 200910138950 as CLM_NUM_CD
| Code: |
SELECT
A.CLM_NUM_CD,
A.UPDT_TS,
M.SITUS_ST
FROM
F3696DB.TUDCLINF A,
F3696DB.TUDEMPLR M
WHERE
A.RPRT_NUM = M.RPRT_NUM
[b] AND A.CLM_NUM_CD = '200910138950' [/b]
-- AND (M.SITUS_ST IN ('NY',' ') OR (M.SITUS_ST IS NULL))
WITH UR; |
|
|
| Back to top |
|
|
Mukesh Pandey
Active User
Joined: 11 Nov 2008
Posts: 143
Location: India
|
|
|
|
hi Craq and others please clear my below doubt..
IF PLN-SITUS-ST > SPACES
MOVE PLN-SITUS-ST TO EMPLR-SITUS-ST
ELSE
MOVE SPACES TO EMPLR-SITUS-ST
END-IF.
WILL THE ABOVE CODE CHECK FOR ALL LOW VALUES AND AVOID IT GETTING POPULATED IN THE VAR EMPLR-SITUS-ST ?
I WANT TO AVOID HEX( ) AND USE '>' OPERATOR IN MY COBOL PROGRAM FOR SIMPLICITY.
PLEASE ADVICE.
Thanks in advance, |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If you are only interested in preventing low-values (x'00'), you might consider creating a working-storage item called ws-low-valu and give a VALUE of x'00'. Then your IF can be a simple = comparison. |
|
| Back to top |
|
|
Mukesh Pandey
Active User
Joined: 11 Nov 2008
Posts: 143
Location: India
|
|
|
|
Hi dick, Thanks for your suggestion. i understand what you suggest.
Also please advice if my code will bypass all the low values or not.
IF PLN-SITUS-ST > SPACES
MOVE PLN-SITUS-ST TO EMPLR-SITUS-ST
ELSE
MOVE SPACES TO EMPLR-SITUS-ST
END-IF.
Thanks, |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Also please advice if my code will bypass all the low values or not. |
I don't understand what this is asking? What does "all the low values" mean? There is only 1 low-value. . .
A space is an x'40' which will always compare higher than an x'00'. If there are other non-displayable values, comparing for greater than spaces would not be sufficient. |
|
| Back to top |
|
|
|
|
