Passing values from a select to subselect

Question

Passing values from a select to subselect

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

I have the following query:

 SELECT rp.colaborador as codigo_, t.nome, count(rp.*) as presencas, 
    ((100 * (select count(rp.*) from empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
    where r.data_reuniao > t.data_cadastro and t.codigo = ?
    group by rp.colaborador, t.codigo )) / (select max(codigo) from empresa.cad_reunioes rp) || '%') as porcentagem 
    FROM empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
    where r.data_reuniao > t.data_cadastro
    group by t.nome, rp.colaborador order by presencas desc;

Where do I have ? (question mark) I would like to pass the Third party code to correctly pick up the percentage of attendance at the meetings, how could I do this? If I put a fixed value it works, but then all lines appear only from a third party.

sql join postgresql select where

asked by anonymous 21.05.2015 / 18:51

2 answers

0

Another solution to the problem I found for anyone who has an interest. I created and I used a function, and in place of? (asterisk) I call her. The problem was the performance issue, the select takes 144ms the first time.

CREATE OR REPLACE FUNCTION  empresa.getpercpresencaterceiro(codigo_terceiro integer)
RETURNS integer AS
$BODY$
BEGIN

RETURN (select(count(x.codigo)) from empresa.cad_reunioes x join   empresa.cad_terceiros t on t.codigo =  codigo_terceiro where x.data_reuniao >= t.data_cadastro);

END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION empresa.getpercpresencaterceiro(integer)
OWNER TO postgres;

24.05.2015 / 22:50

Data does not appear on page How to make a popup to insert an email appear at the click of a button

score 1 · Accepted Answer

Giancarlo, you can use temporary tables or join with the select, follow the examples below.

Using temporary table.

CREATE TABLE #TABLE_PORCENTAGEM (
QUANTIDADE BIGINT,
CODIGOT NVARCHAR(MAX)
)



INSERT INTO #TABLE_PORCENTAGEM (QUANTIDADE, CODIGOT)
select 
    count(rp.*), 
    t.codigo 
from 
    empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
where
     r.data_reuniao > t.data_cadastro
group by 
    rp.colaborador, 
    t.codigo 


 SELECT
     rp.colaborador as codigo_, 
     t.nome, 
     count(rp.*) as presencas, 
     ((100 * ISNULL(tp.QUANTIDADE,0)) / (select max(codigo) from empresa.cad_reunioes rp) || '%') as porcentagem 
FROM 
    empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
    left join #TABLE_PORCENTAGEM tp on tp.CODIGOT = t.codigo
where 
    r.data_reuniao > t.data_cadastro
group by 
    t.nome, 
    rp.colaborador 
order by 
    presencas desc;

DROP TABLE #TABLE_PORCENTAGEM

Using join with select

 SELECT
     rp.colaborador as codigo_, 
     t.nome, 
     count(rp.*) as presencas, 
     ((100 * ISNULL(tp.QUANTIDADE,0)) / (select max(codigo) from empresa.cad_reunioes rp) || '%') as porcentagem 
FROM 
    empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
    left join (
                select 
                count(rp.*) as QUANTIDADE, 
                t.codigo as CODIGOT 
                from 
                    empresa.cad_reuniao_presencas rp 
                    join empresa.cad_terceiros t on t.codigo = rp.colaborador
                    join empresa.cad_reunioes r on r.codigo = rp.reuniao
                where
                     r.data_reuniao > t.data_cadastro
                group by 
                    rp.colaborador, 
             ) tp on tp.CODIGOT = t.codigo
where 
    r.data_reuniao > t.data_cadastro
group by 
    t.nome, 
    rp.colaborador 
order by 
    presencas desc;