Cognos SQL WITH clause generating syntax error

I have an SQL query I’m running in Cognos 11 and am trying to use a WITH clause but keep getting an error for incorrect syntax near “WITH”. I thought my org admins had disabled WITH but I have confirmed with them that it’s enabled and I cannot figure out what is wrong with my syntax. I’ve tried this in both Native SQL as well as Pass-through SQL.

I have this wrapped in a CASE that is otherwise working fine:

CASE 
WHEN SAPStreams.Stream_Type = 'INTELILINK' THEN (
    WITH IntelilinkValues AS (
    SELECT 
        LBD.Amount,
        LBD.Payment_Frequency,
        LBD.No_of_Periods
    FROM _SYS_BIC.LeasingRebooksDetails LBD 
    WHERE LBD.Contract_Number='D003498006')
)
ELSE NULL END

Pretty straight forward and I am more convinced there’s something wrong with my org’s implementation of Cognos than I am that the SQL is faulty. Hoping for some help from this community.

Answer

You can’t put a common table expression inside a query. Even if I correct your CTE:

CASE 
WHEN SAPStreams.Stream_Type = 'INTELILINK' THEN (
    WITH IntelilinkValues (
        Amount,
        Frequency,
        Periods
    )
    AS (
    SELECT 
        LBD.Amount,
        LBD.Payment_Frequency,
        LBD.No_of_Periods
    FROM _SYS_BIC.LeasingRebooksDetails LBD 
    WHERE LBD.Contract_Number='D003498006'
    )
    select Amount
    from IntelilinkValues
)
ELSE NULL END

…it still won’t work.

But this might work:

WITH IntelilinkValues (
    Amount,
    Frequency,
    Periods
)
AS (
SELECT 
    LBD.Amount,
    LBD.Payment_Frequency,
    LBD.No_of_Periods
FROM _SYS_BIC.LeasingRebooksDetails LBD 
WHERE LBD.Contract_Number='D003498006'
)
select Amount
into #ilv
from IntelilinkValues

select ss.somestuff, 
CASE 
WHEN SAPStreams.Stream_Type = 'INTELILINK' THEN (
    select Amount
    from #ilv
)
ELSE NULL END,
sot.someotherstuff

from SAPStreams ss
  inner join someothertable sot on sot.id = ss.id

where somecondition = 'true'

drop table #ilv