Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    47

    Unanswered: How to create MQT(Summary Table)

    I am trying to create a summary table from two physical tables. I can't use
    join between those two tables because I don't have one(Many) to one relation between them.


    Table Name: FIELD_MEMBER_ACCT_RELATION

    FMID BIGINT
    ACCT_NBR VARCHAR
    OFF_NBR CHAR
    RR_NBR CHAR
    RELATED_PARTY CHAR
    ACCT_CATEGORY INT

    Primary Key(FMID,ACCT_NBR)

    Table Name: TARGET_ACCT_RELATION

    ACCT_NBR VARCHAR
    TARGET_ID BIGINT
    STATUS SHORT
    INDICATOR SHORT

    Primary Key(ACCT_NBR)

    Note:
    A Given ACCT_NBR of FIELD_MEMBER_ACCT_RELATION may or may not exists in TARGET_ACCT_RELATION table.
    But Still I want that record as part of summary table.

    How can I create a MQT(Summary Table) with following
    information

    COLUMN(Table Name)

    FMID(FIELD_MEMBER_ACCT_RELATION)
    ACCT_NBR(FIELD_MEMBER_ACCT_RELATION)
    OFF_NBR(FIELD_MEMBER_ACCT_RELATION)
    RR_NBR(FIELD_MEMBER_ACCT_RELATION)
    RELATED_PARTY(FIELD_MEMBER_ACCT_RELATION)
    ACCT_CATEGORY(FIELD_MEMBER_ACCT_RELATION)
    TARGET_ID(TARGET_ACCT_RELATION)


    In the summary table, I want the data like this

    a. Take ACCT_NBR from FIELD_MEMBER_ACCT_RELATION.
    If the ACCT_NBR exists in TARGET_ACCT_RELATION then take
    TARGET_ID from TARGET_ACCT_RELATION.
    b. If the ACCT_NBR does not exists in TARGET_ACCT_RELATION then put
    some dummy value(-999) as TARGET_ID


    Let me know if additional information is required.


    Thanks
    Shri
    p.srinivasarao

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can join them, just use LEFT OUTER JOIN. This will give all qualifying rows in the "left" table even if there is not any dependant rows in the "right" table. I suggest that you read the SQL manual.

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    47
    Thanks Winner. I had gone thru the SQL documentation and prepared the following query.


    SELECT FM.FMID, FM.ACCT_NBR, FM.OFF_NBR, FM.RR_NBR, FM.RELATED_PARTY, FM.ACCT_CATEGORY, TA.TARGET_ID
    FROM FIELD_MEMBER_ACCT_RELATION FM LEFT JOIN TARET_MASTER ON FM.ACCT_NBR= TM.ACCT_NBR AND FM.ACCT_CATEGORY=3


    FIELD_MEMBER_ACCT_RELATION contains 2Million records while as TARGET contains 800,000 records. I need your suggestion about the approach.

    1.Run this sql in production for each request
    2.Create a summary table and access this information from summary table.

    Thanks
    Shri
    p.srinivasarao

  4. #4
    Join Date
    Jan 2003
    Posts
    47
    Thanks Winner. I had gone thru the SQL documentation and prepared the following query.


    SELECT FM.FMID, FM.ACCT_NBR, FM.OFF_NBR, FM.RR_NBR, FM.RELATED_PARTY, FM.ACCT_CATEGORY, TA.TARGET_ID
    FROM FIELD_MEMBER_ACCT_RELATION FM LEFT JOIN TARET_MASTER ON FM.ACCT_NBR= TM.ACCT_NBR AND FM.ACCT_CATEGORY=3


    FIELD_MEMBER_ACCT_RELATION contains 2Million records while as TARGET contains 800,000 records. I need your suggestion about the approach.

    1.Run this sql in production for each request
    2.Create a summary table and access this information from summary table.

    Thanks
    Shri
    p.srinivasarao

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Will you be running the same query every time ? or will you be saying 'where account number = xyz and fmid=abc " where xyz and abc vary each time ?

    How frequently are these tables updated or new rows inserted ?

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2003
    Posts
    47

    Comments on Changes

    Will you be running the same query every time ? or will you be saying 'where account number = xyz and fmid=abc " where xyz and abc vary each time ?

    <Shri> Parameter value changes for each request

    How frequently are these tables updated or new rows inserted ?
    <Shri>Changes takes place in two ways
    1. During Market Hours(9:30 ET to 4:00 PM ET) changes will be rare.
    2. At 12:00 , one of the base table will get dropped and recreated with new data.

    Note: I am not sure I can access summary table by dropping the underlying physical table?
    p.srinivasarao

Posting Permissions

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