Professional Web Applications Themes

Problem with casting when using common-table-expression - IBM DB2

Hi everyone, I have a very strange problem. I'm not an expert with DB2, but I don't think I ever faced that kind of problem with any other DBMS. I want to make two queries and then join their results. I found that the common-table-expression is a good solution, since I don't want to create a view. When I do either of the select statements on their own, they run successfully and produce results. When I join them, I get a problem with the first one of them. Here goes the queries: The first: select prddlib.GIPMST.GIPID AS GROUP_PURPOSE, prddlib.GIPMST.GIGID AS ...

  1. #1

    Default Problem with casting when using common-table-expression

    Hi everyone,

    I have a very strange problem. I'm not an expert with DB2, but I don't
    think I ever faced that kind of problem with any other DBMS.

    I want to make two queries and then join their results. I found that
    the common-table-expression is a good solution, since I don't want to
    create a view.

    When I do either of the select statements on their own, they run
    successfully and produce results. When I join them, I get a problem
    with the first one of them.

    Here goes the queries:

    The first:

    select prddlib.GIPMST.GIPID AS GROUP_PURPOSE, prddlib.GIPMST.GIGID AS
    GROUP, prddlib.CMACLM.CMACLI AS CLIENT_ID
    from prddlib.CMACLM, prddlib.GIPMST, prddlib.GMPMST
    where prddlib.GIPMST.GIGID=prddlib.GMPMST.GMGID
    and prddlib.GIPMST.GIPID='DURAGRPS'
    and prddlib.GIPMST.GIMTY='CLIENT'
    and prddlib.CMACLM.CMACLI = INT(prddlib.GMPMST.GMMID);

    The second:

    select prddlib.ATAHLD.ATACLI AS CLIENT_ID, SUM(prddlib.ATAHLD.ATAQTY)
    AS AMOUNT
    from prddlib.ATAHLD
    where prddlib.ATAHLD.ATAISR='EC0915' and prddlib.ATAHLD.ATAISE='48'
    and prddlib.ATAHLD.ATASCD='7'
    group by prddlib.ATAHLD.ATACLI;

    Both:

    WITH CLIENTS_TOTALS AS (
    select prddlib.ATAHLD.ATACLI AS CLIENT_ID, SUM(prddlib.ATAHLD.ATAQTY)
    AS AMOUNT
    from prddlib.ATAHLD
    where prddlib.ATAHLD.ATAISR='EC0915' and prddlib.ATAHLD.ATAISE='48'
    and prddlib.ATAHLD.ATASCD='7'
    group by prddlib.ATAHLD.ATACLI),

    CLIENTS_GROUPS AS (
    select prddlib.GIPMST.GIPID AS GROUP_PURPOSE, prddlib.GIPMST.GIGID AS
    GROUP, prddlib.CMACLM.CMACLI AS CLIENT_ID
    from prddlib.CMACLM, prddlib.GIPMST, prddlib.GMPMST
    where prddlib.GIPMST.GIGID=prddlib.GMPMST.GMGID
    and prddlib.GIPMST.GIPID='DURAGRPS'
    and prddlib.GIPMST.GIMTY='CLIENT'
    and prddlib.CMACLM.CMACLI = INT(prddlib.GMPMST.GMMID))

    select GROUP AS GROUP_ID, CLIENTS_TOTALS.CLIENT_ID, AMOUNT AS TOTAL
    from CLIENTS_TOTALS, CLIENTS_GROUPS
    where CLIENTS_TOTALS.CLIENT_ID=CLIENTS_GROUPS.CLIENT_ID
    order by CLIENT_ID;

    I get this error:

    SQL0420] Character in CAST argument not valid. Cause . . . . . : A
    character in the argument for the CAST function was not correct.
    Recovery . . . : Change the result data type to one that recognizes
    the characters in the CAST argument, or change the argument to contain
    a valid representation of a value for the result data type. Try the
    request again.

    The problem is, in the first query, the columns which are used for
    joining are not of the same data type and hence the casting
    INT(prddlib.GMPMST.GMMID). Also, this field contains non numerical
    values for some of the rows. In the query, this is handled by a couple
    of conditions in where clause that are enough to remove all the rows
    with the non-numerical values before the join.

    It seems that when I use common-table-expression, the query optimizer
    chooses to do the join before using the conditions in the where clause
    and that causes the problem. I even tried adding the conditions to the
    full-select at the end but still, the problem remained. Is there a way
    I can prevent the optimizer from messing up with my query? Or even,
    enforce a certain ordering of the conditions?

    Any help appreciated.
    Tarek M. Nabil Guest

  2. #2

    Default Re: Problem with casting when using common-table-expression

    There are two ideas. Though, I'm not shure these will resolve your problem or not.
    First idea: Use "inner join" syntax for final select.
    select GROUP AS GROUP_ID, CLIENTS_TOTALS.CLIENT_ID, AMOUNT AS TOTAL
    from CLIENTS_TOTALS
    inner join
    CLIENTS_GROUPS
    on CLIENTS_TOTALS.CLIENT_ID=CLIENTS_GROUPS.CLIENT_ID
    order by CLIENT_ID;

    Second idea: use "group by" for CLIENTS_GROUPS.
    CLIENTS_GROUPS AS (
    select prddlib.GIPMST.GIPID AS GROUP_PURPOSE, prddlib.GIPMST.GIGID AS
    GROUP, prddlib.CMACLM.CMACLI AS CLIENT_ID
    from prddlib.CMACLM, prddlib.GIPMST, prddlib.GMPMST
    where prddlib.GIPMST.GIGID=prddlib.GMPMST.GMGID
    and prddlib.GIPMST.GIPID='DURAGRPS'
    and prddlib.GIPMST.GIMTY='CLIENT'
    and prddlib.CMACLM.CMACLI = INT(prddlib.GMPMST.GMMID)
    group by prddlib.GIPMST.GIPID, prddlib.GIPMST.GIGID, prddlib.CMACLM.CMACLI
    )
    Tokunaga T. Guest

  3. #3

    Default Re: Problem with casting when using common-table-expression

    First of all, I'd like to thank you for your help.

    The "group by" idea worked just great. I think that solves my problem.

    I also tried the "inner join" thing but it didn't make any difference.
    In the manual, it says "Using the INNER JOIN syntax with a
    join-condition will produce the same result as specifying the join by
    listing two tables in the FROM clause separated by commas and using
    the where-clause to provide the condition." So, I guess the different
    syntax doesn't have an effect on the results.

    Thanks again :)
    Tarek M. Nabil Guest

  4. #4

    Default Re: Problem with casting when using common-table-expression

    com (Tarek M. Nabil) wrote in message news:<google.com>... 

    Tarek,

    DB2 performs extensive query rewrite and optimization, so you can't
    make the assumption that, just because your subselect has been
    evaluated one particular way when run stand-alone, this will hold when
    it is merged in with other SQL.

    <digression>
    Commmon table expressions are an especially interesting case because,
    when referenced more than once in a query, they should guarantee
    consistent results. This is suggestive that they tend to be evaluated
    "up front", which I agree would then make your experience a bit
    surprising - however in your case the CTE's are referenced just once,
    so it's not unreasonable to speculate that the DB2 optimiser might
    therefore rewrite them as regular table expressions.
    </digression>

    Anyway, I don't think this is so much a case of you being unlucky that
    your full query didn't work... more that you were lucky that the
    subselect didn't exhibit the same problem in the first place when run
    stand-alone. There's no guarantee that filter predicates will be
    evaluated before join predicates at the same level.

    I can't see how GROUP BY is a guarantee against the problem either.
    AFAICS the only correct solution is to place your filtering predicates
    in their own separate scope (inside a table expression), and do the
    join subsequently. Something of the form:

    SELECT
    *
    FROM
    (
    SELECT
    INTEGER(join_column),
    etc
    FROM
    table_1
    WHERE
    filter_predicates...
    ) AS a
    INNER JOIN
    table_2 AS b
    ON
    a.join_column = b.join_column

    More or less like that anyway.


    Jeremy Rickard
    Jeremy Guest

Similar Threads

  1. Casting Problem returning an ArrayList
    By Paul D. Fox in forum ASP.NET Web Services
    Replies: 5
    Last Post: December 10th, 06:19 PM
  2. Replies: 2
    Last Post: August 4th, 08:36 PM
  3. Expression result not entered in table
    By Kerman in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 05:02 PM
  4. SELECT to find items NOT common in a table
    By Ivan Demkovitch in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 08:10 PM
  5. sed expression problem ?
    By David selby in forum Debian
    Replies: 4
    Last Post: July 1st, 01:00 AM

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