Help converting SQL LiveReport to Oracle

Pinki_Pathak

Hi All,

Could anyone please help me in converting below query in Oracle

select * from tblprojects where project like (%1 + '%%') or description like (%2 + '%%')

Where %1 and %2 are user inputs in string form and report fetch the date based on wildcard search.

And Tbl project is our custom table.

Thanks in advance for help.

Pinki Pathak

Find more posts tagged with

Comments

Colin_Schmidt

Change (%1 + '%%') to %1||'%%'. The "+" is SQL's variation on concatenation. Oracle uses "||'.

Colin J.

On Wed, May 6, 2015 at 8:28 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Help converting SQL LiveReport to Oracle Posted bypinki.pathak@nationalgrid.com (pathak, Pinki)On 05/06/2015 08:25 AM

Hi All,Could anyone please help me in converting below query in Oracleselect * from tblprojects where project like (%1 + '%%') or description like (%2 + '%%')Where %1 and %2 are user inputs in string form and report fetch the date based on wildcard search.And Tbl project is our custom table.Thanks in advance for help.Pinki Pathak
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Pinki_Pathak

Hi Colin,

Thank you for your help. Actually I missed some thing in my previous post.

I have converted the query to Oracle:

select * from tblprojects where project like (%1 || '%%' ) or description like (%2 || '%%')

But now the problem is the same query in SQL was sorting the result based on project when the description field was empty unlike in Orcale.

In Oracle when we write e,g 123 as input to %1 and leaving %2 input as empty, it is giving whole result.

i.e select * from tblprojects.

Please advise.

Thank you

Warm Regards,

Pinki Pathak

Colin_Schmidt

Sorting is done with "ORDER".

select * from tblprojects where project like (%1 || '%%' ) or (description like (%2 || '%%') and %2 is not NULL) order by project

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Office)

+1 918.904 9223 (Cell)

MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Fri, May 8, 2015 at 2:37 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Help converting SQL LiveReport to Oracle Posted bypinki.pathak@nationalgrid.com (pathak, Pinki)On 05/08/2015 02:32 AM

Hi Colin,Thank you for your help. Actually I missed some thing in my previous post.I have converted the query to Oracle:select * from tblprojects where project like (%1 || '%%' ) or description like (%2 || '%%')But now the problem is the same query in SQL was sorting the result based on project when the description field was empty unlike in Orcale.In Oracle when we write e,g 123 as input to %1 and leaving %2 input as empty, it is giving whole result.i.e select * from tblprojects.Please advise.Thank you :)Warm Regards,Pinki Pathak
[To post a comment, use the normal reply function]
Topic: Help converting SQL LiveReport to Oracle
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center