Between with null fields

1

Good day I have a table where I have to make a query between two columns of type date, the problem is that the end date column can have null data, is there any way to perform the query ???

Here my query

SELECT ACC INTO OUT_M1_PRIMNIV 
        FROM(SELECT ACC FROM T_M1_PRIMNIV 
        WHERE IN_APLICATIONDATE BETWEEN FECHA_INICIO AND FECHA_FIN AND
        (LINEA_NEGOCIO = IN_LINEA_NEG OR LINEA_NEGOCIO = '*') AND
        (ID_PRODUCTO = IN_ID_PROD OR ID_PRODUCTO = '*') AND 
        (ID_TARIFA = IN_ID_TARIFA OR ID_TARIFA = '*') AND
        (ID_CONVENIO = IN_ID_CONVENIO OR ID_CONVENIO = '*') AND
        (ID_VERSION = IN_ID_VER OR ID_VERSION = '*') AND
        (AGENCIA = IN_AGENCIA OR AGENCIA = '*') AND
        (CANAL_VENTA = IN_CANAL_VENTA OR CANAL_VENTA = '*') AND
        (SUBTIPO_DE_VEHICULO = F_SUBTIPO_VEHICULO OR SUBTIPO_DE_VEHICULO = '*') AND
        (CATEGORIA_DE_CIRCULACION = F_CAT_CIRC OR CATEGORIA_DE_CIRCULACION = '*') AND
        (GPOEST = F_GPO_EST OR GPOEST = '*') AND
        (CLAVE = IN_CLAVE OR CLAVE = '*') AND
        (MODELO = IN_MODELO OR MODELO = '*') AND
        (ESTADO = IN_ESTADO OR ESTADO = '*') AND
        (POBLACION = IN_POBLACION OR POBLACION = '*') AND
        (CP = IN_CP OR CP = '*') AND
        (GENERO = IN_GENERO OR GENERO = '*') AND
        (EDAD = IN_EDAD OR EDAD = '*') AND
        (USO = IN_USO OR USO = '*') ORDER BY ID)
        WHERE ROWNUM = 1;

What I'm looking for is that even if the end date is null, it returns the value of the field requested in the select or said in another way that only considers that the start date data is less than the application_date value and that the others are considered criteria.

    
asked by Olinser David Lobo Meneses Tel 09.11.2017 в 18:46
source

1 answer

1
  

What I'm looking for is that even if the end date is null, it returns the value of the field requested in the select or said in another way that only considers that the start date data is less than the application_date value and that the others are considered criteria.

It is best not to use BETWEEN . In this way, you can take this condition:

WHERE IN_APLICATIONDATE BETWEEN FECHA_INICIO AND FECHA_FIN

and modify it to take into account the possibility of null in this way:

WHERE IN_APLICATIONDATE >= FECHA_INICIO
AND (FECHA_FIN IS NULL OR IN_APLICATIONDATE <= FECHA_FIN)
    
answered by 09.11.2017 / 19:06
source