Announcement

Collapse
No announcement yet.

Prepare stmt pivot SQL in grid's SQL section

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

  • Prepare stmt pivot SQL in grid's SQL section

    Hello
    I'm, trying to fill a grid with a pivot sql, using prepare stmt, but I can?t figure out how to put the sql statement in the sql section on the grid, can anyone shed some light?

    Code:
    SET @sql = (
        SELECT GROUP_CONCAT(DISTINCT 
            CONCAT(
                "sum(case when `idcatcxp`='", idcatcxp, "' then `monto` end) as `", `idcatcxp`, "`"
            )
        ) 
        FROM cxp
    );
    
    SET @sql = CONCAT("select idvehiculo, ", @sql, " from cxp group by `idvehiculo`");
    
    
    PREPARE stmt FROM @sql;
    
    EXECUTE stmt;
    If I try to save this inside the SQL section of the grid SC throws an error on saving.

    thnx in advance!

  • #2
    I think it can't be done since SC expects to create the fields from the SQL statement and this cannot be done because there's no telling how many columns the SQL will return, any other suggestions?

    Comment


    • #3
      I created this code in a blank app
      Code:
      // Check for record
      $check_sql = '
      SET @sql = (
          SELECT GROUP_CONCAT(DISTINCT
              CONCAT(
                  "sum(case when `idcatcxp`=", idcatcxp, " then `monto` end) as `", `idcatcxp`, "`"
              )
          )
          FROM cxp
      );
      
      SET @sql = CONCAT("select idvehiculo, ", @sql, " from cxp group by `idvehiculo`");
      
      PREPARE stmt FROM @sql;
      
      EXECUTE stmt;
      
      ';
      
      sc_lookup(rs, $check_sql,"conn_LoteAutomotriz");
      
         print_r($rs);
      if (isset({rs[0][0]}))     // Row found
      {
      }
              else     // No row found
      {
                 echo 'nada';
      }
      If I run the sql in mysqlyog or mysql workbench, it returns data perfectly, but when excecuting the blank app it returns nothing.

      Code:
      Array ( ) nada
      Captura.PNG
      Last edited by lachof; 11-29-2019, 11:38 AM.

      Comment


      • #4
        Try creating an sql procedure saving the data in a table, on SQL prepare grid

        Comment

        Working...
        X