Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2009
    Location
    Israel
    Posts
    4

    Angry Unanswered: User Define Functions

    I want to find the Median value in a query statment , since DB2 Ver 8 dose not have sach table fanction U luke to wite table user define function that calculate the median. If someone have a solotion how to create a table user define function that will be grate/

  2. #2
    Join Date
    Feb 2009
    Posts
    114

    median

    The following SQL will get median npages from sysibm.systables

    with

    t3 as
    (select npages from sysibm.systables),

    t1 (npages, rownum) as
    (select npages, rownumber() over() from t3
    order by npages),

    t2 (cnt) as
    (select count(npages) from t3)


    values(
    case when (select (cnt/2)*2 from t2) <> (select cnt from t2) then
    (select npages from t1 where rownum = (select (cnt/2)+1 from t2))
    else
    (select (x.npages+y.npages)/2 from
    (select npages from t1 where rownum=(select cnt/2 from t2)) x,
    (select npages from t1 where rownum=(select (cnt/2)+1 from t2)) y)
    end)


    ;

  3. #3
    Join Date
    Jul 2009
    Posts
    150
    Quote Originally Posted by db2dummy1
    The following SQL will get median npages from sysibm.systables

    with

    t3 as
    (select npages from sysibm.systables),

    t1 (npages, rownum) as
    (select npages, rownumber() over() from t3
    order by npages),

    t2 (cnt) as
    (select count(npages) from t3)


    values(
    case when (select (cnt/2)*2 from t2) <> (select cnt from t2) then
    (select npages from t1 where rownum = (select (cnt/2)+1 from t2))
    else
    (select (x.npages+y.npages)/2 from
    (select npages from t1 where rownum=(select cnt/2 from t2)) x,
    (select npages from t1 where rownum=(select (cnt/2)+1 from t2)) y)
    end)


    ;
    I like your solution, but with only one NOTE:

    Your solution is good for V9, not not for V8.

    The "Order by" is not possible inside of WITH, rownumber() over() function doesn't exist in V8

    Code:
    t1 (npages, rownum) as
    (select npages, rownumber() over()  from t3
    order by npages)
    Sincerely, Kara S.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DB2Plus
    I like your solution, but with only one NOTE:

    Your solution is good for V9, not not for V8.

    The "Order by" is not possible inside of WITH, rownumber() over() function doesn't exist in V8
    I believe that it does work in DB2 LUW V8.2. I assume you are referring to DB2 z/OS.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2009
    Posts
    114

    median

    Here is the query running on V8

    => db2 -tvf x
    with t3 as (select npages from sysibm.systables), t1 (npages, rownum) as (select npages, rownumber() over() from t3 order by npages), t2 (cnt) as (select count(npages) from t3) values( case when (select (cnt/2)*2 from t2) <> (select cnt from t2) then (select npages from t1 where rownum = (select (cnt/2)+1 from t2)) else (select (x.npages+y.npages)/2 from (select npages from t1 where rownum=(select cnt/2 from t2)) x, (select npages from t1 where rownum=(select (cnt/2)+1 from t2)) y) end)

    1
    -----------
    -1

    1 record(s) selected.


    => db2level
    DB21085I Instance "xxxxxx" uses "64" bits and DB2 code release "SQL08029"
    with level identifier "030A0106".
    Informational tokens are "DB2 v8.1.1.156", "s080813", "U823673", and FixPak
    "17".
    Product is installed at "/usr/opt/db2_08_01".

    ---------------------------------------------------

    Marcus, go away! This thread went unanswered for 10 days - you had your chance. Why don't you go and copy/paste from the manual into some other thread.

  6. #6
    Join Date
    Jul 2009
    Posts
    150
    Quote Originally Posted by Marcus_A
    I believe that it does work in DB2 LUW V8.2. I assume you are referring to DB2 z/OS.
    You are right. My notes for standard DB2 v8.

    Maybe DB2 LUW V8.2 is closer to V9 for z/os, I don't know that.

    Thanks, Kara

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think ORDER BY in a OLAP function was supported on DB2 V8.

    This was tedted on DB2 V9.7, because I have no DB2 V8 environment.
    Updated
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT workdept
         , DEC(
           AVG(CASE 
    --         WHEN rn_salary * 2 - cnt_part - 1 IN (-1, 0, 1) THEN
               WHEN rn_salary * 2 - cnt_part IN (0, 1, 2) THEN
                    salary
               END
              )
           , 9, 2) AS median_salary
      FROM (SELECT workdept
                 , salary
                 , ROWNUMBER()
                     OVER(PARTITION BY workdept
                              ORDER BY salary  ) rn_salary
                 , COUNT(*)
                     OVER(PARTITION BY workdept) cnt_part
              FROM employee
           ) s
     GROUP BY
           workdept
    ;
    ------------------------------------------------------------------------------
    
    WORKDEPT MEDIAN_SALARY
    -------- -------------
    A00           49250.00
    B01           94250.00
    C01           71110.00
    D11           57740.00
    D21           47250.00
    E01           80175.00
    E11           36250.00
    E21           41895.00
    
      8 record(s) selected.
    Last edited by tonkuma; 09-14-09 at 21:55.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2dummy1
    Marcus, go away! This thread went unanswered for 10 days - you had your chance. Why don't you go and copy/paste from the manual into some other thread.
    The statement that I believed to be incorrect was "rownumber() over() function doesn't exist in V8". I didn't have to look it up in the manual since I have used it many times myself when I used version 8 previously. But anyway, version 8 is obsolete (no longer even supported by IBM).

    I am not sure why you object to people providing quotes from the IBM documentation to answer questions when appropriate, as opposed to some people who just shoot from the hip.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2009
    Posts
    114
    Because reading English documentation is a good practice for most of those who advertise themselves as "database specialists" nowadays

  10. #10
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Dousing the fire.....

    you can have a look on this thread too going simultaneously in some other forum

    Elegant query for a weighted Median? - comp.databases.ibm-db2 | Google Groups
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Another way to calculate Median ?

    I did not use Order By, and did not use any Scalar Functions:

    Code:
    with input(dblcol) as
    (
    select 
    double(1) c1 from sysibm.sysdummy1
    union all
    select 7  c1 from sysibm.sysdummy1 
    union all
    select 12 c1 from sysibm.sysdummy1
    union all
    select 5  c1 from sysibm.sysdummy1
    union all
    select 8  c1 from sysibm.sysdummy1
    union all
    select 10 c1 from sysibm.sysdummy1
    )
    select i1.dblcol median, 0, 0
      from input i1, 
    table
    (select count(*) cnt 
       from input                ) x0
    ,
    table
    (select count(*) leftcnt from input i2
      where i1.dblcol > i2.dblcol ) x1
    ,
    table
    (select count(*) righcnt from input i2
      where i1.dblcol < i2.dblcol ) x2
    where leftcnt = righcnt
      and mod(cnt, 2) = 1
    
    union 
    select (max(i1.dblcol) + min(i3.dblcol)) / 2 median, max(i1.dblcol), min(i3.dblcol)
      from input i1, input i3, 
    table
    (select count(*) cnt 
       from input                ) x0
    ,
    table
    (select count(*) leftcnt from input i2
      where i2.dblcol < i1.dblcol ) x1
    ,
    table
    (select count(*) righcnt from input i2
      where i2.dblcol > i3.dblcol ) x2
    where leftcnt     = righcnt 
      and i3.dblcol   > i1.dblcol  
      and mod(cnt, 2) = 0
    Lenny

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by db2dummy1
    Because reading English documentation is a good practice for most of those who advertise themselves as "database specialists" nowadays
    What's wrong with reading English documentation?

Posting Permissions

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