Query result help

[dbsql]

Hi i am trying to query oracle from SSRS and so far successful but when i try to do this:

select testname, testid
from test
where testname like '%'+upper(?) +'%'

then get error saying that invalid number i know i could do something like that in SQL but having trouble in oracle!

i am doing that so in parameter user don't have to put % !!

thanks


Thanks!!

 

[LKBrwnDBA]

Try this:

...like UPPER('%scott%')




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

DBSQL,

I am not familiar with SRSS (is that an FLA?), so I am not familiar with how that environment resolves requests for user-supplied value entries (which I presume you are requesting with the "?" entry.

Therefore, I'll show you have to resolve your problem using SQL*Plus (to which you should have access if you are running Oracle), and which you can then extrapolate to the SRSS environment.

Following is a script which I have named, "tt_419.sql":

set verify off
accept tname prompt "Enter (part of) the name of a test to display: "
select testname, testid
from test
where upper(testname) like '%'||upper('&tname')||'%';

Notice that we concatenate in Oracle by using the "||" operator. Also note that the SQL*Plus string-replacement symbol is "&".

Following are the invocation and the results of invoking "tt_419.sql" from SQL*Plus:

SQL>  @tt_419
Enter (part of) the name of a test to display: test

TESTNAME                 TESTID
-------------------- ----------
This is test 1.               1

Let us know if this leads to a resolution to your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

And, yes, LKBrwnDBA's suggestions further simplifies the code:

...where upper(testname) like upper('%&tname%');

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

dbsql,

I am guessing here, but do you come from a sql server background? I believe that the '+' symbol is used for string concatenation in sql server, but for future reference, you should be aware that the Oracle equivalent is a double pipe '||'.

Regards

Tharg

Grinding away at things Oracular