[LCA] Lateral column alias ValidationError

[vijaypavann-db]

SQL failed while validating...

ValidationError(file_name='usr_actv.sql', exception='Unsupported operation found in file usr_actv.sql Needs manual review of transpiled query. Lateral column aliases `dvc_key` found in window expressions.')

delete from db.schema.table
where p_date=iff(date_part('hour',date_trunc('hour', to_timestamp('$[yyyy-MM-dd HH:mm:ss]')))=0,dateadd(day,-1,to_date('$[yyyy-MM-dd HH:mm:ss]')),to_date('$[yyyy-MM-dd HH:mm:ss]'));

insert into db.schema.table
SELECT  tt.col1
        , try_cast(iff(nvl(tt.extra_props:lvl::string,'')='','0',tt.extra_props:lvl::string) as int) as lvl
        , tt.dvc_key
        , tt.extra_props
  FROM (
    SELECT COL1, EXTRA_PROPS,
    ,md5(id_col||channel_col||dvc_id) as **dvc_key**
    ,max(actv_tm) OVER (PARTITION BY id_col, **dvc_key** ORDER BY evnt_srvr_tm desc) AS actv_tm
    from src_table where rn=1
  )tt;

[ganeshdogiparthi-db]

I am also facing a similar issue for another customer. Below is the validation error.

ValidationError(file_name='/Users/XX/DB/XX/XX.sql', exception='Unsupported operation found in file /Users/XX/DB/XX/XX.sql. Needs manual review of transpiled query. Lateral column aliasesevent, dayfound in where clause. Lateral column aliasestsfound in window expressions.')

My request is please add the line number for the validation error along with the message. Since my transpiled file has 50K lines of code I am not able to identify the culprit SQL.

CC @vijaypavann-db @sundarshankar89 @bishwajit-db

[sundarshankar89]

@ganeshdogiparthi-db currently this generated warning without processing or executing the query, it will be difficult to surface run time errors with exact line numbers cc: @bishwajit-db

[bishwajit-db]

@vijaypavann-db LCA error has been resolved in PR #219 . @ganeshdogiparthi-db The error message enhancement will be tried under issue #218