SQL Server - CONCAT + IF in VIEW

Navigation
1

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,whereinline5afteratotalof9resultsthatdoesnothaveanyid_grupoeconomicoassigned,thatis,itis0.I'mnotabletoputaconditiontocheckifNULLandsubstitutesforamessage(SEMGRUPOECONOMICO).Ineedthisinformationtocomeas:

Totade9cliente(s)SEMGRUPOECONOMICO

Afterthiscomesthebiggestproblem,asIapplyittoaVIEWbecausewhenItrytocreateit,ithasaproblemwithCONCAT().

LookingatsomepostsI'vemovedthequerytoVIEWtowork,butintheresults,line5comesasNULL(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?

    
asked by anonymous 07.08.2018 / 22:57

2 answers

3

You can use "CASE WHEN" to solve your problem on the first query, it works as an IF: 

SELECT
    CONCAT('Total de ', COUNT(E.ID_GRUPOECONOMICO), ' cliente(s) ' + CASE WHEN GP.GRPECON_DESCRICAO IS NULL THEN 'SEM GRUPO ECONOMICO' ELSE  GP.GRPECON_DESCRICAO END) 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
    
07.08.2018 / 23:04
1

The simplest way is to apply a ISNULL to the GRPECON_DESCRIÇÃO column: 

SELECT      CONCAT('Total de ', CAST(COUNT(E.ID_GRUPOECONOMICO) AS VARCHAR), ' cliente(s) ' + ISNULL(GP.GRPECON_DESCRICAO, 'SEM GRUPO ECONOMICO')) AS 'QUANTIDADE CLIENTE POR GRUPO ECONOMICO' 
FROM        dbo.ENTIDADES       E 
LEFT JOIN   dbo.GRUPOECONOMICO  GP ON GP.ID_GRUPOECONOMICO = E.ID_GRUPOECONOMICO 
GROUP BY    GP.GRPECON_DESCRICAO

Do not forget to make a CAST in E.ID_GRUPOECONOMICO because if it is of numeric type it will give error in CONCAT .

    
08.08.2018 / 10:34
Error: Service Intent must be explicit Variable value in PHP