creating a Report

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

j_ammann

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

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

Knut Stolze

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

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

Knut Stolze

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

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

Knut Stolze

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

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

Tonkuma

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

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

Brian Tkatch

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