Andrew B
|
| Posted: 10/17/2002, 8:16 PM |
|
I am having a prob. getting CCS to do exactly what I want with SQL statements. Here is my problem :
I need to use 'DISTINCT' in one of my queries (don't ask, i didn't do the data), as well as search on 5 of the fields. When I do this the normal way in the builder, the 'table' method, I can get all my searches in there just fine. If I don't search it doesn't use any search params, but it doesn't let me use 'DISTINCT'!
If I use the 'SQL' route and hand code my query I can use distinct. However, it appears to always include the search params (i.e, WHERE MyID={MyID}) which makes it fail or go wrong since it searches for blank numbers or the defaults.
Is there any way around this? I understand the issues for the CCS guys in making the generic query engine, but it would be nice to have a 'SQL with AutoParams' selection in the 'Data Source Type' dropdown. If there is a good way to combine the two, that would be great.
Thanks,
Andrew
|
|
|
 |
Ryan
|
| Posted: 12/08/2002, 4:45 PM |
|
I am having the same problem and would appreciate a response.
|
|
|
|
Martin
|
| Posted: 12/09/2002, 4:59 AM |
|
In situations like this I have found it best to create a separate "View" comprised of the sql query and incorporating the "DISTINCT" clause in the sql view.
Select this "view" rather than using your hand coded sql... and pass the params as you normally would.
|
|
|
|
AndrewI
|
| Posted: 12/09/2002, 4:59 AM |
|
Try:
WHERE ... AND ( MyID = {MyID} OR {MyID} = '' ) AND ...
So, if {MyID} is a search parameter, the first part searches for it in the normal way. If {MyID} is blank (''), the second part of the clause is True, and all records are returned. You can do this for a number of different search fields, but note the bracketing.
-I tend to set up the parameter (in the SQL builder) for IDs so that the default value is 0. Then I use the following, which avoids any problem with the quote marks:
WHERE ... (MyID = {MyID} OR {MyID} = 0) ...
|
|
|
|
|
