0

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.

1 Answer 1

5

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
  1. 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)
1
  • I just had to comment that this was a well explained and thorough answer! Thanks so much for sharing. Commented May 11, 2020 at 14:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .