How to selecting value from another data extension in select statement in SFMC

Question

I have a DE extension where I have saved 1 record by querying:

Select top 1
JobID,
EmailName,
CreatedDate,
JobType,
JobStatus
from _job

Output is saved in data extension: DataView_Job_DE

Now, I am writing another query where I want to get value from DataView_Job_DE in my select statement like this:

SELECT
CustomerID,
CustomerEmail,
'Orange' as FavColor,
(SELECT TOP 1 EmailName FROM ENT.DataView_Job_DE) AS EmailName
FROM Customer_DE

The above query is throwing an error:

An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax.

How can I resolve this error? Any advise?

Answer 1

Surely there is a more direct way to this - but to stick with your approach to first have one record isolated, here's how it could be achieved.

number your first query's result:

Select top 1
JobID
,EmailName
,CreatedDate
,JobType
,JobStatus
,Row_Number() OVER (Partition by jobid ORDER BY Jobid) as rowNumber
from _job

...returning something like this to your DataView_Job_DE (which needs an extra field), which still has one record, but the record is numbered:

JobID EmailName CreatedDate JobType JobStatus rowNumber

12345 / Test / Nov 22 2022 3:22AM / MULTIPLE_SEND / New / 1

Then select from your customers, joining the other table to get the row with rowNumber being 1.

SELECT
CustomerId, 
'Orange' as FavColor,
pre.EmailName AS EmailName
FROM Customer_DE c

INNER JOIN 

[result of first Query] pre
on pre.rowNumber = 1