SQL concatenate results

390 pts.
Tags:
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'

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    There is no '%' in your example. Also, what field corresponds to the Outlet code ? Please provide more details.
    69,365 pointsBadges:
    report
  • carlosdl
    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 "
    69,365 pointsBadges:
    report
  • 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 @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.
    390 pointsBadges:
    report
  • carlosdl
    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.
    69,365 pointsBadges:
    report
  • devan
    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
    390 pointsBadges:
    report
  • carlosdl
    Great ! Thanks for sharing.
    69,365 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following