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....

Accepted Solutions (0)

Answers (4)

Answers (4)

Maya_Shiff
Product and Topic Expert
Product and Topic Expert

Hi Dov,

Thank you for your question.

I cannot validate your query, however the WITH clause is indeed supported by the Query Generator - It enables assigning a name to a subquery.

For example:

WITH A AS (SELECT * FROM OINV)

SELECT * FROM A WHERE "DocNum">100

UNION ALL

SELECT * FROM A OINV WHERE "DocType" = 'I'

A is treated as a view/table.

Regards,

Maya

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...

Johan_H
Active Contributor
0 Kudos

Hi Dov,

You could just use a simple subquery:

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

Regards,

Johan

Johan_H
Active Contributor
0 Kudos

or try this:

/* 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
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