creating a Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vp - 02 Mar 2006 19:15 GMT

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

Reply to this Message

j_ammann@yahoo.com - 03 Mar 2006 11:58 GMT

vp schrieb:

> Hi all,
>
[quoted text clipped - 18 lines]
>
> 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

Reply to this Message

vp - 03 Mar 2006 15:01 GMT

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

Reply to this Message

Knut Stolze - 03 Mar 2006 15:03 GMT

> 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 ...

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

vp - 03 Mar 2006 15:57 GMT

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...

Reply to this Message

Knut Stolze - 03 Mar 2006 16:18 GMT

> Yes I am using
>
[quoted text clipped - 13 lines]
>
> How should I add ROW1 and ROW2 names in this format...

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

vp - 06 Mar 2006 19:04 GMT

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

Reply to this Message

Knut Stolze - 07 Mar 2006 18:34 GMT

> Hi Knut,
>
[quoted text clipped - 5 lines]
> UNION ALL
> SELECT 'ROW' || CHAR(ROW_NUMBER() OVER()), ...

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

vp - 07 Mar 2006 18:49 GMT

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??

Reply to this Message

Tonkuma - 10 Mar 2006 02:47 GMT

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

Reply to this Message

vp - 10 Mar 2006 20:57 GMT

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

Reply to this Message

Brian Tkatch - 13 Mar 2006 15:53 GMT

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.

Reply to this Message