1 2 Previous Next 17 Replies Latest reply on May 2, 2016 4:54 AM by beverly

    ExecuteSQL help

    KrisYells

      HI,

      It was recently suggested to me I try to use Execute SQL and Virtual List technique to produce my crosstab report. unfortunately my situation is different than all the training material  and tutorials I have been working with. Can some one help with how the Execute SQL should be set up?

       

      I have 2 tables joined by a property ID field. The parent table is a list of properties ID's. The child  consist of multiple records entered via a portal from the parent table. The child records are events that include the actual event and the date of the event. The event field is picked from a value list so it's the same field, different records.  An Event can be Documents Received, Documents Signed, Documents Mailed. I have 160 different events that could happen on a property. Not every property will have every event.

       

      In the past, If I pull a crosstab list view from the parent table it will only give me the first event entered for the property.

      or if I pull it from the events table it will give me correct event result, it wont include all the properties that don't have any events.

       

      My boss will come to me and say I need all the properties in a list showing these 3 events. I have all this in a spread sheet so getting her the report is no problem. I would like to solve this issue so I can commit fully to Filemaker.

       

      example:

      Property IDDocuments ReceivedDocuments SignedDocuments Mailed
      5015/1/20165/2/20165/3/2016
      502
      5035/2/2015

       

       

      thank you

        • 1. Re: ExecuteSQL help
          okramis

          Try something like this:

           

          Let ( [

          _headers = "Property ID" & Char(9) & "Documents Received" & Char(9) & "Documents Signed" & Char(9) & "Documents Mailed"

          ; _list = ExecuteSQL ( "

          SELECT p.PropertyID,

          (SELECT CASE WHEN c.EventDate IS NULL THEN '' ELSE c.EventDate END FROM ChildTable c WHERE c.PropertyID=p.PropertyID AND c.Event='Documents Received'),

          (SELECT CASE WHEN c.EventDate IS NULL THEN '' ELSE c.EventDate END FROM ChildTable c WHERE c.PropertyID=p.PropertyID AND c.Event='Documents Signed'),

          (SELECT CASE WHEN c.EventDate IS NULL THEN '' ELSE c.EventDate END FROM ChildTable c WHERE c.PropertyID=p.PropertyID AND c.Event='Documents Mailed')

          FROM ParentTable p

          " ; Char(9) ; "" )

          ] ;

          _header & ¶ &

          _list

          )

           

          Add as many sub-selects as you need Events

           

          best regards

          Otmar

          • 2. Re: ExecuteSQL help
            siplus

            In case of multiple events of same type for the same property, you probably want the last one to appear, so I'd use MAX(c.eventDate).

             

            Also don't forget that SQL returns dates in YYYY-MM-DD format, use a custom function to reformat the dates the way you like them.

            • 3. Re: ExecuteSQL help
              beverly

              no need to use custom function to post-process, if you request the date in the query:

              (using Otmar's query)

               

              SELECT CASE WHEN c.EventDate IS NULL THEN '' ELSE c.EventDate END

              change to:

              SELECT CASE WHEN c.EventDate IS NULL THEN '' ELSE COALESCE(c.EventDate,'') END

               

              you will be converting-on-the-fly from YYYY-MM-DD to standard FileMaker dates (m/d/yyyy or d/m/yyyy depending on your language settings/FM version). NOTE: the '' is two single quotes when used in COALESCE().

               

              In fact, you probably can throw out the entire CASE and just use COALESCE() which return the first non-NULL result or NULL if empty:

               

              ExecuteSQL ( "

              SELECT p.PropertyID,

              (SELECT COALESCE(c.EventDate,'') FROM ChildTable c WHERE c.PropertyID=p.PropertyID AND c.Event='Documents Received'),

              (SELECT COALESCE(c.EventDate,'') FROM ChildTable c WHERE c.PropertyID=p.PropertyID AND c.Event='Documents Signed'),

              (SELECT COALESCE(c.EventDate,'') FROM ChildTable c WHERE c.PropertyID=p.PropertyID AND c.Event='Documents Mailed')

              FROM ParentTable p

              ORDER BY p.PropertyID

              " ; "|" ; "" ) // pipe used for column separators to use with virtual list

               

              My notes on COALESCE/date formatting in eSQL:

              1.

              /*

              The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must be at least 2 arguments.

              COALESCE(LastName||', ', '')||FirstName

              */

              /*

              COALESCE(expression1,...n) is equivalent to the following CASE expression:

               

              CASE

                 WHEN (expression1 IS NOT NULL) THEN expression1

                 WHEN (expression2 IS NOT NULL) THEN expression2

                 ...

                 ELSE expressionN

              END

              */

               

              2.

              John Renfrew shared that COALESCE on a date returns FMdate instead of SQLdate.

              COALESCE(CURRENT_DATE,'') ==> 5/1/2016

               

              3.

              Applied to timestamps:

              COALESCE( CURRENT_TIMESTAMP,'' ) ==> 5/1/2016 7:24 AM

               

              4.

              Jason Young uses this method:

              ''||CURRENT_DATE

              Which effectively casts the date as text by concatenating empty at the beginning, but does not use the COALESCE().

               

              5.

              Depending on the OS/FM version, the date (and in timestamps) can be returned as

              m/d/yyyy or d/m/yyyy

               

              beverly

              • 4. Re: ExecuteSQL help
                siplus

                Coalesce and MAX don't play well together, AFAIK.

                • 5. Re: ExecuteSQL help
                  beverly

                  You are correct! The only place I see MAX() is in your reply not in KrisYells question.

                   

                  If this is a desired column, I might use FM's Max() to get the latest date and put into another column which could be queried in eSQL without any post-processing if using COALESCE().

                   

                  beverly

                  • 6. Re: ExecuteSQL help
                    okramis

                    Yes, coalesce is a great thing! The Max would be needed, if the values of subselects are not distinct, as a subselect as column can not return more then 1 value. Instead of a CF, I usually populate a VL with a first query (using MAX() in this case) and do a 2nd query on the VL this time with the coalesce

                    • 7. Re: ExecuteSQL help
                      beverly

                      I still don't see where MAX() was required by OP or in your initial query.

                      Maybe I'm missing some information from Kris?

                       

                      If MAX() is needed, I still might get calculation fields in the Parent record for those values and the use eSQL to make the cross-tab data work well. Or, because the values are already in the Parent record, just use a report to show them.

                       

                      The need is for a report showing only 3 related events. eSQL may or may not be the best solution.

                      beverly

                      • 8. Re: ExecuteSQL help
                        okramis

                        MAX would only be needed, if Siplus' assumtion of multiple records for Property ID and Event type would be true. I didn't see this need in the OP either...

                        Otmar

                        • 9. Re: ExecuteSQL help
                          siplus

                          OP says

                           

                          In the past, If I pull a crosstab list view from the parent table it will only give me the first event entered for the property.

                           

                          which means that he already went to hunt for related records via a relationship and obviously got only the first one. Of course, I don't know if he used the event type in the relationship as well, but being able to read beyond words and see beyond exposed problem, anticipating what's hiding behind the corner, is something that does make a difference in my business and profession.

                           

                          As of MAX not playing well with the well-known coalesce strategy for dates, it's an implementation flaw which did hit me before, so the opportunity of pointing it out was tempting and I went for it.

                           

                           

                          • 10. Re: ExecuteSQL help
                            KrisYells

                            Thank you everyone for the help.

                             

                            Yes, MAX will be necessary. I do have the same events that happen multiple times on the same property. In those case I would only need to report on the latest dated event.

                             

                            in reply to Beverly - Yes, this sample is for 3 events. the actual requests can be any number of requests from any number of the events.

                             

                            This is a lot of great information. I do have 1 Question. What is the "c." in SELECT CASE WHEN c.EventDate IS NULL THEN '' ELSE c.EventDate END FROM ChildTable c?

                             

                            thank you again.

                            • 11. Re: ExecuteSQL help
                              okramis

                              Yes, MAX will be necessary. I do have the same events that happen multiple times on the same property. In those case I would only need to report on the latest dated event.

                               

                              in reply to Beverly - Yes, this sample is for 3 events. the actual requests can be any number of requests from any number of the events.

                               

                              This is a lot of great information. I do have 1 Question. What is the "c." in SELECT CASE WHEN c.EventDate IS NULL THEN '' ELSE c.EventDate END FROM ChildTable c?

                              the c here is just a alias for "ChildTable" to shorten the query, same for p as alias for "ParentTable".

                              I did some tests and it seems MAX() and CASE don't play together, but I think the CASE WHEN... is obsolet anyways as if MAX(date) returns NULL, it will be an empty value for the column

                               

                              Otmar

                              • 12. Re: ExecuteSQL help
                                beverly

                                ok, fix your query to include MAX()?

                                beverly

                                • 13. Re: ExecuteSQL help
                                  user19752

                                  It the request is "any number of the events", you need 2 SQLs. 1st make dynamic SELECT list and second execute it.

                                  • 14. Re: ExecuteSQL help
                                    beverly

                                    agreed!

                                    beverly

                                    1 2 Previous Next