Professional Web Applications Themes

VALUE(SELECT_STMT1, SELECT_STMT2) - IBM DB2

Klemens wrote on Thursday 03 July 2003 18:43: > I try > INSERT INTO TAB1 (COL1) SELECT VALUE(SELECT_STMT1, SELECT_STMT2) FROM TAB2 > > Why is SELECT_STMT2 executed even if SELECT_STMT1 returns a NOT NULL value? > > I tried to workaround with: > CASE WHEN SELECT_STMT1 IS NULL THEN SELECT_STMT2 ELSE SELECT_STMT1 END > > But here SELECT_STMT2 again was executed while SELECT_STMT1 returned a NOT > NULL value > > The problem with this behavior is that I have data where SELECT_STMT2 > returns more than 1 row but only while SELECT_STMT1 returns 1 row. So if > SELECT_STMT2 ...

  1. #1

    Default Re: VALUE(SELECT_STMT1, SELECT_STMT2)

    Klemens wrote on Thursday 03 July 2003 18:43:
    > I try
    > INSERT INTO TAB1 (COL1) SELECT VALUE(SELECT_STMT1, SELECT_STMT2) FROM TAB2
    >
    > Why is SELECT_STMT2 executed even if SELECT_STMT1 returns a NOT NULL value?
    >
    > I tried to workaround with:
    > CASE WHEN SELECT_STMT1 IS NULL THEN SELECT_STMT2 ELSE SELECT_STMT1 END
    >
    > But here SELECT_STMT2 again was executed while SELECT_STMT1 returned a NOT
    > NULL value
    >
    > The problem with this behavior is that I have data where SELECT_STMT2
    > returns more than 1 row but only while SELECT_STMT1 returns 1 row. So if
    > SELECT_STMT2 would only be executed if SELECT_STMT1 IS NULL I wouldn't have
    > a problem using this Statement.
    Which version of DB2 are ou using and on which platform?

    I remember having seen this behaviour (with the COALESCE function) sometime in
    the yearly V6 or V7 stages in DB2 LUW. It was changed later on, but I don't
    remember in which FixPack that happened.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  2. #2

    Default Re: VALUE(SELECT_STMT1, SELECT_STMT2)

    In V8 you can do: COALESCE(SELECT_STMT1, (SELECT ... FETCH FIRST ROW
    ONLY))

    Why is that? It's not clear that COALESCE (aka VALUE) executes the
    second expression only if the first is NULL.
    AFAIK COALESCE is simply a multi-argument fucntion, and it is normal to
    compute argumnets before executing the function, ....

    Cheers
    Serge

    --
    Serge Rielau
    DB2 UDB SQL Compiler Development
    IBM Software Lab, Toronto

    Visit DB2 Developer Domain at
    [url]http://www7b.software.ibm.com/dmdd/[/url]


    Serge Rielau Guest

  3. #3

    Default Re: VALUE(SELECT_STMT1, SELECT_STMT2)


    "Knut Stolze" <stolzede.ibm.com> schrieb im Newsbeitrag
    news:be3t36$p6q$1fsuj29.rz.uni-jena.de...
    > Klemens wrote on Thursday 03 July 2003 18:43:
    >
    > > I try
    > > INSERT INTO TAB1 (COL1) SELECT VALUE(SELECT_STMT1, SELECT_STMT2) FROM
    TAB2
    > >
    > > Why is SELECT_STMT2 executed even if SELECT_STMT1 returns a NOT NULL
    value?
    > >
    > > I tried to workaround with:
    > > CASE WHEN SELECT_STMT1 IS NULL THEN SELECT_STMT2 ELSE SELECT_STMT1 END
    > >
    > > But here SELECT_STMT2 again was executed while SELECT_STMT1 returned a
    NOT
    > > NULL value
    > >
    > > The problem with this behavior is that I have data where SELECT_STMT2
    > > returns more than 1 row but only while SELECT_STMT1 returns 1 row. So if
    > > SELECT_STMT2 would only be executed if SELECT_STMT1 IS NULL I wouldn't
    have
    > > a problem using this Statement.
    >
    > Which version of DB2 are ou using and on which platform?
    >
    > I remember having seen this behaviour (with the COALESCE function)
    sometime in
    > the yearly V6 or V7 stages in DB2 LUW. It was changed later on, but I
    don't
    > remember in which FixPack that happened.
    >
    > --
    > Knut Stolze
    > Information Integration
    > IBM Germany / University of Jena
    I use UDB7.2 Fix9 on Win2000.

    Klemens


    Klemens Guest

  4. #4

    Default Re: VALUE(SELECT_STMT1, SELECT_STMT2)


    "Serge Rielau" <srielauca.eye-bee-em.com> schrieb im Newsbeitrag
    news:3F057D8A.3050605ca.eye-bee-em.com...
    > In V8 you can do: COALESCE(SELECT_STMT1, (SELECT ... FETCH FIRST ROW
    > ONLY))
    >
    > Why is that? It's not clear that COALESCE (aka VALUE) executes the
    > second expression only if the first is NULL.
    > AFAIK COALESCE is simply a multi-argument fucntion, and it is normal to
    > compute argumnets before executing the function, ....
    >
    > Cheers
    > Serge
    >
    > --
    > Serge Rielau
    > DB2 UDB SQL Compiler Development
    > IBM Software Lab, Toronto
    >
    > Visit DB2 Developer Domain at
    > [url]http://www7b.software.ibm.com/dmdd/[/url]
    >
    >
    But shouldn't the failed workaround with CASE Statement only execute that
    Statements it really needs?

    Klemens



    Klemens Guest

  5. #5

    Default Re: VALUE(SELECT_STMT1, SELECT_STMT2)

    CASE expression, yes. If you read the SQL Ref it talks indeed about the
    execution order.
    A lot of the issues (especially with functions with EXTERNAL ACTION)
    have been fixed in recent fixpacks (certainly on V8). I would need to
    check whether this includes scalar subqueries.
    It's a funny thing with scalar subqueries since they do have the same
    power as raise_error() which is considered to have external action, but
    in general DB2 does not treat them as such.
    If you wish you can open a PMR, be aware that it won't be an over night
    patch though .....

    Cheers
    Serge

    --
    Serge Rielau
    DB2 UDB SQL Compiler Development
    IBM Software Lab, Toronto

    Visit DB2 Developer Domain at
    [url]http://www7b.software.ibm.com/dmdd/[/url]


    Serge Rielau Guest

  6. #6

    Default Re: VALUE(SELECT_STMT1, SELECT_STMT2)

    Lennart Jonsson wrote on Saturday 05 July 2003 08:34:
    > db2 "values coalesce(1, 1/0)"
    >
    > or
    >
    > db2 "values case when 1=1 then 1 else 1/0 end"
    According to the SQL standard, those two statements should behave exactly the
    same. See SQL99, Subclause 6.21 "<case expression>", Syntax Rules 2) and 3).

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139