Spark SQL select a sub query into a struct

Question

I have an UDF accept a Row which is the row of sub query. My SQL shows below

SELECT
  main.id as id,
  my_udf(other) as other_content
FROM
  main_tabel AS main
LEFT JOIN other_table AS other ON main.id = other.id 

However, other is not a valid field in this SQL, and the SQL dose not work. My question is how can I get a Row of the other with all fields in other_table?

Answer 1

There are three problems.

SELECT * FROM
(
  SELECT ObjectName = SCHEMA_NAME(obj.[schema_id]) 
                      + '.' + obj.name, 
         [RowCount] = SUM(p.rows) 
  FROM        sys.objects AS obj
  INNER JOIN sys.partitions AS p 
    ON obj.[object_id] = p.[object_id] 
  WHERE       obj.type = 'U' 
  GROUP BY    obj.[schema_id], obj.name
) AS Result
WHERE Result.[RowCount] > 0;

1. When you move a query into a derived table, subquery, or CTE, all of the columns need to have names.
2. 'Alias' should be [Alias] since the former makes it look like a string and that form is deprecated in some contexts.
3. As Larnu pointed out, ROWCOUNT needs to be escaped in all spots, not just one.