Spark with Scala/Lobby

Hi I am trying to copy a query into spark sql. Does the syntax below make sense. I am just constantly getting an error and can't fix it. I took in two data tables from a database and joined them together in one and created a temp view referred to as temp... so the from temp is the temp view that I created

SELECT
case when RESULT_CD is not null then substr(RESULT_CD,1,1)
else
case when RESULT_RSN_CD is not null then RESULT_RSN_CD else null end
end
as LOSSCD, LGL_SYS_ID as LGL_SYS_ID
FROM (
SELECT
LGL_SYS_ID,
MATTER_SYS_ID,
row_number() OVER (PARTITION BY LGL_SYS_ID ORDER BY
(cast(substr(MATTER_SYS_ID,instr(MATTER_SYS_ID,':')1)+) DESC) as int) rank ,
FTRSYS_ID, RESULT_RESLTN_CD, RESULT_RSN_CD
FROM TEMP
WHERE HCO_MATTER.LGL_MATTER_SYS_ID = HCO_FTR.FTR_SYS_ID
ORDER BY LGL_MATTER_HCS_SYS_ID desc)
WHERE rank=1 --