SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issue with String concatenation need help please


Issue with String concatenation need help please

Author
Message
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42137 Visits: 19829
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42137 Visits: 19829
Have you tried it?


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
Hi Luis,
I tried but seems SQL server 2008 does not recognize 'IIF' is that being deprecated ?

thanks
dj
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
IIF comes from SQL 2012 and not sqo 2008 hence need some alternative soultion please
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search