Automic Workload Automation

Expand all | Collapse all

How to handle SQL-results with multiline cells

  • 1.  How to handle SQL-results with multiline cells

    Posted 02-11-2019 07:06 AM

    Hello,

     

    i have a table which stores additional information to files which should be send via email. One column contains the e-mail-body which could contain multiline data.

     

    Now i want to get this information in a SQL-job and give them to an other job. All of the columns have to be forwarded to the next jobs.

     

    I tried to set up a SQL-Job suggested here: https://community.automic.com/discussion/10372/convert-an-sql-vara-to-an-exec-vara-sql-job.

     

    The table has 8 columns and there is one dataset in it. The resultset should be 8 columns and one row. But it is 3 columns and 7 rows, because of the line breaks in one column (e-mail-body). The number of rows depends on the amount of lines in the e-mail-body column. E-mail-body is the third column in the SQL-statement.

     

    I need any suggestion how to get the mulitline column in a variable.

     

    We are using AE 12.1.

     

    When you need further information, please let me know.

     

     

    Yours,

     

     

    Erich Lerch



  • 2.  Re: How to handle SQL-results with multiline cells

    Posted 02-11-2019 12:44 PM

    Why are you doing this in AE?  Why isn't this being built in a more robust application language, and then AE can schedule and run that application solution? 

     

    To return your multiline column into variables, you could put your SQL inside of a VAR/SEC_SQL object and retrieve it via script with the PREP_PROCESS_VAR() function such that you can loop through each line.