remove the double quotes of a subquery

¿how do I remove the double quotes of a subquery in postgresql?

i have the example next:

Error Number:

ERROR: syntax error at or near "AS" LINE 7: ...d"."udi_id" DESC, "m"."mat_anio_academico"" DESC)" AS "mudi" ^

SELECT m.mat_anio_academico, mu.udi_id, u.udi_nombre, u.sem_codigo, u.udi_letra_nomina, mu.mud_nota_final, mu.mud_nota_recuperacion, m.mma_id FROM (SELECT DISTINCT ON (mud.udi_id) mud.mud_id FROM "matricula_unidad_didactica" AS "mud" JOIN "matricula" AS "m" ON "mud"."mat_id"="m"."mat_id" JOIN "alumno" AS "a" ON "m"."alu_id"="a"."alu_id" WHERE "a"."alu_id" = 415 ORDER BY "mud"."udi_id" DESC, "m"."mat_anio_academico"" DESC)" AS "mudi" JOIN "matricula_unidad_didactica" AS "mu" ON "mudi"."mud_id"="mu"."mud_id" JOIN "unidad_didactica" AS "u" ON "mu"."udi_id"="u"."udi_id" JOIN "matricula" AS "m" ON "mu"."mat_id"="m"."mat_id" JOIN "alumno" AS "a" ON "m"."alu_id"="a"."alu_id" WHERE "a"."alu_id" = 415 ORDER BY "u"."sem_codigo" ASC, "u"."udi_letra_nomina" ASC, "m"."mat_anio_academico" ASC

this code has the model:

$this->db->select('DISTINCT ON (mud.udi_id) mud.mud_id',FALSE);
$this->db->from('matricula_unidad_didactica AS mud');
$this->db->join('matricula AS m', 'mud.mat_id=m.mat_id');
$this->db->join('alumno AS a', 'm.alu_id=a.alu_id');
$this->db->where('a.alu_id',415);
$this->db->order_by('mud.udi_id, m.mat_anio_academico', 'DESC');

$subquery =  $this->db->get_compiled_select();

$this->db->select('m.mat_anio_academico,mu.udi_id,u.udi_nombre,u.sem_codigo,u.udi_letra_nomina,mu.mud_nota_final,mu.mud_nota_recuperacion,m.mma_id',FALSE);

$this->db->join('matricula_unidad_didactica AS mu','mudi.mud_id=mu.mud_id');
$this->db->join('unidad_didactica AS u','mu.udi_id=u.udi_id');
$this->db->join('matricula AS m','mu.mat_id=m.mat_id');
$this->db->join('alumno AS a','m.alu_id=a.alu_id');
        
$this->db->where('a.alu_id',415);
        
$this->db->order_by('u.sem_codigo,u.udi_letra_nomina,m.mat_anio_academico','ASC');
$query = $this->db->get("(".$subquery.") AS mudi");
return $query;


¿Could they help me? thanks!!

You have the wrong syntax for ordering.

It should be:

$this->db->order_by('title DESC, name ASC');

Not:

$this->db->order_by('title, name DESC');

See: http://www.codeigniter.com/user_guide/da...ng-results

thanks for you help. i'm trying with the next code in the subquery.

$this->db->order_by('u.sem_codigo', 'ASC');
$this->db->order_by('u.udi_letra_nomina','ASC');
$this->db->order_by('m.mat_anio_academico', 'ASC');

equal i have the error.

ERROR: syntax error at or near "AS" LINE 7: ...d"."udi_id" DESC, "m"."mat_anio_academico"" DESC)" AS "mudi" ^

this happens in the subquery. In clausule: "from"

¿how i can remove the quotes doubles of clausule "from"?

You may need to split this up and test the two queries separately.

It looks like "$query = $this->db->get("(".$subquery.") AS mudi");" is the issue?

I returned to the simple example

$subquery = 'SELECT DISTINCT ON (mud.udi_id) mud.mud_id FROM matricula_unidad_didactica AS mud INNER JOIN matricula AS m ON mud.mat_id=m.mat_id INNER JOIN alumno AS a ON m.alu_id=a.alu_id WHERE a.alu_id = 415 ORDER BY mud.udi_id DESC,"m"."mat_anio_academico" DESC';

