ValueError: 'COLUMN_NAME' is not in list

[ghost]

Hi, While Parsing a query I'm getting a value error . For a column that is in the query. Can you please let me know the reason for this.

Error :

ValueError Traceback (most recent call last) in ----> 1 Parser(data).columns

~\Anaconda3\lib\site-packages\sql_metadata\parser.py in columns(self) 236 ) 237 continue --> 238 column = self._resolve_sub_queries(column) 239 self._add_to_columns_with_tables(token, column) 240 self._add_to_columns_subsection(

~\Anaconda3\lib\site-packages\sql_metadata\parser.py in _resolve_sub_queries(self, column) 798 column names as they appear in the query 799 """ --> 800 column = self._resolve_nested_query( 801 subquery_alias=column, 802 nested_queries_names=self.subqueries_names,

~\Anaconda3\lib\site-packages\sql_metadata\parser.py in _resolve_nested_query(subquery_alias, nested_queries_names, nested_queries, already_parsed) 850 if "*" in subparser.columns: 851 return column_name --> 852 raise exc # pragma: no cover 853 resolved_column = subparser.columns[column_index] 854 return [resolved_column]

~\Anaconda3\lib\site-packages\sql_metadata\parser.py in _resolve_nested_query(subquery_alias, nested_queries_names, nested_queries, already_parsed) 843 return subparser.columns 844 try: --> 845 column_index = [x.split(".")[-1] for x in subparser.columns].index( 846 column_name 847 )

ValueError: 'EMP_PARTY_PD_ID' is not in list

[collerek]

Can you post a query, otherwise it's hard to tell.

[ghost]

@collerek Did you get a chance to check the query?

[collerek]

I see that you use same subquery names for different subqueries.

Although technically possible for different level of nesting our parser keeps all subqueries definition by name regardless of the level, that means that one subquery definition is overwritten by another one, and in a result the columns are not found in the subquery definition (hence the error).

-- around line 1315
	) q --here
	INNER JOIN BDGDFDB.ADMIN.W_PFG_WK_WORKER_PARTY_EXTRACT w 	ON q.WORKER_PARTY_SK = w.WORKER_PARTY_SK
		              												AND q.CNTC_START_DT BETWEEN w.VALID_FROM_TS AND w.VALID_TO_TS
	INNER JOIN BDGDFDB.ADMIN.I_PFG_UTL_SK_PARTY party 			ON q.AA_PARTY_SK = party.AA_PARTY_SK
																	AND party.AA_PARTY_ID_TYPE_CD = 'AASU'
	WHERE JOB_CLASS_DESC LIKE 'Retirement Income Spec%'
		OR CNTC_START_DT < '2020-06-07' 

	GROUP BY 1,2,3,4,5,6,7,8,9,10
			,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27

		
) q --and here

We will see what we can do about this, but in the meantime please use distinct names for subqueries and it should work fine.

[hustontrevor]

I'm seeing this problem with this query. It looks like there is an issue with case sensitivity. If I uppercase the whole thing, then it works.

select a11.OPERATING_UNIT OPERATING_UNIT from MY_PS_LEDGER a11 join (select mo_id, ytd_mo_id from RLT_YTD_MO_T where ytd_mo_id not like '%00' ) a15 on (a11.MO_ID = a15.YTD_MO_ID)