How to pass multiple values to a single variable?

Hi,

  I have a variable @v. I have to assign multiple values to it, like 'AB','CD','EF' that are input through Business objects,so the format would be strictly 'AB','CD','EF'.Now, i need to assign these to @v as 'AB,CD,EF',as a single string.Please let me know how i can do that.

Thanks!
pvsbandiAsked:
Who is Participating?
 
grant300Commented:
I am obviously unaware of all the moving parts you have integrated into this whole thing.  It sounds like an architecture without an architect.  If you are ever going to have clean solutions to these integration issues, somebody has to be looking at how to make all the pieces work and play well together.

A much more effective way than trying to automate filling in prompt values in regularly scheduled reports is to have the values live in one or more parameter tables in the database.  The report SQL then pulls the parameters from the tables directly and you don't have all this mess and you can stop trying to push on a rope.

You can still use a Java application to fire off reports with special sets of parameters; you just have it create a unique ID that identifies the particular parameter set, stuff the parameters in the table(s) with that id, then schedule the report in BCA filling in a single prompt, the parameter_set_id.  For regularly scheduled daily reports, the parameter sets would be pre-built in nightly batch jobs and the reports schedule with a default or hard coded parameter_set_id.

It sounds as if your site has been trying to take un-modified user-written reports and automate them.  While it is theoretically possible (you are doing it) it is kind of unrealistic.  The automated reports should be setup to be automated and not simply duplicates of interactive reports.  At least that is what I have found works cleanest and most efficiently with BO.

Regards,
Bill
0
 
grant300Commented:
The short answer is, you can't.

Your only option is to enclose the prompt variable in double quotes put in front and back as literals.  Then you will get 'AB','CD','EF' in @v.

At that point, you have a couple of choices.  You can either write some T-SQL or maybe a JAVA function to reformat the string the way you want it or you can rework you code to use the list as it is passed in.

Just out of curiosity, how are you using the @v variable in T-SQL?  Can you show us a code snippet?

Probably the easiest way to reformat the string will be to do a STR_REPLACE on single quotes and replace them with nothing.  You can then enclose it in single quote literals if need be.

Regards,
Bill
0
 
pvsbandiAuthor Commented:
This is how iam having rightnow.

DECLARE @v char(50)

SELECT @v =  "@Prompt('Enter v','A',{'AB','CD','EF','GH'},MULTI,CONSTRAINED)"

SELECT @v

But we have an application that passes values to BCA and it doesn't parse this prompt function.
I have to somehow get those double quotes off of that @prompt.

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
grant300Commented:
O.K., I'm confused.

Is @v a Sybase T-SQL variable or a BO variable ?
@PROMPT is obviously the BO function.

If @v is a T-SQL variable, your code fragment does not make sense since you are mixing two different environments.

Are you calling a Sybase stored procedure or simply having BO construct SQL on the fly?

Clip the SQL that BO is generating and dump it in the code snippet for us.

Thanks,
Bill
0
 
pvsbandiAuthor Commented:
@v is a sybase T-SQL command and it works fine.
@Prompt is a BO command.

Iam trying to get all those values that a user is entering through the @Prompt into the variable @v,so i can do further calculations in the report.
BO is not generating any SQL.This is the Freehand SQL that iam writing.
Hope iam clear.
0
 
pvsbandiAuthor Commented:
OK. I have made it to work for now.But if you have a more elegant way,please let me know.

DECLARE @v char(50)

CREATE TABLE #T(AAA CHAR(2))
INSERT INTO #T VALUES('AB')
INSERT INTO #T VALUES('CD')
INSERT INTO #T VALUES('EF')
INSERT INTO #T VALUES('GH')

SELECT *
INTO #V
  FROM #T
WHERE AAA IN @Prompt('Enter v','A',{'AB','CD','EF','GH'},MULTI,CONSTRAINED)


UPDATE #V
       SET @v = @v + AAA + ","

SELECT @v = substring(@v,1,char_length(@v)-1)

SELECT @v
0
 
grant300Commented:
Ahhh, free form SQL.  Now I get it.

Wow, nice!  You packed a lot of clever techniques in a few lines.  Creating and loading a temporary helper table and the infamous Update trick.  I just wrote an article in the ISUG Tech Journal edition about to come out on that technique.

First, I would make the #T table a permanent helper table.
Second, I think you can skip creating the #V temp table altogether.
Finally, you can simplify the substring by prepending the comma instead of postpending it

See the snippet below for the modifications.

I am still curious.  Are you using the reformatted @v in an SQL someplace or are you just returning it as a formatted string to be displayed in your report?

Regards,
Bill

CREATE TABLE MP(AAA CHAR(2))
INSERT INTO MP VALUES('AB')
INSERT INTO MP VALUES('CD')
INSERT INTO MP VALUES('EF')
INSERT INTO MP VALUES('GH')
------------------------------------------------
DECLARE @v varchar(50)
 
UPDATE MP
   SET @v = @v + ',' + AAA
 WHERE AAA IN @Prompt('Enter v','A',{'AB','CD','EF','GH'},MULTI,CONSTRAINED)
 
SELECT @v = substring(@v,2,50)
 
SELECT @v

Open in new window

0
 
pvsbandiAuthor Commented:
Thanks Bill! Actually,@v is not used anywhere in the report.But when entering multiple values in the prompt,we have to assign it to a variable to be able to run the report.But the userresponse will be used in the report.

Can we just prompt the users and catch the userresponse,without having to assign a variable to it?

Thanks!
0
 
grant300Commented:
How is the @v value getting displayed on the report?  Is it on each row or is it in some header information?

Actually, I am not sure it matters.  You should be able to define a local BO variable and assign the prompt value to it, even if it is an isolated query in BO.  The the cell where you want to display it just displays the value of the BO variable.  I'm pretty sure there is no reason to run it all the way to the database engine and back.

Regards,
Bill
0
 
pvsbandiAuthor Commented:
Bill,

  Sure,there should be a variable assigned to the @prompt function.But the only catch is that this variable can't hold multiple values.I circumvent this problem by wrapping the prompt with double quotes.But there is a Java application at my work place which fills prompt values in report to be fed into BCA.This Java application is not parsing the prompt that is having these double quotes.So, i had to somehow avoid quoting the prompt and thus,all this procedure.

I hope i didn't confuse you :)
0
 
IncisiveOneCommented:
Actually, the solution is to use ONE @prompt for each prompt, and entirely avoid packing many promt values into one variable (which breaks many design rules).  Each @promt valu cabe be compared to table values for validation, etc. but it is not required to have prompt values in db tables.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.