Help with CASE in SQL Query

0

First I will clarify a point that this question is similar to the one previously formulated, entering the following link you can see:

Case in sql query

But given that that question could not solve my doubt, after of course having reviewed it well, that's why I'm going to ask the following question.

I have a stored procedure which, when executed, throws a series of data to me by entering a range of time, day, month and year. And among those data there is a state field, which shows me with numbers, and my idea is to show it to me written in words. I leave the code of the procedure:

USE [PRUEBA_TRACK]
GO
/****** Object:  StoredProcedure [dbo].[OBTIENE_INFORME_GESTION]    
Script Date: 03/20/2018 11:29:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[OBTIENE_INFORME_GESTION]
@FDESDE VARCHAR(10),
@FHASTA VARCHAR(10)

AS

SELECT E.ID_ENVIO,CL.CLIENTE, E.TIPO, E.MODO, E.VOLUMEN, E.PESO, E.ALTO, 
E.LARGO, E.CONTENIDO, E.OBSERVACIONES, E.ESTADO, 

E.COD_INTERNO, E.NOMBRE_1, E.EMPRESA_1, E.NOMBRE_2,  S.SUCURSAL,
S.DIRECCION, C.COMUNA, E.REGION_2, 

E.ADMISION, E.FECHA_ADM, E.DESPACHO, T.vc1Valor


FROM SOLICITUD AS SC, CLIENTE AS CL, ENVIO AS E, TABLAS AS T, 
SUCURSALES AS S, COMUNA AS C


WHERE
E.ID_ENVIO = SC.ENVIO AND
E.ID_CLIENTE = CL.COD_CLIENTE AND 

E.REGION_2 = T.codigo AND T.codTabla = 12 AND

E.LOCALIDAD_2 = S.ID_SUCURSAL AND E.ID_CLIENTE = S.ID_CLIENTE AND 
E.COMUNA_2 = C.ID_COMUNA AND 

E.FECHA_ADM >= @FDESDE AND E.FECHA_ADM <= @FHASTA

ORDER BY CLIENTE

Then to that stored procedure I added the following CASE:

CASE WHEN E.ESTADO = 1 THEN 'INGRESADO'
WHEN E.ESTADO = 2 THEN 'EN TRANSITO'
WHEN E.ESTADO = 3 THEN 'ENTREGADO'
WHEN E.ESTADO = 4 THEN 'OBJETADO DEFINITIVO'
WHEN E.ESTADO = 5 THEN 'OBJETADO NO DEFINITIVO'
WHEN E.ESTADO = 6 THEN 'ENTREGADO REMITENTE'
ELSE 'CODIGO GENERADO'
END

But clearly that adds a column to me, although the data shown is fine, it's just that my question is, by way of example:

What should I do so that when the value of the STATE field is 1 shows me 1, I show myself ENTERED and not 1?

That by way of example and clear if its value is 2 will show IN TRANSIT and if its value is 3 it must show DELIVERED and so on; Also, what should I do so that I do not have a new column added?

I hope you understood, thank you.

    
asked by 20.03.2018 в 18:45
source

1 answer

2

It's quite easy and there are several options. Let's go with the first using your query and your case

USE [PRUEBA_TRACK]
GO
/****** Object:  StoredProcedure [dbo].[OBTIENE_INFORME_GESTION]    
Script Date: 03/20/2018 11:29:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[OBTIENE_INFORME_GESTION]
@FDESDE VARCHAR(10),
@FHASTA VARCHAR(10)

AS

SELECT E.ID_ENVIO,CL.CLIENTE, E.TIPO, E.MODO, E.VOLUMEN, E.PESO, E.ALTO, 
E.LARGO, E.CONTENIDO, E.OBSERVACIONES,
CASE WHEN E.ESTADO = 1 THEN 'INGRESADO'
WHEN E.ESTADO = 2 THEN 'EN TRANSITO'
WHEN E.ESTADO = 3 THEN 'ENTREGADO'
WHEN E.ESTADO = 4 THEN 'OBJETADO DEFINITIVO'
WHEN E.ESTADO = 5 THEN 'OBJETADO NO DEFINITIVO'
WHEN E.ESTADO = 6 THEN 'ENTREGADO REMITENTE'
ELSE 'CODIGO GENERADO'
END as Estado, 

E.COD_INTERNO, E.NOMBRE_1, E.EMPRESA_1, E.NOMBRE_2,  S.SUCURSAL,
S.DIRECCION, C.COMUNA, E.REGION_2, 

E.ADMISION, E.FECHA_ADM, E.DESPACHO, T.vc1Valor


FROM SOLICITUD AS SC, CLIENTE AS CL, ENVIO AS E, TABLAS AS T, 
SUCURSALES AS S, COMUNA AS C


WHERE
E.ID_ENVIO = SC.ENVIO AND
E.ID_CLIENTE = CL.COD_CLIENTE AND 

E.REGION_2 = T.codigo AND T.codTabla = 12 AND

E.LOCALIDAD_2 = S.ID_SUCURSAL AND E.ID_CLIENTE = S.ID_CLIENTE AND 
E.COMUNA_2 = C.ID_COMUNA AND 

E.FECHA_ADM >= @FDESDE AND E.FECHA_ADM <= @FHASTA

ORDER BY CLIENTE

The other way would be to make a table of states and do the JOIN so that if tomorrow you have to add a state you do not have to modify the code, just add a record to the table states

    
answered by 20.03.2018 / 18:51
source