ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Summary fields on joined header/detail files

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

  • Summary fields on joined header/detail files

    I have an SQL view (joins invoice header and detail files) that's used for reporting... The header file contains the invoice total, tax total, and freight total. When joined to the detail file, I need those values to not repeat for each row - otherwise, users could inadvertently sum them when grouping by fields in the detail file.

    My view currently contains a case statement that sets these columns to zero unless the Invoice Detail Sequence number is 1 (representing the first line of the invoice). That works nearly all of the time. However, for some unknown reason, we have invoices where the first detail line starts with a sequence number other than 1.

    There has to be a better way of handling this... is there an SQL function or method that would accomplish the same thing based on the first detail row found for each corresponding header row?

  • #2
    I'm not aware of any better way, but there are many people more knowledgeable than me on this forum.

    But handling the cases where the first invoice number is not 1. My first thought is that you use a subselect within the when clause of the case statement, that calculates and returns the first invoice sequence number. So if this is your view (simplified):
    Code:
    select case when d1.seq = 1 then 0 else h1.total end
    from header h1
    inner join detail d1 on h1.xyz = d1.xyz
    then maybe you could do this:
    Code:
    select case when
    d1.seq = (select min(seq) from detail d2 where h1.xyz = d2.xyz) = 1 then 0 else h1.total end
    from header h1
    inner join detail d1 on h1.xyz = d1.xyz
    The complexity goes way up if you have multiple fields affected by this case statement, but there are plenty of ways of refining it I'm sure, such as maybe separating out the subselect into a Common Table Expression

    There is also some kind of recursive SQL statement that can make a result row reference it's previous or next row. I do not know anything more about them so I don't know for sure if that would work in this case

    Comment


    • #3
      Since you're joining the detail, why include the totals on the header at all? All of that information should be able to be ascertained via the detail.

      Comment


      • #4
        Originally posted by Rocky View Post
        Since you're joining the detail, why include the totals on the header at all? All of that information should be able to be ascertained via the detail.
        One would think so... but that's not the case.
        1. The Tax amount is only on the header
        2. The open/unpaid amount is only on the header
        3. The Shipping Charges are on the header, or in a separate miscellaneous charges detail file

        This is a legacy database.

        I need a relatively efficient way of doing this given the number of times it will be used.

        Comment


        • #5
          You may also use an OLAP Functions which avoids an extra Sub-Select.

          Code:
          Select  k.*, d.*,
                  Case When row_Number() Over (Partition By d.Company, d.OrderNo
                                               Order By d.Company, d.OrderNo, OrderPos) = 1
                       Then HeaderTotal Else 0 End Total
            from    OrderHdrx k join OrderDetx d
                 on     k.company = d.Company
                    and k.OrderNo = d.OrderNo
            Order By d.Company, d.OrderNo, OrderPos;
          If multiple Columns Header Columns must be selected, an additional CTE should be used, so that the ROW_NUMBER() OVER() is coded/executed only once.

          Birgitta

          Comment


          • #6
            Ok... This is a data source for DB2 WebQuery, so I definitely need to select multiple columns from the header file (as well as the detail file)

            I also have 4 columns that should have a value from the header only when joined with the first (or last) row of the detail file, otherwise these columns should be zero.

            This is the "middle" of my SELECT... (IDxxx are detail fields, IHxxx are header fields)
            Code:
            ...
                    WHS_NAME,
                    CASE
                        WHEN IDSEQ# = 1 THEN (IHORG$-IHORD$)
                        ELSE 0
                    END,
                    CASE
                        WHEN IDSEQ# = 1 THEN CAST(ROUND(IHTAX$,2) AS DEC(13,2))
                        ELSE 0
                    END,
                    CASE
                        WHEN IDSEQ# = 1 THEN IHAMT$
                        ELSE 0
                    END,
                    CASE
                        WHEN IDSEQ# = 1 THEN
                            CAST(ROUND(COALESCE((SELECT SUM(ICMIS$) FROM ASTDTA.OEINDLIC
                             WHERE IHCOM# = ICCOM# AND IHIDC#=ICIDC# AND ICMISC = 'FRT'),0),2) AS DEC(13,2))
                         ELSE 0
                    END,
            ...
            So I'm not sure how to do a CTE to accomplish this. After spending some time in Run Sql Scripts, I'm left wondering why there isn't a function to create a column that represents the row number and be able to qualify on it.
            Last edited by gwilburn; July 19, 2018, 02:40 PM.

            Comment


            • #7
              For the CTE... this is what I came up with in Run SQL Scripts... my actual view will be much more complicated. Fields beginning with "ih" are header and "id" are detail.
              Code:
              with InvoiceDetail as (
              select row_number() over(partition by idcom#, ididc#) as rownum, ihcom#, ihent#, ihidc#, (ihorg$-ihord$) as INVTOT,
                       ihamt$, ihtax$, idseq#, idprt#, idshp#, idntu$  
              from INVHDRIH
              join INVDTLID on idcom#=ihcom# and ididc#=ihidc#)
              
              Select  ihcom#, ihent#, ihidc#,
              case when rownum=1 then invtot else 0 end as INVTOT,
              case when rownum=1 then ihamt$ else 0 end as OPNAMT,
              case when rownum = 1 then ihtax$ else 0 end as TAXTOT,
              idseq#, idprt#, idshp#, idntu$
              from InvoiceDetail;
              Where is it best and/or the most efficient place to have the "where" clause and "join" to other files?

              Comment


              • #8
                What I've tried:
                1. The subselect
                2. The OLAP function, and
                3. The OLAP function in a CTE

                All of these options seemed to create quite a bit of overhead (i.e. queries over the resulting SQL view were very, very slow when compared to the original). Do I have any other options? Should I create a small SQL view that is simply the "key fields" and row number for the detail file, and then JOIN to that new view so I can select ROWNUM=1?

                I'm well beyond my knowledge of SQL here.

                Comment

                Working...
                X