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 6.20.05.04 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 all-in-one reliable software suite, from which you can cover all aspects of your karting & entertainment business, strategic advising, and marketing. By giving you these tools you can deliver your visitors fun, relaxing, and entertaining experiences that will keep them coming back for more!

POS system, CRM management, booking scheduling and online booking/ website shop for family entertainment centers.

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.

en English
X