Select non-NULL records within a CASE

Question

Select non-NULL records within a CASE

Navigation

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

2

You can help me with the following, I currently have a query from a table sql-server which brings me the records depending on a condition in WHERE and classifies those records into 4 categories which are' Contrato ',' Otrosies ',' Garantias 'and' Polizas '. This is done through the function CASE , the name of the category the store in an extra column which the function CASE gives me the possibility to create, this column will assign the name " CLASIFICACION ",

The records are classified successfully in the categories mentioned above but the problem I have is that I need my query to only bring me the records that are within these 4 categories, since when finding records that do not meet these conditions in the CASE , in my extra column " CLASIFICACION " assigns them the value NULL and also brings me those records.

Here my query:

SELECT 
       TituloDocumento,                                                                                      TipoDocumentoAcuerdo,
       Contenido,
       CLASIFICACION=
       CASE 
       WHEN
       TipoDocumentoAcuerdo IN ('CONTRATO','CONTRATO E＆P', 'CONTRATOS CON SOCIOS') THEN 'CONTRATO'
       WHEN
       TipoDocumentoAcuerdo = ('OTROSIES') THEN 'OTROSIES'
       WHEN
       TipoDocumentoAcuerdo = ('GARANTÍAS') THEN 'GARANTÍAS'
       WHEN
       TipoDocumentoAcuerdo = ('GARANTÍAS') AND TituloDocumento LIKE ('%POLIZA%') THEN 'POLIZAS'
       END
       FROM reporte_documentos_Tecnicos
       WHERE Contenido = ('Acuerdos de Gobierno')

Thank you very much for your help.

mysql sql sql-server

asked by Sebastián Espinel 13.08.2018 в 15:57

source

2 answers

2

Increase a condition to where so that you omit the results null :

       SELECT 
       TituloDocumento,                                                                                      TipoDocumentoAcuerdo,
       Contenido,
       CLASIFICACION=
       CASE 
       WHEN
       TipoDocumentoAcuerdo IN ('CONTRATO','CONTRATO E＆P', 'CONTRATOS CON SOCIOS') THEN 'CONTRATO'
       WHEN
       TipoDocumentoAcuerdo = ('OTROSIES') THEN 'OTROSIES'
       WHEN
       TipoDocumentoAcuerdo = ('GARANTÍAS') THEN 'GARANTÍAS'
       WHEN
       TipoDocumentoAcuerdo = ('GARANTÍAS') AND TituloDocumento LIKE ('%POLIZA%') THEN 'POLIZAS'
       END
       FROM reporte_documentos_Tecnicos
       WHERE Contenido = ('Acuerdos de Gobierno') AND TipoDocumentoAcuerdo IS NOT NULL

answered by 13.08.2018 в 18:02

ComboBox in DataGridView getting the value in the SelectedIndexChanged event Get moving data from input type="range"

score 0 · Accepted Answer

Following the advice of Francisco if I added in where the condition AND TipoDocumentoAcuerdo IS NOT NULL , the column CLASSIFICATION continued to show results with NULL , in the end it could be solved thanks to the user Luis Gutierrez who suggested that in the where add the condicion AND TipoDocumentoAcuerdo IN ('CONTRATO','CONTRATO E＆P', 'CONTRATOS CON SOCIOS', 'OTROSIES', 'GARANTÍAS') for which the query was

SELECT 
       TituloDocumento,                                                          
       TipoDocumentoAcuerdo,
       Contenido,
       CLASIFICACION=
       CASE 
       WHEN
       TipoDocumentoAcuerdo IN ('CONTRATO','CONTRATO E＆P', 'CONTRATOS CON SOCIOS') THEN 'CONTRATO'
       WHEN
       TipoDocumentoAcuerdo = ('OTROSIES') THEN 'OTROSIES'
       WHEN
       TipoDocumentoAcuerdo = ('GARANTÍAS') THEN 'GARANTÍAS'
       WHEN
       TipoDocumentoAcuerdo = ('GARANTÍAS') AND TituloDocumento LIKE ('%POLIZA%') THEN 'POLIZAS'
       END
       FROM reporte_documentos_Tecnicos
       WHERE Contenido = ('Acuerdos de Gobierno')
AND TipoDocumentoAcuerdo IN ('CONTRATO','CONTRATO E＆P', 'CONTRATOS CON SOCIOS', 'OTROSIES', 'GARANTÍAS') 

y en los resultados la columna CLASIFICACION ya me trae los valores que cumplen las condiciones en el case, es decir 'CONTRATO' - 'OTROSIES' - 'GARANTÍAS' y 'POLIZAS'.