RES
Posts: 21
|
| Posted: 06/27/2005, 7:49 AM |
|
in my datasource for a grid i have the following query:
SELECT CARE_ENCOUNTER.ENCOUNTER_ID, PATIENT.PATIENT_IDENTIFIER, PATIENT.DATE_OF_BIRTH, PATIENT.ADDRESS_LINE_1, PATIENT.ADDRESS_LINE_2, PATIENT.CITY,PATIENT.STATE,
PATIENT_DEMOGRAPHIC.FAMILY_NAME, PATIENT_DEMOGRAPHIC.GIVEN_NAME, PATIENT_DEMOGRAPHIC.MIDDLE_NAME, PATIENT_DEMOGRAPHIC.ZIP_CODE, CARE_ENCOUNTER.ENCOUNTER_START_DATE
FROM CARE_ENCOUNTER,PATIENT,PATIENT_DEMOGRAPHIC
WHERE ((CARE_ENCOUNTER.PATIENT_IDENTIFIER = PATIENT.PATIENT_IDENTIFIER(+))
AND PATIENT.PATIENT_IDENTIFIER = PATIENT_DEMOGRAPHIC.PATIENT_IDENTIFIER)
AND ( ( CARE_ENCOUNTER.ENCOUNTER_ID = '{s_keyword}'
OR PATIENT.PATIENT_IDENTIFIER = '{s_keyword}' ) )
Because I set up my form to use only one textbox with a keyword it takes way too long to post the result. I want to set up an if statement that will run one query if 8 characters long, or run another query if 16 characters long; for an encounter id and patient id respectively. Where would i put the logic to select the query when i hit search. And what would i put in the data source box for the solution. Thanks in advance.
_________________
RES |
 |
 |
RES
Posts: 21
|
| Posted: 06/27/2005, 11:15 AM |
|
anyone?
_________________
RES |
|
|
|
peterr
Posts: 5971
|
| Posted: 06/27/2005, 11:47 AM |
|
I think that such query could be very fast (instant) if those fields were indexed: CARE_ENCOUNTER.ENCOUNTER_ID and PATIENT.PATIENT_IDENTIFIER.
But if indexiing doesn't solve this for some reason (maybe because of joins between tables) then you could:
1. Create two grids and programmatically hide one of them that doesn't need to display the data.
or
2. Use PHP function like str_replace to dynamically replace part of your query, like:
$GridName->ds->SQL = str_replace("SELECT * FROM TABLE1", "SELECT * FROM TABLE2", $GridName->ds->SQL)
See http://docs.codecharge.com/studio/html/ProgrammingTechn...yingOutput.html and http://docs.codecharge.com/studio/html/Components/RTProperties/PHP/SQL.html on how to access the current SQL statement.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
RES
Posts: 21
|
| Posted: 06/27/2005, 12:16 PM |
|
Here is my code in the BeforeBuildSelect. But when i run the page and i set the debugging to 1 in db_oci.php the query shows that the where statement is not being changed to PATIENT.PATIENT_IDENTIFIER. But i get no error.
global $CARE_ENCOUNTER_PATIENT;
// Write your own code here.
$searchstring = CCGetParam("s_keyword");
$countofstring = strlen($searchstring);
if($countofstring==16){
$CARE_ENCOUNTER_PATIENT->ds->SQL = str_replace("WHERE ((CARE_ENCOUNTER.PATIENT_IDENTIFIER = PATIENT.PATIENT_IDENTIFIER(+)) AND PATIENT.PATIENT_IDENTIFIER = PATIENT_DEMOGRAPHIC.PATIENT_IDENTIFIER) AND ( ( CARE_ENCOUNTER.ENCOUNTER_ID = '{s_keyword}') )", "WHERE ((CARE_ENCOUNTER.PATIENT_IDENTIFIER = PATIENT.PATIENT_IDENTIFIER(+)) AND PATIENT.PATIENT_IDENTIFIER = PATIENT_DEMOGRAPHIC.PATIENT_IDENTIFIER) AND ( ( PATIENT.PATIENT_IDENTIFIER = '{s_keyword}') )", $CARE_ENCOUNTER_PATIENT->ds->SQL);
}
_________________
RES |
|
|
|
wkempees
|
| Posted: 06/27/2005, 12:55 PM |
|
RES
Do it in BeforeExecuteSelect.
Peterr will posibly respond and explain why.
I don't know why because documentation clearly states that what you do
is right, but this morning I did the same and tried the
BeforeExecuteSelect and that does modify the SQL nicely.
Concerning your SQL (not knowing which Db you are using)
SELECT CARE_ENCOUNTER.ENCOUNTER_ID,
PATIENT.PATIENT_IDENTIFIER,
PATIENT.DATE_OF_BIRTH,
PATIENT.ADDRESS_LINE_1,
PATIENT.ADDRESS_LINE_2,
PATIENT.CITY,PATIENT.STATE,
PATIENT_DEMOGRAPHIC.FAMILY_NAME,
PATIENT_DEMOGRAPHIC.GIVEN_NAME,
PATIENT_DEMOGRAPHIC.MIDDLE_NAME,
PATIENT_DEMOGRAPHIC.ZIP_CODE,
CARE_ENCOUNTER.ENCOUNTER_START_DATE
FROM CARE_ENCOUNTER
LEFT JOIN PATIENT ON CARE_ENCOUNTER.PATIENT_IDENTIFIER =
PATIENT.PATIENT_IDENTIFIER(+)
LEFT JOIN PATIENT_DEMOGRAPHIC ON PATIENT.PATIENT_IDENTIFIER =
PATIENT_DEMOGRAPHIC.PATIENT_IDENTIFIER
WHERE ( CARE_ENCOUNTER.ENCOUNTER_ID = '{s_keyword}'
OR PATIENT.PATIENT_IDENTIFIER = '{s_keyword}' )
Simplifies the Where clause to be handled, joining should speed things
up, and an EXPLAIN should tell you which indices you need.
PS LEFT RIGHT JOIN is up to you, as you know the DB schematics.
Walter
|
|
|
|
RES
Posts: 21
|
| Posted: 06/27/2005, 1:19 PM |
|
thanks for your help. but the BeforeExecuteSelect didn't work either. It's not even picking it up.
as far as the query goes, my SQL is definately at an intermediate level. still learning the syntax, i am not too familiar with the JOINS. if i can get this strReplace to work, then I dont think i will need to change the SQL. but i will try these joins when i read up on them more. by the way, it's an oracle db.
_________________
RES |
|
|
|
|
