December 5, 2013 at 12:00 pm
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
December 5, 2013 at 12:10 pm
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.
December 5, 2013 at 12:17 pm
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
December 5, 2013 at 12:40 pm
December 5, 2013 at 1:11 pm
Hi Luis,
I tried but seems SQL server 2008 does not recognize 'IIF' is that being deprecated ?
thanks
dj
December 5, 2013 at 2:06 pm
IIF comes from SQL 2012 and not sqo 2008 hence need some alternative soultion please
December 5, 2013 at 2:30 pm
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
endas varchar(255))as [ES Fully approved final data to display]
from #tmp_SLII_FINAL
It gives me the desired O/P now.
RegardsDJ
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply