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
 
SELECT Statement DB2

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

New User


Joined: 16 Jun 2010
Posts: 9
Location: Chennai

PostPosted: Thu Aug 12, 2010 2:58 pm    Post subject: SELECT Statement DB2
Reply with quote

Need a Query for the following :
--------------------------------------

I want to select 3 columns or fields (ID_BRANCH_NUM = 05, ID_BRANCH_NAME = BAL, ID_BRANCH_AREA= CHE) from a table (BRANCH_TABLE).

In the Output DSN.
1.) ID_BRANCH_NUM = 05 must be under the heading BRANCH NUMBER.
2.) ID_BRANCH_NAME = BAL must be under the heading BRANCH NAME.
3.) ID_BRANCH_AREA= CHE must be under the heading BRANCH AREA.

Using AS and each field in the output DSN should be delimited by ';'

Can any one help me with this issue??
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: Sat Aug 14, 2010 1:47 am    Post subject:
Reply with quote

Hello and welcome to the forum,

You would probably have gotten a reply sooner if this had been posted in the DB2 part of the forum rather than this "out of the way" part of the forum icon_wink.gif

Do do what you want simply specify ';' between the columns in the SELECT statement.

If this does not do what you want, please clarify.
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 Aug 14, 2010 2:32 am    Post subject:
Reply with quote

What have you tried on your own and what results have you gotten?
Back to top
View user's profile Send private message
barajendran1

New User


Joined: 16 Jun 2010
Posts: 9
Location: Chennai

PostPosted: Sun Aug 15, 2010 1:36 pm    Post subject: SELECT Statement DB2
Reply with quote

Thanks for your kind replies

Sorry Dick Am new to this forum so I am not sure where to put this post...

Hi Craq, This is the thing which I tried and it works fine for the heading delimited by ';' when I transferred the Output to the Excel Sheet...

SELECT ID_BRANCH_NUM AS "BRANCH NUMBER",';',
ID_BRANCH_NAME AS "BRANCH NAME",';',
ID_BRANCH_AREA AS "BRANCH AREA",';'
FROM BRANCH_TABLE
WHERE ID_BRANCH_NUM = '05'
AND ID_BRANCH_NAME = 'BAL'
AND ID_BRANCH_AREA = 'CHE';

I got the records delimited by ; with the Name changed in the Output DSN.

But When transferred this to the Excel sheet the alignment of the headings is not like that what I want...

The headings were not delimited by ';' , but the records seems finely delimited by ';'.

May I know how to solve this...
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 Aug 15, 2010 10:21 pm    Post subject:
Reply with quote

Hello,

Quote:
Sorry Dick Am new to this forum so I am not sure where to put this post...
Not to worry - we'll eventually get it right icon_smile.gif

Quote:
The headings were not delimited by ';'
I'm not where i can test today, but you could try:
Code:
SELECT ID_BRANCH_NUM  AS "BRANCH NUMBER",';' AS ";",
       ID_BRANCH_NAME AS "BRANCH NAME",';' AS ";",
       ID_BRANCH_AREA AS "BRANCH AREA",';' AS ";"
Back to top
View user's profile Send private message
shanuss

New User


Joined: 16 Jul 2008
Posts: 17
Location: Chennai

PostPosted: Mon Aug 16, 2010 4:03 pm    Post subject:
Reply with quote

Even I cannot test it now... But see if this clue helps

SELECT ID_BRANCH_NUM AS "BRANCH NUMBER;",';',
ID_BRANCH_NAME AS "BRANCH NAME;",';',
ID_BRANCH_AREA AS "BRANCH AREA;",';'
FROM BRANCH_TABLE
WHERE ID_BRANCH_NUM = '05'
AND ID_BRANCH_NAME = 'BAL'
AND ID_BRANCH_AREA = 'CHE';

I added ';' to end of column name... Please post if you get a chance to try this.
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Mon Aug 16, 2010 6:52 pm    Post subject:
Reply with quote

These queries relies on the query tool to produce the column headings in the output. If that does not work you could try:
Code:

SELECT "BRANCH NUMBER;",
            "BRANCH NAME;",
            "BRANCH AREA;"
FROM SYSIBM.SYSDUMMY
UNION
SELECT ID_BRANCH_NUM,';',
ID_BRANCH_NAME,';',
ID_BRANCH_AREA,';'
FROM BRANCH_TABLE
WHERE ID_BRANCH_NUM = '05'
AND ID_BRANCH_NAME = 'BAL'
AND ID_BRANCH_AREA = 'CHE';
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Mon Aug 16, 2010 7:01 pm    Post subject:
Reply with quote

Perhaps the "problem" lies in the fact that generating just the constant ';' results in a VARCHAR formated output field ( with a binary length field preceding the constant ). This might cause EXCEL to hiccup in determining what columns to put the headings in.
Try ID_BRANCH_NUM AS "BRANCH NUMBER", CHAR(';'), ID_BRANCH_NAME AS "BRANCH NAME", CHAR(';'), etc. and see how that works.
Back to top
View user's profile Send private message
barajendran1

New User


Joined: 16 Jun 2010
Posts: 9
Location: Chennai

PostPosted: Mon Sep 06, 2010 3:16 pm    Post subject: Reply to: SELECT Statement DB2
Reply with quote

Hi All,

I tried the query framed by Dick and it works very fine.

I got the answer exactly what I needed.

Thank U for Dick and everyone.
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: Mon Sep 06, 2010 9:59 pm    Post subject: Reply to: SELECT Statement DB2
Reply with quote

Good to hear it is working - thank you for letting us know icon_smile.gif

d
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 WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm

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