select * ledger led left outer join (if exists(select accountcode b_c_j_trans accountcode = '001809' , companycode='ram' --and entryno='25' , mode='j') select companycode, sum(amount * 0) amount b_c_j_trans bcjtrans (companycode = 'ram') , (mode = 'j') group companycode else select companycode, sum( case when bcjtrans.accountcode = '800100' bcjtrans.amount else 0 end) amount b_c_j_trans bcjtrans (companycode = 'ram') , (mode = 'j') group companycode) on led.companycode = a.companycode , led.bookcode = a.bookcode , led.[type] = a.mode , led.financialyear = a.entryyear , led.voucherno = a.entryno led.companycode = 'ram' , led.acccode = '800100' , led.voucherdate >= '2015-04-01' , led.voucherdate <= '2015-05-31'
this query want use if exist in left outer join of ledger table
move exists
where
clause
select * ledger led left outer join (select companycode, sum(amount * 0) amount b_c_j_trans bcjtrans ( companycode = 'ram' ) , ( mode = 'j' ) , exists(select accountcode b_c_j_trans accountcode = '001809' , companycode = 'ram' --and entryno='25' , mode = 'j') group companycode union select companycode, sum(case when bcjtrans.accountcode = '800100' bcjtrans.amount else 0 end) amount b_c_j_trans bcjtrans ( companycode = 'ram' ) , ( mode = 'j' ) , not exists(select accountcode b_c_j_trans accountcode = '001809' , companycode = 'ram' --and entryno='25' , mode = 'j') group companycode)as on led.companycode = a.companycode , led.bookcode = a.bookcode , led.[type] = a.mode , led.financialyear = a.entryyear , led.voucherno = a.entryno led.companycode = 'ram' , led.acccode = '800100' , led.voucherdate >= '2015-04-01' , led.voucherdate <= '2015-05-31'
am sure code can simplified if share original logic
Comments
Post a Comment