How to make a CASE WHEN from a Nvarchar Field that is sometimes Null or empty?

Navigation
3

Well I have the following Query, it is a stored procedure for HANA STUDIO I have the Field A. "NumAtCard" which is of type nvarchar what happens is that many times the field will be empty or it will be null and I would like to know how to make a CASE so that the query does not throw me that empty value or Null 

ALTER PROCEDURE SBO_COMP_RETENCION(
    IN FECHAINI DATE,
    IN FECHAFIN DATE,
    IN FECHAUS DATE
)
LANGUAGE SQLSCRIPT
AS
BEGIN
SELECT
A."CardCode" "Cod Cliente", 
A."CardName" "Cliente",
C."City",
A."Address",
A."U_nrc_prov",
C."U_NIT" "NIT",       
A."NumAtCard" "Documento",
(select SUM(x."LineTotal") FROM "PCH1" x WHERE x."DocEntry" = A."DocEntry") AS "Importe",
"SBO_CANTIDADCONLETRA"(IFNULL ((SELECT ROUND(X."WTAmnt",2) FROM  "PCH5" X WHERE X."AbsEntry" =  A."DocEntry" AND X."WTCode" = '1%RF'), 0))|| ' DOLARES' AS "CantidadEnLetras",
IFNULL ((SELECT X."WTAmnt" FROM  "PCH5" X WHERE X."AbsEntry" =  A."DocEntry" AND X."WTCode" = '1%RF'), 0) as "Retención",
A."DocDate" "Fecha",
'Factura Proveedores' as "Tipo",
'RET 1%RF' AS "COD"
FROM "OPCH" A
left JOIN "OCRD" C ON A."CardCode" = C."CardCode"
WHERE A."CANCELED" NOT IN('C','Y')
AND A."DocDate" BETWEEN FECHAINI AND FECHAFIN;
END
    
asked by Luis M. López 09.11.2018 в 15:54
source

2 answers

2

It's very simple, you can do it in the following way: 

...
,CASE
    when A."NumAtCard" is null then 'No tiene'
    when A."NumAtCard"='' then 'No capturada'
    else A."NumAtCard"
end "Documento",
...
    
answered by 09.11.2018 / 16:17
source
1

If you are using SQL Server, you can use ISNULL () .

The ISNULL function receives two parameters:

  • Value to verify if NULL .
  • If NULL , use this value.

    • Since you do not specify which is the value that returns NULL, this is an example to validate that "if the field C."City" is NULL", return another value.

    Example: If the city is NULL , display the text "No City" . 

    SELECT ISNULL(C.[City], 'Sin Ciudad')
FROM <tu_tabla>
        
    answered by 09.11.2018 в 16:10
    problems installing Adminlte in laravel string to list in python