Welcome Guest, Not a member yet? Register   Sign In
Problem with query on sqlsrv
#1

We have a problem result of this query.
On this query we have 4 column on this have 3 don't have column name. (This is a problem), When we put the column name this return everything correct but when we don't column name don't return nothing.

On codeigniter have any workaround to put dynamically the name on columns? We collect this query to other system and is impossible make change on that.

$res = $this->DB2->query("SELECT cast(VENDAS_ANO_ATUAL as money),cast(VENDAS_ANO_ANTERIOR as money), cast(cast(CASE WHEN VENDAS_ANO_ANTERIOR = 0 THEN 0 ELSE ((VENDAS_ANO_ATUAL - VENDAS_ANO_ANTERIOR) / VENDAS_ANO_ANTERIOR) * 100 END as int) as varchar) + '%' AS PERCENTAGEM, case when VENDAS_ANO_ANTERIOR < VENDAS_ANO_ATUAL then '' else case when VENDAS_ANO_ANTERIOR> VENDAS_ANO_ATUAL then '' else '' end end FROM ( SELECT SUM(CASE WHEN Ano = YEAR(GETDATE()) THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ATUAL, SUM(CASE WHEN Ano = YEAR(GETDATE())-1 THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ANTERIOR FROM AcumuladosVendas (NOLOCK) JOIN DocumentosVenda ON DocumentosVenda.Documento=AcumuladosVendas.TipoDoc WHERE AcumuladosVendas.VENDEDOR IN (1) AND DocumentosVenda.TipoDocumento = 4 ) AS DATA")->result();

Thanks
Reply
#2

@renatoribeiro,

You should be able to write your query in such a way that if there is no value it still has a column name. Which columns are you having issues with? If you are using MySQL all you have to do is ... https://www.w3schools.com/sql/sql_alias.asp
Reply
#3

(09-13-2018, 07:32 AM)php_rocs Wrote: @renatoribeiro,

You should be able to write your query in such a way that if there is no value it still has a column name.  Which columns are you having issues with?  If you are using MySQL all you have to do is ... https://www.w3schools.com/sql/sql_alias.asp

Hi,
We recived this query by other software, only get the same and use. We don't have control of that.
We know that, but is impossible for me manage this.
Reply
#4

So ... you can see the code but can't change it?
Your question was answered on our slack channel, but it sounds like the solution isn't an option for you.

It sounds like you are looking for a way to change code that you have no control over, which sounds like an impossibility Sad
Reply
#5

It's quite strange situation... maybe sql query is generated from some third-party service (via some sort of api) and must be executed dynamically?
I'm not sure...


Based on the sql query from the first post I manage to write a very simple (and ugly) helper for query modification. It won't cover all the possibilities but hey... it's a start.

