1 2 Previous Next 23 Replies Latest reply on Nov 23, 2015 10:09 AM by okramis

    ExcecuteSQL when result is NULL?

    weedonpaul

      To Start off I am new to the world of ExecuteSQL. I have had a lot of help from this community to the point where I have created a script that works.

       

      I did raise this question at the end of another thread but it was getting 3 pages long and I figured It's a different question than the original.

       

      The script below is for one of the Y values series (4 in all) where I want a graph to show the breakdown of coverage for a given month. The problem is that when it gets a null result. The script below, when run over the last 3 months will only return 2 results as one month had no "feature" coverage. I would like it to put a zero in when the result is NULL

       

      Some thing along the lines of CASE WHERE \"_kf_clients_id\"= ? AND publication_date >= ? AND publication_date <= ? AND type = ? IS NULL THEN "0"

       

      Obviously as I understand nothing about this and so putting this in didn't work, Can anyone Help?

       

      Currently the result is

      2

      4

       

      But I would Like

      2

      0

      4

       

      ExecuteSQL (

       

      "SELECT COUNT(publication_month_number)

      FROM coverage_For_Report

      WHERE \"_kf_clients_id\"= ? AND publication_date >= ? AND publication_date <= ? AND type = ?

      GROUP BY publication_month_number "

      ;"";"";Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date; "Feature")

        • 1. Re: ExcecuteSQL when result is NULL?
          beverly

          you are asking to FIND data which does not exist. If you perform a native find, you won't find any either!

           

          I believe the suggestion was made elsewhere to create a table with the "month_number" and JOIN (LEFT OUTER JOIN) it with your query in a way that you get "something" for every month, even if NULL. However, you may need to include the "month_number" in your results and then perhaps "parse" to get the data you need.

           

          1|2

          2|

          3|4

           

          If you use the "|" delimiter between "fields/columns", you can see that "month_number" (2) is NULL because you didn't have anything that month, just make it "0". something like

               Substitute($results;"|"&Char(13);"|0"&Char(13))

           

          Then you can extract every value after the "|".

           

          beverly

          • 2. Re: ExcecuteSQL when result is NULL?
            okramis

            ExecuteSQL (

             

            "SELECT COUNT(publication_month_number)

            FROM coverage_For_Report

            WHERE \"_kf_clients_id\"= ? AND publication_date >= ? AND publication_date <= ? AND type = ?

            GROUP BY publication_month_number "

            ;"";"";Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date; "Feature")

            As written in the other thread:

             

            Make a helper table lets say "Months" with a number field "MonthNr" and a text field "MonthNm". Generate it 12 records and fill them: "MonthNr" from 1 to 12, "MonthNm" January to December, then do the following query (you might even omit the a.MonthNm column):

             

            Let ( [

            _start = Coverage_Report::start_date

            ; _end = Coverage_Report::end_date

            ; _monthlist = ListOfMonths ( Month ( _start ) ; Month ( _end ) )

            ] ;

            ExecuteSQL ( "


            SELECT

            a.MonthNm,

            (SELECT COUNT(*)

            FROM coverage_For_Report b

            WHERE MONTH(b.publication_date)=a.MonthNr

            AND \"_kf_clients_id\"= ?

            AND b.publication_date >= ?

            AND b.publication_date <= ?

            AND type = ?) AS nDates

            FROM Months a

            WHERE a.MonthNm IN(" & _monthlist & ")

             

            " ; "" ; "" ; Coverage_Report::_kf_clients_id ; _start ; _end ; "Feature")

             

            ) //end let

             

            //Limitation: date range can't exceed one year span

             

            ===========================

            cf ListOfMonths ( _startmonth ; _endmonth )

            ===========================

            Let ( [

            _month = Mod ( _startmonth ; 12 )

            ; _month = If ( _month = 0 ; 12 ; _month )

            ; _endmonth = If ( _endmonth < _startmonth ; _endmonth + 12 ; _endmonth )

            ; _nextmonth = _startmonth + 1

            ] ;

            _month & If ( _nextmonth  ≤  _endmonth ;

            ", " & ListOfMonths ( _nextmonth ; _endmonth )

            ; "" )

            )

            • 3. Re: ExcecuteSQL when result is NULL?
              weedonpaul

              Okramis

               

              The trouble is I need to be more dynamic than that as I may need to do 24 months or longer. also december can come before january or after january depending on my period.

               

              I did have a nice little solution that worked for 3 month intervals but the wife wants to be able to run reports for some of the smaller clients over longer periods.

               

              Thank you

              Paul

              • 4. Re: ExcecuteSQL when result is NULL?
                weedonpaul

                Beverly

                 

                I think that might work I am now trawling through recourses you sent me to work out exactly what to type in.

                 

                Thanks

                Paul

                • 5. Re: ExcecuteSQL when result is NULL?
                  beverly

                  You may need a field that I OFTEN create when dealing with dates (especially with charting). I have an "auto-enter" field (as TEXT) called 'yr_mon' and it populates with YYYY_MM (2015_01, for example). It's "sortable" (alphabetically), it's able to be searched, it's able to be easy converted to 'human dates' (JAN 2015, for example).

                   

                  You would still be able to use a LEFT OUTER JOIN and the sort (ORDER BY) would allow several years and months.

                   

                  beverly

                  • 6. Re: ExcecuteSQL when result is NULL?
                    weedonpaul

                    where would I put LEFT OUTER JOIN into this code. I have another field called "coverage_report::x_number" which stores year month so september would be 201509. This is generated by the executeSQL function on the x axix so for the last 3 months it would have

                    201509

                    201510

                    201511

                     

                    ExecuteSQL (

                     

                    "SELECT COUNT(publication_month_number)

                    FROM coverage_For_Report

                    WHERE \"_kf_clients_id\"= ? AND publication_date >= ? AND publication_date <= ? AND type = ?

                    GROUP BY publication_month_number "

                    ;"";"";Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date; "Feature")

                    • 7. Re: ExcecuteSQL when result is NULL?
                      weedonpaul

                      I've got this but returns a ?

                       

                      ExecuteSQL (

                       

                      "SELECT COUNT(publication_month_number)

                      FROM coverage_For_Report

                      LEFT OUTER JOIN x_number ON

                      WHERE \"_kf_clients_id\"= ? AND publication_date >= ? AND publication_date <= ? AND type = ?

                       

                      GROUP BY publication_month_number "

                       

                      ;"";"";Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date; "Feature")

                      • 8. Re: ExcecuteSQL when result is NULL?
                        user19752

                        You need to put joining field after ON keyword.

                         

                        Another way is, get the result in columns.

                        ExecuteSQL("

                        SELECT

                        SUM(CASE WHEN publication_month_number = 201509 THEN 1 ELSE 0 END),

                        SUM(CASE WHEN publication_month_number = 201510 THEN 1 ELSE 0 END),

                        SUM(CASE WHEN publication_month_number = 201511 THEN 1 ELSE 0 END)

                        FROM coverage_For_Report

                        WHERE \"_kf_clients_id\"= ? AND publication_date >= ? AND publication_date <= ? AND type = ?

                        ";Char(13);"";Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date; "Feature")


                        You need to make column list from start/end data using some custom function for real (dynamic) query.

                        • 9. Re: ExcecuteSQL when result is NULL?
                          okramis

                          Hi Paul

                           

                          Here's a sample file with extended helper table. There are now 4 years defined, for more just create a month set with idxYear "5" and so on. It needs no calculated fields in the data table, only the helper table and two custom functions.

                           

                          best regards

                          Otmar

                           

                          P.S. no pass for admin and sorry didn't take out the open script trigger

                          • 10. Re: ExcecuteSQL when result is NULL?
                            weedonpaul

                            This does it exactly your a genius, but I have no idea how it works. I can see that you have the executeSQL function there but just as text in a field. I can't see where the executeSQL script is called, but if I take it out of the field it doesn't work.

                             

                            I'm going to try copying the scripts and fields and your months table over to my database and see if it works

                             

                            I would love to know how it works

                            • 11. Re: ExcecuteSQL when result is NULL?
                              okramis

                              Have a look at the Script "Set Result". In the 2nd script step it just takes the text from Reports::query and replaces <yearmonthlist> with the in the let statement set local variable _yearmonthlist and uses it as the query in ExecuteSQL.

                              The global variable $$YearList sets the unstored calculated field "MonthYear" - GetValue ( $$YearList ; IdxYear ) * 100 + MonthNr - in the Months table.

                              • 13. Re: ExcecuteSQL when result is NULL?
                                weedonpaul

                                is there any reason why I couldn't use the calculation in a calculation field of graph calculation?

                                 

                                I can then do a similar calculation to compare coverage type's "features""news""mention""social"?

                                 

                                I am currently going through the script and adding my field names in, I keep missing bits so Months::MonthYear isn't population at the moment.

                                 

                                this is amazingly clever

                                • 14. Re: ExcecuteSQL when result is NULL?
                                  weedonpaul

                                  This is it now that I've changed it. I have copied across the custom functions and the months table, but it's not working like your sample file. have I missed anything? the table months is as it was I have imported the data from your file. My equivalent to your events is called "Coverage_For_Reports"

                                   

                                  /*Let ( [

                                  _start = Coverage_Report::g_start_date //Date ( 7 ; 1 ; 2014 )

                                  ; _end = Coverage_Report::g_end_date //Date ( 12 ; 31 ; 2016 )

                                  ; _id = Coverage_Report::g_kf_clients_id //1040

                                  ; _yearmonthlist = ListOfYearMonths ( _start ; _end )

                                  ; $$YearList = ListOfYears ( _start ; _end )

                                  ; $$yearmonthlist = _yearmonthlist

                                  ] ;

                                   

                                   

                                  ExecuteSQL ( Substitute ( Reports::query ; "<yearmonthlist>" ; _yearmonthlist )

                                   

                                   

                                  /*"SELECT

                                   

                                   

                                  (SELECT COUNT(*)

                                  FROM Coverage_For_Report b

                                  WHERE (YEAR(b.publication_date)*100+Month(b.publication_date))=a.MonthYear

                                  AND b.g_kf_clients_id= ?

                                  AND b.publication_date >= ?

                                  AND b.publication_date <= ?

                                  AND b.type = ?) AS nDates

                                   

                                   

                                  FROM Months a

                                  WHERE a.MonthYear IN(" & _yearmonthlist & ")

                                   

                                   

                                  ORDER BY a.MonthYear

                                   

                                  "*/ ; "" ; "" ; _id ; _start ; _end ; "Features"

                                   

                                   

                                  ) //end esql

                                   

                                  ) //end let*/

                                  1 2 Previous Next