Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fetch info from DB2 table about field having junk values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Fetch info from DB2 table about field having junk values
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: 2161
Location: @my desk

PostPosted: Wed Nov 12, 2008 4:56 pm    Post subject:
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    Post subject:
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    Post subject:
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: 2161
Location: @my desk

PostPosted: Wed Nov 12, 2008 7:35 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Nov 12, 2008 10:24 pm    Post subject:
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: 7913
Location: Bellevue, IA

PostPosted: Wed Nov 12, 2008 10:30 pm    Post subject:
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: 1238
Location: Richfield, MN, USA

PostPosted: Thu Nov 13, 2008 4:31 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Thu Nov 13, 2008 11:36 am    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 2161
Location: @my desk

PostPosted: Wed Nov 19, 2008 5:25 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 17 Tue Dec 06, 2016 8:04 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts IMS Database backup info ashek15 IMS DB/DC 14 Wed Nov 16, 2016 5:29 am
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us