Cannot join two table after using AS in SQL Anywhere

Question

I am relatively new to SQL (in SQL Anywhere). I tried to join two tables but always get an error

SQL Anywhere Error -131: Syntax error near 'left' on line 17

Is there any mistake for my left join? Thank you

(
SELECT 
    Text_ID, Asdate, BigUnitCode AS Big_Code, 
    (BigUnitCode +'@'+ BigUnitName) as Main_ID, Method, xt.[Comment], 
    SUM(FIvalue) AS CurrentValue 
FROM
    (SELECT * FROM dmf_dbo.main1
     WHERE Text_ID = 440168) AS xt
GROUP BY 
    Text_ID, Asdate, BigUnitCode, Method, xt.[Comment], BigUnitName
) AS gz
LEFT JOIN
    (SELECT
         (BigUnitCode +'@'+ BigUnitName) AS Main_ID, BigDep_code AS group_id, 
         BigDep_name AS group_name, SmallUnit_code AS sub_id, 
         SmallUnit_name AS sub_name 
     FROM
         dmf_dbo.main2 
     WHERE 
         ASOFDATE = '2020-07-11'
     GROUP BY 
         SmallUnit_code, SmallUnit_name) AS ho ON gz.Main_ID = ho.Main_ID

Answer 1

A little tricky to do without seeing your data or what your expected output looks like so I take no responsibility for your output, but try this. And remember, it's often easier to see what's going on if you format your code and use (something approaching) correct indentation.

Let's take a look at your first statemnent.

(
SELECT 
    Text_ID
    ,Asdate
    ,BigUnitCode AS Big_Code
    ,(BigUnitCode +'@'+ BigUnitName) as Main_ID 
    ,Method
    ,xt.[Comment]
      FROM
          (
           SELECT * FROM dmf_dbo.main1
           WHERE Text_ID = 440168)
          )
) AS gz

You will notice the removal of one of your misplaced parenthses (brackets) and removal of the GROUP BY and SUM, we'll place these within the outermost query in the very last step. This is because you are not using SUM in your second query so it's hard to forsee how the resultant dataset would've actually worked, subsequent to the JOIN.

Now let's look at the second query. I'm not sure why you are using GROUP BY here as there appears to be no aggregation. Unless you are trying to remove duplicates (as in DISTINCT), there is no need to do this. Since you have not provided context, I will assume you do not wish to do this.

  (
     SELECT
          (BigUnitCode +'@'+ BigUnitName) AS Main_ID
          ,BigDep_code AS group_id
          ,BigDep_name AS group_name
          ,SmallUnit_code AS sub_id
          ,SmallUnit_name AS sub_name 
         FROM
             dmf_dbo.main2 
         WHERE ASOFDATE = '2020-07-11'
 ) AS ho  

Now, let's put it all together.

    SELECT 
    gz.Text_ID
    ,gz.Asdate
    ,gz.Big_Code
    ,gz.Main_ID
    ,gz.Method
    ,gz.Comment 
    ,ho.Main_ID
    ,ho.group_id
    ,ho.group_name
    ,ho.sub_id
    ,ho.sub_name
    ,SUM(gz.FIvalue) AS CurrentValue 
      FROM 
          ( 
           (SELECT 
           Text_ID
           ,Asdate
           ,BigUnitCode AS Big_Code
           ,(BigUnitCode +'@'+ BigUnitName) as Main_ID 
           ,Method
           ,xt.[Comment]
             FROM
                (
                 SELECT * FROM dmf_dbo.main1
                 WHERE Text_ID = 440168)
                 )
         ) AS gz
          
           LEFT JOIN 
         
         (
           SELECT
           (BigUnitCode +'@'+ BigUnitName) AS Main_ID
           ,BigDep_code AS group_id
           ,BigDep_name AS group_name
           ,SmallUnit_code AS sub_id
           ,SmallUnit_name AS sub_name 
           FROM
           dmf_dbo.main2 
           WHERE ASOFDATE = '2020-07-11'
         ) AS ho  
        
         ON gz.Main_ID = ho.Main_ID
      
         )

GROUP BY 
  gz.Text_ID
    ,gz.Asdate
    ,gz.Big_Code
    ,gz.Main_ID
    ,gz.Method
    ,gz.Comment 
    ,ho.Main_ID
    ,ho.group_id
    ,ho.group_name
    ,ho.sub_id
    ,ho.sub_name