Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
creating a list of invalid values from a DB2 table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: creating a list of invalid values from a DB2 table
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: 788
Location: Chennai, India

PostPosted: Thu Dec 13, 2007 4:42 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Thu Dec 13, 2007 10:21 pm    Post subject:
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    Post subject:
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

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Thu Dec 13, 2007 11:36 pm    Post subject:
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    Post subject:
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

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Fri Dec 14, 2007 12:22 am    Post subject:
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

Site Director


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

PostPosted: Fri Dec 14, 2007 2:45 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri Dec 14, 2007 8:29 am    Post subject: Reply to: creating a list of invalid values from a DB2 table
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    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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Extend the decimal values in DIVISION Balaryan DFSORT/ICETOOL 3 Thu Oct 05, 2017 4:05 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us