PHP Code:
function prepare_columns($str)
{
    if (preg_match('/SELECT (.*?) FROM/'$str$match) !== 1)
    {
        return $str;
    }

    $columns array_map('trim'explode(','$match[1]));

    foreach ($columns as $key => &$col)
    {
        if (substr($col, -1) === ')')
        {
            $col .= ' AS column_' . ++$key;
        }
        else
        
{
            $words explode(' '$col);
            if (strtolower($words[count($words) - 2]) !== 'as')
            {
                $col .= ' AS column_' . ++$key;
            }
        }
    }

    return str_replace($match[1], implode(','$columns), $str);


Usage:
PHP Code:
$sql "SELECT cast(VENDAS_ANO_ATUAL as money),cast(VENDAS_ANO_ANTERIOR as money), cast(cast(CASE WHEN VENDAS_ANO_ANTERIOR = 0 THEN 0 ELSE ((VENDAS_ANO_ATUAL - VENDAS_ANO_ANTERIOR) / VENDAS_ANO_ANTERIOR) * 100 END as int) as varchar) + '%' AS PERCENTAGEM, case when VENDAS_ANO_ANTERIOR < VENDAS_ANO_ATUAL then '' else case when VENDAS_ANO_ANTERIOR> VENDAS_ANO_ATUAL then '' else '' end end FROM ( SELECT SUM(CASE WHEN Ano = YEAR(GETDATE()) THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ATUAL, SUM(CASE WHEN Ano = YEAR(GETDATE())-1 THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ANTERIOR FROM AcumuladosVendas (NOLOCK) JOIN DocumentosVenda ON DocumentosVenda.Documento=AcumuladosVendas.TipoDoc WHERE AcumuladosVendas.VENDEDOR IN (1) AND DocumentosVenda.TipoDocumento = 4 ) AS DATA";

echo 
prepare_columns($sql); 

This will produce:
Code:
SELECT cast(VENDAS_ANO_ATUAL as money) AS column_1,cast(VENDAS_ANO_ANTERIOR as money) AS column_2,cast(cast(CASE WHEN VENDAS_ANO_ANTERIOR = 0 THEN 0 ELSE ((VENDAS_ANO_ATUAL - VENDAS_ANO_ANTERIOR) / VENDAS_ANO_ANTERIOR) * 100 END as int) as varchar) + '%' AS PERCENTAGEM,case when VENDAS_ANO_ANTERIOR < VENDAS_ANO_ATUAL then '' else case when VENDAS_ANO_ANTERIOR> VENDAS_ANO_ATUAL then '' else '' end end AS column_4 FROM ( SELECT SUM(CASE WHEN Ano = YEAR(GETDATE()) THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ATUAL, SUM(CASE WHEN Ano = YEAR(GETDATE())-1 THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ANTERIOR FROM AcumuladosVendas (NOLOCK) JOIN DocumentosVenda ON DocumentosVenda.Documento=AcumuladosVendas.TipoDoc WHERE AcumuladosVendas.VENDEDOR IN (1) AND DocumentosVenda.TipoDocumento = 4 ) AS DATA
Reply
#6

@renatoribeiro,

This seems like a very odd situation. Is it possible to see more details...
Is it possible to see the actual controller that is calling the model? It wouldn't hurt to see the actual model as well. What version of CI are you using?
Reply
#7

(09-13-2018, 10:22 AM)michalsn Wrote: It's quite strange situation... maybe sql query is generated from some third-party service (via some sort of api) and must be executed dynamically?
I'm not sure...


Based on the sql query from the first post I manage to write a very simple (and ugly) helper for query modification. It won't cover all the possibilities but hey... it's a start.

PHP Code:
function prepare_columns($str)
{
    if (preg_match('/SELECT (.*?) FROM/'$str$match) !== 1)
    {
        return $str;
    }

    $columns array_map('trim'explode(','$match[1]));

    foreach ($columns as $key => &$col)
    {
        if (substr($col, -1) === ')')
        {
            $col .= ' AS column_' . ++$key;
        }
        else
        
{
            $words explode(' '$col);
            if (strtolower($words[count($words) - 2]) !== 'as')
            {
                $col .= ' AS column_' . ++$key;
            }
        }
    }

    return str_replace($match[1], implode(','$columns), $str);


Usage:
PHP Code:
$sql "SELECT cast(VENDAS_ANO_ATUAL as money),cast(VENDAS_ANO_ANTERIOR as money), cast(cast(CASE WHEN VENDAS_ANO_ANTERIOR = 0 THEN 0 ELSE ((VENDAS_ANO_ATUAL - VENDAS_ANO_ANTERIOR) / VENDAS_ANO_ANTERIOR) * 100 END as int) as varchar) + '%' AS PERCENTAGEM, case when VENDAS_ANO_ANTERIOR < VENDAS_ANO_ATUAL then '' else case when VENDAS_ANO_ANTERIOR> VENDAS_ANO_ATUAL then '' else '' end end FROM ( SELECT SUM(CASE WHEN Ano = YEAR(GETDATE()) THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ATUAL, SUM(CASE WHEN Ano = YEAR(GETDATE())-1 THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ANTERIOR FROM AcumuladosVendas (NOLOCK) JOIN DocumentosVenda ON DocumentosVenda.Documento=AcumuladosVendas.TipoDoc WHERE AcumuladosVendas.VENDEDOR IN (1) AND DocumentosVenda.TipoDocumento = 4 ) AS DATA";

echo 
prepare_columns($sql); 

This will produce:
Code:
SELECT cast(VENDAS_ANO_ATUAL as money) AS column_1,cast(VENDAS_ANO_ANTERIOR as money) AS column_2,cast(cast(CASE WHEN VENDAS_ANO_ANTERIOR = 0 THEN 0 ELSE ((VENDAS_ANO_ATUAL - VENDAS_ANO_ANTERIOR) / VENDAS_ANO_ANTERIOR) * 100 END as int) as varchar) + '%' AS PERCENTAGEM,case when VENDAS_ANO_ANTERIOR < VENDAS_ANO_ATUAL then '' else case when VENDAS_ANO_ANTERIOR> VENDAS_ANO_ATUAL then '' else '' end end AS column_4 FROM ( SELECT SUM(CASE WHEN Ano = YEAR(GETDATE()) THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ATUAL, SUM(CASE WHEN Ano = YEAR(GETDATE())-1 THEN TotalLiqMBase ELSE 0 END) AS VENDAS_ANO_ANTERIOR FROM AcumuladosVendas (NOLOCK) JOIN DocumentosVenda ON DocumentosVenda.Documento=AcumuladosVendas.TipoDoc WHERE AcumuladosVendas.VENDEDOR IN (1) AND DocumentosVenda.TipoDocumento = 4 ) AS DATA

Hi,
the regular expression don't work we don't know the reason. But we seeand everything is correct is possible help me?
Reply
#8

Sure, I will try but "regular expression don't work" tells me nothing.

Please provide your input and output. Is any error is returned? What is your PHP version?
Reply




Theme © iAndrew 2016 - Forum software by © MyBB