Hi Kurt.
I hope you had a nice weekend. I read up on the cte and tried to apply your suggestions over the weekend. I keep getting compile errors on the ';with', and the ending, 'O_Orders_CTE (co_num,' Any suggestions?
/***************** START CTE *********/
;with C_ORDERS_CTE (
co_num,
co_line,
item,
cust_num,
cust_seq,
qty_ordered,
qty_ready,
qty_shipped,
promise_date,
ship_date,
order_date,
stat,
Uf_PJ_AllocatedDate, ---as AvailableDate,
@Pattern, ---as pattern,
@Room, ---as room,
@tag)
AS
(
SELECT coitem.co_num,
coitem.co_line,
coitem.item,
coitem.cust_num,
coitem.cust_seq,
qty_ordered,
qty_ready,
qty_shipped,
promise_date,
coitem.ship_date,
co.order_date,
coitem.stat,
coitem.Uf_PJ_AllocatedDate as AvailableDate,
@Pattern as pattern,
@Room as room,
FROM coitem
inner join co on co.co_num = coitem.co_num
--inner join customer on customer.cust_num = coitem.cust_num and customer.cust_seq = coitem.cust_seq
Where (coitem.promise_date >= @StartInvoiceDate and coitem.promise_date <=
@EndInvoiceDate) and (substring (coitem.item,1,3) = @Pattern and
SUBSTRING (coitem.item, 6,1) = @Room) and coitem.stat = 'C'
), O_Orders_CTE (co_num,
co_line,
coitem.item,
coitem.cust_num,
coitem.cust_seq,
qty_ordered,
qty_ready,
qty_shipped,
promise_date,
coitem.ship_date,
co.order_date,
coitem.stat,
coitem.Uf_PJ_AllocatedDate as AvailableDate,
@Pattern as pattern,
@Room as room,
)
AS
(
SELECT coitem.co_num,
coitem.co_line,
coitem.item,
coitem.cust_num,
coitem.cust_seq,
qty_ordered,
qty_ready,
qty_shipped,
promise_date,
coitem.ship_date,
co.order_date,
coitem.stat,
coitem.Uf_PJ_AllocatedDate as AvailableDate,
@Pattern as pattern,
@Room as room,
FROM coitem
inner join co on co.co_num = coitem.co_num
--inner join customer on customer.cust_num = coitem.cust_num and customer.cust_seq = coitem.cust_seq
Where (coitem.promise_date >= @StartInvoiceDate and coitem.promise_date <=
@EndInvoiceDate) and (substring (coitem.item,1,3) = @Pattern and
SUBSTRING (coitem.item, 6,1) = @Room) and coitem.stat = 'O'
)