Insert filename and line number into stage for Json Format
Solution 1:
Missing the FROM in the SELECT portion of the COPY statement:
copy into PB_INVEOR_JSON_2(VAR,FILE_NAME, LINE_NUMBER)
from (
select $1,metadata$filename, metadata$file_row_number
from @investor_stage_s3/EMBRO_20220111/
)
pattern='.*Investor_.*.json'
FILE_FORMAT=(TYPE= 'JSON' strip_outer_array=true)
on_error=continue FORCE = TRUE