在sql querry下面进行必要的修正 [英] make necessory correction in below sql querry

问题描述

选择

rpad（a.farmer_code，6，''）代码，

rpad（b.farmer_name，30，''）fname，

rpad（d.subcircle_name，25，''）cname，

rpad（c.village_name，20，''）vname，

rpad（a。 bank_code，5，''）出价，

rpad（e.banK_name，40，''）bname，

rpad（a.Bank_acno，20，''）sbac ，

rpad（a.net_payment，8，''）netpay

IF（{e.ISFC_CODE}）LIKE（* ALLA *）和长度（{a .BANK_ACNO}）= 11然后正确否则

IF（{e.ISFC_CODE}）LIKE（* UTIB *）AND Length（{a.BANK_ACNO}）= 15然后CORRECT否则

IF（{e.ISFC_CODE}）LIKE（* BKID *）AND Length（{a.BANK_ACNO}）= 15然后CORRECT否则

IF （{e.ISFC_CODE}）LIKE（* MAHB *）AND Length（{a.BANK_ACNO}）= 11然后CORRECT否则

IF（{e.ISFC_CODE}）LIKE（ * CNRB *）和长度（{a.BANK_ACNO}）= 13然后正确否则

IF（{e.ISFC_CODE}）LIKE（* CBIN *）和长度（{a .BANK_ACNO}）= 10然后正确否则

IF（{e.ISFC_CODE} ）LIKE（* CORP *）AND Length（{a.BANK_ACNO}）= 15然后CORRECT否则

IF（{e.ISFC_CODE}）LIKE（* FDRL *）AND长度（{a.BANK_ACNO}）= 14然后正确否则

IF（{e.ISFC_CODE}）LIKE（* HDFC *）和长度（{a.BANK_ACNO}）= 14然后正确否则

IF（{e.ISFC_CODE}）LIKE（* ICIC *）和长度（{a.BANK_ACNO}）= 12然后正确否则

IF（{e.ISFC_CODE}）LIKE（* IOBA *）AND Length（{a.BANK_ACNO}）= 15然后CORRECT否则

IF（{e.ISFC_CODE} ）LIKE（* KARB *）AND Length（{a.BANK_ACNO}）= 16然后CORRECT否则

IF（{e.ISFC_CODE}）LIKE（* KVGB *）AND长度（{a.BANK_ACNO}）= 11然后正确否则

IF（{e.ISFC_CODE}）LIKE（* SBIN *）和长度（{a.BANK_ACNO}）= 11然后正确否则

IF（{e.ISFC_CODE}）LIKE（* SBMY *）和长度（{a.BANK_ACNO}）= 11然后正确否则

IF（{e.ISFC_CODE}）LIKE（* SYNB *）AND Length（{a.BANK_ACNO}）= 14然后CORRECT否则

IF（{e.ISFC_CODE} ）LIKE（* RATN *）和长度（{a.BANK_AC NO}）= 12然后正确否则

IF（{e.ISFC_CODE}）LIKE（* RATN *）AND Length（{a.BANK_ACNO}）= 16然后CORRECTelse

IF（{e.ISFC_CODE}）LIKE（* UBIN *）AND Length（{a.BANK_ACNO}）= 15然后CORRECT否则

IF（ {e.ISFC_CODE}）LIKE（* VIJB *）AND Length（{a.BANK_ACNO}）= 15然后CORRECT否则

IF（{e.ISFC_CODE}）LIKE（* BKDN *）AND长度（a.BANK_ACNO}）= 12然后正确否则

IF（{e.ISFC_CODE}）LIKE（* IDIB *）和长度（{a.BANK_ACNO }）= 10然后正确否则

IF（{e.ISFC_CODE}）LIKE（* IDIB *）AND Length（{a.BANK_ACNO}）= 9则CORRECTelse

IF（{e.ISFC_CODE}）LIKE（* KAIJ *）AND Length（{a.BANK_ACNO}）= 15然后CORRECT否则

IF（{ e.ISFC_CODE}）LIKE（* VYSA *）AND Length（{a.BANK_ACNO}）= 12然后CORRECT否则

IF（{e.ISFC_CODE}）LIKE（* SVCB *）和长度（{a.BANK_ACNO}）= 15然后正确否则

IF（{e.ISFC_CODE}）LIKE（* ORBC *）和长度（{a.BANK_ACNO }）= 14然后正确否则

IF （{e.ISFC_CODE}）LIKE（* BARB *）AND Length（{a.BANK_ACNO}）= 14然后CORRECT否则

IF（{e.ISFC_CODE}）=（ IBKL0101BDC ）和长度（{a.BANK_ACNO}）= 16然后 正确的 其他

