maggiemel
Posts: 75
|
| Posted: 01/12/2004, 11:53 AM |
|
Hello all. I have a CCS page with a grid & search form. The grid displays information from two tables, Clients and Matters. I use an outer join so I can see a list of Clients even if there are no Matters assigned for a given Client.
The search form allows me to filter by Client # or Matter # and my custom SQL string looks like this:
SELECT ClientID, ClientNumber, ClientName, MatterID, MatterNumber, MatterName
FROM Clients LEFT OUTER JOIN Matters ON Clients.ClientID = Matters.FK_ClientID
WHERE ClientNumber LIKE '%{s_ClientNumber}' AND MatterNumber LIKE '%{s_MatterNumber}'
ORDER BY ClientNumber, MatterNumber
I am pretty sure this worked nicely for me for a while, but I recently noticed that my grid no longer displays Clients for whom there are no Matters listed. If I remove the WHERE clause I get a correct/complete list, but if I leave it in I get only those Clients who have matching records in the Matters table. Weird!
Of course, if I remove the WHERE clause then I get the correct results in my grid form, but my search form is then useless.
The parameters, I believe, are specified correctly. In any event -- nothing has changed since I first created this page.
Anyone have any ideas?
Thanks.
Melissa
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
 |
 |
Edd
Posts: 547
|
| Posted: 01/12/2004, 1:39 PM |
|
Sound like MatterNumber and ClientNumber are numeric. Like clauses where designed for alpha searching.
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/12/2004, 1:48 PM |
|
No, ClientNumber and MatterNumber fields are in fact defined as varchar (to allow the LIKE searching).
Thanks for the suggestion, but I'm still stuck.
Anyone else?
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
peterr
Posts: 5971
|
| Posted: 01/12/2004, 7:28 PM |
|
Could you please specify:
1. The programming language that you use (if we want to test this).
2. The database type.
3. The Default Value of the s_ClientNumber and s_MatterNumber parameters (selected in parameter's properties dialog).
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Nicole
Posts: 586
|
| Posted: 01/13/2004, 6:06 AM |
|
Melissa,
I suggest that you print the generated query on the live page and test it against the database. It helps you to identify what’s wrong. May be the data in tables were modified since you didn’t modify the query but get the different results.
_________________
Regards,
Nicole |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/14/2004, 6:29 AM |
|
Peter: this app uses ASP w/MS SQL. The default values are empty for both s_ClientNumber and s_MatterNumber.
Nicole: I took your suggestion and printed out the SQL, ORDER and WHERE variables before showing my gird and what I get is this:
SQL:SELECT ClientID, ClientNumber, ClientName, MatterID, MatterNumber, MatterName FROM Clients LEFT OUTER JOIN Matters ON Clients.ClientID = Matters.FK_ClientID WHERE Clients.ClientNumber LIKE '%{s_ClientNumber}' AND Matters.MatterNumber LIKE '%{s_MatterNumber}'
ORDER BY:ClientNumber, MatterNumber
WHERE:
When I run this against the db directly I get the same results as I do with my app, so there must be something wrong with the query. But I'm very confused because I'm sure it was working at one time. The data in the db has not changed.
Anyway, I thank you for your help but it's back to the drawing board for me in terms of constructing the filter piece. If you have any suggestions, please let me know!
Melissa
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
Edd
Posts: 547
|
| Posted: 01/14/2004, 11:29 PM |
|
Melissa,
On the BeforeExecuteSelect event Add some code to display the Grid's SQL, e.g.
Response.Write GridName.SQL & "<BR>" & GridName.Where
This will show you what is going on in the SQL
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
jstuart
Posts: 26
|
| Posted: 01/15/2004, 7:41 AM |
|
Melissa,
What has changed between the time the query was working and now? IE any upgrades to the database?
_________________
Jeff Stuart
jstuart@computer-city.net
|
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/15/2004, 8:15 AM |
|
Edd: thanks -- that's just what I did to get the output I show in the previous post.
Jeff: nothing changed re: the database or web server.
I've thought of a work-around, though. What I might do is create TWO grids: one that is visible when no search parameters are passed; the other visible with the returned results of the search. It's kinda dumb, I know, but I just can't figure out how and why this page stopped functioning the way it originally did (i.e., the outer join worked properly ).
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/15/2004, 8:34 AM |
|
Ah ha! I think I can see what's happening, but I'm not sure how to fix it.
When the join happens, where there is no matching Matter for a Client, the fields from the Matter table show NULL. I'm thinking that the LIKE statement won't work against these NULL fields and wondering how I can convert those fields to empty values rather than null ones.
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/15/2004, 8:50 AM |
|
TA DA!
Thanks everyone for all your support. The answer is to modify the query to allow for NULL values where there is no matching Matter for a Client. The new where statement:
WHERE ClientNumber LIKE '%' AND (MatterNumber LIKE '%' OR
MatterNumber IS NULL)
returns all the properly joined records and still keeps the functionality of the search form. Hooray!
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
|
