Wildcard parameter 1

[robinsql]

I need to know if there is a wildcard character for a number field parameter. I am using crystal reports from the web and passing parameters into the where clause of my SQL Query via a URL string. My original report used string fields and the * as the wildcard. Is it possible to do this with a number field? Any help gratefully accepted. Thanks.

 

[Naith]

In a true number field, you can only substitute a number as your wildcard value.

Naturally, you can't use '*'. Try using an unlikely numerical value like -99999999. The syntax would be the same as you've used for '*'.

Naith

 

[robinsql]

Thanks Naith
I don't think that will work though. I don't think I explained very well what I needed. I'll try and be a bit clearer.
SELECT
VFleetSurvey.* FROM
Database
WHERE
ClientID = 123 AND '123 and 176 are the params
DepotID = 176
ORDER BY
ClientName ASC,
DepotName ASC
This works fine if I want to see the details for this particular client and depot, but if I want to see the details for this client and all depots associated with it, how can I pass in the parameter without changing the SQL query. Is there a like clause that I can use with numbers?
Something like
WHERE
ClientID = 123 AND
DepotID like *
i realise i cannot use the * symbol but is there another way of doing this without creating a whole new report?
Thank you. Robin

 

[Naith]

If you used a record selection formula like:

(If {?ClientID} <> -99999999
Then {ClientID} = {?ClientID}
Else
	If {?ClientID} = -99999999
	Then True)
)
and
(If {?DepotID} <> -99999999
Then {DepotID} = {?DepotID}
Else
	If {?DepotID} = -99999999
	Then True)
)

then in the case where you have a parameter value set for the ClientID, but the DepotID is left as the default -99999999 (you might want to show the description as 'All' or something, instead of showing the value -99999999. You can set this from the Parameter Default Options dialogue box) - then your inherited SQL would appear like this:

SELECT
    VFleetSurvey.* FROM
    Database
WHERE
    ClientID = 123
ORDER BY
    ClientName ASC

The wildcarded field simply isn't passed at all.

Naith

 

[Naith]

Ah. Belatedly, I notice you're passing your values through a URL. In this case, when you want a wildcard, if the default value doesn't change, simply pass != -99999999 instead of =.

So, your SQL ends up as

...
WHERE
    ClientID = 123 AND
    DepotID !=/<> -99999999
ORDER BY
    ClientName ASC,
    DepotName ASC

Naith

 

[robinsql]

Thanks a million Naith - problem solved.
Robin