IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Fetch info from DB2 table about field having junk values


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 12, 2008 3:49 pm
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Nov 12, 2008 4:56 pm
Reply with quote

Raghu,

What do you mean by a "junk character"?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Nov 12, 2008 5:42 pm
Reply with quote

What is the sql code after the fetch? Is col1 a nullable column?
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 12, 2008 6:47 pm
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Nov 12, 2008 7:35 pm
Reply with quote

Code:
select col1 from table1
where col1 in ('.' , '!' , '@' , '#' , '%' , '$' , ....other values which you feel junk)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 12, 2008 7:37 pm
Reply with quote

Quote:

Junk Value less than low-values like .!@#%$ etc


there is nothing less than low-values.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Nov 12, 2008 10:24 pm
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA

PostPosted: Wed Nov 12, 2008 10:30 pm
Reply with quote

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
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1248
Location: Richfield, MN, USA

PostPosted: Thu Nov 13, 2008 4:31 am
Reply with quote

One man's junk is another man's whatever? icon_smile.gif
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Thu Nov 13, 2008 11:28 am
Reply with quote

Guys

I am looking for Answers not comments icon_redface.gif If it sound bad i am really sorry icon_lol.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Thu Nov 13, 2008 11:36 am
Reply with quote

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
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 19, 2008 2:57 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 19, 2008 3:51 pm
Reply with quote

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
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 19, 2008 3:55 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 19, 2008 5:13 pm
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Nov 19, 2008 5:25 pm
Reply with quote

Dick,

I think he's expecting something like this.icon_lol.gif
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
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Thu Nov 20, 2008 10:39 am
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Help required to reset decimal points... DFSORT/ICETOOL 8
No new posts Sorting Date Field DFSORT/ICETOOL 4
No new posts how to eliminate null indicator value... DB2 7
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
Search our Forums:

Back to Top