6 Replies Latest reply on May 11, 2017 6:54 PM by beverly

    Formatting SQL Output


      Making a stab at using SQL, I am trying to output the SQL result in the following javascript format, the output will be used in a javascript program to generate a chart. The SQL script works and will output the, key: "Jones", portion of the format.

      I can't concatenate the, value literal, to the number, SUM(pa.~extPrice) As amt, to get, value: 132.  I have not included, pa.~pid_Contractor, in the data set of the java script program yet.


          {key: "Mike Jones",               value: 132},

          {key: "Mary Smith",               value: 71},

          {key: "Heather McDonald",   value: 337},

          {key: "John Welch",              value: 93},

          {key: "Mark Markus",            value: 21}



      This is the SQL script:

      Let ( [ ~sql = "

                 SELECT '{\"' || pa.~pid_Contractor || '\", key: \"' || pa.~nameFull || ',value: ' || SUM(pa.~extPrice) As amt

                 FROM ~@Participants pa

                 WHERE pa.~periodStart  >= ?

                 AND pa.~periodStart <= ?

                 GROUP BY pa.~pid_Contractor, pa.~nameFull

                 ORDER BY amt DESC  ";


      $sqlQuery = Substitute ( ~sql ;

              [ "~@Participants" ; SQLTableName ( LineItems::ID_LineItem ) ];

              [ "~nameFull" ; SQLFieldName ( LineItems::Contractor Name ) ];

              [ "~extPrice" ; SQLFieldName ( LineItems::ExtendedPrice ) ];

              [ "~pid_Contractor" ; SQLFieldName ( LineItems::id_product ) ];

              [ "~periodStart" ; SQLFieldName ( LineItems::Period Start ) ]



          $sqlResult = ExecuteSQL ( $sqlQuery ; "\", " ; "},¶" ;






          If ( $sqlResult = "?" ;


              Let ( $$D3.DATA.SALES3 = "[" & $sqlResult & "}" & "]" ; False )



        • 1. Re: Formatting SQL Output

          Should there be concatenation between full name  and 'value'? Is the comma in the right spot?


          i would start with the simplest query of concatenation value to the sum only first. Have you considered concatenation to the end of nameFull instead?

          • 2. Re: Formatting SQL Output

            I might just chuck the whole concatenation with "||" and make a space your column delimiter as JSON won't care about the whitespace.


            SELECT '{\"',


               '\", key: \"',


               ',value: ',

               SUM(pa.\"~extPrice\") As amt


            Then your

            ExecuteSQL ( $sqlQuery ; Char(32) ; ... )


            I wasn't entirely sure what matched the query with your result, as they didn't seem to be the same columns.

            Also the field name with the tilde may be choking the sql, so double-quote the name of the field



            • 3. Re: Formatting SQL Output

              "Also the field name with the tilde may be choking the sql, so double-quote the name of the field"


              Better look again, there are no field names with tilde characters. The substitute function replaces that text with actual field names and may even quote them at the same time if the CF used is defined to do so.

              • 4. Re: Formatting SQL Output

                I was able to get the proper formatted output by concatenating to the end of nameFull, thanks Bigtom.

                    SELECT '{\"' || pa.~pid_Contractor || '\", key: \"' || pa.~nameFull||'\"' || ', value: ', SUM(pa.~extPrice) As amt

                Out of curiously, any thoughts on why I couldn't concatenate to the front of SUM(pa.~extPrice) As amt


                Also used your suggestion Beverly, of using a space as the column delimiter.

                   $sqlResult = ExecuteSQL ( $sqlQuery ; Char(32) ; "},¶" ;


                It was very easy to get lost with all of the symbols. Now to get the data set into the java script.

                Thanks Bigtom and Beverly for the help.

                • 5. Re: Formatting SQL Output

                  Happy to help.

                  • 6. Re: Formatting SQL Output

                    aggregates used in ExecuteSQL() seem to have their own rules and many of us are discovering them slowly. I try to avoid using them except as you did (a separate column).