Here’s my contribution to Joel Kallman Day 2022.
The picture is from the last time I met Joel. It was taken at ilOUG Tech Days 2020, just before an invigorating keynote by Joel.
Four years ago, as part of ODC Appreciation Day 2018 (as the annual Oracle community appreciation day was called that year), I wrote about Pattern Matching in SQL. That post was not a technical one.
This time I decided to return to the same topic, with a technical post.
I recorded a video that explains Pattern Matching by analyzing the winners of the Eurovision Song Contest over the years.
Thank you for taking your time and explaining this, it is very helpful. When I run this following query on 21c (Oracle Autonomous Cloud) I am getting an error.
— Multiple Variables
select *
from (select *
from euro_song_contest_winners
where year between 1990 and 2000
)
match_recognize (
order by year
all rows per match
pattern (i ln uk?)
define
i as winner like ‘I%’,
ln as length(song) >= 18,
uk as host = ‘United Kingdom’
);
Missing IN or OUT parameter at index:: 1
I tried in another database as well, with the same error. Could you please point me in the right direction?
Thanks
Arun, what is your client? SQL Developer? Which version?
I have tried it on SQLcl: Release 22.3 and SQL Developer Version 22.2.1.234.
The JDBC standard defines the ? character as a parameter marker, so in JDBC-based tools (like SQLcl and SQL Developer) you should escape question marks that are used as pattern quantifiers:
select * from (select * from euro_song_contest_winners where year between 1990 and 2000 ) match_recognize ( order by year all rows per match pattern (i ln uk{\?\}) define i as winner like 'I%', ln as length(song) >= 18, uk as host = 'United Kingdom' );See also the JDBC documentation.
[Thanks Jeff Smith for your help]
Thank you, appreciate the help.