SSRS 2008 Oracle LIKE with parameter - invalid number error 1

[SSRS4Evr]

Good day everyone,

I am working with SSRS 2008 with an Oracle datasource -

Need to give the report parameter for the OrgSequenceNumber ... but they only need to be able to give the first couple numbers ...in other words they want a "starts with" parameter. I know this is LIKE @Parameter + '%' -- but I keep getting an "invalid number" error from the datasource. Any idea what I'm doing wrong?

THIS WORKS--

SELECT     DESCRIPTION, DAILY_INJ_DATE, METERED_VOL, INJ_TUBING_PRESS, INJ_FLUID_TYPE, SHORT_ORGSEQNO
from         ODSVIEWS.ODSV_MONTHLY_INJ_REPORT 
WHERE (SHORT_ORGSEQNO LIKE '16%')

But this DOES NOT...

SELECT     DESCRIPTION, DAILY_INJ_DATE, METERED_VOL, INJ_TUBING_PRESS, INJ_FLUID_TYPE, SHORT_ORGSEQNO
from         ODSVIEWS.ODSV_MONTHLY_INJ_REPORT 
WHERE (SHORT_ORGSEQNO LIKE :OrgSeqNo + '%')

When prompted I enter 16 as the parameter value and then I get the "ORA-01722:Invalid Number" error

I even tried...

SELECT     DESCRIPTION, DAILY_INJ_DATE, METERED_VOL, INJ_TUBING_PRESS, INJ_FLUID_TYPE, SHORT_ORGSEQNO
from         ODSVIEWS.ODSV_MONTHLY_INJ_REPORT 
WHERE (SHORT_ORGSEQNO LIKE TO_CHAR(:OrgSeqNo)+'%')

The SHORT_ORGSEQNO field is a varchar(8) so there should be no need for the TO_CHAR, but I thought I'd try anything to get it working. I am missing something though -- anyone got any ideas?

Thanks in advance for any help!!
Sincerely
Brenda L.

 

[skaestner]

select * from mytable where name like '%' + @myparam + '%'

or in case of oracle i think use this

select * from mytable where name like '%' + :myparam + '%'

i'm not sure this is what you are looking for

 

[skaestner]

oops forgot Oracle does not use the + to concatenate

select * from mytable where name like '%' || :myparam || '%'

 

[SSRS4Evr]

You! ...are a genius! That was it exactly!

I now remember having to concatenate with ORACLE with the || ("pipes") once before, but it had not occured to me this time. Thank you very much!!

Sincerely,
Brenda L.