SQL Server 根据条件更改具有多个字段的选择结果(SQL)

mzmfm0qo 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(205)

I have this script:

SELECT TRANSACTION_CODE, LINK_NUMBER, INTERNAL_REFERENCE, PORTFOLIO_CODE, FILE_CODE, CMP_CODE, COUNTERPARTY_CODE, TRX_AMOUNT, CONVERT(varchar, BEGINNING_DATE, 3) AS 'BEGINNING_DATE', CONVERT(varchar, BOOK_DATE, 3) AS 'BOOK_DATE', 
             CONVERT(varchar, END_DATE, 3) AS 'END_DATE', AMORT_FREQUENCY, END_OF_MONTH, NUMBER_OF_AMORT_PERIODS, CONVERT(varchar, FIRST_REDEMPTION_DATE, 3) AS 'FIRST_REDEMPTION_DATE', AMORT_PREPAID_FLAG, AMORT_CALC_MODE, 
             AJUST_FIRST_AMORT_FLAG, AMORT_CALC_RATE, AMORT_RATE_INCREMENT, AMORT_FIRST_ANNUITY, AMORT_FIRST_REDEMPTION, INTEREST_FREQUENCY, CONVERT(varchar, FIRST_INT_DATE, 3) AS 'FIRST_INT_DATE', NUMBER_OF_INT_PERIODS, INT_PREPAID_FLAG, 
             INT_CALC_BASIS, INT_RATE_TYPE, INT_FIXED_RATE, INT_MARKUP, INT_FLOOR, INT_CEILING, REFERENCE, PAYMENTS_CMP_ACC, AMORT_CMP_ACC, INT_CMP_ACC, FEE_CMP_ACC, NOTE_1, ADDIT_PAYMENT_CMP_ACCOUNT, ZU_01, ZU_02, ZU_03, ZU_04, ZU_05, ZU_06, 
             ZU_07, ZU_08, ZU_09, ZU_10
FROM   LOANS
WHERE (LAST_VERSION_FLAG = - 1) AND (NUMBER_OF_PAYMENTS = 1) AND (PERIOD_DEFERRED_FLAG = 0) AND (TRX_CATEGORY = 75) AND (AMORT_CALC_MODE IN (0, 2, 3))

I would like to add a condition that would make the field AMORT_FREQUENCY behave as this:
WHEN O THEN 3 WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE 0
I have tried CASE but I can't figure out how to make it work. I am sorry if this is a very basic question.
Thanks in advance.
Mario.
[ADDED]
The field AMORT_FREQUENCY can contein this values: 0, 1, 2, 3 I want to change the output value of this field:
When the field contains 0, change to 3 When the field contains 1, change to 2 When the field contains 2, change to 1 When the field contains 3, change to 4
In this select I am extracting a huge table and I need those values to be changed.

sql-server

来源：https://stackoverflow.com/questions/74716386/changing-the-result-of-a-select-with-multiple-fields-based-on-conditions-sql

1条答案

按热度按时间

zxlwwiss1#

You can use CASE this way:

select
  AMORT_FREQUENCY,
  case AMORT_FREQUENCY
   when 0 then 3
   when 1 then 2
   when 2 then 3
   when 3 then 4
  end as amort_new
from test

See fiddle: https://dbfiddle.uk/ITjjwjTx
You can then put the CASE part into your query by replacing the AMORT_FREQUENCY , something like (added parentheses for readability):

SELECT TRANSACTION_CODE, LINK_NUMBER, INTERNAL_REFERENCE, PORTFOLIO_CODE, FILE_CODE, CMP_CODE, COUNTERPARTY_CODE, TRX_AMOUNT, CONVERT(varchar, BEGINNING_DATE, 3) AS 'BEGINNING_DATE', CONVERT(varchar, BOOK_DATE, 3) AS 'BOOK_DATE', 
         CONVERT(varchar, END_DATE, 3) AS 'END_DATE', (case AMORT_FREQUENCY when 0 then 3 when 1 then 2 when 2 then 3 when 3 then 4 end), END_OF_MONTH, NUMBER_OF_AMORT_PERIODS, CONVERT(varchar, FIRST_REDEMPTION_DATE, 3) AS 'FIRST_REDEMPTION_DATE', AMORT_PREPAID_FLAG, AMORT_CALC_MODE, 
         AJUST_FIRST_AMORT_FLAG, AMORT_CALC_RATE, AMORT_RATE_INCREMENT, AMORT_FIRST_ANNUITY, AMORT_FIRST_REDEMPTION, INTEREST_FREQUENCY, CONVERT(varchar, FIRST_INT_DATE, 3) AS 'FIRST_INT_DATE', NUMBER_OF_INT_PERIODS, INT_PREPAID_FLAG, 
         INT_CALC_BASIS, INT_RATE_TYPE, INT_FIXED_RATE, INT_MARKUP, INT_FLOOR, INT_CEILING, REFERENCE, PAYMENTS_CMP_ACC, AMORT_CMP_ACC, INT_CMP_ACC, FEE_CMP_ACC, NOTE_1, ADDIT_PAYMENT_CMP_ACCOUNT, ZU_01, ZU_02, ZU_03, ZU_04, ZU_05, ZU_06, 
         ZU_07, ZU_08, ZU_09, ZU_10
FROM   LOANS
WHERE (LAST_VERSION_FLAG = - 1) AND (NUMBER_OF_PAYMENTS = 1) AND (PERIOD_DEFERRED_FLAG = 0) AND (TRX_CATEGORY = 75) AND (AMORT_CALC_MODE IN (0, 2, 3))

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 根据条件更改具有多个字段的选择结果(SQL)

1条答案

相关问题

热门标签

最新问答