7 Replies Latest reply on Jul 30, 2017 8:17 AM by beverly

    executeSQL

    haider2016

      Hi there,

       

      I am stuck with this problem, I applied an executesql to get all iD number of clients using the following:

       

      set variable [$allID; value: ExecuteSQL(" SELECT empID from employees";"";"" )]

       

       

      I have got a list of all IDs, then I tried to deal with these IDs individually by applying the following instruction:

       

      set field [ $current; GetAsNumber ( GetColumn ($allID;1;char(13) )) ]

       

      the problem is the variable $current got all IDs !

       

      how do I get each ID individually from the resulted list $allID?

       

      any help would appreciated

       

      Regards

        • 1. Re: executeSQL
          beverly

          If your query uses the default ("") for rowSeparator, then you have a list of values (return delimited). As such you can use:

          • GetValue($allID ; 2)

          for example, to grab the 2nd value

          Beverly

          Sent from miPhone

          • 2. Re: executeSQL
            haider2016

            Dear Beverly,

            Thanks a lot

             

            • 3. Re: executeSQL
              taylorsharpe

              beverly wrote:

               

              If your query uses the default ("") for rowSeparator, the you have a list of values (return delimited). As such you can use:

              • GetValue($allID ; 2)

              for example, to grab the 2nd value

              Beverly

              Sent from miPhone

               

              Hmmm... most intersting.  I did not know there were default values for Record and Field Separators!  I guess I just learned a tidbit!  Thanks. 

              • 4. Re: executeSQL
                beverly

                Yes, Taylor! What you put in the separators can be very cool. The defaults are just the standard for CSV (comma between columns and carriage return between rows).

                But you can get really creative by changing these values.

                • Char(9) - the tab - can be used between columns and then format the result field with tab stops to "columnize" the columns.

                • Char(13) - the carriage return - can be used between columns, too, to make "address labels".

                • I've seen some creative use of HTML table tags in the separators to make the result ready to be HTML viewable in Web Viewer as a table!

                • the pipe ("|") - char(124) - as the fieldSeparator is used in the methods to make ExecuteSQL and Virtual list/table to parse more easily.

                • use your imagination...

                 

                Beverly

                • 5. Re: executeSQL
                  taylorsharpe

                  Cool to know the defaults.  In reality is mostly use Char ( 9 ) and ¶, but there are reasons to do other things too.  Just when I think I really know a function, there is a tidbit about it I didn't. 

                  • 6. Re: executeSQL
                    danielfarnan

                    Even better, you're not limited to a single character as your separator. I often use "','" (<single-quote><comma><single-quote>) for those times when I want to get a list of values to place in an "IN" clause for a subsequent SQL query. Set the _inClause to "'" & ExecuteSQL ( _qry ; "" ; "','" ; [parameters] ) & "'" and then in the later query, "SELECT blah FROM table WHERE theField IN (" & _inClause & ")"

                    • 7. Re: executeSQL
                      beverly

                      Mais oui!

                      Just a reminder: numbers don't get quoted. But you would not have a series of mixed numbers and text. So just make the delimiter appropriate for your series.

                       

                      I also don't use just the default comma when needed. Most often I want a comma & space between columns. Also a handy way to "gather" related values into one field in the parent if this pair of characters is used between the rows/records.

                       

                      "Gathering" a group of email addresses for a BCC? You might have semi-colon (;) as a delimiter. Check your email client, as for some the comma (or even a return) is valid.

                       

                      Getting creative here!

                      Beverly

                      Sent from miPhone