Welcome Guest, Not a member yet? Register   Sign In
Error in query
#1

Hi, i am trying to do this query in my model

$this->db->select( "r.cod_colaborador, r.cod_supervisor" );
$this->db->from( "tbl_rhm_colaboradores as r" );
$this->db->where( "r.sit_cadastro", "A" );
$this->db->order_by( "r.cod_supervisor, r.cod_colaborador asc" );
$subQuery1 = $this->db->get_compiled_select();

$this->db->select( "cod_colaborador" );
$this->db->from( "( ". $subQuery1 . " ) as tbl_cad_corretores, ( SELECT @pv := " . $cod_colaborador . " ) initialisation" );
$this->db->group_start();
$this->db->where( "find_in_set( cod_supervisor, @pv ) > 0" );
$this->db->where( "@pv := CONCAT( @pv, ',', cod_colaborador )" );
$this->db->group_end();
$this->db->or_where( "cod_colaborador = @pv" );
$subQuery2 = $this->db->get_compiled_select();

$this->db->from( $this->tabela );
$this->db->select( 'cli.num_cpf, cli.nom_cliente, c1.nom_apelido, c2.nom_apelido as nom_gerente, cli.sit_cadastro, cli.cod_usuario' );
$this->db->join( 'tbl_rhm_colaboradores c1','c1.cod_colaborador = cli.cod_corretor', 'inner' );
$this->db->join( 'tbl_rhm_colaboradores c2','c2.cod_colaborador = c1.cod_supervisor', 'left' );
$this->db->where_in( "cli.cod_corretor", $subQuery2, false );


But have a error 

ERROR - 2019-06-10 14:00:23 --> Query error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`:` = CONCAT( @pv, ',', cod_colaborador)
 )
OR `cod_colaborador` = `@pv`)
ORDER ' at line 12 - Invalid query: SELECT `cli`.`num_cpf`, `cli`.`nom_cliente`, `c1`.`nom_apelido`, `c2`.`nom_apelido` as `nom_gerente`, `cli`.`sit_cadastro`, `cli`.`cod_usuario`
FROM `tbl_cad_clientes` `cli`
INNER JOIN `tbl_rhm_colaboradores` `c1` ON `c1`.`cod_colaborador` = `cli`.`cod_corretor`
LEFT JOIN `tbl_rhm_colaboradores` `c2` ON `c2`.`cod_colaborador` = `c1`.`cod_supervisor`
WHERE cli.cod_corretor IN(SELECT `cod_colaborador`
FROM ( SELECT `r`.`cod_colaborador`, `r`.`cod_supervisor`
FROM `tbl_rhm_colaboradores` as `r`
WHERE `r`.`sit_cadastro` = 'A'
ORDER BY `r`.`cod_supervisor`, `r`.`cod_colaborador` asc ) as tbl_cad_corretores, ( SELECT @pv := 214 ) initialisation
WHERE   (
find_in_set( cod_supervisor, @pv ) >0
AND `@pv` `:` = CONCAT( @pv, ',', cod_colaborador)
 )
OR `cod_colaborador` = `@pv`)
ORDER BY `cli`.`nom_cliente` ASC
 LIMIT 10

The error is `@pv` `:` = 

and 

OR `cod_colaborador` = `@pv`)

The Correct is to

@pv := 
and 

`cod_colaborador` = @pv)

How to resolve this?
Reply


Messages In This Thread
Error in query - by pedrossjr - 06-10-2019, 10:17 AM
RE: Error in query - by php_rocs - 06-10-2019, 11:53 AM



Theme © iAndrew 2016 - Forum software by © MyBB