Automic Workload Automation

  • Private Community

  • 1.  How to store the column values of a SQL variable to a script array

    Posted Jan 05, 2018 12:06 PM
    Team,

    We are on V12. How can we store list of column values that we get from a SQL variable into a script array using :FILL ? 
    Using GET_VAR() we can only map key to value but I want all the RESULT column values of a dynamic variable gets stored in an array.

    Any script function that I am missing out,Please help me out in this.

    Thanks,
    Srujan.


  • 2.  How to store the column values of a SQL variable to a script array

    Posted Jan 05, 2018 01:55 PM
    Hi Team,

    the :fill script command is intendet to be used when you have a string containing values with a seperator
    In your case its rather useless.

    If you really want to create an ARRAY you can adress it in a loop with a counter.

    heres a simple example, my SQLI VARA queries the Activities window....

    :DEFINE &ARRAY#, STRING, 50
    :SET &COUNTER# = 1
    !fill array with data from SQL VARA
    :SET &HND#=PREP_PROCESS_VAR(VARA.SQLI.GET_EH.CONTENT)
    :PROCESS &HND#
    :  SET &RESULT_VALUE# = GET_PROCESS_LINE(&HND#, 1)
    :  SET &COUNTER# = FORMAT(&COUNTER#)
    :  SET &ARRAY#[&COUNTER#] = &RESULT_VALUE#
    :  PRINT "Val: &RESULT_VALUE# -- CNT: &COUNTER#"
    :  SET &COUNTER# = ADD(&COUNTER#, 1)
    :ENDPROCESS
    :CLOSE_PROCESS &HND#
    !
    :PRINT "=================================="
    !
    :SET &COUNTER# = 1
    :SET &COUNTER# = FORMAT(&COUNTER#)
    :SET &MAXVALUE# = LENGTH(&ARRAY#[], SIZE)
    :SET &MAXVALUE# = FORMAT(&MAXVALUE#)
    :PRINT "ARR Size: &MAXVALUE# "
    !
    !Loop for printing ARRAY content to report
    :WHILE &COUNTER# <= &MAXVALUE#
    :  SET &COUNTER# = FORMAT(&COUNTER#)
    :  PRINT "&COUNTER# -- &ARRAY#[&COUNTER#]"
    :  SET &COUNTER# = ADD(&COUNTER#, 1)
    :ENDWHILE

    Your Team


  • 3.  How to store the column values of a SQL variable to a script array

    Posted Jan 07, 2018 07:54 AM
    Thanks FrankMuffke
     
    It worked well.

    -Srujan


  • 4.  How to store the column values of a SQL variable to a script array

    Posted Jan 07, 2018 08:22 AM
    You 're welcome :-)


  • 5.  How to store the column values of a SQL variable to a script array

    Posted Jan 08, 2018 04:18 AM

    Srujan_Pathuri_9871: You can use :FILL and GET_VAR together to insert into a script array multiple values from the same row of a VARA.

    For example, let’s say you have the following static VARA:

    UC0.VARA1

    KeyValue 1Value 2Value 3Value 4Value 5
    Vara1Key1Vara1Key1Value1Vara1Key1Value2Vara1Key1Value3Vara1Key1Value4Vara1Key1Value5
    Vara1Key2Vara1Key2Value1Vara1Key2Value2Vara1Key2Value3Vara1Key2Value4Vara1Key2Value5
    Vara1Key3Vara1Key3Value1Vara1Key3Value2Vara1Key3Value3Vara1Key3Value4Vara1Key2Value5
    Vara1Key4Vara1Key4Value1Vara1Key4Value2Vara1Key4Value3Vara1Key4Value4Vara1Key4Value5
    Vara1Key5Vara1Key5Value1Vara1Key5Value2Vara1Key5Value3Vara1Key5Value4Vara1Key5Value5


    You can then define a script like this:

    UC0.SCRI1

    :SET &VARA# = UC0.VARA1
    :SET &KEY# = Vara1Key1
    :DEFINE &ARRAY#, string, 5
    :FILL &ARRAY#[] = GET_VAR(&VARA#, &KEY#)
    :PRINT "VARA &VARA#, key &KEY#, value 1: &ARRAY#[1]"
    :PRINT "VARA &VARA#, key &KEY#, value 2: &ARRAY#[2]"
    :PRINT "VARA &VARA#, key &KEY#, value 3: &ARRAY#[3]"
    :PRINT "VARA &VARA#, key &KEY#, value 4: &ARRAY#[4]"
    :PRINT "VARA &VARA#, key &KEY#, value 5: &ARRAY#[5]"

    Run the script, and you’ll get the following output:

    U00020408 VARA UC0.VARA1, key Vara1Key1, value 1: Vara1Key1Value1
    U00020408 VARA     UC0.VARA1, key Vara1Key1, value 2: Vara1Key1Value2
    U00020408 VARA     UC0.VARA1, key Vara1Key1, value 3: Vara1Key1Value3
    U00020408 VARA     UC0.VARA1, key Vara1Key1, value 4: Vara1Key1Value4
    U00020408 VARA     UC0.VARA1, key Vara1Key1, value 5: Vara1Key1Value5

    There is currently no way to use :FILL and GET_VAR together to insert multiple rows from a VARA into a script array. However, I opened an enhancement request for this. If you like the idea, please vote for it:

    Fill array from VARA row or column using :FILL and GET_VAR



  • 6.  How to store the column values of a SQL variable to a script array

    Posted Jan 08, 2018 05:19 AM
    Michael_Lowry

    Exactly, even I felt the same. It could have been better if we could fill multiple rows in the array just like how easily we do with single row values. I feel glad that you opened an enhancement request regarding this and hope that they would come up with the solution, may be in the next release.

    -Srujan