Invalid Column Name in a table created with Querys in a Stored Procedure

Navigation
0

I want to create an SP with two parameters, Date and Bank, the purpose is that each bank must show (among other data) all the clients in that bank and the amount of cash that will be available in 24.48 and 72 hours. after the Date parameter. The question is that I have a table where some general data of the accounts are stored and another table where I store the flows, from the second table I "calculate" the future flow that will be for each period (24,48,72)
 and I try to show you this in a temporary table of 3 columns, then I try to join this table with the general data table with left join . What I have has the following structure: 

      CREATE PROCEDURE .dbo.SALDOS_24_48_72 @pmInstitución nvarchar(25), @pmFechaDeConsulta date
  as
  Declare @24hrs date,@48hrs date, @72hrs date, @96hrs date
  , @pmFechadeConsulta date='2017-02-14'                --EJEMPLO
    DECLARE @pmInstitución nvarchar(50)='Banco' --EJEMPLO

    Set @24hrs=(SELECT DATEADD(DAY,1,@pmFechaDeConsulta)) --24 horas después de la fecha de consulta
    Set @48hrs=(SELECT DATEADD(DAY,2,@pmFechaDeConsulta)) --48 horas después de la fecha de consulta
    Set @72hrs=(SELECT DATEADD(DAY,3,@pmFechaDeConsulta)) --72 horas después de la fecha de consulta
    Set @96hrs=(SELECT DATEADD(DAY,4,@pmFechaDeConsulta)) --96 horas después de la fecha de consulta 
    ;
WITH #Liquidacion as (
(SELECT SUM(importe) as UnDia,A.Cliente from MovimientosEDGE A LEFT JOIN  pruebaEDGE.dbo.Clientes B ON A.Cliente=B.Clave Left join pruebaEDGE.dbo.CuentasEDGE C on A.Cliente=C.Cliente WHERE 
C.Proveedor=@pmInstitución and B.Activo=1 and Liquidacion>@pmFechaDeConsulta and liquidacion< @48hrs GROUP BY A.Cliente)            --Efectivo 24 hrs
UNION
(SELECT SUM(importe) as DosDias,A.Cliente from MovimientosEDGE A LEFT JOIN  pruebaEDGE.dbo.Clientes B ON A.Cliente=B.Clave Left join pruebaEDGE.dbo.CuentasEDGE C on A.Cliente=C.Cliente WHERE 
C.Proveedor=@pmInstitución and B.Activo=1 and Liquidacion> @24hrs and liquidacion< @72hrs  GROUP BY A.Cliente)                      --Efectivo 48 hrs
UNION
(SELECT SUM(importe) as TresDias,A.cliente from MovimientosEDGE A LEFT JOIN  pruebaEDGE.dbo.Clientes B ON A.Cliente=B.Clave Left join pruebaEDGE.dbo.CuentasEDGE C on A.Cliente=C.Cliente WHERE 
C.Proveedor=@pmInstitución and B.Activo=1 and Liquidacion> @48hrs and liquidacion< @96hrs  GROUP BY A.Cliente)                      --Efectivo 72 hrs
) 
SELECT  A.Cliente,A.Alias,Cuenta,Valor,Saldo,C.UnDia,C.DosDias,C.TresDias FROM CuentasEDGE A LEFT JOIN Clientes B ON A.Cliente=B.Clave LEFT JOIN #Liquidacion C ON A.Cliente=C.Cliente  WHERE 
B.Activo=1 and A.Proveedor=@pmInstitución

In the Declarations I use an example of date and Bank to execute the code but it returns an ERROR:

  

Msg 207, Level 16, State 1, Line 28 Invalid column name 'TwoDays'. Msg   207, Level 16, State 1, Line 28 Invalid column name 'ThreeDays'.

That is, it does not recognize the values of the temporary table. One issue that I observed is that I do not have records in the flow table for 48 and 72 hours. Is the fact that these columns are empty the reason for the error? Is there a better way to generate this final table? I want, in the end, to have this form:

  

Customer | Alias | Account | Value | Balance | 24hrs | 48 hrs | 72 hrs

     

00001 | Red | 000666 | 1421 | 63.50 | 125 | 0 | 0 |

    
asked by Edgard Huerta 09.10.2017 в 22:22
source

2 answers

1

I would use table variables. It is faster than a temporary table and very practical and behaves exactly like a table. You also do not have to worry about destroying it 

