Query Question (automation failed due to system error)

Question

THe code below is throwing an error "failed due to system error" - is it because of the subquery?

I double checked my DE's data types and length - looks good but this is throwing an error

SELECT user_Id,
value_2, 
(SELECT city from IGO_PROFILES  where city = 'riga' 
) as cityoption

from IGO_PROFILES 

Answer 1

That query studio error - "automation failed due to system error" shows up intermittently - Sounds dumb, but try the same query again.

Also you can just build a quick target DE and try it in automation studio, or without the DE at least validate the syntax.

That aside, looking at the query itself:

I would use a CASE statement if I understand your intention right: Select all users, but fill 'riga' into cityOption field in case 'riga' is in the city field.

SELECT 
user_Id
,value_2
,CASE WHEN city = 'riga' THEN
    city
ELSE
    ''
END AS cityOption

from IGO_PROFILES

Answer 2

If your only after the User_ID & Value_2 when City = 'Riga' try the below

SELECT 
  user_Id
, value_2 
, city
from IGO_PROFILES 
Where City = 'riga'