Home > IT Answers > Development > SQL concatenate results
devan
365 pts.
 SQL concatenate results
Concatenate, SQL, SQL Server
Hi

Need help here:-

 I am running this sql on business object and when I prompt for the Outlet Code, I need the results to automatically add in the % on the result field like the one in the example. How can i do this?

SELECT FDDWHD20.DCOUT1, FSDWHE01.FSSHIP, FDDWHD20.DCNAME, FDDWHD20.DCMKDS, FDDWHD20.DCOTD1, FDDWHD20.DCOTD2, FDDWHD20.DCOTD3, FDDWHD20.DCOUT3, FDDWHD20.DCOUT2, FDDWHD20.DCOTD6, FDDWHD20.DCOUT6 FROM FDDWHD20, FSDWHE01 WHERE ( FDDWHD20.DCCO=FSDWHE01.FSCO ) AND ( FDDWHD20.DCSHIP=FSDWHE01.FSSHIP ) AND FDDWHD20.DCOUT1 LIKE '2'



Software/Hardware used:
ASKED: February 21, 2011  11:31 AM
UPDATED: February 23, 2011  1:46 PM
RELATED QUESTIONS
Answer Wiki:
You could do something like this: <pre>... AND FDDWHD20.DCOUT1 LIKE <b>{?your_parameter} & "*"</b></pre> Or you could use a Record Selection formula, using the <i><b>startswith operator</b></i>
Last Wiki Answer Submitted:  February 21, 2011  3:26 pm  by  devan   365 pts.
All Answer Wiki Contributors:  devan   365 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

There is no ‘%’ in your example.
Also, what field corresponds to the Outlet code ?

Please provide more details.

carlosdl
 63,535 pts.
 

Additional details provided by Devan:

“SELECT FDDWHD20.DCOUT1, FSDWHE01.FSSHIP, FDDWHD20.DCNAME, FDDWHD20.DCMKDS, FDDWHD20.DCOTD1, FDDWHD20.DCOTD2, FDDWHD20.DCOTD3, FDDWHD20.DCOUT3, FDDWHD20.DCOUT2, FDDWHD20.DCOTD6, FDDWHD20.DCOUT6 FROM FDDWHD20, FSDWHE01 WHERE ( FDDWHD20.DCCO=FSDWHE01.FSCO ) AND ( FDDWHD20.DCSHIP=FSDWHE01.FSSHIP ) AND FDDWHD20.DCOUT1 LIKE ’2%’

From the above,
the % will be after the ’2%’
Outlet code is FDDWHD20.DCOUT1
When prompted, i don’t want our users to enter 2%, I want them to enter 2 and then the above sql must automatically genreated results with the % eg all the coutlet code that starts with 2 must appear on the report.
You will also note that there is customer as well and the same field is being used for both customer and outlet “

carlosdl
 63,535 pts.
 

SELECT
FDDWHD20.DCOUT1,
FSDWHE01.FSSHIP,
FDDWHD20.DCNAME,
FDDWHD20.DCMKDS,
FDDWHD20.DCOTD1,
FDDWHD20.DCOTD2,
FDDWHD20.DCOTD3,
FDDWHD20.DCOUT3,
FDDWHD20.DCOUT2,
FDDWHD20.DCOTD6,
FDDWHD20.DCOUT6
FROM
FDDWHD20,
FSDWHE01
WHERE
( FDDWHD20.DCCO=FSDWHE01.FSCO )
AND ( FDDWHD20.DCSHIP=FSDWHE01.FSSHIP )
AND
FDDWHD20.DCOUT1 LIKE @prompt(‘Enter Outlet Code L1:’,'A’,'Outlet GroupingOutlet Code L1′,Mono,Free,Persistent,,User:0)

Hi Carlosdl
From the above, when I select the use custom query, it appears like this.

When I enter what you suggested
FDDWHD20.DCOUT1 LIKE {@prompt(‘Enter Outlet Code L1:’,'A’,'Outlet GroupingOutlet Code L1′,Mono,Free,Persistent,,User:0)} & “*”
it comes up with an error ORA-00911
is there something you can see that I am doing incorrectly.

devan
 365 pts.
 

Have you tried without the curly brackets ?

...LIKE @prompt(’Enter Outlet Code L1:’,'A’,'Outlet GroupingOutlet Code L1′,Mono,Free,Persistent,,User:0) & “*”

instead of:

...LIKE {@prompt(’Enter Outlet Code L1:’,'A’,'Outlet GroupingOutlet Code L1′,Mono,Free,Persistent,,User:0)} & “*”

ps. I don’t a have a BO installation at hand to test it.

carlosdl
 63,535 pts.
 

Hi Carlosdl

I have tried without the curly brackets and same error.
However, I have tried it with double pipes and it worked

Thanx for all your help

Devan

devan
 365 pts.
 

Great !

Thanks for sharing.

carlosdl
 63,535 pts.