Remove From My Forums

How To Achive This.... ?

Question

Hi All,

Friend first of sorry for setting title as "How To Achive This..?" as I was not sure what to write ;).

So coming to the question, I have to check multiple conditions for a specific row and then display custom message if it meet specific conditions.

eg:

drop table #temp

Create table #temp (GLSectorCodeLevel0 varchar(10),IssuerCountry varchar(10), IssuerType varchar(10),IssuerCode varchar(10),
                    IssuerName varchar(10), ABSSectorCodeHiport varchar(10),IssuerCounterpartytype varchar(10))
go

insert into #temp Values
('GL990',NULL,NULL,NULL,NULL,NULL,NULL), 
('GL900','AUS','U',NULL,NULL,NULL,NULL) ,
('GL900','IND','U','eee',NULL,NULL,NULL),
('GL900','AUS','X',NULL,NULL,NULL,NULL), 
('GL900','USA','A','xxx','',NULL,NULL), 
('GL900','AUS','E','ttt','ttt',NULL,NULL), 
('GL900','APT','E',NULL,'','',''),
('GL910','APT','E',NULL,'',NULL,NULL), 
('GL910','APT','E',NULL,'','','')

In above code we inserted 9 records and then we want to display it like below.

GLSectorCode0	StatusExplanation
GL990	Items should not be allocated to this GL Sector, ABS Sector code required,
GL900	Issuer Type is Unallocated, Issuer Code not in Broker Maintenance
GL900	Issuer Code not found in Issuer Table
GL900	Issuer Code not in Broker Maintenance
GL900	Issuer Code not found in Issuer Table
GL900
GL900	Issuer Code not in Broker Maintenance
GL910	ABS Sector code required
GL910	ABS Sector code required

I wrote a code which displays it in below format. Want to remove those extra commas

GLSectorCodeLevel0   StatusExplanation
GL990                       Items should not be allocated to this GL Sector, , , , ABS Sector code required,
GL900                       , Issuer Type is Unallocated, Issuer Code not in Broker Maintenance, , ,
GL900                       , , , Issuer Code not found in Issuer Table, ,
GL900   , , Issuer Code not in Broker Maintenance, , ,
GL900      , , , Issuer Code not found in Issuer Table, ,
GL900   , , , , ,
GL900                        , , Issuer Code not in Broker Maintenance, , ,
GL910                          , , , , ABS Sector code required,
GL910                       , , , , ABS Sector code required,

Code which I written is



Select GLSectorCodeLevel0, COALESCE(CASE 
		WHEN GLSectorCodeLevel0 = 'GL990'
			THEN 'Items should not be allocated to this GL Sector' 
		ELSE '' END +', ','')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and IssuerCountry ='AUS' and IssuerType = 'U' /* Defect#123 */
			THEN 'Issuer Type is Unallocated' 
		ELSE '' END +', ','')
		+
     	COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and (IssuerCode = '' OR IssuerCode IS NULL)
			THEN 'Issuer Code not in Broker Maintenance' 
		ELSE '' END +', ','')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and ((IssuerCode IS NOT NULL and IssuerName IS NULL) OR (IssuerCode <> '' and IssuerName = '' ))
			THEN 'Issuer Code not found in Issuer Table'
		ELSE '' END + ', ','')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 <> 'GL900' AND ((ABSSectorCodeHiport = '' and IssuerCounterpartytype='') OR (ABSSectorCodeHiport IS NULL and IssuerCounterpartytype IS NULL))
			THEN 'ABS Sector code required'
		ELSE '' END + ', ','')
	AS StatusExplanation
From #temp

Regards

Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

Thursday, May 12, 2016 6:19 AM

Answers

Try this

Select GLSectorCodeLevel0, SUBSTRING (  COALESCE(CASE 
		WHEN GLSectorCodeLevel0 = 'GL990'
			THEN 'Items should not be allocated to this GL Sector' +', '
		ELSE '' END,'' )
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and IssuerCountry ='AUS' and IssuerType = 'U' /* Defect#123 */
			THEN 'Issuer Type is Unallocated' +', '
		ELSE '' END ,'')
		+
     	COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and (IssuerCode = '' OR IssuerCode IS NULL)
			THEN 'Issuer Code not in Broker Maintenance' +', '
		ELSE '' END ,'')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and ((IssuerCode IS NOT NULL and IssuerName IS NULL) OR (IssuerCode <> '' and IssuerName = '' ))
			THEN 'Issuer Code not found in Issuer Table' + ', '
		ELSE '' END ,'')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 <> 'GL900' AND ((ABSSectorCodeHiport = '' and IssuerCounterpartytype='') OR (ABSSectorCodeHiport IS NULL and IssuerCounterpartytype IS NULL))
			THEN 'ABS Sector code required' + ', '
		ELSE '' END ,'') , 
		0 , 
		LEN ( COALESCE(CASE 
		WHEN GLSectorCodeLevel0 = 'GL990'
			THEN 'Items should not be allocated to this GL Sector' +', '
		ELSE '' END,'' )
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and IssuerCountry ='AUS' and IssuerType = 'U' /* Defect#123 */
			THEN 'Issuer Type is Unallocated' +', '
		ELSE '' END ,'')
		+
     	COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and (IssuerCode = '' OR IssuerCode IS NULL)
			THEN 'Issuer Code not in Broker Maintenance' +', '
		ELSE '' END ,'')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and ((IssuerCode IS NOT NULL and IssuerName IS NULL) OR (IssuerCode <> '' and IssuerName = '' ))
			THEN 'Issuer Code not found in Issuer Table' + ', '
		ELSE '' END ,'')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 <> 'GL900' AND ((ABSSectorCodeHiport = '' and IssuerCounterpartytype='') OR (ABSSectorCodeHiport IS NULL and IssuerCounterpartytype IS NULL))
			THEN 'ABS Sector code required' + ', '
		ELSE '' END ,'')  ) )
	AS StatusExplanation
