|
View previous topic :: View next topic
|
| Author |
Message |
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Hi DB2 Folks,
Can you give me a query which fetches information from DB2 table about some Field provided that field will have junk character.
ie
consider a table having columns like
| Code: |
| Col1 col2 col3 col4 |
In case the col1 will have junk charcater, i need information of that tuple |
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Raghu,
What do you mean by a "junk character"? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| What is the sql code after the fetch? Is col1 a nullable column? |
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Arun,
| Quote: |
| What do you mean by a "junk character"? |
Means, Junk Value less than low-values like .!@#%$ etc
I am looking for single query which has to fetch information from that table in case if that col1 in that table will have the above junk values.. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Code: |
select col1 from table1
where col1 in ('.' , '!' , '@' , '#' , '%' , '$' , ....other values which you feel junk) |
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
Junk Value less than low-values like .!@#%$ etc
|
there is nothing less than low-values. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Means, Junk Value less than low-values like .!@#%$ etc |
Whoever told you that there are values less than low-values? Suggest you find a new source for technical info. . .
As DBZ mentioned, nothing is less than low-values (by definition, actually).
Unload a few of these problem rows into a qsam file and post the hex from that unloaded file (not all of the columns, just a couple of the "bad" ones). We will be able to clarify what is in the data. |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| Quote: |
Quote:
What do you mean by a "junk character"?
Means, Junk Value less than low-values like .!@#%$ etc |
The mind boggles. |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1248
Location: Richfield, MN, USA
|
|
|
|
One man's junk is another man's whatever?  |
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Guys
I am looking for Answers not comments  If it sound bad i am really sorry  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| If it sound bad i am really sorry |
Not to worry - when things aren't working, it is frustrating.
| Quote: |
| I am looking for Answers not comments |
Then you need to provide what we ask for. . .
To repeat:
| Quote: |
Unload a few of these problem rows into a qsam file and post the hex from that unloaded file (not all of the columns, just a couple of the "bad" ones). We will be able to clarify what is in the data.
|
Once we better understand what you are finding in the data, we can move forward from there. It may help if you post the host variables where there are problems as well. |
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Hello,
Sorry For misleading you about Junk values(Characters), First i Thought we can validate Junk values by using < Low values.. Sorry For that.
Consider a fallowing scenario
File Structure
01 EMP-RECORD.
05 EMP-ID PIC X(05).
05 EMP-NAME PIC X(20).
Assume There is Junk character in EMP-NAME Field, Now I have to replace those Junk Character by spaces. To do so
We can use fallowing code ie after read records to record layout
| Code: |
PERFORM WS-I FROM 1 BY 1 UNTIL WS-I > 20
IF EMP(WS-I:1) < Spaces
Move space to EMP(WS-I:1)
END-IF
END-PERFORM |
Then rewrite it
The above code will replace junk character
But my question is
Can you give me a query which fetches information from DB2 table about some Field say col1 provided that field will have junk character.
ie
consider a table having columns like
| Code: |
Code:
Col1 col2 col3 col4 |
In case the col1 will have junk charcater, i need information of that tuple |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
are you looking for:
Select *
from your.table
where substr(col1,1,1) < ' '
or substr(col1,2,1) < ' '
or substr(col1,3,1) < ' '
or substr(col1,4,1) < ' '
or substr(col1,5,1) < ' ' |
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Dick,
Thank you Dick, This is one way of doing but I am looking for a query which will have looping concept instead of validating each and every character in the column(Field of Db2 table) |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
but I am looking for a query which will have looping concept
|
you should expand this thought with examples or better definition.
Looping? looping where? |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Dick,
I think he's expecting something like this.
| Code: |
Select *
from your.table
where
loop from i=1 to 5
substr(col1,i,1) < ' '
end-loop |
revel,
What's wrong in coding a few lines as shown by Dick. |
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Dick,
I mean to say
| Code: |
where substr(col1,1,1) < ' '
or substr(col1,2,1) < ' '
or substr(col1,3,1) < ' '
or substr(col1,4,1) < ' '
or substr(col1,5,1) < ' ' |
The problem with above code is; In case if we are having a field which is of 100 bytes long then we need to validate every character in WHERE part of SQL Query but I am looking for QUERY which will have SINGLE CODE which replace Code which is defined in WHERE PART Of above Query.
Its just bugging me; Just i want to know whether any code we are having in DB2 SQL.. |
|
| Back to top |
|
|
|
|
