Translate states to readable names

When extending the software with custom queries or reports, it is sometimes needed to translate values in the database to readable states.

Nerd alert, not for all audiences   

The query below to get the states of automatic emails below was hard to process

2020-04-11 08_03_36-IBExpert - [SQL Editor __ 1 __ Fast (SQL Dialect 3)]

2020-04-11 08_04_34-IBExpert - [SQL Editor __ 1 __ Fast (SQL Dialect 3)]

What does it mean, state 2 or 4…  Even when you would make a nice mapping of these values yourself in Excel or another tool, we might change the values or add new ones.  This would mean you would no longer be able to make clear queries.

In version you’ll be able to use new functions that resolve these values to readable text. Use the functions CS_ENUM_AS_TEXT and CS_ENUM_AS_ENUM to translate between values and text.

2020-04-11 08_08_34-IBExpert - [SQL Editor __ 1 __ Fast (SQL Dialect 3)]

2020-04-11 08_08_49-IBExpert - [SQL Editor __ 1 __ Fast (SQL Dialect 3)]

The biggest advantage of this approach is that by writing it once you have a readable representation and you are ready for changes in the future.  You’ll never have to touch this query again and you’ll always have a nice textual representation of the state.

To list all types available you can use the function cs_enum_types

select * from cs_enum_types()

To get all values and names of a state us cs_enum_values

select * from cs_enum_values('UnitKind')

2020-04-11 08_37_43-IBExpert - [SQL Editor __ 1 __ Fast (SQL Dialect 3)]



SMS-Timing and BMI Leisure are brands of B&MI NV. We offer an all-in-one reliable software suite, covering all aspects of your karting & entertainment business, including strategic advising, and marketing. We help you deliver fun, relaxing, and entertaining experiences to your visitors that will keep them coming back for more!

Our system includes a comprehensive POS system, CRM management, online booking & website shop for family entertainment centers all over the globe.

More information?

Check out our website: BMI Leisure and SMS-Timing

Need more help with this feature? Click here:  24/7 Support service

Check out other updated features here: devblogs 

Remember also to check our Marketing blogs for some inspiration! 

This site uses Akismet to reduce spam. Learn how your comment data is processed.