How to save a common table expressions (CTE) to a variable in sql

Question

How to save a common table expressions (CTE) to a variable in sql

Navigation

#1 by (1 votes)

1

Hello, I'm trying to save the value of a common table expressions (CTE) in a declared variable.

This is all I have.

DECLARE @valor decimal(12,0)

with xc as ( 
                                     select v.productoid, p.nombre, eq.unidadid, eq.FactorConversion, sum(v.cantidad) as cantidad
                                     from ventas as v
                                         inner join productos as p on p.id = v.productoid
                                         left join unidades as u on u.id = p.unidadid
                                         left join equivalencias as eq on eq.unidadid = v.unidadid
                                     where v.productoid = 1270
                                     group by v.productoid, p.nombre, eq.unidadid, eq.FactorConversion

                            ) select ( cantidad * FactorConversion ) as Cantidad
                            from xc inner join (
                                            select p.id, p.nombre, p.pesomililitros from productos as p where p.id = 1270 
                            ) as x1
                on x1.id = productoid
select @valor = Cantidad from xc

When I execute it, it tells me this:

(1 row(s) affected)
Msg 208, Level 16, State 1, Line 17
Invalid object name 'xc'.

It does not seem to recognize the xc object. The value that the CTE obtains is

700.000

That value I want to save it in my variable @valor if I just execute .. this ..

with xc as ( 
                                     select v.productoid, p.nombre, eq.unidadid, eq.FactorConversion, sum(v.cantidad) as cantidad
                                     from ventas as v
                                         inner join productos as p on p.id = v.productoid
                                         left join unidades as u on u.id = p.unidadid
                                         left join equivalencias as eq on eq.unidadid = v.unidadid
                                     where v.productoid = 1270
                                     group by v.productoid, p.nombre, eq.unidadid, eq.FactorConversion

                            ) select ( cantidad * FactorConversion ) as Cantidad
                            from xc inner join (
                                            select p.id, p.nombre, p.pesomililitros from productos as p where p.id = 1270 
                            ) as x1
                on x1.id = productoid

I hope someone will guide me. thanks

sql sql-server

asked by JuanL 30.08.2018 в 19:45

source

1 answer

use 'where' with 'like' in an eloquent realation Validate array within a JSON in node.js

score 1 · Answer 1

Hello that JuanL that you would like to try in the following way.

;with xc as ( 
    select v.productoid, p.nombre, eq.unidadid, eq.FactorConversion, sum(v.cantidad) as cantidad
    from ventas as v
         inner join productos as p on p.id = v.productoid
         left join unidades as u on u.id = p.unidadid
         left join equivalencias as eq on eq.unidadid = v.unidadid
    where v.productoid = 1270
    group by v.productoid, p.nombre, eq.unidadid, eq.FactorConversion

), px as (

    select p.id, 
           p.nombre, 
           p.pesomililitros 
    from 
           productos as p 
    where 
           p.id = 1270 
)



select 
    @valor = (xc.cantidad*xc.FactorConversion)
from xc 
    inner join px as x1 on x1.id = xc.productoid

I comment you can use different CTE's is only necessary to separate them by a comma at the end of each CTE and when you finish declaring your CTE's needed after you can joins between them or play with them as you see fit, I hope this helps you , greetings.