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
 

 

Query required

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Query required
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    Post subject:
Reply with quote

WHERE SOME_X_COLUMN = 'A9999'
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 Feb 10, 2010 10:58 pm    Post subject:
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    Post subject:
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    Post subject: Reply to: Query required
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    Post subject:
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

Site Director


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

PostPosted: Thu Feb 11, 2010 11:37 am    Post subject:
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    Post subject:
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    Post subject: Reply to: Query required
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

Site Director


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

PostPosted: Sun Feb 14, 2010 1:09 am    Post subject:
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    Post subject: Reply to: Query required
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

Site Director


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

PostPosted: Sun Feb 14, 2010 4:52 am    Post subject:
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    Post subject: Reply to: Query required
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

Site Director


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

PostPosted: Sun Feb 14, 2010 11:29 pm    Post subject:
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    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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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