Passing values from a select to subselect

Asked

Viewed 2,034 times

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 has the ? (question mark) would you like to pass Third Party code to correctly pick up the percentage attendance at meetings, how could you do that? If I put a fixed value works, but ai on all lines appears only from a Third.

sql postgresql select join where

2 answers

1


Giancarlo, you can use temporary tables or Join with select, follow 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;

  • Thank you very much, I solved with a Function, but using temporary tables unaware.

0

Another solution to the problem I found for those interested. I created and used a Function instead of ? (asterisk) make her call. The problem was the performance issue, 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;

Browser other questions tagged sql postgresql select join where

You are not signed in. Login or sign up in order to post.