mismatched input 'from' expecting SQL

I am running a process on Spark which uses SQL for the most part. In one of the workflows I am getting the following error:

mismatched input 'from' expecting

The code is

 select a.ACCOUNT_IDENTIFIER,a.LAN_CD, a.BEST_CARD_NUMBER, decision_id, case when a.BEST_CARD_NUMBER = 1 then 'Y' else 'N' end as best_card_excl_flag from (select a.ACCOUNT_IDENTIFIER,a.LAN_CD, a. decision_id row_number() over (partition by CUST_GRP_MBRP_ID order by coalesce(BEST_CARD_RANK,999)) as BEST_CARD_NUMBER from Accounts_Inclusions_Exclusions_Flagged a) a 

I cannot figure out what the error is for the life of me

I've tried checking for comma errors or unexpected brackets but that doesn't seem to be the issue.

4 Answers

In the 4th line of you code, you just need to add a comma after a.decision_id, since row_number() over is a separate column/function.

P.S.: Try yo use indentation in nested select statements so you and your peers can understand the code easily. Cheers!

I think your issue is in the inner query. You have a space between a. and decision_id and you are missing a comma between decision_id and row_number().

Is this what you want?

 SELECT a.ACCOUNT_IDENTIFIER, a.LAN_CD, a.BEST_CARD_NUMBER, decision_id, CASE WHEN a.BEST_CARD_NUMBER = 1 THEN 'Y' ELSE 'N' END AS best_card_excl_flag FROM ( SELECT a.ACCOUNT_IDENTIFIER, a.LAN_CD, a.decision_id, row_number() OVER (partition BY CUST_GRP_MBRP_ID ORDER BY COALESCE(BEST_CARD_RANK,999)) AS BEST_CARD_NUMBER FROM Accounts_Inclusions_Exclusions_Flagged A ) A; 

I want to say this is just a syntax error. I think it is occurring at the end of the original query at the last FROM statement. Of course, I could be wrong.

Try the following:

'SELECT a.ACCOUNT_IDENTIFIER, a.LAN_CD, a.BEST_CARD_NUMBER, decision_id, CASE WHEN a.BEST_CARD_NUMBER = 1 THEN 'Y' ELSE 'N' END AS best_card_excl_flag FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CUST_GRP_MBRP ORDER BY COALESCE(BEST_CARD_RANK, 999)) AS BEST_CARD_NUMBER, a.ACCOUNT_IDENTIFIER, a.LAN_CD, a.decision_id FROM Accounts_Inclusions_Exclusions_Flagged) AS a;' 

I hope this helps some.

FROM should not be in the last sentence.