Left outer join + CTE
-
Hello everyone,
I have a situation where I am trying to select * from one table and make a left outer join on a query that starts from CTE. Basic syntax below:
Server doesn't want to accept LEFT OUTER JOIN on a CTE. What am I doing wrong?
SELECT *
FROM table fa
LEFT OUTER JOIN (
;with t AS (
SELECT CASE
WHEN S1UPSP LIKE '%RIOUS%' THEN '0'
WHEN CHARINDEX(char(32), LTRIM(RTRIM(S1UPSP)), 1)>0 THEN '0'
WHEN LEN(S1UPSP)>8 THEN RIGHT(S1UPSP, 8)
WHEN S1UPSP = '' THEN '0'
ELSE S1UPSP
END AS AR_LOAD, S1SDAT, S1AGCY ,
DATEADD( DAY , 7 - DATEPART(WEEKDAY, CAST(S1SDAT AS CHAR(12))), CAST (CAST(S1SDAT AS CHAR(12)) AS DATE )) AS 'WEEK'
, S1CHAN, S1WHS#, S1SCDR
, sum(S1CTN#) as S1CTN# , sum(s1WGTA) as s1WGTA
, S1SCDA
FROM shiptable
WHERE [S1SDAT] between @Last5WeekDATE and @LWDATE
AND S1UPSP=' 1577955'
GROUP BY S1UPSP, S1SDAT, S1AGCY, S1CHAN, S1WHS#, S1SCDR, s1ctn#, s1WGTA, S1SCDR, S1SCDA
)
SELECT AR_LOAD, S1SDAT, S1AGCY, WEEK, S1CHAN, S1WHS#, S1SCDR, sum(S1CTN#) as S1CTN# , sum(s1WGTA) as s1WGTA
, S1SCDA
FROM t -
CTE is defined BEFORE any of the remaining sql.
with cte1 as (select ...), cte2 as (select ...), cte3 as (select ...)
select *
from table f
inner join cte1 on ...
left outer join cte2 on ...
left outer join cte3 on ... -
frederico_fonseca thank you!!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply