rmassey
Posts: 12
|
| Posted: 05/10/2004, 11:16 AM |
|
I have a SQL question if I can ask it in this forum. I use CCS and MS SQL Server for the DB Server.
I need to search a view I built and another table. The view is a full outer join between two other tables. These two tables in the view relate although I need it for searching purposes. This is why I chose a full outer join to combine the data without merging the fields. The view is basically a merge between two old systems we would like to search on for comparisons. I would like to see both this view and the audit table in 1 grid. I would have the user search on SSN, Name, Med_RecNo, or even account number. It should pull all records with data from any of the 3 tables that it finds a match. If there is NOVA data then the clinstar may or may not have data in the same row it would be NULL. Same if it were reversed. This seems simple although I keep running into road blocks that doesn't get what I need.
To sum it up I have a total of three tables I want the user to search on SSN, Name, Med_RecNo, and or AcctNo. I need all three tables to report the resultes in a single grid row on the matches. 2 of the tables are in a full outer join view. I was thinking it would help CCS out better. I thought I would take a chance and post this here also. I read SQL if one knows how to write this problem out. I tried a full outer join on all three tables and it does not work.  Any help, code, or suggestions is greatly appreciated.
Thanks
-Robert
|
 |
 |
peterr
Posts: 5971
|
| Posted: 05/10/2004, 11:55 PM |
|
I'm not sure if you have a problem with getting this SQL to work at all, or your SQL is already working and tested and you only want to use in CCS?
I would basically start with writing an SQL query that works by itself and even add a WHERE clause for testing. Once I know that the SQL works then using it as a grid datasource in CCS should not be a problem.
That's why I'd like to understand where you're stuck: writing the SQL, using SQL in CCS, or using the WHERE clause for searching.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
rmassey
Posts: 12
|
| Posted: 05/11/2004, 8:01 AM |
|
Thanks Peter, I had to break it down further in order to get what I needed. Although, the query takes sometime to process. Even in SQL query Analyzer. I was wondering and I looked in help and was unable to find away to not show all records on start up. For instance when a user enters the search page I don't want to return any rows at that time. It takes forever for the page to load. I am assuming I would add an event to not process the SQL on load. I am afraid it will process the event each time even when a search is requested. There is probably some thread explaining how to do this. I will search there also and report back if I find anything. Thanks for your time and help. CCS rocks!! Oh and the Help documentation/examples has gotten me through many hurdles on this first real big app I am writing for my Hospital. Thanks again...
|
|
|
|
peterr
Posts: 5971
|
| Posted: 05/11/2004, 8:15 AM |
|
Great.
I recommend doing some work on properly indexing your tables. It often can solve many performance issues.
Regarding showing an empty grid take a look at http://support.codecharge.com/kb_article.asp?article_id=65
The SQL that will be executed in such case may be instant although I'm not sure.
You can also hide the whole grid in the Page After Initialization event when no search value was specified. For example in ASP:
If CCGetParam("s_field","")="" Then GridName.Visible = False
(not sure though if the SQL would be executed in such case therefore you might want to test it out.)
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
rmassey
Posts: 12
|
| Posted: 05/11/2004, 8:35 AM |
|
I tried both and the first option runs the SQL of course but the second option does not. So I only see the search grid which is cool in my case. It loads fast now... Thanks again Peter...
|
|
|
|
|
