10 Replies Latest reply on Mar 16, 2016 3:02 PM by user19752

    Virtual list creation

    Stu412

      Hi all

       

      I'm using the technique found in the FM 13 advanced training series to generate virtual lists.

       

      I'm using virtual lists to quickly transfer data to a scratch table used for reporting.  I have an ExecuteSQL formula to gather some of the data in question:

      ExecuteSQL (

      "

      SELECT GroupID, SUM(RoundedValue)

      FROM DataTable_Home

      WHERE  CustID_FK = ? and Code < ? and PeriodNumber = 2 and

      (GroupID = 68 or GroupID = 3)

      GROUP BY GroupID

      ";

      "|" ; "|" ;

      Cust_Home::CustID_PK;

      "500"

      )

       

      This generates in most cases a dataset of:

      68|-3600|3|-7200| <<< as an example for this customer with 68 and 3 being the required group 'names' or headers.

       

      This data is passed via scripting into a field on the scratch table called ListData which has several fields dedicated to particular values, two of which are dedicated to Group68 values and Group3 values.  The formula in each is:

       

      Get Value(ScratchTable::ListData;2)<<<For Group 68, -3600

      Get Value(ScratchTable::ListData;4)<<<For Group 3. -7200

       

      However, there may be cases where a customer does not have any values associated to either 68 or 3 which obviously reduces the return on the dataset and makes the parse section inaccurate.  On my scratch table, I have several fields

       

      If a particular customer has NO value for group 68, the returned data set instead looks like:

      3|-7200

      and this will break all downstream GetValue(Field;x) calculations.

       

      So, what I really need to have is a reliable way of ALWAYS returning 4 pipe separated results in the situation that a customer has data missing from one or other of the groups, and that's where I'm stuck.  If I could get to:

       

      68|0|3|-7200 or

      68|-3600|3|0

      as an example, this would enable me to parse the 0 values to where they should be.

       

      Any help on this one greatly appreciated.

       

      Thanks

        • 1. Re: Virtual list creation
          Mike_Mitchell

          Hey, Stu. I believe the COALESCE function will help you here:

           

          SELECT COALESCE ( GroupID, '0' ), COALESCE ( SUM(RoundedValue), '0' )

          FROM DataTable_Home

          WHERE  CustID_FK = ? and Code < ? and PeriodNumber = 2 and

          (GroupID = 68 or GroupID = 3)

          GROUP BY GroupID

          ";

          "|" ; "|" ;

          Cust_Home::CustID_PK;

          "500"

          )

           

          What COALESCE does is returns the first non-NULL value from the list. So if your column is null for that particular field, the above query will substitute a 0.

           

          HTH

           

          Mike

          • 2. Re: Virtual list creation
            beverly

            the "0|" for a GroupID that has no values probably would not do. And I'm not entirely sure ExecuteSQL() allows anything nested when it comes to aggregates, so YMMV.

             

            I think I'd just use two queries (one for each GroupID) and make the first value as constant:


            ~query1 = " SELECT '68', COALESCE(SUM(RoundedValue),'0') FROM ... WHERE ...

            ~query1 = " SELECT '3', COALESCE ....

            Then combine with the "|" between them, in a Let(), final calc.

            beverly



            • 3. Re: Virtual list creation
              user19752

              Perhaps COALESCE(SUM()) don't work in FM. If so, SUM(COALESCE()) will work.

              • 4. Re: Virtual list creation
                beverly

                did you test it?

                beverly

                • 5. Re: Virtual list creation
                  Stu412

                  Hi Beverly

                   

                  Just testing this now, although not having too much luck with any COALESCE commands when included.  Currently looking at this although ending up with just '?'.

                   

                  ExecuteSQL (

                  "

                  SELECT COALESCE (GroupID,'0'), COALESCE(SUM(RoundedValue),'0')

                  FROM DataTable_Home

                  WHERE  CustID_FK = ? and Code < ? and PeriodNumber = 1 and

                  (GroupID = 68 or GroupID = 3)

                  GROUP BY GroupID

                  ";

                  "|" ; "|" ;

                  Cust_Home::CustD_PK;

                  "500"

                  )

                  • 6. Re: Virtual list creation
                    Mike_Mitchell

                    I believe Beverly is correct on her suspicion regarding aggregates.

                     

                    error.png

                    • 7. Re: Virtual list creation
                      Mike_Mitchell

                      Oops! My bad; forgot the GROUP BY clause.

                       

                      user19752 appears to be correct. This works:

                       

                      error.png

                      • 8. Re: Virtual list creation
                        user19752

                        COALESCE doesn't make result record, it does only return alternate value for NULL.

                         

                        I tested

                        SELECT 68, SUM(COALESCE(RoundedValue,0)) ...

                        but this returns empty value for both column as

                        ,

                        (only column separater there)

                         

                        COUNT(68) returns 0 for 0 found set, but other aggregate functions can't return 0 nor literal value.

                        • 9. Re: Virtual list creation
                          beverly

                          Well, exactly! this doesn't work for OP.

                           

                          This may need the separate queries along with the SQL CASE() function to return 0 if there would be no result (because no data to test!)

                           

                          beverly

                          • 10. Re: Virtual list creation
                            user19752

                            ExecuteSQL (

                            "

                            SELECT 68,SUM(CASE WHEN GroupID=68 THEN RoundedValue ELSE 0 END),

                              3,SUM(CASE WHEN GoupID=3 THEN RoundedValue ELSE 0 END)

                            FROM DataTable_Home

                            WHERE  CustID_FK = ? and Code < ? and PeriodNumber = 1 and

                            (GroupID = 68 or GroupID = 3)

                            ";

                            "|" ; "|" ;

                            Cust_Home::CustD_PK;

                            "500" //Code is text??

                            )

                             

                            This will return always 4 column, but can be null if there is no found set.

                             

                            Or if there is group master table, LEFT JOIN may be used.