CREATE PROCEDURE .dbo.SALDOS_24_48_72 @pmInstitución nvarchar(25), 
@pmFechaDeConsulta date
  as
  Declare @24hrs date,@48hrs date, @72hrs date, @96hrs date
  , @pmFechadeConsulta date='2017-02-14'                --EJEMPLO
  DECLARE @pmInstitución nvarchar(50)='Banco' --EJEMPLO

  Declare @Liquidacion table (cliente int,UnDia int,DosDias 
  int,TresDias int)


  Set @24hrs=(SELECT DATEADD(DAY,1,@pmFechaDeConsulta)) --24 horas 
después de la fecha de consulta
  Set @48hrs=(SELECT DATEADD(DAY,2,@pmFechaDeConsulta)) --48 horas 
después de la fecha de consulta
  Set @72hrs=(SELECT DATEADD(DAY,3,@pmFechaDeConsulta)) --72 horas 
después de la fecha de consulta
  Set @96hrs=(SELECT DATEADD(DAY,4,@pmFechaDeConsulta)) --96 horas 
después de la fecha de consulta 

insert into @Liquidacion
SELECT SUM(importe) as UnDia,0 as DosDias,0 as TresDias,A.Cliente from MovimientosEDGE A LEFT 
JOIN  pruebaEDGE.dbo.Clientes B ON A.Cliente=B.Clave Left join 
pruebaEDGE.dbo.CuentasEDGE C on A.Cliente=C.Cliente WHERE 
C.Proveedor=@pmInstitución and B.Activo=1 and 
Liquidacion>@pmFechaDeConsulta and liquidacion< @48hrs GROUP BY 
A.Cliente)            
UNION
(SELECT 0 as UnDia,SUM(importe) as DosDias,0 as TresDias,A.Cliente from MovimientosEDGE A LEFT 
JOIN  pruebaEDGE.dbo.Clientes B ON A.Cliente=B.Clave Left join 
pruebaEDGE.dbo.CuentasEDGE C on A.Cliente=C.Cliente WHERE 
C.Proveedor=@pmInstitución and B.Activo=1 and Liquidacion> @24hrs and 
liquidacion< @72hrs  GROUP BY A.Cliente)                      
UNION
(SELECT 0 as UnDIa,0 as DosDias,SUM(importe) as TresDias,A.cliente from MovimientosEDGE A LEFT 
JOIN  pruebaEDGE.dbo.Clientes B ON A.Cliente=B.Clave Left join 
pruebaEDGE.dbo.CuentasEDGE C on A.Cliente=C.Cliente WHERE 
C.Proveedor=@pmInstitución and B.Activo=1 and Liquidacion> @48hrs and 
liquidacion< @96hrs  GROUP BY A.Cliente)                      

SELECT  
A.Cliente,A.Alias,Cuenta,Valor,Saldo,C.UnDia,C.DosDias,C.TresDias 
FROM 
CuentasEDGE A 
LEFT JOIN Clientes B ON A.Cliente=B.Clave 
LEFT JOIN @Liquidacion C ON A.Cliente=C.Cliente  
WHERE B.Activo=1 and A.Proveedor=@pmInstitución

I hope it helps you. Greetings

    
answered by 09.10.2017 / 22:51
source
0

The problem has already been correctly indicated by Hector in the comments, you are making a union but you return a single column, for what you are looking for, you should modify this query by a conditional summation. Something like this: 

WITH #Liquidacion as (
(
    SELECT  
        SUM(CASE WHEN                           Liquidacion< @48hrs THEN importe ELSE 0) as UnDia,
        SUM(CASE WHEN Liquidacion >= @48hrs AND Liquidacion< @72hrs THEN importe ELSE 0) as DosDia,
        SUM(CASE WHEN Liquidacion >= @72hrs AND Liquidacion< @96hrs THEN importe ELSE 0) as TresDia
        A.Cliente 
        from MovimientosEDGE A 
        LEFT JOIN  pruebaEDGE.dbo.Clientes B 
            ON A.Cliente=B.Clave 
        Left join pruebaEDGE.dbo.CuentasEDGE C 
            on A.Cliente=C.Cliente 
        WHERE C.Proveedor=@pmInstitución 
            and B.Activo=1 
            and Liquidacion>@pmFechaDeConsulta and liquidacion<@96hrs
        GROUP BY A.Cliente
)
    
answered by 09.10.2017 в 22:59
How to send an IList data set from the Controller to the View? How do I get back to the main menu in this program?