If you want to try it with views it will be hard to walk you through it this way but I can try. There is probably a more elegant case statement process but here is one way. I use a lot of views to manipulate and pair down the data before i bring it into crystal.
Go into Enterprise Manager and under your DB and right click on views.
Select new view.
Add your client code table to the view.
Add the client name and client code into the view.
Group by client name
Uncheck the output next to the clientcode field (you only need the client name for this).
in the criteria on the client code field add your select statement (in ["362.11", "401.9", "402.00", "402.01", "402.1", "402.10", "402.11", "402.9", "402.90", "402.91", "403.00", "403.01", "403.11", "403.91", "404.00", "405.01", "405.09", "405.11", "405.19", "405.91", "405.99", "425.1", "437.2", "997.91"])
Save the view. I highly recommend using a good naming structure for views or you will get lost in them later.
Add another view and repeat the process but this time in the criteria get your second set of criteria ({client_code_id} in ["250" to "250.93", "362.00" to "362.02", "357.2", "366.41"])). here you want to find these not exclude them.
Create a third view but instead of adding a table add the first and second views you just created and join them on the client name (only item in each view) and make the join as <>. Add the client name from view1 to this view. You now have alist of clinets that meet your criteria.
Basically you are creating a table (view1) that lists all of clients you MAY want to show in the report (condition 1 met). In table/view2 you are creating a list of all clients you MAY want to exclude from the report (condition 2 met). View 3 compares them and gives you a list of clients that you ultimately do want to include (conditionas 1 and 2 met).
From here you just add view3 into your report and inner join it back to your table on the client name. This will then only return records from your table where these are matching and remove the need for your select statement.
IP Logged
