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
LoHa
Active Contributor
0 Kudos

Hi Dov,

I think the secret is you have to use a ; before of the first with.
The following with need ,

e.g.

/*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.....) , tb1 (ItemCode,...
Johan_H
Active Contributor
0 Kudos

I am pretty sure that is what I said about a month ago...