Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    1

    Unanswered: Help with scalability design

    Guys,

    My first post here, hello to all, thanks in advance for any help you can give.

    Ok so I'll cut straight to it, here's the problem.

    I have a table 2 tables with the following columns

    TABLE1:
    SUMMARY_DATE
    SORT_CODE
    ACCOUNT_NO

    which in any given month will only have one sc and acc-no

    TABLE2
    SUMMARY_DATE
    SORT_CODE
    ACCOUNT_NO
    TYPE
    VOL

    which in any given month could potentially have the same sc acc-no but a different type.

    so

    DATE1 SC1 ACC1 TYPE1 VOL
    DATE1 SC1 ACC1 TYPE2 VOL

    Now when I try and join the 2 tables

    Code:
    SELECT T1.SUMMARY_DATE, 
           T1.ACCOUNT_NO,
           T1.SORT_CODE,
           CASE WHEN TYPE = 'A' THEN
                VOL
           END AS VOL1,
           CASE WHEN TYPE = 'B' THEN
                VOL
           END AS VOL2,
    FROM   TABLE1 T1
           LEFT JOIN
           TABLE2 T2
    ON     T1.SORT_CODE = T2.SORT_CODE
    AND    T1.ACCOUNT_NUMBER = T2.ACCOUNT_NUMBER
    AND    T1.SUMMARY_DATE = T2.SUMMARY_DATE

    You can see that the volumes will be duplicated.

    This can be solved by joining TABLE2 twice

    Code:
    SELECT T1.SUMMARY_DATE, 
           T1.ACCOUNT_NO,
           T1.SORT_CODE,
           T2,VOL,
           T3.VOL
    FROM   TABLE1 T1
    
           LEFT JOIN
    
           TABLE2 T2
    ON     T1.SORT_CODE = T2.SORT_CODE
    AND    T1.ACCOUNT_NUMBER = T2.ACCOUNT_NUMBER
    AND    T1.SUMMARY_DATE = T2.SUMMARY_DATE
    AND    T2.TYPE = 'A'
            
            LEFT JOIN
    
           TABLE2 T3
    ON     T1.SORT_CODE = T3.SORT_CODE
    AND    T1.ACCOUNT_NUMBER = T3.ACCOUNT_NUMBER
    AND    T1.SUMMARY_DATE = T3.SUMMARY_DATE
    WHERE  T3.TYPE = 'B'
    Another solution to this problem would be to change my table from a row to column base.

    so i/o

    DATE1 SC1 ACC1 TYPE1 VOL
    DATE1 SC1 ACC1 TYPE2 VOL

    I would have

    DATE1 SC1 ACC1 TYPEA_VOL TYPEB_VOL

    The problem with both of these approaches is that I have no room for scalability, if I add any products (TYPE) it would mean altering the code

    So I'm looking for a slightly better solution or suggestions?

    P.S Table1 cannot be modified to bring TYPE in

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    MickeySox, This isn't answering you question directly but...

    This should get you the data without duplicating Volume or require the third (fourth, fifth, etc.) joins:
    Code:
    SELECT T1.SUMMARY_DATE, T1.ACCOUNT_NO, T1.SORT_CODE, T2.VOL001, T2.VOL002
    FROM table1 AS T1
           LEFT OUTER JOIN
           (SELECT SUMMARY_DATE, SORT_CODE, ACCOUNT_NO
                 , MAX( CASE WHEN TYPE = 'A' THEN VOL END ) AS VOL001
                 , MAX( CASE WHEN TYPE = 'B' THEN VOL END ) AS VOL002
            FROM table2
            GROUP BY SUMMARY_DATE, SORT_CODE, ACCOUNT_NO
           ) AS T2
             ON     T1.SUMMARY_DATE = T2.SUMMARY_DATE
                AND T1.ACCOUNT_NO   = T2.ACCOUNT_NO
                AND T1.SORT_CODE    = T2.SORT_CODE
    Basically, it is transposing the rows to columns on TABLE2 before joining to table1.

    You would still have to alter the code to add a new VOLxxx to the Select and another MAX(CASE...) to the nested table query. The good news is that it would be a much smaller change. Also, the performance wouldn't decrease very much as more TYPEs are added (as opposed to adding another join for every TYPE).

    But altering the code is 'almost' unavoidable. I can think of two potential ways around this (they may not be the best ways or even the only ways).

    A) If you know what the new TYPEs will be (C, D, E,..., Z), you could go ahead and code all 26 letters of the alphabet (in this example), and have VOL001 through VOL026 in the Select list and 26 MAX(CASE...) statements.

    This way as, the new code is added, no changes are needed (until you try to get past Z).

    If you didn't like the 'extra' columns on your output, you could create a View with just the current valid TYPES and not all the 'future expansion' TYPES. However, you would still need to change the View as new Types are added.

    B) Dynamically build the SQL. I don't use this and may not explain it well. In essence you would build up a character string that contains the SQL you will be running. First you would have to query table2 to find the MAX number of TYPES. Then you would start building the SQL statement:

    SQLSTATEMENT = 'SELECT T1.SUMMARY_DATE, T1.ACCOUNT_NO, T1.SORT_CODE'
    Loop from 1 to Max types
    SQLSTATEMENT = SQLSTATEMENT + ', VOL' + character(loop value)
    end loop
    SQLSTATEMENT = SQLSTATEMENT + 'FROM...ACCOUNT_NO'
    loop from 1 to Max Types
    SQLSTATEMENT = SQLSTATEMENT = ', MAX(CASE...) AS VOL' + charter(loop value)
    end loop
    SQLSTATEMENT + 'FROM...T2.SORT_CODE'

    (Problems: How to put in the comparison types ( = 'A', = 'B', etc.) and how to handle embedded apostrophes).

    This is VERY rough pseudo code but hopefully you get the idea.

    Then you have to prepare/execute the SQLSTATEMENT.

    PS Beyond the general idea, I am not very proficient with generating dynamic SQL. If you have question, I probably can't answer them.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to change your design - whenever you see semething like some_col_1, some_col_2, ..., some_col_n alarm bells should be going off in your head.

    Can you describe in words about your design - it's awefully hard to follow as it stands!
    George
    Home | Blog

Posting Permissions

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