# Query required

Author Message
bharath18

New User

Joined: 12 Sep 2006
Posts: 15

 Posted: Wed Feb 10, 2010 9:00 pm Hi, I want to do a query on some x column where that x column is 5 char length, but i want to find in particular the A9999. i.e first character should be alphabetic and other 4 characters should be numeric, is that possible? if so please let me know. Thanks, Bharat
Craq Giegerich

Senior Member

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

 Posted: Wed Feb 10, 2010 9:26 pm WHERE SOME_X_COLUMN = 'A9999'
dick scherrer

Moderator Emeritus

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

Posted: Wed Feb 10, 2010 10:58 pm

Hi Craig,

Tough morning lots of places . . .
 Quote: i.e first character should be alphabetic and other 4 characters should be numeric,

@Bharat
Possibly by using a substring for each "piece" of the column. . .
Craq Giegerich

Senior Member

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

 Posted: Wed Feb 10, 2010 11:13 pm Last time I checked A is alphabetic and 9999 are numeric. If Bharat wants any alphabetic followed by any 4 numerics then that is a different matter, more complicated and very inefficient.
bharath18

New User

Joined: 12 Sep 2006
Posts: 15

 Posted: Thu Feb 11, 2010 11:24 am hey i was not able to get the query, i tried the below one a lengthy one... SELECT * FROM xxxxxx WHERE SUBSTR(ABC,1) IN ('A','B'..(removed in b/w).. 'Z') AND SUBSTR(abc,2) IN ('1','2','3','4','5','6','7','8','9','0') OR SUBSTR(abc,3) IN ('1','2','3','4','5','6','7','8','9','0') OR SUBSTR(abc,4) IN ('1','2','3','4','5','6','7','8','9','0') OR SUBSTR(abc,5) IN ('1','2','3','4','5','6','7','8','9','0');;;;; but i get irrelevant rows... such as aj123 ab345 cj222 I should get the result as a1234 b2345 c3456. I could not get the desired result, is that i am querying wrong????
Escapa

Senior Member

Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

 Posted: Thu Feb 11, 2010 11:35 am Yes your query is wrong. I don't understand why you have used ORs. It should be AND I guess.
dick scherrer

Moderator Emeritus

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

 Posted: Thu Feb 11, 2010 11:37 am Hello, Why did the code change from AND to ORs? I believe only rows that meet all conditions should be selected, but the ORs do not provide this restriction. Also, this should only run across a small number of rows. . . This code will perform terribly. . .
Kjeld

Active User

Joined: 15 Dec 2009
Posts: 365
Location: Denmark

 Posted: Thu Feb 11, 2010 4:10 pm It can't be done with SQL in an effecient way, but will always force DB2 to do a scan of all data pages. I will suggest that you omit the criteria from your select, and conditionally processes the rows that meets the desired condition. If this is a one-off selection you might get the aforementioned code to work, provided you replace the ORs with ANDs.
Kylash V

New User

Joined: 13 Feb 2010
Posts: 5
Location: Chennai

 Posted: Sun Feb 14, 2010 12:43 am Hi Bharath, You could try the following query SELECT * FROM XXXX WHERE ABC >= 'A0000' AND ABC <= 'z9999' I believe DB2 does character-by-character ASCII comparison to achieve the final result. So, while A1111 would fall in this list, 12345 would be < A0000 and be out of range; same with 99999. However, i am not sure of Aa234. I would expect expect it to be greater than A0000 but also greater than z9999 and hence not fall into this category.
dick scherrer

Moderator Emeritus

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

Posted: Sun Feb 14, 2010 1:09 am

Hello,

 Quote: You could try the following query SELECT * FROM XXXX WHERE ABC >= 'A0000' AND ABC <= 'z9999'
Please do not post suggestions/solutions that are not tested. . .

The posted code will execute but will not provide the requested result (on neither an ASCII nor an EBCDIC collating sequence.

Also, typically db2 on the mainframe does not use ASCII. Almost all mainframe processing is EBCDIC. On the mainframe 12345 is greater than A0000. . .
Kylash V

New User

Joined: 13 Feb 2010
Posts: 5
Location: Chennai

 Posted: Sun Feb 14, 2010 2:18 am Hi Dick My apologies for having presented an untested solution. And thanks for letting me know that DB2 does not take the ASCII sequence durting comparisons. Thanks again!!
dick scherrer

Moderator Emeritus

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

Posted: Sun Feb 14, 2010 4:52 am

Hello Kylash,

 Quote: And thanks for letting me know that DB2 does not take the ASCII sequence durting comparisons.

You're welcome.

However, you also need to be aware that "range checking" cannot work for this requirement. This requirement deals with the type of the data bytes not the combined field content (1st byte must be alpha, the others numeric).
jitu852

New User

Joined: 30 Apr 2007
Posts: 27

 Posted: Sun Feb 14, 2010 4:42 pm Replace all 'OR' by 'AND' in your original query then it will work fine ............
dick scherrer

Moderator Emeritus

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

Posted: Sun Feb 14, 2010 11:29 pm

Hello,

 Quote: Replace all 'OR' by 'AND' in your original query then it will work fine ............
It is best not to post solutions aleady given in the topic. . . As in:
 Quote: Yes your query is wrong. I don't understand why you have used ORs. It should be AND I guess.
and
 Quote: Why did the code change from AND to ORs? I believe only rows that meet all conditions should be selected, but the ORs do not provide this restriction.
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics Issue with EXEC CICS QUERY SECURITY c... CICS 6 DB2, write report based on query outp... DB2 1 Using WITH UR in SELECT SUM query DB2 1 SQLCODE = -122 while using the scalar... DB2 4 DB2: Need helping below DB2 query DB2 13
Search our Forums:

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