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

creating a list of invalid values from a DB2 table
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
nagarajan.dharani

New User


Joined: 27 Dec 2006
Posts: 36
Location: Chennai
PostPosted: Thu Dec 13, 2007 3:54 pm
 Reply with quote
Hi,

Can some one help me with the following requirement.

I have a DB2 table and it has a tow columns EMP_NAME and EMP_ID

The EMP_ID is alpha numeric and it is of 7 characters.

The requirement is like i need to create a list of employees where the EMP_ID
has the invalid values. The invalid values are other than A-Z and 0-9.

For example if the EMP_ID is '$34438_' it is invalid because it has a special symbol($)
and a space(_).

Can this be done through a SQL query..??

Thanks,
Dharani
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
PostPosted: Thu Dec 13, 2007 4:42 pm
 Reply with quote
SELECT '$34438_' FROM SYSIBM.SYSDUMMY1;

Correct me if I am wrong.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
PostPosted: Thu Dec 13, 2007 6:13 pm
 Reply with quote
You are wrong. That will only grab his example and not even from the table he wants to check.
Back to top
View user's profile Send private message
vijayakumar.yellala

New User


Joined: 19 Apr 2006
Posts: 63
Location: Chennai
PostPosted: Thu Dec 13, 2007 9:37 pm
 Reply with quote
I think through SQL query its not possible...

As my suggession is: for that you need to write a code...

first u have to store all the values into table & then you need to check each characker. based on that you can create Invalid emp-ids...


Please correct me if i am wrong....
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
PostPosted: Thu Dec 13, 2007 9:48 pm
 Reply with quote
It isn't all that hard to write but I don't think the performance would be very good. I haven't tested this but it should work.

Code:
SELECT EMP_NAME , EMP_ID FROM TABLE
WHERE SUBSTR(EMP_ID,1,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,2,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,3,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,4,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,5,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,6,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9')
OR SUBSTR(EMP_ID,7,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'0','1','2','3','4','5','6','7','8','9');
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
PostPosted: Thu Dec 13, 2007 9:55 pm
 Reply with quote
Craq nice brute force solution. Its obvious on how to add lowercase even though the OP is not specifying lower as valid.

I wouldn't want to be there when the DBA comes pounding on the desk saying your query consumed 100% of the CPU for 2 days though.
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
PostPosted: Thu Dec 13, 2007 10:21 pm
 Reply with quote
You can use BETWEEN with character fields:

IN ('A','B','C','D','E') same as BETWEEN 'A' AND 'E'.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
PostPosted: Thu Dec 13, 2007 10:53 pm
 Reply with quote
Phrzby Phil wrote:
You can use BETWEEN with character fields:

IN ('A','B','C','D','E') same as BETWEEN 'A' AND 'E'.


Yes, it just makes the coding messier.
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
PostPosted: Thu Dec 13, 2007 11:36 pm
 Reply with quote
You think
Quote:
NOT BETWEEN 'A' AND 'Z'
is messier than listing 26 values, along with ensuring that they are correct?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
PostPosted: Thu Dec 13, 2007 11:43 pm
 Reply with quote
Phrzby Phil wrote:
You think
Quote:
NOT BETWEEN 'A' AND 'Z'
is messier than listing 26 values, along with ensuring that they are correct?


In EBCDIC between 'A' and 'Z' there are 13 characters that are not alphabetic!
There are 6 non-alphabetic characters between I and J, and 7 more between R and S.
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
PostPosted: Fri Dec 14, 2007 12:22 am
 Reply with quote
Granted - I tried to slide past that but got caught.

But - if the user "knows" that only display/keyboard characters are used, then the intervening hex values will not be present, and my code works.

In any case, BETWEEN '0' AND '9' works.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Fri Dec 14, 2007 2:45 am
 Reply with quote
Hello,

I realize that this will sound horribly "out of tune", but is there some reason the data was not validated before it was put into the database?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
PostPosted: Fri Dec 14, 2007 7:25 am
 Reply with quote
dick scherrer wrote:
Hello,

I realize that this will sound horribly "out of tune", but is there some reason the data was not validated before it was put into the database?

Would you believe it wasn't in the original spec's?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Fri Dec 14, 2007 8:29 am
 Reply with quote
Quote:
Would you believe it wasn't in the original spec's?


The longer i do this, the more i'd believe. . .

d
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 INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts How to create a list of SAR jobs with... CA Products 3
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us