cancel
Showing results for 
Search instead for 
Did you mean: 

consultas con parámetros nulos

former_member220991
Participant
0 Kudos

Buenas tardes foro tengo una consulta y no se si se pueda realizar

estoy realizando la siguiente consulta

declare @i datetime, @f datetime

set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]') 

set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')

select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',

B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',

B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'

from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry

WHERE A.DocDate BETWEEN @i and @f  AND B.FromWhsCod = '[%2]' and A.U_Turno = '[%3]'

ORDER BY A.DocNum,B.FromWhsCod

donde me muestra los traspasos realizados por rango de fechas turno y almacen

pero quisiera saber si se puede realizar que cuando yo no quiera ingresar el filtro de almacenes o de turno me muestre todo los movimientos de tras pasos que se realizaron durante esa fecha.

por que actual mente si no le pongo el rango completo (fechas,almacen y turno) me marca error y quisiera saber si se puede realizar esta operación 

Accepted Solutions (0)

Answers (1)

Answers (1)

felipe_loyolarodriguez
Active Contributor
0 Kudos

Puedes agregar "If" para corroborar los parametros

Ejemplo


declare @i datetime, @f datetime

set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]') 

set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')

if (@i is null and @f is not null)

begin

    select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',

    B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',

    B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'

    from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry

    WHERE A.DocDate <= @f  AND B.FromWhsCod = '[%2]' and A.U_Turno = '[%3]'

    ORDER BY A.DocNum,B.FromWhsCod

end

else if (@f is null and @i is not null)

begin

    select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',

    B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',

    B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'

    from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry

    WHERE A.DocDate >= @i  AND B.FromWhsCod = '[%2]' and A.U_Turno = '[%3]'

    ORDER BY A.DocNum,B.FromWhsCod

end

else

    select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',

    B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',

    B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'

    from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry

    WHERE A.DocDate between @i and @f  AND B.FromWhsCod = '[%2]' and A.U_Turno = '[%3]'

    ORDER BY A.DocNum,B.FromWhsCod

Saludos

former_member220991
Participant
0 Kudos

Gracias Felipe pero me marca un error al momento de correrlo

Must specify table to select from.'Alertas recibidas'(OAIB)

felipe_loyolarodriguez
Active Contributor
0 Kudos

Eso es porque los parámetros [%2] y [%3] deben estar contenidos también en la cabecera como las fechas.

Saludos

former_member220991
Participant
0 Kudos

ok felipe

he estado tratando de programar los parámetros que me comentas

este es mi consulta si me puedes explicar como seria la estructura te lo agradecería por que he buscado en internet pero la verdad no entiendo como se componen estos parámetros

declare @i datetime, @f datetime, @g VarChar, @h VarChar

set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]')   

set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]') 

set @g=(/*select top 2 A.FromWhsCod from WTR1 A where A.FromWhsCod=*/'[%2]')

set @h=(/*select top 3 A.U_Turno from OWTR A where A.U_Turno=*/'[%3]')

if (@i is null and @f is not null and @g is not null and @h is not null ) 

begin 

    select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo', 

    B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada', 

    B.Quantity as 'Cantidad',A.DocDate AS 'Fecha' 

    from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry 

    WHERE A.DocDate <= @f  AND B.FromWhsCod = @g and A.U_Turno = @h 

    ORDER BY A.DocNum,B.FromWhsCod 

end 

else if (@f is null and @i is not null and @g is not null and @h is not null) 

begin 

    select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo', 

    B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada', 

    B.Quantity as 'Cantidad',A.DocDate AS 'Fecha' 

    from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry 

    WHERE A.DocDate >= @i  AND B.FromWhsCod = @g and A.U_Turno = @h

    ORDER BY A.DocNum,B.FromWhsCod 

end 

else 

    select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo', 

    B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada', 

    B.Quantity as 'Cantidad',A.DocDate AS 'Fecha' 

    from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry 

    WHERE A.DocDate between @i and @f  AND B.FromWhsCod = @g and A.U_Turno = @h  

    ORDER BY A.DocNum,B.FromWhsCod