CASE RETURNING MORE THAN ONE LINE IN A SUBCONSULTA

Asked

Viewed 56 times

-2

I’m having a problem in a sub-sale that returns me the payment type (CREDIT OR DEBIT) and whether the payment was in card or not.

inserir a descrição da imagem aqui

However, if I have a payment with the two types of card the subconsulta will return two records and will end up breaking, what I could do for when I have two types of payment I consider only one, in my case I wanted to consider only credit.

I consider commissions through the type of payment; CREDIT + DEBIT = CREDIT (I want to assemble a case if one of the types was credit he consider only credit as type of payment)

follow what I did:

CASE 
        WHEN X.cartao = 'S' AND X.tipo = 'CREDITO' THEN 'CREDITO'
        WHEN X.cartao = 'S' AND X.tipo = 'DEBITO'  THEN 'DEBITO'
        WHEN X.cartao = 'S' AND X.tipo = 'CREDITO' AND X.tipo = 'DEBITO' THEN 'CREDITO' -- 
   ELSE 'N' END cartao,
sql sql-server

  • Include your SQL, only his CASE doesn’t make any sense.

  • It is impossible that X.tipo = 'CREDITO' and X.tipo = 'DEBITO' simultaneously and therefore this third condition will never be satisfied. Since you haven’t explained enough how your data is organized maybe there can be multiple rows in your table, one for each card?

1 answer

2

Good morning,

I think an alternative would be to use Top(1) with Order by within the subconsultation. As 'CREDIT' comes before 'DEBIT' in ascending order, the sub-allowance must return 'CREDIT' when there are 2 types of card. Ex:

(
  select top(1)
    CASE 
        WHEN X.cartao = 'S' AND X.tipo = 'CREDITO' THEN 'CREDITO'
        WHEN X.cartao = 'S' AND X.tipo = 'DEBITO'  THEN 'DEBITO'
        WHEN X.cartao = 'S' AND X.tipo = 'CREDITO' AND X.tipo = 'DEBITO' THEN 'CREDITO' -- 
        ELSE 'N' 
    END cartao,
    -- ...

  order by cartao
)

I hope it helps

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.