$this->db->select('m.mat_anio_academico,mu.udi_id,u.udi_nombre,u.sem_codigo,u.udi_letra_nomina,mu.mud_nota_final,mu.mud_nota_recuperacion,m.mma_id',FALSE);

$this->db->from("($subquery) AS mudi");
$this->db->join('matricula_unidad_didactica AS mu','mudi.mud_id=mu.mud_id');
$this->db->join('unidad_didactica AS u','mu.udi_id=u.udi_id');
$this->db->join('matricula AS m','mu.mat_id=m.mat_id');
$this->db->join('alumno AS a','m.alu_id=a.alu_id');
$this->db->where('a.alu_id',415);
$this->db->order_by('u.sem_codigo','ASC');
$this->db->order_by('u.udi_letra_nomina','ASC');
$this->db->order_by('m.mat_anio_academico ASC');
$query = $this->db->get();
return $query;

in the variable $subquery if I remove the final order by

,"m". "mat_anio_academico" DESC

It works !!

but I need both.

Is there any extra help?

Does "m"."mat_anio_academico" exist in your table?

Have you tried running the sub-query just in a DB tool?

if you find the field!
to correct the double quote and try pgadmin3 works.

This is the modified query and running

SELECT m.mat_anio_academico, mu.udi_id, u.udi_nombre, u.sem_codigo, u.udi_letra_nomina, mu.mud_nota_final, mu.mud_nota_recuperacion, m.mma_id FROM (SELECT DISTINCT ON (mud.udi_id) mud.mud_id FROM matricula_unidad_didactica AS mud INNER JOIN matricula AS m ON mud.mat_id=m.mat_id INNER JOIN alumno AS a ON m.alu_id=a.alu_id WHERE a.alu_id = 415 ORDER BY mud.udi_id DESC, "m"."mat_anio_academico" DESC) AS "mudi" JOIN "matricula_unidad_didactica" AS "mu" ON "mudi"."mud_id"="mu"."mud_id" JOIN "unidad_didactica" AS "u" ON "mu"."udi_id"="u"."udi_id" JOIN "matricula" AS "m" ON "mu"."mat_id"="m"."mat_id" JOIN "alumno" AS "a" ON "m"."alu_id"="a"."alu_id" WHERE "a"."alu_id" = 415 ORDER BY "u"."sem_codigo" ASC, "u"."udi_letra_nomina" ASC, "m"."mat_anio_academico" ASC

I have not found help. Now I created a function in postgresql 9.5



CREATE OR REPLACE FUNCTION certificado_notas()
RETURNS TABLE(mat_anio_academico integer, udi_id integer, udi_nombre character varying, sem_codigo character, udi_letra_nomina character, mud_nota_final integer, mud_nora_recuperacion integer, mma_id integer)
AS $BODY$
BEGIN
RETURN QUERY SELECT
m.mat_anio_academico,
mu.udi_id,
u.udi_nombre,
u.sem_codigo,
u.udi_letra_nomina,
mu.mud_nota_final,
mu.mud_nota_recuperacion,
m.mma_id

FROM (SELECT
DISTINCT ON (mud.udi_id) mud.mud_id
FROM matricula_unidad_didactica AS mud
INNER JOIN matricula AS m
ON mud.mat_id=m.mat_id
INNER JOIN alumno AS a
ON m.alu_id=a.alu_id
WHERE a.alu_id = 415
ORDER BY mud.udi_id DESC, m.mat_anio_academico DESC
) AS mudi
JOIN matricula_unidad_didactica AS mu
ON mudi.mud_id=mu.mud_id
JOIN unidad_didactica AS u
ON mu.udi_id=u.udi_id
JOIN matricula AS m
ON mu.mat_id=m.mat_id
JOIN alumno AS a
ON m.alu_id=a.alu_id
WHERE a.alu_id = 415
ORDER BY u.sem_codigo ASC, u.udi_letra_nomina ASC, m.mat_anio_academico ASC;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


attempt to call in 2 ways.

$this->db->query("SELECT certificado_notas()");

$this->db->call_function("certificado_notas");

in both no error.

as I can call a function in codeigniter postgresql