|
View previous topic :: View next topic
|
| Author |
Message |
Krishnadeva Reddy
New User
Joined: 14 Nov 2007
Posts: 37
Location: Chennai
|
|
|
|
Hi,
Our process has inserted invalid values in a table with X'05' in all the columns.The reason for wrong insertion is due to the X'05' were not removed from the input file before running the process.
Please help me out to write a query to fetch the values from table consisting of X'05' value.
The invalid data looks like as shown below:
| Code: |
Table: DEVD.TABLE1(T1)
EFF_DATE REC_NUM FIRST_NAME LAST_NAME
----------- ------------------------------------------------------
2007-01-01 2007-01. .MARIAJON. .JR.
2007-01-01 2007-01
2007-01-01 2007-02. .CHARLE. .JOSEPH.
2007-01-01 2007-02 CHARLE JOSEPH
2007-01-01 2007-03. .. ..
2007-01-01 2007-03
|
The dots '.' are X'05' values.To fetch this I have written a query as mentioned below, so result set is of valid and invalid rows.
| Code: |
SELECT * FROM DEVD.TABLE1
WHERE REC_NUM LIKE '2007-0%'
|
Please help me to write a query to fetch only the X'05' rows.Desired output should be
| Code: |
Table: DEVD.TABLE1(T1)
EFF_DATE REC_NUM FIRST_NAME LAST_NAME
----------- ------------------------------------------------------
2007-01-01 2007-01. .MARIAJON. .JR.
2007-01-01 2007-02. .CHARLE. .JOSEPH.
2007-01-01 2007-03. .. ..
|
|
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you unload the table, correct the data and reload the data - either for some particular date(s) or for the entire table. . .
If only part of the data was to be corrected (rather than the entire table), those rows would need to be deleted. |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| Krishnadeva Reddy wrote: |
Hi,
Our process has inserted invalid values in a table with X'05' in all the columns.The reason for wrong insertion is due to the X'05' were not removed from the input file before running the process.
Please help me out to write a query to fetch the values from table consisting of X'05' value.
The invalid data looks like as shown below:
| Code: |
Table: DEVD.TABLE1(T1)
EFF_DATE REC_NUM FIRST_NAME LAST_NAME
----------- ------------------------------------------------------
2007-01-01 2007-01. .MARIAJON. .JR.
2007-01-01 2007-01
2007-01-01 2007-02. .CHARLE. .JOSEPH.
2007-01-01 2007-02 CHARLE JOSEPH
2007-01-01 2007-03. .. ..
2007-01-01 2007-03
|
The dots '.' are X'05' values.To fetch this I have written a query as mentioned below, so result set is of valid and invalid rows.
| Code: |
SELECT * FROM DEVD.TABLE1
WHERE REC_NUM LIKE '2007-0%'
|
Please help me to write a query to fetch only the X'05' rows.Desired output should be
| Code: |
Table: DEVD.TABLE1(T1)
EFF_DATE REC_NUM FIRST_NAME LAST_NAME
----------- ------------------------------------------------------
2007-01-01 2007-01. .MARIAJON. .JR.
2007-01-01 2007-02. .CHARLE. .JOSEPH.
2007-01-01 2007-03. .. ..
|
|
You can use something like this:
| Code: |
SELECT * FROM DEVD.TABLE1
WHERE REC_NUM LIKE '2007-0%'
and possn(REC_NUM || first_name || last_name , X'05') > 0 |
Lenny |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
or you could just
| Code: |
update devd.tabl1
set last_name = replace(last_name,x'05','')
,first_name = replace(first_name,x'05','')
where (last_name like '%'!!x'05'!!'%' or first_name like '%'!!x'05'!!'%')
and REC_NUM LIKE '2007-0%' |
|
|
| Back to top |
|
|
Krishnadeva Reddy
New User
Joined: 14 Nov 2007
Posts: 37
Location: Chennai
|
|
|
|
Thanks for your help.
With POSSTR function I was able to get desired results from the below query.
| Code: |
SELECT * FROM DEVD.TABLE1
WHERE REC_NUM LIKE '2007-0%'
AND POSSTR(ID_REC || NME_FRST_PROV || NME_LAST_PROV, X'05') > 0
|
|
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| Code: |
SELECT * FROM DEVD.TABLE1
WHERE REC_NUM LIKE '2007-0%'
and POSSTR(REC_NUM || first_name || last_name , X'05') > 0 |
But finally you can update without select
| Code: |
Update DEVD.TABLE1
set
REC_NUM = replace(REC_NUM, X'05', ' '),
first_name = replace(first_name, X'05', ' '),
last_name = replace(last_name, X'05', ' ')
WHERE REC_NUM LIKE '2007-0%'
and POSSTR(REC_NUM || first_name || last_name , X'05') > 0 |
I see you found and corrected my mistake. 
Thanks, Lenny  |
|
| Back to top |
|
|
|
|
