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
 

 

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: 1529
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: 6966
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: 1529
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: 6966
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: 685
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
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm


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