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

Query required


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

New User


Joined: 12 Sep 2006
Posts: 15
Location: Chennai, Tamilnadu, India

PostPosted: Wed Feb 10, 2010 9:00 pm
Reply with quote

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
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 Feb 10, 2010 9:26 pm
Reply with quote

WHERE SOME_X_COLUMN = 'A9999'
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 Feb 10, 2010 10:58 pm
Reply with quote

Hi Craig,

Tough morning lots of places . . . icon_smile.gif
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. . .
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 Feb 10, 2010 11:13 pm
Reply with quote

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.
Back to top
View user's profile Send private message
bharath18

New User


Joined: 12 Sep 2006
Posts: 15
Location: Chennai, Tamilnadu, India

PostPosted: Thu Feb 11, 2010 11:24 am
Reply with quote

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????
Back to top
View user's profile Send private message
Escapa

Senior Member


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

PostPosted: Thu Feb 11, 2010 11:35 am
Reply with quote

Yes your query is wrong.

I don't understand why you have used ORs. It should be AND I guess.
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 Feb 11, 2010 11:37 am
Reply with quote

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. . .
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Thu Feb 11, 2010 4:10 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Kylash V

New User


Joined: 13 Feb 2010
Posts: 5
Location: Chennai

PostPosted: Sun Feb 14, 2010 12:43 am
Reply with quote

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.
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: Sun Feb 14, 2010 1:09 am
Reply with quote

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. . .
Back to top
View user's profile Send private message
Kylash V

New User


Joined: 13 Feb 2010
Posts: 5
Location: Chennai

PostPosted: Sun Feb 14, 2010 2:18 am
Reply with quote

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!!
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: Sun Feb 14, 2010 4:52 am
Reply with quote

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).
Back to top
View user's profile Send private message
jitu852

New User


Joined: 30 Apr 2007
Posts: 27
Location: Hyderabad

PostPosted: Sun Feb 14, 2010 4:42 pm
Reply with quote

Replace all 'OR' by 'AND' in your original query then it will work fine ............
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: Sun Feb 14, 2010 11:29 pm
Reply with quote

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.
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 10
No new posts Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
Search our Forums:

Back to Top