Konz Mama Konz Mama - 7 months ago 22
SQL Question

CASE in select statement MySQL

I am having error in mysql query, so i need an if statement based on the previous select value.

here is my query,

SELECT
ds.master_rcpt_gen,
ds.visa_rcpt_gen,
dsp.curr_amount,
dsp.curr_id,
mcr.conv_rate,
mcr.curr_code,
mcr.curr_desc,
mcr.curr_type,
(conv_rate / (select mcr.conv_rate from mst_currencies_rate mcr where mcr.curr_code = 'USD')) conv_ratio,
curr_amount * (conv_rate / (select mcr.conv_rate from mst_currencies_rate mcr where mcr.curr_code = 'USD')) conv_usd,
(select mtr.USD_tax_rate from mst_tax_rate mtr) * (curr_amount * (conv_rate / (select mcr.conv_rate from mst_currencies_rate mcr where mcr.curr_code = 'USD'))) AS conv_taxrate_commission,
CASE WHEN mcr.curr_type = 'LOCAL' THEN conv_taxrate_commission = dsp.curr_amount ELSE (select mtr.USD_tax_rate from mst_tax_rate mtr) * (curr_amount * (conv_rate / (select mcr.conv_rate from mst_currencies_rate mcr where mcr.curr_code = 'USD')))
END final_conv_rate
FROM
dtl_sr ds
INNER JOIN dtl_sr_payment dsp ON dsp.flight_id = ds.flight_id
AND dsp.sr_id = ds.SR_ID
INNER JOIN mst_currencies_rate mcr ON mcr.curr_id = dsp.curr_id


My error happened in case statement, i need the case statement to read value from conv_taxrate_commission. IF mcr.curr_type = 'LOCAL' i need conv_taxrate_commission to be equal to dsp.curr_amount, else i need to leave the conv_taxrate_commission as is.

Please help me

Answer

Try this, I assume when mcr.curr_type = 'LOCAL', final_conv_rate is dsp.curr_amount;)

SELECT
  ds.master_rcpt_gen,
  ds.visa_rcpt_gen,
  dsp.curr_amount,
  dsp.curr_id,
  mcr.conv_rate,
  mcr.curr_code,
  mcr.curr_desc,
  mcr.curr_type,
  (conv_rate / (SELECT mcr.conv_rate
                FROM mst_currencies_rate mcr
                WHERE mcr.curr_code = 'USD'))                                                 conv_ratio,
  curr_amount * (conv_rate / (SELECT mcr.conv_rate
                              FROM mst_currencies_rate mcr
                              WHERE mcr.curr_code = 'USD'))                                   conv_usd,
  CASE WHEN mcr.curr_type = 'LOCAL'
    THEN dsp.curr_amount
  ELSE
    (SELECT mtr.USD_tax_rate
     FROM mst_tax_rate mtr) * (curr_amount * (conv_rate / (SELECT mcr.conv_rate
                                                           FROM mst_currencies_rate mcr
                                                           WHERE mcr.curr_code = 'USD'))) END conv_taxrate_commission,
  CASE WHEN mcr.curr_type = 'LOCAL'
    THEN dsp.curr_amount
  ELSE (SELECT mtr.USD_tax_rate
        FROM mst_tax_rate mtr) * (curr_amount * (conv_rate / (SELECT mcr.conv_rate
                                                              FROM mst_currencies_rate mcr
                                                              WHERE mcr.curr_code = 'USD')))
  END                                                                                         final_conv_rate
FROM
  dtl_sr ds
  INNER JOIN dtl_sr_payment dsp ON dsp.flight_id = ds.flight_id
                                   AND dsp.sr_id = ds.SR_ID
  INNER JOIN mst_currencies_rate mcr ON mcr.curr_id = dsp.curr_id