From #temp

Proposed as answer by senthilswing Thursday, May 12, 2016 7:26 AM
Marked as answer by GURSETHI Thursday, May 12, 2016 9:01 PM

Thursday, May 12, 2016 7:26 AM

Hi GURSETHI,

To remove the extra comma, you can reference below way.

SELECT GLSectorCodeLevel0
	,STUFF(
	COALESCE(CASE 
			WHEN GLSectorCodeLevel0 = 'GL990'
				THEN ', '+'Items should not be allocated to this GL Sector'
			ELSE ''
			END , '') + COALESCE(CASE 
			WHEN GLSectorCodeLevel0 = 'GL900'
				AND IssuerCountry = 'AUS'
				AND IssuerType = 'U' /* Defect#123 */
				THEN ', '+'Issuer Type is Unallocated'
			ELSE ''
			END , '') + COALESCE(CASE 
			WHEN GLSectorCodeLevel0 = 'GL900'
				AND (
					IssuerCode = ''
					OR IssuerCode IS NULL
					)
				THEN ', '+'Issuer Code not in Broker Maintenance' 
			ELSE ''
			END , '') + COALESCE(CASE 
			WHEN GLSectorCodeLevel0 = 'GL900'
				AND (
					(
						IssuerCode IS NOT NULL
						AND IssuerName IS NULL
						)
					OR (
						IssuerCode <> ''
						AND IssuerName = ''
						)
					)
				THEN ', '+'Issuer Code not found in Issuer Table'
			ELSE ''
			END , '') + COALESCE(CASE 
			WHEN GLSectorCodeLevel0 <> 'GL900'
				AND (
					(
						ABSSectorCodeHiport = ''
						AND IssuerCounterpartytype = ''
						)
					OR (
						ABSSectorCodeHiport IS NULL
						AND IssuerCounterpartytype IS NULL
						)
					)
				THEN ', '+'ABS Sector code required' 
			ELSE ''
			END , '')
	,1,2,'') AS StatusExplanation
FROM #temp

STUFF (Transact-SQL)

If you have any question, feel free to let me know.

Eric Zhang
TechNet Community Support

Marked as answer by GURSETHI Thursday, May 12, 2016 9:01 PM

Thursday, May 12, 2016 9:03 AM

Create table #temp (GLSectorCodeLevel0 varchar(10),IssuerCountry varchar(10), IssuerType varchar(10),IssuerCode varchar(10),
                    IssuerName varchar(10), ABSSectorCodeHiport varchar(10),IssuerCounterpartytype varchar(10))
go

insert into #temp Values
('GL990',NULL,NULL,NULL,NULL,NULL,NULL), 
('GL900','AUS','U',NULL,NULL,NULL,NULL) ,
('GL900','IND','U','eee',NULL,NULL,NULL),
('GL900','AUS','X',NULL,NULL,NULL,NULL), 
('GL900','USA','A','xxx','',NULL,NULL), 
('GL900','AUS','E','ttt','ttt',NULL,NULL), 
('GL900','APT','E',NULL,'','',''),
('GL910','APT','E',NULL,'',NULL,NULL), 
('GL910','APT','E',NULL,'','','')



Select GLSectorCodeLevel0,Reverse(Stuff(Reverse(Rtrim(COALESCE(CASE 
		WHEN GLSectorCodeLevel0 = 'GL990'
			THEN 'Items should not be allocated to this GL Sector' 
		+', ' ELSE '' END  ,'')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and IssuerCountry ='AUS' and IssuerType = 'U' /* Defect#123 */
			THEN 'Issuer Type is Unallocated' 
		+', ' ELSE '' END  ,'')
		+
     	COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and (IssuerCode = '' OR IssuerCode IS NULL)
			THEN 'Issuer Code not in Broker Maintenance' 
		+', ' ELSE '' END ,'')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 = 'GL900' and ((IssuerCode IS NOT NULL and IssuerName IS NULL) OR (IssuerCode <> '' and IssuerName = '' ))
			THEN 'Issuer Code not found in Issuer Table'
		+ ', ' ELSE '' END  ,'')
		+
		COALESCE(CASE WHEN GLSectorCodeLevel0 <> 'GL900' AND ((ABSSectorCodeHiport = '' and IssuerCounterpartytype='') OR (ABSSectorCodeHiport IS NULL and IssuerCounterpartytype IS NULL))
			THEN 'ABS Sector code required'
		+ ', ' ELSE '' END ,'') )),1,1,'') )
	AS StatusExplanation
From #temp
 

drop table #temp

Marked as answer by GURSETHI Thursday, May 12, 2016 9:01 PM

Thursday, May 12, 2016 1:52 PM

All replies