Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with String concatenation need help please Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 12:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Dear friends,
i have 8 fields - and I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the Output I get semicolons for the fields that are empty below is my code need hekp please uregnt
-------------
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry' --'it is jp'
else ''
end as [ES Fully approved data 1],
case
when [SLII Request Type] in ('Work Order','Work Order Revision') and [SmartLaborII Request Status] like 'Pending Approval (Level 3%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry'--'it is WO Or WOR'
else ''
end as [ES Fully approved data 2],
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [Position Duration Audit Flag] = 'FLAG - AC reqs cannot be entered for more than 12 months'
then 'Reject – Duration greater than 12 months'
else ''
end as [ES Fully approved data 3],
case
when [SLII Request Type] in ('Work Order','Work Order Revision') and [SmartLaborII Request Status] like 'Pending Approval (Level 3%'
and [Position Duration Audit Flag] = 'FLAG - AC reqs cannot be entered for more than 12 months'
then 'Reject – Duration greater than 12 months'
else ''
end as [ES Fully approved data 4],
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [Tenure Audit Flag] = 'FLAG - Tenure audit cannot be completed'
then'Reject – Invalid GHRMS Worker ID'
else ''
end as[ES Fully approved data 5],
case
when [SLII Request Type] in ('Work Order','Work Order Revision') and [SmartLaborII Request Status] like 'Pending Approval (Level 3%'
and [Tenure Audit Flag] = 'FLAG - Tenure audit cannot be completed'
then'Reject – Invalid GHRMS Worker ID'
else ''
end as [ES Fully approved data 6],
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [Number of Positions Audit Flag] like 'FLAG%'
then 'Reject – Multi Position'
else ''
end as [ES Fully approved data 7],
case
when [SLII Request Type] in ('Work Order','Work Order Revision') and [SmartLaborII Request Status] like 'Pending Approval (Level 3%'
and [Number of Positions Audit Flag] like 'FLAG%'
then 'Reject – Multi Position'
else ''
end as [ES Fully approved data 8],
-----------------------------------------


select *,
cast (
case
when [ES Fully approved data 1] = ''
and[ES Fully approved data 2] = ''
and [ES Fully approved data 3] = ''
and[ES Fully approved data 4] = ''
and [ES Fully approved data 5] = ''
and [ES Fully approved data 6] = ''
and [ES Fully approved data 7] = ''
and [ES Fully approved data 8] = ''

then
''
else
[ES Fully approved data 1] + ';'
+ [ES Fully approved data 2] + ';'
+ [ES Fully approved data 3] + ';'
+ [ES Fully approved data 4] + ';'
+ [ES Fully approved data 5] + ';'
+ [ES Fully approved data 6] + ';'
+ [ES Fully approved data 7] + ';'
+ [ES Fully approved data 8]


end
as varchar(255))
as [ES Fully approved final data to display]
from #tmp_SLII_FINAL

------ the O/P comes in--
Reject – New, Extension, Replacement invalid entry;Reject – Duration greater than 12 months;;;Reject – Multi Position

instead as of what I need to be-
Reject – New, Extension, Replacement invalid entry;Reject – Duration greater than 12 months;Reject – Multi Position

Thanks
DJ
Post #1520277
Posted Thursday, December 5, 2013 12:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 3,353, Visits: 7,245
Example:
IIF ( [ES Fully approved data 1] = '', '', [ES Fully approved data 1] + ';') + 
IIF ( [ES Fully approved data 2] = '', '', [ES Fully approved data 2] + ';')

This way you might end up with a semicolon at the end of the string.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520286
Posted Thursday, December 5, 2013 12:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
so if any of the string is '' then I do not want semicolon for it the problem im facing , will this be resolved ?

thanks
DJ
Post #1520291
Posted Thursday, December 5, 2013 12:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 3,353, Visits: 7,245
Have you tried it?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520301
Posted Thursday, December 5, 2013 1:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Hi Luis,
I tried but seems SQL server 2008 does not recognize 'IIF' is that being deprecated ?

thanks
dj
Post #1520316
Posted Thursday, December 5, 2013 2:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
IIF comes from SQL 2012 and not sqo 2008 hence need some alternative soultion please
Post #1520326
Posted Thursday, December 5, 2013 2:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Thanks much Luis for the help I used instead of IIF the alternative-
select *, cast (
case
when [ES Fully approved data 1] = ''
and[ES Fully approved data 2] = ''
and [ES Fully approved data 3] = ''
and[ES Fully approved data 4] = ''
and [ES Fully approved data 5] = ''
and [ES Fully approved data 6] = ''
and [ES Fully approved data 7] = ''
and [ES Fully approved data 8] = ''

then
null
else
case
when [ES Fully approved data 1] = ''
then ''
else [ES Fully approved data 1] + ';'
end
+
case
when [ES Fully approved data 2] = ''
then ''
else [ES Fully approved data 2] + ';'
end
+
case
when [ES Fully approved data 3] = ''
then ''
else [ES Fully approved data 3] + ';'
end
+
case
when [ES Fully approved data 4] = ''
then ''
else [ES Fully approved data 4] + ';'
end
+
case
when [ES Fully approved data 5] = ''
then ''
else [ES Fully approved data 5] + ';'
end
+
case
when [ES Fully approved data 6] = ''
then ''
else [ES Fully approved data 6] + ';'
end
+
case
when [ES Fully approved data 7] = ''
then ''
else [ES Fully approved data 7] + ';'
end
+
case
when [ES Fully approved data 8] = ''
then ''
else [ES Fully approved data 8]
end
end as varchar(255))as [ES Fully approved final data to display]
from #tmp_SLII_FINAL


It gives me the desired O/P now.

RegardsDJ
Post #1520335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse