Error : Unable to create temporary data extension: Field name cannot contain the character ,. Field name cannot contain the character

Question

SELECT 
    LEFT(SUBSTRING(j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1, CHARINDEX('_', j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1) - CHARINDEX('_', j.Journey_Name) - 1), LEN(SUBSTRING(j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1, CHARINDEX('_', j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1) - CHARINDEX('_', j.Journey_Name) - 1)) AS Brand_Name,
    j.Journey_Name,
    j.Journey_Version,
    j.Journey_Status,
    COUNT(s.SubscriberKey) AS Email_Sends,
    GETDATE() AS Last_Modified,
    s.SubscriberKey
FROM 
    (
        SELECT 
            Journey_Name,
            Journey_Version,
            Journey_Status
        FROM 
            _Journey 
        WHERE 
            DATEPART(month, Last_Modified) = DATEPART(month, DATEADD(month, -1, GETDATE())) 
            AND DATEPART(year, Last_Modified) = DATEPART(year, DATEADD(month, -1, GETDATE()))
    ) AS j
LEFT JOIN 
    (
        SELECT 
            JourneyID,
            SubscriberKey
        FROM 
            _Sent 
        WHERE 
            DATEPART(month, EventDate) = DATEPART(month, DATEADD(month, -1, GETDATE())) 
            AND DATEPART(year, EventDate) = DATEPART(year, DATEADD(month, -1, GETDATE()))
    ) AS s 
ON 
    j.JourneyID = s.JourneyID 
GROUP BY 
    LEFT(SUBSTRING(j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1, CHARINDEX('_', j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1) - CHARINDEX('_', j.Journey_Name) - 1)),
    j.Journey_Name,
    j.Journey_Version,
    j.Journey_Status,
    s.SubscriberKey;

While running above query I am getting error :Unable to create temporary data extension: Field name cannot contain the character ,. Field name cannot contain the character ..

Answer 1

Try to indent your code more, and split your code to isolate errors.

I am not gonna do it all for you, but the first half of the code has a couple syntax errors already, no use working off broken parts.

comments inline, remove them if you want to test in query studio. With these changes, the first half of your code runs;

SELECT 
LEFT(
    SUBSTRING(
        j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1
        , CHARINDEX('_', j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1) - CHARINDEX('_', j.Journey_Name) - 1
    )
    ,LEN(
        SUBSTRING(j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1
            , CHARINDEX('_', j.Journey_Name, CHARINDEX('_', j.Journey_Name) + 1) - CHARINDEX('_', j.Journey_Name) - 1
        )
    ) 
) AS Brand_Name /* added missing closing bracket */ 
,j.Journey_Name
,j.Journey_Version
,j.Journey_Status
/* ,COUNT(s.SubscriberKey) AS Email_Sends */ 
/*commented out for future reference, put back in once you add the s table again  */
,GETDATE() AS Last_Modified
/*,s.SubscriberKey */ 
/* commented out for future reference, put back in once you add the s table again */
FROM 
    (
        SELECT 
            jdv.JourneyName AS Journey_Name, /*removed underscore https://help.salesforce.com/s/articleView?id=sf.mc_as_data_view_journey.htm&type=5*/
            jdv.VersionID AS Journey_Version, /*changed fieldname https://help.salesforce.com/s/articleView?id=sf.mc_as_data_view_journey.htm&type=5*/
            jdv.JourneyStatus AS Journey_Status /*removed underscore https://help.salesforce.com/s/articleView?id=sf.mc_as_data_view_journey.htm&type=5 */
        FROM 
            [_Journey] jdv
        WHERE 
            DATEPART(month, GETDATE()) = DATEPART(month, DATEADD(month, -1, GETDATE())) /*replaced Last_Modified with GETDATE() */
            AND DATEPART(year, GETDATE()) = DATEPART(year, DATEADD(month, -1, GETDATE())) /*replaced Last_Modified with GETDATE() */
    ) j