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.

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.