Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2006
    Posts
    10

    Unanswered: db2 nested procs..

    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

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

    Thumbs up

    Quote Originally Posted by kingp
    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.
    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.....


    Quote Originally Posted by kingp
    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
    use the week scalar function if i have guessed your problem correctly....
    Last edited by nick.ncs; 05-17-07 at 04:51.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2006
    Posts
    10

    thanks

    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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 )".
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2006
    Posts
    10
    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...

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    date1 BETWEEN startDate AND endDate
    or
    Code:
    date1 BETWEEN input_date AND DATE(input_date) - 7 DAYS
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Dec 2006
    Posts
    10
    Quote Originally Posted by stolze
    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:
    Code:
    date1 BETWEEN startDate AND endDate
    or
    Code:
    date1 BETWEEN input_date AND DATE(input_date) - 7 DAYS
    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

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    "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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •