DX Infrastructure Manager

Expand all | Collapse all

SQL: Convert a numeric value into a string?

Jump to Best Answer
  • 1.  SQL: Convert a numeric value into a string?

    Posted 06-14-2017 07:50 AM

    How can I convert this numeric value (which should be '4'), into a text that says 'Active' ?

     

    SQL:

    SELECT s.samplevalue

    FROM s_Qos_data d

    JOIN s_qos_snapshot s
    on d.table_id = s.table_id

    WHERE d.source = 'pulfcsw193'
    AND d.target = 'pulfcsw193'
    AND d.qos = 'QOS_SNMP_VARIABLE_PORT_15__193_PORTHWSTATE'

     

    Possible values are:

    • unknown (1)

    • failed (2) Port failed diagnostics (port_flt_state).

    • bypassed (3) FCAL bypass, loop only (not used).

    • active (4) Connected to a device (light and sync are present).

    • loopback (5) Port in ext loopback (loopback state).

    • txfault (6) Transmitter fault (bad GBIC).

    • noMedia (7) Media not installed (GBIC removed).

    • linkDown (8) Waiting for activity—rx sync (light with no sync).



  • 2.  Re: SQL: Convert a numeric value into a string?

    Posted 06-14-2017 08:13 AM

    QOS is stored int he database as numeric values.

    We do not store QOS as text so I am not sure how you would accomplish what you are looking to in the current design of the software.

     

    You would ise an if then else statement in your SQL query to check the value of the field and return it as 1 for active or 0 for not active.

     

    in your custom dashboard you could then display this as text for active or not.

     

    Or might be easier in your custom dashboard to just check the value and display the test you want.



  • 3.  Re: SQL: Convert a numeric value into a string?

    Posted 06-14-2017 08:19 AM

    Hi Gene,

     

    It's the SQL-query that I want to change so it can convert the numeric value into a text string.

    So if the value is '4' i want to receive the value 'Active' and if the value is '7' i want to receive the value 'noMedia'.

     

    If I do this in the dashboard without the convert, I would need a help text on the side, which says what status the current number is.



  • 4.  Re: SQL: Convert a numeric value into a string?

    Posted 06-14-2017 08:23 AM

    so lets clarify, where are you running this query?

    If you are using the sqlserver probe or other probe there is no way to get this into the database to be displayed on a remote as the database sample value is a numeric field

     

    below is a link on how to use an if then else or case statement in a SQL select query.

    sql server - How do I perform an IF...THEN in an SQL SELECT? - Stack Overflow 

     

    This same logic could be used in a dashboard



  • 5.  Re: SQL: Convert a numeric value into a string?

    Posted 06-14-2017 09:28 AM

    I'm running this on the dashboard with a text-widget. So I want that text to say 'Active' if the value is '4', and 'noMedia' if the value is '7', and so on.

    So I guess I need the numeric value to turn in to a text string somehow.



  • 6.  Re: SQL: Convert a numeric value into a string?

    Posted 06-14-2017 02:22 PM

    CASE (Transact-SQL) | Microsoft Docs 

     

    About half way down is an example of exactly (at least in my understanding of your ask) what you want to do.

     

    -Garin



  • 7.  Re: SQL: Convert a numeric value into a string?
    Best Answer

    Posted 06-14-2017 02:58 PM

    Try this:

     

    SELECT case s.samplevalue
    when 1 then 'unknown (1)'
    when 2 then 'failed (2) Port failed diagnostics (port_flt_state).'
    when 3 then 'bypassed (3) FCAL bypass, loop only (not used).'
    when 4 then 'active (4) Connected to a device (light and sync are present).'
    when 5 then 'loopback (5) Port in ext loopback (loopback state).'
    when 6 then 'txfault (6) Transmitter fault (bad GBIC).'
    when 7 then 'noMedia (7) Media not installed (GBIC removed).'
    when 8 then 'linkDown (8) Waiting for activity—rx sync (light with no sync).'
    end as ErrorText

    FROM s_Qos_data d
    JOIN s_qos_snapshot s
    on d.table_id = s.table_id
    WHERE d.source = 'pulfcsw193'
    AND d.target = 'pulfcsw193'
    AND d.qos = 'QOS_SNMP_VARIABLE_PORT_15__193_PORTHWSTATE'



  • 8.  Re: SQL: Convert a numeric value into a string?

    Posted 06-15-2017 08:07 AM

    Garin, my man!! Thank you  That's exactly what I was looking for!