RQWHERE is probably the most useful function filter in Murex. It basically lets you filter based on SQL statements. This gives you complete freedom as to what criteria you want to use in order to choose a certain results population.
RQWHERE is also a pain in the neck to use the first times (and the times after!) as it is purely based on the datamodel (which as we discussed before is not documented). Based on the datamodel means that you need to understand how the data is structured and how the different tables are related together. It also means that if the datamodel changes, the filter will need to be adapted.
So if you tick the following boxes:
– Know how to make simple SQL statements
– Know how the data you need is organized
– Can’t make the filter you need with existing functionalities
RQWHERE is for you!
Basically RQWHERE calls for 2 arguments, the first one being a string and the actual select statement you wish to use and the second one being also a string but which I’ve never ever used. If someone recommends it, feel free to comment below 🙂
The way you structure your select statement is a bit up to you and while I can’t help you with it (your prefered Murex consultant can and will though 🙂 ) there is one very neat thing that can take your filter from being good to being very good: parser functions.
Indeed, you can enrich your string statement with interactive variables or parser functions. This means that the filter can prompt the enduser for a string/number/date prior to being executed which will then be used when building the string that will be sent to the function.
For example, you want to retrieve with a number higher than x. Your first argument will be something like “[start of your statement] where NUMBER>”+<interactive numeral variable>+”[end of your statement”. If you’re using strings or dates make sure that you use single and double quotes correctly: “[start of your statement] where STRING='”+<interactive string variable>+”‘[end of your statement]” (yes that STRING=<single quote> <double quote>). The built statement string will then be “[start of your statement] where STRING='<input variable>’ [end of your statement]”. A perfectly built RQWHERE!
How to debug RQWHERE?
Sometimes your RQWHERE will not work as expected. Either it will return nothing with no error or sometimes it will spam errors. If the later, Murex will show you the statements which are failing and you can fix your RQWHERE by looking at the final result.
If there is no error, then turn on a DB trace (on screen or in logs) and check the built SQL statement if it is the one you want or not.
Questions, comments, feel free!