Hi !!

1.
You can use this SQL on db2 it should work (just tested it)..:):).

select val1-val2,val1+val2,val1*val2 from
		(select count(*) as val1 from order ) as t1,
		(select count(*) as val2 from customers) as t2

2. I could work with SP on DB2 so I don't think I can help you here but I do
know that there are some limit about amount information I can move from DB
to client and back.
I had a big field that I need to update , a lot of text, and it didn't work,
so when DBA was checking that they told me that I have limit and I need to
use some other variable
to be able transfer this amount of data.
I think it would be your case as well since 2048 looks like round number
(2K). :):).

But I do have question to you too.
How do you right your SP on PC side or on the MF ?

Thanks,
Oleg.

-----Original Message-----
From: Gandhi, Gulshan [mailto:Gulshan.Gandhi@F...]
Sent: February 07, 2002 10:07 PM
To: DB2
Subject: [db2] arithematic query..


Hi,

I have 2 queries:

1. How do i perform arithematic operation like (sum,subtract, multiply) on a
db2 query as:

db2 "select count(*) from order - select count(*) from customers" where
order and customers are 2 different tables of the same database.

2. How can i extract a stored procedure  TEXT value into a file in unix i.e
i use a query:
db2 "select text from syscat.procedures where procname='SP_FMSI'" > 1

When i redirect it to file 1 on unix and i open the file it says "A line
cannot be longer than 2048 characters" and does not show anything in the
file.

My SP Is displayed below and i want to extract this portion only into unix
file

-------------------------------------------------------------------
CREATE PROCEDURE ncsdbo.SP_FMSI (  )
    RESULT SETS 1
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

DECLARE c1 CURSOR WITH RETURN FOR
(select
        distinct
        sca_sca_id "Fund Id",
        sca_acct_shrt_name "Fund Short Name",
        sca_bp_id "FM Id",
        bpt_shrt_name "FM Name",
        sco1.sco_long_desc "Cash Stock Option",
        sco.sco_long_desc "144A Exchange",
        a.fni_effv_dt "Effv Dt",
        sca_acct_full_name "Fund Full Name",
        rit_rpt_gen_date,
        rit_rpt_file_name,
        rit_rpt_params

from

        ncsdbo.rit_rpt_instr,
        ncsdbo.sca_safe_cstdy_acct ,
        ncsdbo.bpt_bp,
        ncsdbo.fni_fnd_instr a
        left outer join ncsdbo.sco_system_codes sco on
        sco.sco_code_type = a.fni_144a_opt_cd
        and
        sco.sco_code_value = a.fni_144a_opt
        left outer join ncsdbo.sco_system_codes sco1 on
        sco1.sco_code_type = a.fni_dvdnd_opt_cd
        and
        sco1.sco_code_value = a.fni_dvdnd_opt

where
        sca_sca_id = a.fni_fnd_id
        and
        sca_bp_id = bpt_bp_id
        and
         a.fni_effv_dt = (select max(aa.fni_effv_dt) from
ncsdbo.fni_fnd_instr aa,ncsdbo.syg_system_globals where
                       aa.fni_fnd_id = a.fni_fnd_id and aa.fni_effv_dt <
syg_stlmnt_dt and aa.fni_sta =2 )

        and
        rit_rpt_ident = 'RPBP001'
)

open c1;


END P1


> Regards
> Gulshan Gandhi
> *617-563-2508
> *  gulshan.gandhi@f...
>
>