topic Re: WHERE function with multiple values in App Development

WHERE function with multiple values

mattphillip — Tue, 30 Jan 2024 09:38:17 GMT

Hoping someone can help. I've included a WHERE clause I'm trying to add to our ETL staging app for one of our student number reporting apps. Code is included below. Basically I'm trying to exclude students with a LVL_DETAILED value of 'F-FIFER' from the ACAD_SESSION between 2017/18-2022/23 (I'd like to retain all other students in those years), BUT I'd also like to exclude students with a SCHOOL value of AbLE and S5 across all years.

WHERE ([LVL_DETAILED]='F-FIFER' and match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23'))
or not match(SCHOOL,'AbLE','S5');

Currently the code excludes all students from with an ACAD_SESSION between 2017/18-2022/23.

Any help would be most appreciated.

Thank you.

Matt

Re: WHERE function with multiple values

Anil_Babu_Samineni — Tue, 30 Jan 2024 09:58:35 GMT

@mattphillip I would prefer like below for last condition.

AND match(SCHOOL,'AbLE','S5'); // Note, Here I removed "OR" and "Not"

Re: WHERE function with multiple values

Nagaraju_KCS — Tue, 30 Jan 2024 10:01:24 GMT

may be this..

WHERE [LVL_DETAILED]='F-FIFER' and (match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23')
and not match(SCHOOL,'AbLE','S5'));

Re: WHERE function with multiple values

mattphillip — Tue, 30 Jan 2024 10:15:21 GMT

Hi Anil,

Thanks for the suggestion. Unfortunately that didn't load any records to the front end. I realise my script quoted is wrong as I'm trying to exclude these records not include them. It should read:

WHERE (not match([LVL_DETAILED]='F-FIFER') and not match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23'))
or not match(SCHOOL,'AbLE','S5');

But this script filters out all the years between 2017/18 and 2022/23 for all students and not just for those students with a LVL_DETAILED value of 'F-FIFER'.

Re: WHERE function with multiple values

mattphillip — Tue, 30 Jan 2024 10:20:45 GMT

Thanks for the reply. Unfortunately that didn't work either. My current attempt is the below:

WHERE (not match([LVL_DETAILED],'F-FIFER') and not match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23'))
or not match(SCHOOL,'AbLE','S5');

However, I still have the problem that its excluding those five years for all students.

Re: WHERE function with multiple values

marcus_sommer — Wed, 31 Jan 2024 13:11:30 GMT

It's not really clear for me what do you want to filter but chaining multiple conditions with extra NOT statements is rather not expedient - probably possible but more complex as sensible. Simpler are often approaches like:

rangesum(
sign(match([LVL_DETAILED],'F-FIFER')), sign(match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23')),
sign(match(SCHOOL,'AbLE','S5'))) >= CheckValue

or maybe

(sign(match([LVL_DETAILED],'F-FIFER')) * sign(match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23'))) +
sign(match(SCHOOL,'AbLE','S5'))