Evaluate a Column with SELECT CASE in mySQL and Returns three columns as Result -

hi have mysql table these 5 columns

1) validationsts 2) comprobante_tipo_comprobante 3) comprobante_subtotal 4) impuesto_trasladado 5) comprobante_total

so, need evaluate first validationsts, when equals "cancelado", multiply zero, in order 0 result case else pass nested select case.

in nested select case eval "comprobante_tipo_comprobante",and eval 2 cases: "ingreso" , "egreso". when case equals "ingreso", result column "comprobante_subtotal" when case equals "egreso", result column "comprobante_subtotal" in negative.

so that's code , returns 1 column result:

select  case validationsts when 'cancelado' 0*comprobante_subtotal   else   case when comprobante_tipo_de_comprobante = 'egreso' -1*comprobante_subtotal when comprobante_tipo_de_comprobante='ingreso' comprobante_subtotal end   end   facturas_recibidas receptor_rfc= 'rfc' , emisor_rfc='rfclist' , comprobante_fecha between 'srchfechainicial.sqldate + " 00:00:00.000' ,   srchfechafinal.sqldate + " 23:59.59.997'  order comprobante_fecha asc"

as said, works, can returns 1 column result.

but if want return 3 columns? example: comprobante_subtotal, impuesto , comprobante_total , apply same treatment comprobante_subtotal.

i've tried and, , splitting comma unsuccessfully. :(

any clues? regards

case cannot result in more 1 field-value(column) per row; you'd have reproduce logic each column. alternative have logic performed once, instead have result in "handling" code can used in simpler logic in wrapping query.

it looks scenario, "handling" code multiplier want use (-1, 0, 1).

---

select @multi :=     case when validationsts = 'cancelado' 0       when comprobante_tipo_de_comprobante = 'egreso' -1        when comprobante_tipo_de_comprobante='ingreso' 1    end multi    , comprobante_subtotal * @multi    , impuesto * @multi    , comprobante_total * @multi facturas_recibidas  receptor_rfc= 'rfc'     , emisor_rfc='rfclist'     , comprobante_fecha between [startdatetime] , [enddatetime] order comprobante_fecha asc; 

note: version above takes advantage of session variable (@multi) avoid need subquery. in case, should fine; people averse them, or find them unreliable. if of apply, can used without session variable.

select comprobante_subtotal * multi    , impuesto * multi    , comprobante_total * multi (    select        case when validationsts = 'cancelado' 0          when comprobante_tipo_de_comprobante = 'egreso' -1           when comprobante_tipo_de_comprobante='ingreso' 1       end multi       , comprobante_subtotal, impuesto, comprobante_total       , comprobante_fecha /* subqueries can't guarantee ordering, needed outer query can final ordering */     facturas_recibidas     receptor_rfc= 'rfc'        , emisor_rfc='rfclist'        , comprobante_fecha between [startdatetime] , [enddatetime] ) subq order comprobante_fecha asc; 

---

as side note, should point out got rid of nested case logic; using case when [condition1] x when [condition2] .... version instead of case [switching value] when [value1] x when [value2] .... makes nesting unnecessary.