Field alias in oracle where clause

2

I have a Decode in my select like so:

.....
DECODE(MAT.IND_TIPO_MAT_MED,
           '1', 'BRASINDICE',
           '2', 'SIMPRO',
           '9', 'TABELAPROPRIA') TIPOTABELA,
.....

I need to pass these values on the where clause, since they come with querystring , and on querystring I get those values: BRASINDICE , SIMPRO or TABELAPROPRIA . It turns out that as TIPOTABELA is an alias, in the Where en clause I can not get them. If I get by the real field name, I do not have these values, but only: 1 , 2 or 9 , and in querystring are passed values represented and my query will not return anything. I need to make an inversion in the query, that is, to receive% of the values by% and at the time of passing to the querystring clause, pass the actual values. How do I do this?

Note: Via querystring I get: where , BRASINDICE or SIMPRO and the field accepts: TABELAPROPRIA , 1 or 2 . Is there a way to make a reversal?

I made a case and found half a gambi. I did so:

if(tipoTabela != string.Empty)
            {
                switch(tipoTabela)
                {
                    case "BRASINDICE":
                        tipoTabela = "1";
                        break;

                    case "SIMPRO":
                    tipoTabela = "2";
                    break;

                    case "TABELAPROPRIA":
                    tipoTabela = "9";
                    break;

                    default:
                    tipoTabela = string.Empty;
                    break;

                }
    
asked by anonymous 13.01.2016 / 13:49

1 answer

1

What you can do is to use a sub-select and then apply the filter:

SELECT * FROM
(
    .....
    DECODE(MAT.IND_TIPO_MAT_MED,
               '1', 'BRASINDICE',
               '2', 'SIMPRO',
               '9', 'TABELAPROPRIA') TIPOTABELA,
    .....
) AS TBL_CONSULTA
WHERE TIPOTABELA LIKE 'SIMPRO'
    
13.01.2016 / 15:09