Announcement

Collapse
No announcement yet.

executing a prepared mysql

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • executing a prepared mysql

    Seen below is my mysql query which creates the output I want. My goal is to get the output into a grid created in Scriptcase.
    I can't figure out where to write my @sql variable, or where to execute it. I've tried to place the code to create it in the onapplicationinit event, but SC doesn't like it.



    SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
    "sum(case when wohdate2 = '", wohdate2,"' then contract_price else 0 end) as `", wohdate2, "`"
    )
    )
    into @sql
    from wohdata;

    SET @sql = CONCAT('SELECT woh_job_desc, ', @sql, ' FROM wohdata GROUP BY woh_job_desc');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

  • #2
    You must use a macro sc_lookup Example:

    sc_lookup(dataset, "select customer_id, customer_name, credit_limit from customers" );

    To have access to the first line (Dataset), use :
    {customer_id} = {dataset[0][0]};
    {customer_name} = {dataset[0][1]};
    {credit_limit } = {dataset[0][2]};

    To have access to the second line (Dataset), use:
    {customer_id} = {dataset[1][0]};
    {customer_name} = {dataset[1][1]};
    {credit_limit} = {dataset[1][2]};

    Comment

    Working...
    X