By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

creating a Report

P: n/a
vp
Hi all,

Can we create a report of this format using DB2.
Report#
Date:

column1 column2 column3
total
Field Name1 999 999 999
9999

Field Name 2 888 888 88
8888

Field Name 3 77 77 777
7777
Please suggest me a way to do... as of now I am using DB2 SQL in AIX
korn shell script... and we are not using any tool so is the problem...

thanks

Mar 2 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a

vp schrieb:
Hi all,

Can we create a report of this format using DB2.
Report#
Date:

column1 column2 column3
total
Field Name1 999 999 999
9999

Field Name 2 888 888 88
8888

Field Name 3 77 77 777
7777
Please suggest me a way to do... as of now I am using DB2 SQL in AIX
korn shell script... and we are not using any tool so is the problem...

thanks


are you trying to reformat a resultset (tabular-output) you are getting
with:
( only an example!)
db2 "select name,amount,remark from user_table"

NAME AMOUNT REMARK
--------------------------- --------------------
-----------------------------------------
Bush 10000.00 president
Schwarzenegger 20000.00 gouvernor

2 record(s) selected

to a report look-alike with header, footer, sum-ups ? or what are you
trying to achieve ?

use a perl-script to parse the output from the db2-command

joerg

Mar 3 '06 #2

P: n/a
vp
Hi,

I have data coming from DB2 tables using EXPORT utility and on top of
it I have to include some text to identify every column and row with
some CAPTION.
thanks

Mar 3 '06 #3

P: n/a
vp wrote:
Hi,

I have data coming from DB2 tables using EXPORT utility and on top of
it I have to include some text to identify every column and row with
some CAPTION.


Yes, you can. Another thread discussed this just last week. Have a look
there.

In short, you can add another row that contains the caption:

VALUES 'col1', 'col2', 'col3'
UNION ALL
SELECT ...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 3 '06 #4

P: n/a
vp
Yes I am using

VALUES 'col1', 'col2', 'col3'
UNION ALL
SELECT.....

and I am getting something like
col1 col2 col3
12 78 90
34 87 45

But I need something like
col1 col2 col3
ROW1 12 78 90
ROW2 34 87 45

How should I add ROW1 and ROW2 names in this format...

Mar 3 '06 #5

P: n/a
vp wrote:
Yes I am using

VALUES 'col1', 'col2', 'col3'
UNION ALL
SELECT.....

and I am getting something like
col1 col2 col3
12 78 90
34 87 45

But I need something like
col1 col2 col3
ROW1 12 78 90
ROW2 34 87 45

How should I add ROW1 and ROW2 names in this format...


VALUES '', 'col1', 'col2', 'col3'
UNION ALL
SELECT 'ROW' || CHAR(ROW_NUMBER() OVER()), ...
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 3 '06 #6

P: n/a
vp
Hi Knut,

I have caption in text for ROW1 and ROW2

Not sure how I could use ur idea ..

VALUES '', 'col1', 'col2', 'col3'
UNION ALL
SELECT 'ROW' || CHAR(ROW_NUMBER() OVER()), ...
thanks

Mar 6 '06 #7

P: n/a
vp wrote:
Hi Knut,

I have caption in text for ROW1 and ROW2

Not sure how I could use ur idea ..

VALUES '', 'col1', 'col2', 'col3'
UNION ALL
SELECT 'ROW' || CHAR(ROW_NUMBER() OVER()), ...


I don't know what you mean with "caption in the text"

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 7 '06 #8

P: n/a
vp
I am sorry for the confusion..

I want some thing like

member_id ssn total

INS_CD 2 5 7
DIS_CD 4 4 8

Can the above cross tab table be implemented using DB2 SQL??

Mar 7 '06 #9

P: n/a
What are the source table(s)?
Please show me DDL(s) and sample data that produced your example.
INS_CD 2 5 7
DIS_CD 4 4 8


Mar 10 '06 #10

P: n/a
vp
Hi,

I dont have a DDL yet... I have a single table were I am pulling data
for the fields...
men women total
INS_CD 2 5 7
DIS_CD 4 4 8

I have to add text like INS_CD and DIS_CD, men, women, total since I am
not getting it from the table. All i am pullng fromt eh table are
2,5,7,4,4,8...

thanks

Mar 10 '06 #11

P: n/a
For the top row, it can be added to the query with a UNION ALL. For the
side bar, a table can be constructed with a WITH statement, and JOINed
into the output table, assuming the correlation can be explained.

B.

Mar 13 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.