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 @ 5:24 PM
Points: 3,784, Visits: 8,489
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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 @ 5:24 PM
Points: 3,784, Visits: 8,489
Have you tried it?


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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