locked
How To Achive This.... ? RRS feed

  • 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

  • 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
  • Thanks Friends. all solutions are working

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

    Thursday, May 12, 2016 8:57 PM