How to choose between operators based on parameter value 1

[reportbert]

I'm trying to figure out how to change the comparison operator in my select statement based on the value of a parameter.

Pseudocode:

if (chooseparam = 'like') then
select column
from table
where column like 'anotherparam%'
else
select column
from table
where column = 'anotherparam'
end if

The problem i keep running into is that if i take this almost-code (corrected for syntax, of course) and put it in the report as a text query, i keep getting an 'invalid sql' error.

Any ideas as to what i'm doing wrong?

Thanks.

 

[unclerico]

can you create a stored procedure??

 

[reportbert]

Yes, and it works fine in the stored procedure, but i also have need of this code within the report.

BTW, i am using the MS OLE DB provider for Oracle in the data source... don't know if that affects it significantly...

 

[unclerico]

take a look at the last post in thread1462-1242761 and see if that helps you out.

 

[reportbert]

Ah.
I think this will work, but there is just one more thing:
how do i refer to the value of 'anotherparam' in custom code?

 

[reportbert]

... or do i just add another string parameter to the custom code function?

 

[unclerico]

that is exactly what you would do...add another parameter

 

[reportbert]

Okay, but now i get an 'invalid character' error:

Function GetSql(ByVal strFileType As String, ByVal strSearchRange As String, ByVal strSearchValue As String) As String

  Dim strSql, strFilestr, strSearchstr As String

  strSql = "SELECT bunch, of, columns"
  strSql += "FROM  table "
  strSql += "WHERE "

  Select Case strFileType
    Case "File Number"
      strFilestr = "FileNumber "
    Case "Agreement Number"
      strFilestr = "AgreementNumber "
  End Select

  strSql += strFilestr

  Select Case strSearchRange
    Case "Search all"
      strSearchstr = "LIKE " + strSearchValue
    Case "Search forward"
      strSearchstr = ">= " + strSearchValue
  End Select

  strSql += strSearchstr

  Return strSql

End Function

 

[unclerico]

you are still going to want single quotes around the string values and also use an ampersand as opposed to the plus symbol like this

Select Case strSearchRange
    Case "Search all"
      strSearchstr = "LIKE [b]'[/b]" [b]&[/b] strSearchValue [b]&[/b] "%'"
    Case "Search forward"
      strSearchstr = ">= [b]'[/b]" [b]&[/b] strSearchValue [b]&[/b] "'"
  End Select

 

[reportbert]

Thank you very much, it works great now!

I guess my VB is a little rusty...

 

[unclerico]

cool beans, glad you got it to work...thanks for the star.