Writing A Subquery with Left and Where

Question

I am trying to make a new column which will take the first letter from the column and then filter the ones that have letter A

ChatGPT totally give me another way but I just want to understand what is wrong with my logic

Select Code,  
FROM WIR  
Where Code = 'A'  
(Select Subject,  
Zone_Location,  
Left (Final_Status,1) As Code  
from `poised-shift-415007.WIR.WIR_CEC` As WIR)'''

I thought that what between brackets will run first then from the result it will select new column (code) from the table that I just named WIR then put a condition just to show what has letter A. I am sure I am missing some fundamentals here but my experience just from google certificate I am a construction management professional.

What I am trying to understand how can I run a query on the new result after running a previous query

Chatgpt gave me the code that I need but not in a way that I want where I can run more queries on results from previous queries

  SELECT 
    YourColumn,   
    LEFT(YourColumn, 1) AS FirstLetter  
FROM   
    YourTable  
WHERE   
    LEFT(YourColumn, 1) = 'A';