• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Function to take a list of values and add qoutes to each value

Does anyone have a function written that can take a list of values and add quotes around each value.  Example input = 1001,1002,1003 Output '1001','1002','1003' However if they just entered one value it would return '1001' if 1001 was the input.  

Thanks in advance for the help,
Montrof
0
montrof
Asked:
montrof
  • 4
  • 4
1 Solution
 
momi_sabagCommented:
you don't need a function for that
try:

select
   case when posstr(list_value, ',') > 0 then ''''|| replace(list_VALUE, ',', ''',''') || ''''
            else ''''|| list_value || ''''

from your_table
0
 
montrofAuthor Commented:
Thank you for the suggestion but it does not seem to work because the case statement can only return one vaule.  The case is located in my where clause  and  {?SalesPerson} is a parameter passed from crystal


AND 
Case
WHEN  '{?Salesperson}' = 'ALL' THEN 1
WHEN '{?Salesperson}' <> 'ALL' AND OASMCD IN (
case when posstr({?Salesperson}, ',') > 0 then
char(39) || replace(char(44),{?Salesperson},  Char(39) || char(44) || char(39)) || char(39)
Else char(39) || {?Salesperson} || char(39)
END ) 
THEN 1
ELSE 0
END = 1

Open in new window

0
 
momi_sabagCommented:
that won't work with a function either
you can't do what you are trying to do
is OASMCD int or char?
if it's char, try to change the condition to
WHEN '{?Salesperson}' <> 'ALL' AND (
 < case comes here > like OASMCD||',%'||
or < case comes here > like ||'%,'||OASMCD||',%'||
or < case comes here > like ||'%,'||OASMCD
or ?Salesperson = OASMCD
)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
montrofAuthor Commented:
The case statement you are refereing to was the one you gave me previously. Just checking.
0
 
momi_sabagCommented:
yep
0
 
montrofAuthor Commented:
Still no go.  
0
 
momi_sabagCommented:
can you post your code here?
0
 
montrofAuthor Commented:
Thanks for the help, I had made a typo that is why it was not working thanks for you help
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now