Hi All ,
I had a concern tht is it possible to call other procedures from one procedure(like proc A calling proc B and C) in DB2... i know we can do it easily in oracle making A main and then using call func. using package also .But was wondering how can it be possible in DB2.
Also I had a date in oracle which has inputs like
week in ( input_date , input_date - 7 )
both week and input_date have date datatype but week is in format like 5/17/2006 12:00:00 AM
how do we tackle it in db2 .....is it possible to delete 7 which is number from date type....ofcourse we can convert it to date but tht would reduce the accuracy as year wont be there to judge.
Please suggest your opinion

Thanks in advance

yes it is very much possible...in db2 also you use the call function

suppose you have a procedure A defined as

create procedure A
begin
some logic

call B (in, out); --where B is some other procedure
end

and then you can call A which in turn will call B....

if you want some value back from B use it in the out variable.....

though there r various ways you can return parameters depending upon wether you r returning it to client or caller.....


use the week scalar function if i have guessed your problem correctly....

Packages in Oracle are basically a collection/schema for procedures. That's all there is to it. If you ignore that piece, you will see that exactly the same stuff can be done in DB2.

What is "input_date - 7" supposed to be? 7 Days, 7 Months, 7 Years? DB2 follows the SQL standard for datetime arithmetics. You can say "input_date - 7 DAYS".

Thanks a lot for all of your kind suggestions.
Call function could be used in calling the other procs which was definitely helpful.
regarding my date problem i have still issues.
Will put up a sample command here for the better understanding of my problem.
I am running a process like as below..

" DELETE FROM xyz
WHERE sample_id = 1
AND week in ( 5/16/2007 , 5/16/2007 - 7 days ) "

my week is in date format as in 5/17/2006 12:00:00 AM thats the way it looks like in the db2 table
i would be supplying the input_date here as in date format too as '5/17/2006'.
And when I subtract 7,what I am looking for is to delete data for this week and for previous week too like in this case it should be
"( 5/16/2007 , 5/09/2007 ) "
But when I execute this query it runs an error like
"ERROR [42816] [IBM][DB2/AIX64] SQL0182N An expression with a datetime value or a labeled duration is not valid. SQLSTATE=42816
"
Please suggest the reason.
Thanks again.

Literal date must be in quotes: "... in ( '5/16/2007' , '5/16/2007' - 7 days )". Or may be even "... in ( date('5/16/2007') , date('5/16/2007') - 7 days )".

thnx for the response i truly appreciate it..
finally gott the prob solved with usage of
week in ( input_date , date(input_date) - 7 days )

thnx again...

Something else you should consider: "date1 IN (startDate, endDate)" will only verify if "date1" is equal to "startDate" or "endDate". Maybe you want to use:
or

ohh Stolze , does it really work like this as i was always under the impression that IN (a,b) would consider two of them together....
thnx

"a IN (b, c)" is equivalent to "a = b OR a = c".

"a BETWEEN b AND c" is equivalent to "a >= b AND a <= c".

So you have to pick what your application semantics require.