Post reply

Left outer join + CTE

  • December 28, 2022 at 6:42 pm

    #4131210

    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?

    Issue1

     

    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

  • December 28, 2022 at 6:48 pm

    #4131211

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

  • December 28, 2022 at 7:42 pm

    #4131223

    frederico_fonseca thank you!!

  • January 4, 2023 at 1:33 am

    This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply