cancel
Showing results for 
Search instead for 
Did you mean: 

The multi-part identifier .. could not be bound

dov_luft3
Explorer
0 Kudos

I have the following query that works perfectly on MSSQL ( without the parameters).

But in SBO I get the following error msg:

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]The multi-part identifier "t2.UpdateDate" could not be bound. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.

/* select * from AITM T0 */


DECLARE @Date1 DATETIME


SET @Date1 = /* T0.upDatedate*/ '[%0]'


select t1.ItemCode ,
t1.ItemName  ,
t1.OnHand ,
t1.LastPurCur ,
t1.LastPurDat ,
t1.LastPurPrc ,
t1.UpdateDate ,
t1.OnHand * t1.LastPurPrc ,
t1.U_SaleIetm 


from AITM  t1 
where t1.UpdateDate=(select max(t2.UpdateDate)
					from AITM  t2 
				        where  t2.ItemCode = t1.ItemCode
					and t2.UpdateDate<=@Date1
					group by t2.ItemCode
					)


and t1.LogInstanc = (select max(t3.LogInstanc)
                      from  AITM t3
					  where t3.ItemCode = t1.ItemCode
					  and t2.UpdateDate<=@Date1
					  group by t3.ItemCode
					  )
order by t1.ItemCode


View Entire Topic
dov_luft3
Explorer
0 Kudos

Thanks.

In principle , is this syntax acceptable in SB1 querying ?

My query is much longer and goes like this and ,at lust for me is much readable

thanks

doc:

with tb (itemCode,UpdateDate,U_SalIetm,OnHand,LogInstanc,LastPurCur,ItemName)
as
(
select t0.itemCode,t0.UpdateDate,t0.U_SaleIetm,t0.OnHand,t0.LogInstanc,t0.LastPurCur,t0.ItemName
from AITM t0
where t0.U_SaleIetm is not null
and t0.OnHand>=0

union All

select t0.itemCode,t0.UpdateDate,t0.U_SaleIetm,t0.OnHand,t0.LogInstanc,t0.LastPurCur,t0.ItemName
from OITM t0
where t0.U_SaleIetm is not null
and t0.OnHand>=0


) ,
 tb1 (itemCode,UpdateDate,U_SaleIetm,OnHand,LogInstanc,LastPurCur,ItemName)  as
      ( select tb.itemCode,tb.UpdateDate,tb.U_SaleIetm,tb.OnHand,tb.LogInstanc,tb.LastPurCur,tb.ItemName
	    from tb
	    where tb.UpdateDate = (select   max(tx.UpdateDate  )
	                          from tb as tx
	               		where tx.itemCode = tb.itemCode
	                	and tx.UpdateDate<=convert(date,'2020-12-31'
	                                   )
						     
                
        
      )
  ) , -- records singulation
  tbx (itemCode,UpdateDate,U_SaleIetm,OnHand,LogInstanc,LastPurCur,ItemName )  as 
  (select tb1.itemCode,tb1.UpdateDate,tb1.U_SaleIetm,tb1.OnHand,tb1.LogInstanc,tb1.LastPurCur,tb1.ItemName
     from tb1 
	 where tb1.LogInstanc = (select max(tx.LogInstanc  )
                              from tb1 as tx
			       where tx.itemCode =tb1.itemCode
	                        )   
  )
    ,


  --last purchse date


 lp (itemCode,LastPurCur,LastPurDat,LastPurPrc,LogInstanc,OnHand) as 
 (  select t0.itemCode,t0.LastPurCur,t0.LastPurDat,t0.LastPurPrc,t0.LogInstanc,t0.OnHand
     from AITM t0
 	where t0.LastPurDat = (select  max(t1.LastPurDat )
	                      from AITM t1
                              where t1.LastPurDat<=convert(date,'2020-12-31')
  	     	              and t0.ItemCode= t1.ItemCode
			    ) 
     and t0.U_SaleIetm is not null
	 and t0.OnHand>=0


 ),lpx (itemCode,LastPurCur,LastPurDat,LastPurPrc,LogInstanc,OnHand) as 
 (  select lp.itemCode,lp.LastPurCur,lp.LastPurDat,lp.LastPurPrc,lp.LogInstanc,lp.OnHand
   from lp 
 where lp.LogInstanc = (select max(lx.LogInstanc)
                        from lp as lx
			where lp.itemCode = lx.itemCode
                        )
 )
   ,
   rt(  RateDate,Currency  ,Rate      )  as 
   (select distinct  t1.RateDate,t1.Currency,t1.Rate
      from lpx
	   join ORTT t1
	  on t1.Currency = lpx.LastPurCur
	  and t1.RateDate = lpx.LastPurDat
	  and ascii(lpx.LastPurCur) <> 63
    ),
	
lpxr ( itemCode,LastPurCur,LastPurDat,LastPurPrc,LogInstanc,OnHand,Rate                    ) as
(select lpx.itemCode,lpx.LastPurCur,lpx.LastPurDat,lpx.LastPurPrc,lpx.LogInstanc,lpx.OnHand,Rate
  from lpx
  left join rt
  on lpx.LastPurDat = rt.RateDate
  and lpx.LastPurCur = rt.Currency
   ),
   --    prices from listprice # 8
   pl8 (ItemCode,Currency,Price,PriceList  )  as
         ( select t0.ItemCode,t0.Currency,t0.Price,t0.PriceList
         from ITM1 t0
	left join tbx 
	on  t0.ItemCode = tbx.itemCode
	and tbx.LastPurCur is null
	where t0.PriceList = 8
	and t0.Price>0
 ) 
    ,
    pl8r(itemCode,Currency,Rate,RateDate,UpdateDate)  as 
 (   select   t2.itemCode, t1.Currency,t1.Rate,t1.RateDate,t2.UpdateDate
      from ORTT t1,tbx  t2,pl8 t4
     where  t1.RateDate = (select max(t3.RateDate) 
      from ORTT t3
     where t3.RateDate<=t2.UpdateDate
     and t2.itemCode=      t4.ItemCode
 )
      and t2.LastPurCur is null
      and t2.itemCode = t4.ItemCode 
 ) 
 select tbx.itemCode  ,
tbx.ItemName ,
tbx.UpdateDate ,
tbx.U_SaleIetm ,
tbx.OnHand as ,
tbx.LogInstanc  ,
lpxr.LastPurCur ,
lpxr.LastPurDat ,
lpxr.LastPurPrc ,
lpxr.Rate ,
pl8.Currency ,
pl8.Price  ,
pl8r.Rate ,
case
  when ASCII(lpxr.LastPurCur) =63  then tbx.OnHand * lpxr.LastPurPrc
  when ASCII(lpxr.LastPurCur) <> 63  then tbx.OnHand * lpxr.LastPurPrc*lpxr.Rate
  when lpxr.LastPurCur is null and   ASCII(pl8.Currency) = 63  then tbx.OnHand * pl8.Price
  when lpxr.LastPurCur is null and   ASCII(pl8.Currency) <> 63  then tbx.OnHand * pl8.Price*pl8r.Rate
end as  stock value
from  tbx 
left join  lpxr
on tbx.itemCode = lpxr.itemCode
left join pl8
on tbx.itemCode = pl8.ItemCode
left join pl8r
on tbx.itemCode = pl8r.itemCode