Do a procedure with SQL language that returns the number of clients that have no business done

Question

Do a procedure with SQL language that returns the number of clients that have no business done

Navigation

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

0

I can not resolve this issue, I want it to show client numbers without primary key repeat, no business done, and show a warning if customers have business done. This error has appeared here.

CREATE FUNCTION sem_negocios() RETURNS integer AS 
$$
    SELECT count(*), CASE WHEN identidade NOT IN (SELECT DISTINCT identidade FROM negocio)
        THEN 'negociado'
        ELSE 'nao negociado'
        END AS identidade
    FROM cliente
    GROUP BY CASE 
        WHEN identidade IN (SELECT DISTINCT identidade FROM negocio)
        THEN 'negociado'
        ELSE 'nao negociado'
        END
    ORDER BY identidade;

$$
LANGUAGE 'SQL';

ERROR: column "customer.identity" should appear in the GROUP BY clause or be used in an aggregate function LINE 3: SELECT count (*), CASE WHEN identity NOT IN (SELECT DISTINCT ... ^

********** Error **********

ERROR: column "customer.identity" should appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803 Character: 83

sql postgresql stored-procedures

asked by anonymous 28.06.2017 / 03:45

2 answers

1

If your function should only return an integer, you can not have other columns, just the count (*). Besides that case when it's a mess.

The function code would look like this: d

CREATE FUNCTION sem_negocios() RETURNS integer AS 
$$
    SELECT 
        count(*)
    FROM cliente
    where cliente.id not in (select distinct negocio.idcliente from negocio);
$$
LANGUAGE 'SQL';

28.06.2017 / 04:13

SSL Identification by Google Which Apache2 Cipher Suite is compatible with Chrome 49 on Windows XP? [closed]

score 1 · Accepted Answer

Assuming your template has a structure similar to this:

CREATE TABLE cliente
(
    cod INTEGER,
    identidade INTEGER
);

CREATE TABLE negocio
(
    cod INTEGER,
    identidade INTEGER
);

With these data:

INSERT INTO cliente( cod, identidade ) VALUES ( 1, 1000 );
INSERT INTO cliente( cod, identidade ) VALUES ( 2, 2000 );
INSERT INTO cliente( cod, identidade ) VALUES ( 3, NULL );
INSERT INTO cliente( cod, identidade ) VALUES ( 4, 4000 );
INSERT INTO cliente( cod, identidade ) VALUES ( 5, NULL );

INSERT INTO negocio( cod, identidade ) VALUES ( 1, 1000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 2, 2000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 3, 4000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 4, 1000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 5, 2000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 6, 4000 );

Your function would look like this:

CREATE OR REPLACE FUNCTION clientes_sem_negocio()
RETURNS INTEGER AS 
$body$
BEGIN
    RETURN(
        SELECT
            DISTINCT count(1)
        FROM
            cliente c
        LEFT JOIN
            negocio n ON ( n.identidade = c.identidade )
        WHERE
            n.identidade IS NULL
    );
END
$body$
LANGUAGE plpgsql;

Testing:

SELECT clientes_sem_negocio() AS qtd;

Output: