The SQL Integration currently supports use of the value_template configuration variable to allow definition of a template to extract a value from the payload.
However, it does not currently support the use of a query_template configuration variable to allow for a dynamically defined SQL query.
The use case I have is to be able to lookup a friendly text for error codes. Normally, I would use a template sensor to achieve this, but there are two concerns with doing this:
- We embed data in our configuration files. That isnāt too bad, but it does mean that a restart is required if a software update on your device has resulted in new or changed error codes and/or descriptions. Itās like a form of hard coding.
- When the number of error codes is large, the resulting configuration will become large and somewhat unwieldy and difficult to maintain. In my use case there are 229 possible error codes.
Hence, Iām wondering if it would be possible to add template support to the query configuration variable in the SQL integration please. I think it would be very powerful and a valuable capability.
An example of the type of configuration that would then be allowed (no matter how many trip codes exist) is below:
- platform: sql
db_url: sqlite:////local_db/local.db
queries:
- name: Trip 0 Text
query: "SELECT * FROM lookup WHERE Code = {{states('sensor.dx_trip_0')}};"
column: 'Friendly Text'
To achieve the same outcome through using a template sensor would result in configuration like this:
dx_error_status_text:
friendly_name: "Trip 0 Text"
entity_id:
- sensor.dx_trip_0
value_template: >-
{% if is_state('sensor.dx_trip_0', '0') %}
No error
{% elif is_state('sensor.dx_trip_0', '10') %}
Solutions Module operating system error
{% elif is_state('sensor.dx_trip_0', '20') %}
Real-time clock error
{% elif is_state('sensor.dx_trip_0', '30') %}
IP54 cooling fan power supply fault
{% elif is_state('sensor.dx_trip_0', '40') %}
Drive to Solutions Module interface error
{% elif is_state('sensor.dx_trip_0', '50') %}
Building Automation Network error
{% elif is_state('sensor.dx_trip_0', '74') %}
Solutions Module PCB over-temperature
{% else %}
Unknown
{% endif %}
Please consider this and vote for it if you see value.