如果（{e.ISFC_CODE}）=（ IBKL0069RSB）和长度（{a.BANK_ACNO}） = 15然后正确否则

IF（{e.ISFC_CODE}）=（IBKL01071BD）和长度（{a.BANK_ACNO}）= 15然后正确否则

如果（{e.ISFC_CODE}）=（ KSCB）和长度（{a.BANK_ACNO}）= 12然后 正确的 其他 错误 的

从farmer_summary_prov一个，farmer_master b ，village_master c，subcircle_master d，bank_master e

其中a.farmer_code = b。 farmer_code和

b.village_code = c.village_code和

c。 SUBCIRCLE_CODE = d。 SUBCIRCLE_CODE和

a.bank_code = e.bank_code

和a。 net_payment> 0

/

解决方案

正如其他帖子中所提到的，不要从table1，table2方法中做到。使用JOINS。请参阅条件如何我可以写入在Sql Query下面[ ^ ]



并将您的IF更改为CASE语句。很容易做到。请参阅我的其他答案。

我注意到在之前 netpay 之后没有逗号IF 第

select
rpad(a.farmer_code,6, ‘ ‘)code,
rpad(b.farmer_name,30, ‘ ‘)fname,
rpad(d.subcircle_name,25,’ ‘)cname,
rpad(c.village_name,20, ‘ ‘)vname,
rpad(a.bank_code,5,’ ‘)bid,
rpad(e.banK_name,40,’ ‘)bname,
rpad(a.Bank_acno,20, ‘ ‘)sbac,
rpad(a.net_payment,8,’ ‘)netpay
IF ({e.ISFC_CODE}) LIKE ("*ALLA*") AND Length ({a.BANK_ACNO})=11 then "CORRECT" else
IF({e.ISFC_CODE}) LIKE ("*UTIB*") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*BKID*") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*MAHB*") AND Length ({a.BANK_ACNO})=11 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*CNRB*") AND Length ({a.BANK_ACNO})=13 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*CBIN*") AND Length ({a.BANK_ACNO})=10 then "CORRECT" else
IF ({e. ISFC_CODE}) LIKE ("*CORP*") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*FDRL*") AND Length ({a.BANK_ACNO})=14 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*HDFC*") AND Length ({a.BANK_ACNO})=14 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*ICIC*") AND Length ({a.BANK_ACNO})=12 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*IOBA*") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*KARB*") AND Length ({a.BANK_ACNO})=16 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*KVGB*") AND Length ({a.BANK_ACNO})=11 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*SBIN*") AND Length ({a.BANK_ACNO})=11 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*SBMY*") AND Length ({a.BANK_ACNO})=11 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*SYNB*") AND Length ({a.BANK_ACNO})=14 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*RATN*") AND Length ({a.BANK_ACNO})=12 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*RATN*") AND Length ({a.BANK_ACNO})=16 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*UBIN*") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*VIJB*") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*BKDN*") AND Length (a.BANK_ACNO})=12 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*IDIB*") AND Length ({a.BANK_ACNO})=10 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*IDIB*") AND Length ({a.BANK_ACNO})=9 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*KAIJ*") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*VYSA*") AND Length ({a.BANK_ACNO})=12 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*SVCB*") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*ORBC*") AND Length ({a.BANK_ACNO})=14 then "CORRECT" else
IF ({e.ISFC_CODE}) LIKE ("*BARB*") AND Length ({a.BANK_ACNO})=14 then "CORRECT" else
IF ({e.ISFC_CODE})=("IBKL0101BDC") AND Length ({a.BANK_ACNO})=16 then "CORRECT" else
IF ({e.ISFC_CODE})=("IBKL0069RSB") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE})=("IBKL01071BD") AND Length ({a.BANK_ACNO})=15 then "CORRECT" else
IF ({e.ISFC_CODE})=("KSCB") AND Length ({a.BANK_ACNO})=12 then "CORRECT" else "WRONG"
From farmer_summary_prov a,farmer_master b,village_master c, subcircle_master d,bank_master e
Where a.farmer_code=b. farmer_code and
b.village_code=c.village_code and
c. SUBCIRCLE_CODE=d. SUBCIRCLE_CODE and
a.bank_code=e.bank_code
and a. net_payment>0
/

解决方案

As mentioned in the other post, don't do your from table1, table2 method. Use JOINS. See How I Can Write If Condition In Below Sql Query[^]

And change your IF to CASE statements. Very easy to do. Refer to my other answer as well.

I noticed that there is no comma after netpay just before the IFs.

这篇关于在sql querry下面进行必要的修正的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！