SQL SUERY GIVING EVENT ID SHOWING NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL SUERY GIVING EVENT ID SHOWING NULL

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2011-11-22 : 09:46:18

hI ALL

select distinct
CGM.sol_id as"Branch code"
,CGM.collection_id as"Remittance ID"
,CGM.lodg_date as"Issue date"
,CGM.collection_code as"Register Type"
,CGM.free_code_3 as"LOB"
,CGM.cif_id as"CIF ID"
--CGM.other_party_name
,CGM.party_name as"Client Name"
,gam.foracid as " Operative Acccount No."
,GAM.SCHM_CODE as "Scheme Code"
,GAM.SCHM_TYPE as "Scheme Type"
,case when CGM.other_party_name is null then fci.other_party_name else CGM.other_party_name end as"Other party Name"
,CGM.collection_crncy as"CCY"
,cast(CGM.collection_amt as numeric(18,2)) as"Amount"
,rtl1.var_crncy_units as"Notional Rate"
--,cast(CGM.notl_conv_rate as numeric(18,4)) as"Exchange Rate"
,(rtl1.var_crncy_units *cgm.collection_amt ) as "Amt in Home CCY "
,case when CGM.other_party_cntry_code is null then fci.other_party_cntry_code else CGM.other_party_cntry_code end as"Country code of other party"
,CGM.collection_status as"Bill Status(lodged/purchased/realised)"
,CGM.purpose_of_rem as"Purpose of remittance"
,LLT.limit_prefix+'/'+ LLT.limit_suffix as"Limit node id",
CGM.COLLECTION_B2K_ID,
CL.EVENT_ID ,
CPM.in_out_ind
from CGM left outer join LLT on CGM.bp_limit_b2kid=LLT.limit_b2kid
inner join cpm on CPM.coll_code=CGM.collection_code
left join fci on fci.bill_id = CGM.COLLECTION_ID
inner join gam on gam.acid=cgm.oper_acid
left join (select rtl.FXD_CRNCY_CODE ,rtl.var_crncy_units , rtl.var_crncy_code,rtl.ratecode, max(lchg_time) max_time from rtl where rtl.var_crncy_code='inr' and rtl.ratecode='nor' group by rtl.var_crncy_units,rtl.var_crncy_code,rtl.ratecode,rtl.FXD_CRNCY_CODE) rtl1
on RTL1.FXD_CRNCY_CODE=CGM.COLLECTION_CRNCY
left join (select cxl.event_id ,CXL.comp_b2kid from CXL WHERE CXL.event_type='KB11%' )cl on substring(rtrim(CL.comp_b2kid),1,12)=CGM.COLLECTION_B2K_ID

where CGM.entity_Cre_flg='Y'
and CGM.del_flg='N'

--order by CGM.other_party_name

I EXECUTED ABOVE QUERY

I GOT CL_EVENT_ID COLUMN FETCHING DUPLICATES
I NEED TO FETCH CL_EVENT VALUES COLUMN NOT NULL

EXAMPLE
ROW1. CL.EVENT_ID 'AIT'

ROW2. CL.EVENT_ID 'NULL'

I NEED FETCH ROW2 ONLY BASED ON COL 'AIT'
LANCHIN_TIME FOR BOTH RECORDS ARE SAME
I CHANGED SUBSTRING VALUES BUT I DIDN'T GET PROPER ONE

PLS ANYBODY HELP

mohan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:00:22

first learn to post question in proper format. Provide details as below and make life easier for somebody trying to help you out

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-23 : 11:38:54

yes visakh16 you are right I'm suffering to try understand any thing above
kond.mohan why u are using capital format in all text?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources