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
 
Fetch Blanks & Values from DB2 Table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Tushar14

New User


Joined: 30 Jun 2012
Posts: 2
Location: India

PostPosted: Sat Jun 30, 2012 4:29 pm    Post subject: Fetch Blanks & Values from DB2 Table
Reply with quote

I have requirement wherein a column of DB2 table could have blanks or values upto 2 digits.I need to fetch those records which has both blanks and values associated with that column.

Emp ID:-
--
99
11
--
11 --
-- 22

I need to write query which could fetch only last 2 records as mentioned in Emp ID.
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1542
Location: Andromeda Galaxy

PostPosted: Sat Jun 30, 2012 7:07 pm    Post subject:
Reply with quote

So you have to fetch only last two records which has spaces or values upto two digits??

or Explain the requirement better
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Sat Jun 30, 2012 10:14 pm    Post subject:
Reply with quote

Any chance you could share the definition of the column with us? DB2 tables have rows not records.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Sun Jul 01, 2012 12:07 am    Post subject:
Reply with quote

is it only one column - emp_ID
or
is it two columns - emp and ID?

assuming that it is one column - emp_ID
then
what is the ddl for the column?
char(2)
NULLABLE
??
answer those questions
denoted in BOLD and underlined, please.
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 Jul 01, 2012 12:53 am    Post subject:
Reply with quote

Hello and welcome to the forum,

As has been mentioned, your request is quite unclear. .

Quote:
Emp ID:-
--
99
11
--
11 --
-- 22

Why do some values have 2 bytes and others 4 bytes?

If you post what has been requested and also exp[lain what this is to accomplish, someone may have a suggestion.
Back to top
View user's profile Send private message
Tushar14

New User


Joined: 30 Jun 2012
Posts: 2
Location: India

PostPosted: Sun Jul 01, 2012 8:44 pm    Post subject: Reply to: Fetch Blanks & Values from DB2 Table
Reply with quote

Guys its only one column.
Yes its Char (2).

Let me explain it more;

Col 1 Col 2
A 99
B 12
C 45
A --

Now I want to write a query which fetches 99 and blanks from col2 for A only.
Thanks much for your replies !!!
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1542
Location: Andromeda Galaxy

PostPosted: Sun Jul 01, 2012 9:04 pm    Post subject:
Reply with quote

Tushar,

Looks to me a straight forward problem

What did you try and what went wrong with the query?

Or is there still some implicit requirement you are yet to share?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Sun Jul 01, 2012 9:05 pm    Post subject:
Reply with quote

what if you don't have a column with two blanks "bb" (b = spaces)

what if you have
b1
bA
1b
Ab

and can the column be NULL?

short of accounting for NULL,
you could UNION ALL two selects
each looking for a space in the col
i.e. POSSTR(emp_ID,' ') > 0/= 0
then sorting descending, each FETCH FIRST ROW ONLY.

and, pleassssssssssssse. do not dare show exasperation with our confusion.
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 Jul 01, 2012 11:00 pm    Post subject:
Reply with quote

Hello,

Please review your last post.

You mention it is "only one column", but the sample data shows 2 . . .

How does your second post relate to the first?

You need to do a much better job of explaining what you "have" and what you want as a result when this new process is run.

Showing the "input" and the output wanted will help.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 686
Location: Earth

PostPosted: Mon Jul 02, 2012 8:27 pm    Post subject:
Reply with quote

Tushar,
Assuming two hyphens (-) represent null values in the column 2,
Please try-
Code:
SELECT * FROM <table-name>
WHERE
COL1 = 'A' AND
(COL2 = '99' OR COL2 IS NULL)


Maybe the beginner's forum is better suited for your needs. If the column 2 values really are spaces then you will need to change the where clause accordingly.
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. Cobol to fetch the content by using s... arunc55 COBOL Programming 4 Fri Nov 17, 2017 1:25 pm
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 Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm

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