Solved

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

Posted on 2009-05-05
8
340 Views
Last Modified: 2012-05-06
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
Comment
Question by:montrof
  • 4
  • 4
8 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24311266
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
 
LVL 1

Author Comment

by:montrof
ID: 24313947
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
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 24314563
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
 
LVL 1

Author Comment

by:montrof
ID: 24314604
The case statement you are refereing to was the one you gave me previously. Just checking.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 37

Expert Comment

by:momi_sabag
ID: 24314969
yep
0
 
LVL 1

Author Comment

by:montrof
ID: 24315567
Still no go.  
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24323082
can you post your code here?
0
 
LVL 1

Author Closing Comment

by:montrof
ID: 31578221
Thanks for the help, I had made a typo that is why it was not working thanks for you help
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now