cancel
Showing results for 
Search instead for 
Did you mean: 

using "with" clause in sab query generator

dov_luft3
Explorer
0 Kudos

Hello

How can I use the following in query generator:

/* select * from AITM T1 */
DECLARE @Date1 DATETIME
DECLARE @SaleItem INT
DECLARE @MinQnty Numeric (19,6)
SET @Date1 = /* T1.UpdateDate*/ '[%0]'
SET @SaleItem =  /* T1.U_SaleIetm*/ '[%1]'
SET @MinQnty =  /* T1.OnHand*/ '[%2]'

with tb (itemCode,UpdateDate,U_SaleIetm,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

etc....
View Entire Topic
dov_luft3
Explorer
0 Kudos

Thanks Maya,

If I execute the query with out passing parameters the query excutes,

but when I declare parameters like :

/* select * from AITM T1 */


DECLARE @Date1 DATETIME
SET @Date1 = /* T1.UpdateDate*/ '[%0]'


with tb 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
)
select * from tb where tb.UpdateDate<=@date1


I am getting errorr:

"[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared."

Thanks

Dov