select with condition if null grab another column in a FUNCTION

Question

select with condition if null grab another column in a FUNCTION

Navigation

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

0

I try to make a query in a FUNCTION but this time with a condition that if in my table the "Strnttr" field is null grab the value of another column I show them my FUNCTION

is from the OJDT table where I want to apply it

ALTER FUNCTION [dbo].[SBO_FN_LGS_OBT_OBTENER_Fecha_doc](@Code varchar(50))  
RETURNS varchar(250)   
AS   
BEGIN  
    DECLARE @resp datetime;  
    declare @st int;
    select @st=StornoToTr from OJDT
     SELECT @resp =b.TaxDate
    from OJDT A INNER JOIN 
     (
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO as tipo from oinv  union all
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from opch union all
    select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from ovpm union all    
    select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from ORCT union all
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from ODLN union all
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from orin union all
    select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from orct union all
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from owtr 
    ) b 
     ON A.TRANSID=B.TRANSID  and a.U_LGS_TIPO=b.tipo
        where a.transid= @code 
    RETURN format(@resp,'dd/MM/yyyy');  
END;

sql-server

asked by Danilo 13.08.2018 в 19:49

source

4 answers

Select the first 7 elements of an enum and display them in a Combobox using datasource Does Array.forEach not work in javascript?

score 1 · Answer 1

1

Depending on the version of your engine you can use:

MSQL 2008 or lower (OR ANSI SQL):

SELECT @st = ISNULL(StornoToTr, OTRA_COLUMNA)

MSQL superior to 2008:

SELECT @st = COALESCE(StornoToTr, OTRA_COLUMNA)

answered by 13.08.2018 в 21:02

score 0 · Answer 2

0

You should try to put a CASE statement

SELECT CASE WHEN COLUMNA1 IS NULL THEN COLUMNA2 ELSE COLUMNA3 END CAMPO
FROM [TABLA]

answered by 13.08.2018 в 20:10

score 0 · Answer 3

Modify your query, with this function (ISNULL) you get your @st variable to take the value of "otracolumna" if StornoToTr is null

 ALTER FUNCTION [dbo].[SBO_FN_LGS_OBT_OBTENER_Fecha_doc](@Code varchar(50))  
      RETURNS varchar(250)   
      AS   
      BEGIN  
          DECLARE @resp datetime;  
          declare @st int;
          select @st=ISNULL(StornoToTr,otracolumna) from OJDT
           SELECT @resp =b.TaxDate
          from OJDT A INNER JOIN 
           (
          select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO as tipo from oinv  union all
          select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from opch union all
          select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from ovpm union all    
          select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from ORCT union all
          select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from ODLN union all
          select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from orin union all
          select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from orct union all
          select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from owtr 
          ) b 
           ON A.TRANSID=B.TRANSID  and a.U_LGS_TIPO=b.tipo
              where a.transid= @code 
          RETURN format(@resp,'dd/MM/yyyy');  
      END;

score 0 · Answer 4

ALTER FUNCTION [dbo].[SBO_FN_LGS_OBT_OBTENER_Fecha_doc](@codes int)
RETURNS varchar(250)   
AS   
BEGIN  
    DECLARE @resp datetime;  
    DECLARE @aa int;  

     SELECT @resp =b.TaxDate,
     @codes=(case when a.StornoToTr is null then a.TransId
                when a.StornoToTr <> null then a.StornoToTr
                else a.TransId end) 

    from OJDT A INNER JOIN 
     (
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO as tipo from oinv  union all
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from opch union all
    select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from ovpm union all    
    select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from ORCT union all
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from ODLN union all
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from orin union all
    select transid,U_LGS_TPCE,TaxDate,U_LGS_TPCE from orct union all
    select transid,U_LGS_TIPO,TaxDate,U_LGS_TIPO from owtr

    ) b 
     ON A.TRANSID=B.TRANSID  and a.U_LGS_TIPO=b.tipo
        where a.transid= @codes
    RETURN format(@resp,'dd/MM/yyyy');  
END;