AnsweredAssumed Answered

Formatting SQL Output

Question asked by laguna92651 on May 11, 2017
Latest reply on May 11, 2017 by beverly

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 ; "\", " ; "},¶" ;

       PREFERENCES::g_date_sql_start;

       PREFERENCES::g_date_sql_end

        )

  ];

  

    If ( $sqlResult = "?" ;

       True;

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

       )

  )

Outcomes