Aquí está la consulta. cuando hago T0.”ItemCode” = ‘HN 210’ en la consulta (todos los lugares)
Funciona bien. Cuando cambio el segmento where a Where T0.”ItemCode” = [%0] es cuando obtengo el error

Select  a.*,b."OldPrice" from (
Select 'm' as "type", T0."ItemCode" , T2."ItemName" , T7."ListName" , T0."Price" as 
"NewPrice" , 
T2."UpdateDate" , '9999999' as "code" , T5."U_NAME" , T2."U_lastpriceupdate", 
T2."CreateDate"
FROM
OITM T2
Inner Join ITM1 T0 on T0."ItemCode" = T2."ItemCode"
INNER JOIN OUSR T5 on T5."INTERNAL_K" = T2."UserSign2"
Inner join OPLN T7 on T0."PriceList" = T7."ListNum"
Where T0."ItemCode" = [%0] and 
T0."PriceList" IN ('5')
UNION ALL
Select 'a' as "type", T0."ItemCode" , T2."ItemName", T7."ListName" , T1."Price" as 
"NewPrice",
T4."UpdateDate" , T1."LogInstanc" as "code" , T5."U_NAME" , T2."U_lastpriceupdate", 
T2."CreateDate" 
FROM
OITM T2
Inner Join ITM1 T0 on T0."ItemCode" = T2."ItemCode"
inner join AIT1 T1 on T0."ItemCode" = T1."ItemCode" and T0."PriceList" = 
T1."PriceList"
INNER JOIN AITM T4 on T4."ItemCode" = T1."ItemCode" and T1."LogInstanc" = 
T4."LogInstanc"
INNER JOIN OUSR T5 on T5."INTERNAL_K" = T4."UserSign2"
Inner join OPLN T7 on T0."PriceList" = T7."ListNum"
Where T0."ItemCode" = [%0] and 
T0."PriceList" IN ('5')
order by "type" desc, "code" desc) as a
LEFT JOIN
(Select 'a' as "type", T0."ItemCode" , T2."ItemName", T7."ListName" , T1."Price" as 
"OldPrice" ,
T4."UpdateDate" , '9999999' as "code"  , T5."U_NAME" , T2."U_lastpriceupdate", 
T2."CreateDate" 
FROM
OITM T2
Inner Join ITM1 T0 on T0."ItemCode" = T2."ItemCode"
inner join AIT1 T1 on T0."ItemCode" = T1."ItemCode" and T0."PriceList" = 
T1."PriceList"
INNER JOIN AITM T4 on T4."ItemCode" = T1."ItemCode" and T1."LogInstanc" = 
T4."LogInstanc"
INNER JOIN OUSR T5 on T5."INTERNAL_K" = T4."UserSign2"
Inner join OPLN T7 on T0."PriceList" = T7."ListNum"
Where T0."ItemCode" = [%0] and 
T0."PriceList" IN ('5') and T1."LogInstanc" IN (
Select max(T1."LogInstanc") as "t"
FROM
OITM T2
Inner Join ITM1 T0 on T0."ItemCode" = T2."ItemCode"
inner join AIT1 T1 on T0."ItemCode" = T1."ItemCode" and T0."PriceList" = 
T1."PriceList"
INNER JOIN AITM T4 on T4."ItemCode" = T1."ItemCode" and T1."LogInstanc" = 
T4."LogInstanc"
INNER JOIN OUSR T5 on T5."INTERNAL_K" = T4."UserSign2"
Inner join OPLN T7 on T0."PriceList" = T7."ListNum"
Where T0."ItemCode" = [%0] and 
T0."PriceList" IN ('5')
GROUP BY T0."ItemCode" )
UNION ALL
Select 'a' as "type", T0."ItemCode" , T2."ItemName", T7."ListName" , T1."Price" as 
"OldPrice" ,
 T4."UpdateDate" , (T1."LogInstanc")+1 as "code"  , T5."U_NAME" , 
T2."U_lastpriceupdate", T2."CreateDate" 
FROM
OITM T2
Inner Join ITM1 T0 on T0."ItemCode" = T2."ItemCode"
inner join AIT1 T1 on T0."ItemCode" = T1."ItemCode" and T0."PriceList" = 
T1."PriceList"
INNER JOIN AITM T4 on T4."ItemCode" = T1."ItemCode" and T1."LogInstanc" = 
T4."LogInstanc"
INNER JOIN OUSR T5 on T5."INTERNAL_K" = T4."UserSign2"
Inner join OPLN T7 on T0."PriceList" = T7."ListNum"
Where T0."ItemCode" = [%0] and 
T0."PriceList" IN ('5')
order by "type" desc, "code" desc
) as b

on a."ItemCode" = b."ItemCode" and b."code" = a."code"
Where a."NewPrice" <> b."OldPrice" 
order by a."type" desc, a."code" desc

He notado en otros lugares donde una variable en una consulta anidada puede no funcionar. Como puede ser el caso aquí. ¿Alguna solución sobre cómo hacer que esto acepte un código de artículo variable? Encontré este enlace relacionado con la configuración de una declaración para nombrar la variable. Pero no tengo la sintaxis correcta para que funcione.

Compartir

1
Add Comment

2 Respuestas 1


Usar parametros de usuarios puede generar inconvenientes, se recomienda utilizar variables.

Con agregar SET @Product = / T0.ItemCode / ‘[%0]’ al inicio de tu query esto para utilizar el valor del item en una variable e irla verificando en el bloque necesario.

×
5
Add Comment