I have a problem and I will try to explain the situation.
I have a client table called ENTIDADES
, in it there is an attribute called ID_GRUPOECONOMICO
where this is a FK
of the GRUPOECONOMICO
table. I am creating a VIEW to return the number of clients in each economic group and the attribute GRPECON_DESCRIÇÃO
(is the description of the economic group eg BRONZE CONTRACT, SILVER CONTRACT, ETC).
So far I'm running this query:
SELECT
CONCAT('Total de ', COUNT(E.ID_GRUPOECONOMICO), ' cliente(s) ' + GP.GRPECON_DESCRICAO) AS 'QUANTIDADE CLIENTE POR GRUPO ECONOMICO'
FROM
dbo.ENTIDADES AS E
LEFT JOIN
dbo.GRUPOECONOMICO AS GP
ON GP.ID_GRUPOECONOMICO = E.ID_GRUPOECONOMICO
GROUP BY
GP.GRPECON_DESCRICAO
Running this query brings me the following information:
Inthisresultoneofmyproblemsispresented,whereinline5
afteratotalof9resultsthatdoesnothaveanyid_grupoeconomico
assigned,thatis,itis0
.I'mnotabletoputaconditiontocheckifNULL
andsubstitutesforamessage(SEMGRUPOECONOMICO
).Ineedthisinformationtocomeas:
Totade9cliente(s)SEMGRUPOECONOMICO
Afterthiscomesthebiggestproblem,asIapplyittoaVIEW
becausewhenItrytocreateit,ithasaproblemwithCONCAT()
.
LookingatsomepostsI'vemovedthequerytoVIEW
towork,butintheresults,line5
comesasNULL
(Ibelieveithastodowithpreviousproblem).
SELECT{fnCONCAT('Totalde',CAST(COUNT(E.ID_GRUPOECONOMICO)ASVARCHAR(10))+'clientes'+GP.GRPECON_DESCRICAO)}AS[QUANTIDADECLIENTEPORGRUPOECONOMICO]FROMdbo.ENTIDADESASELEFTOUTERJOINdbo.GRUPOECONOMICOASGPONGP.ID_GRUPOECONOMICO=E.ID_GRUPOECONOMICOGROUPBYGP.GRPECON_DESCRICAO
andtheresult:
My question is how to write the query to treat these registers with id_group = 0 and if resolving the query, can it be applied to the view too?