"Old style JOIN (ANSI JOINs) syntax" error in a simple query without subqueries and joins

Question

Validating the following query:

SELECT 
    GetDate() as Valid_From,
    'Lorem Ipsum' as Filler
FROM 
    SampleSourceDE

shows this error:

An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax. , is not a known data extension or system data view. You can only query existing data extensions or system data views.

Answer 1

The SQL validation in Marketing Cloud does have a strange quirk that it seems to treat underscores (_) near the From string as spaces - this causes the query to interpret the From part of Valid_From as the command that's responsible for the source of Data.

Since it's not the last column selected, the system tries to look for a data extension named ,.

To confirm this, just reverse the order of desired columns to:

SELECT 
    'Lorem Ipsum' as Filler,
    GetDate() as Valid_From
FROM 
    SampleSourceDE

The error will now be:

Errors: FROM is not a known data extension or system data view. You can only query existing data extensions or system data views.

The following names will not pass validation when selected without square brackets:

• Valid_From_Date - _Date will be treated as a source
• From_Date - _Date will be treated as a source
• Valid_From - as described above the very next string will be interpreted as the source (either , or FROM)
• Valid_____From - same as above, it doesn't matter how many underscores you have

---

Solutions:

1. Wrap Valid_From in square brackets. This query will pass validation:

SELECT 
    GetDate() as [Valid_From],
    'Lorem Ipsum' as Filler
FROM 
    SampleSourceDE

2. Change the column name, so that there's no standalone From string fragment surrounded by underscores, for example ValidationFromDate or Valid_FromDate _{(if you're a monster)}