Professional Web Applications Themes

summary table - IBM DB2

I have a summary table, which is a join from 2 base tables. I found the summary table might be dropped unexpectly if the loading to the base tables failed and a load terminate was applied. Does anyone know when a summary table can be dropped implicitly? (Solaris, DB2 EE 8.1)...

  1. #1

    Default summary table

    I have a summary table, which is a join from 2 base tables. I found
    the summary table might be dropped unexpectly if the loading to the
    base tables failed and a load terminate was applied. Does anyone know
    when a summary table can be dropped implicitly? (Solaris, DB2 EE 8.1)
    Shane Guest

  2. #2

    Default Summary Table

    We're running db2 udb v8.1 on zLinux. I'm getting syntax errors
    creating a summary table. We've done this before successfully; I
    can't pinpoint the problem in this one. There are two see-saw errors.
    Without the "as..." clause on a column, I get "duplicate name"; with
    the "as ..." clause I get "invalid fullselect". The fullselect runs
    successfully removed from the create statement with or without the
    "as...". Hopefully some sharp eyes in the audience can spot the
    problem.

    This is the version with the "as..." clauses.

    create summary table mysummary.table as
    (
    SELECT
    Person.AccessNumber
    , PC.ContactActionTaken
    , CA.Description as ActionDesc
    , PC.ContactDate
    , PC.ContactForm
    , CB.Description as ContactDesc
    , PC.ContactIntCode
    , CC.Description as IntDesc
    , PC.ContactTime
    , PC.ContextCode
    , PC.Notes1 concat rTRIM (PC.Notes2) concat rTRIM(PC.Notes3) concat
    rTRIM(PC.Notes4) concat rTRIM(PC.Notes5)
    , V.NAMESORTFIELD
    , PC.UpdateNatUser
    , PC.UpdateTimestamp
    , PC.PersonId
    FROM
    View1 PERSON,
    View2 PC
    LEFT OUTER JOIN View3 CA
    ON PC.ContactActionTaken = CA.Code
    LEFT OUTER JOIN View4 CB
    ON PC.ContactForm = CB.Code
    LEFT OUTER JOIN View5 CC
    ON PC.ContactIntCode = CC.Code
    LEFT OUTER JOIN View6 V
    ON PC.UCCONTACTID = V.PersonID AND V.CURRENTID = 'Y'
    WHERE PERSON.PERSONID = PC.PERSONID
    and PC.ContextCode not in ('COLL', 'ADM', 'TELC')
    )
    data initially deferred refresh deferred
    ;

    Many thanks for any help,

    Mary Lou Arundell
    mla Guest

  3. #3

    Default Re: Summary Table

    Don't know if it's the same in v8.1, but in v7.2, you get an error on the
    creation of a summary table if the fullselect has a column that is not named
    (such as in your concatenated column you're returning.). If running the
    fullselect by itslef produces any column with an unnamed header, then the
    resultant summary table won't be able to be referenced by name for that
    column. Try adding an alias in your select statement:

    PC.Notes1 concat rTRIM (PC.Notes2) concat rTRIM(PC.Notes3) concat
    rTRIM(PC.Notes4) concat rTRIM(PC.Notes5) AS PCNOTES


    Evan


    "mla" <uc.edu> wrote in message
    news:google.com... 


    Evan Guest

Similar Threads

  1. How to derive summary info from one single table..
    By Anith Sen in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 01:43 PM
  2. Summary fields or Summary table?
    By Scott in forum FileMaker
    Replies: 1
    Last Post: June 27th, 03:34 PM

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