ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Looking for recommendations

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Looking for recommendations

    We basically have two data sources for our General Ledger Data... GLMAST and GLDETAIL

    GLMAST contains one row for each GL Account Number and Year. It contains columns for Beginning Balance (begin of year) and transaction totals for each period in separate columns.

    ACCT_NBR, YEAR, BEG_BAL, PRD01_AMT, PRD02_AMT.... etc.



    GLDETAIL contains a row for each GL transaction. It contains Acct#, Year, Period, Transaction Date, Transaction Number, Transaction Amount, etc..

    ACCT_NBR, YEAR, PERIOD, TRANS_NBR, TRANS_AMT

    Our GL Report writer handles these well, BUT is older than the AS/400... So I'm trying to make this data available to a report writer (DB2 WebQuery) using SQL views and metadata.


    Many GL Reports we currently have select an Account (or range) and a Year/Period (or range), then report a beginning balance, transactions and an ending balance. Some just report summary information. I was hoping to join these two files in a way that would all for efficient reporting. The purpose of the GLMAST was to avoid having to sum all of the detail up to a certain year/period for reporting.

    I'm thinking that the period transaction totals in GLMAST should each be a row instead of separate columns within one row. But I'm not sure how (or if) that can be done efficiently.

    Or maybe I'm approaching this completely wrong??

    Anyone encounter something similar to this?

    thx,

    Greg

  • #2
    To get the separate period total columns to b the same column on separate rows, you need to 'unpivot' them, which you can do with the LATERAL() function. There's info on how to use it here (about half way down): https://www.ibm.com/developerworks/c...bles56?lang=en
    I think something like that would be the best way if you want to be able to join to the period specific total on GLMAST.

    However you join the files, the query is only as efficient as the available indexes.

    I would suggest you build the query however you want (and post it here for feedback), then use the VisualExplain feature in Run SQL Scripts to analyse how efficient it is. VisualExplain includes an Index Advisor feature that will tell you what additional indexes could be created over the files that may make the query more efficient.

    Comment


    • #3
      Thanks Vector! This is exactly what I needed. I'm placing this in an SQL view. The selection will actually take place in DB2 WebQuery, so I will have to look at index advisor after we have some queries built. I looked at it over the select in Run Sql Scripts, and it didn't advise an index unless I added a select clause. Seems very fast though.

      Here's the code
      Code:
      select com_nbr, com_name, com_type, rec_type, glact#, glyer#, prd, begbal, prdamt
      from astdta.glmastgl as g
      inner join V_CLIENTS AS CLT ON G.GLCOM# = CLT.COM_NBR
      inner join V_GL_RECORDTYPE AS REC ON GLRECT=REC.REC_TYPC,
      lateral (values
      (1, cast(g.glbbl$ as dec(13,2)), g.gla01),
      (2, cast(g.glbbl$+g.gla01 as dec(13,2)), g.gla02),
      (3, cast(g.glbbl$+g.gla01+g.gla02 as dec(13,2)), g.gla03),
      (4, cast(g.glbbl$+g.gla01+g.gla02+g.gla03 as dec(13,2)), g.gla04),
      (5, cast(g.glbbl$+g.gla01+g.gla02+g.gla03+g.gla04 as dec(13,2)), g.gla05),
      (6, cast(g.glbbl$+g.gla01+g.gla02+g.gla03+g.gla04+g.gla05 as dec(13,2)), g.gla06),
      (7, cast(g.glbbl$+g.gla01+g.gla02+g.gla03+g.gla04+g.gla05+g.gla06 as dec(13,2)), g.gla07),
      (8, cast(g.glbbl$+g.gla01+g.gla02+g.gla03+g.gla04+g.gla05+g.gla06+g.gla07 as dec(13,2)), g.gla08),
      (9, cast(g.glbbl$+g.gla01+g.gla02+g.gla03+g.gla04+g.gla05+g.gla06+g.gla07+g.gla08 as dec(13,2)), g.gla09),
      (10, cast(g.glbbl$+g.gla01+g.gla02+g.gla03+g.gla04+g.gla05+g.gla06+g.gla07+g.gla08+g.gla09 as dec(13,2)), g.gla10),
      (11, cast(g.glbbl$+g.gla01+g.gla02+g.gla03+g.gla04+g.gla05+g.gla06+g.gla07+g.gla08+g.gla09+g.gla10 as dec(13,2)), g.gla11),
      (12, cast(g.glbbl$+g.gla01+g.gla02+g.gla03+g.gla04+g.gla05+g.gla06+g.gla07+g.gla08+g.gla09+g.gla10+g.gla11 as dec(13,2)), g.gla12)) 
      as t(prd, begbal, prdamt)
      Thanks again. I never thought to Google "unpivot"

      Comment

      Working...
      X