CODEWITHSUNDEEP
sqlsql-server
Leonardo Silva
Leonardo Silva

Reputation: 79

display the same column twice with different results

I have a column that contains Enable and Disable values, which is associated by an event id, in this event table I have the user datatime, I would like to create a query and display the time the enabled event was triggered and in the same row. the time the disable was triggered

SELECT
    valv.valvOf, valv.cam,
    cod.descOf Habilita, cod.descof Desabilita
FROM
    {oj (aspersao.dbo.events events LEFT JOIN aspersao.dbo.valv valv ON events.valv_id = valv."id") LEFT JOIN aspersao.dbo.cod cod ON events.cod_id = cod.id}
WHERE
    valv.cam = '1'
and 
    Habilita = 'Habilitou valvula'
and 
    Desabilita = 'Desabilitou valvula'

Upvotes: 0

Views: 191

Answers (1)

Ross Presser
Ross Presser

Reputation: 6255

Even after correcting your code so that you are not trying to refer to columns by their aliases in the WHERE clause, your code is nonsense. No row will have a column equal to two different values.

Likely you want to pull in the table twice. I'm also getting rid of the ODBC specific curly brace syntax.

SELECT
    valv.valvOf, valv.cam,
    cod1.descOf AS Habilita, cod2.descof AS Desabilita
FROM
    aspersao.dbo.events AS events 
    LEFT JOIN aspersao.dbo.valv AS valv ON events.valv_id = valv."id"
    LEFT JOIN aspersao.dbo.cod AS cod1 
         ON events.cod_id = cod1.id     
         AND cod1.descOf = 'Habilitou valvula'
    LEFT JOIN aspersao.dbo.cod AS cod2
         ON events.cod_id = cod2.id     
         AND cod2.descOf = 'Desabilitou valvula'
WHERE
    valv.cam = '1'

This is pretty much a guess because there really isn't enough info in your question.

EDIT: I had the wrong test string in the cod2.descOf = line; I've fixed that.

Upvotes: 1

Related Questions