Wolfe

Drop all the ?. They are only used in parameterized views. There nothing you

can do to make this query better really as far as I can see. To make it run

as fully optimized make sure there are indexes on the two joined columns and

the four columns that are referenced in the WHERE clause. You'll have to

weigh the cost of maintaining the indexes against the need to minimize

execution time.

-Anders

----- Original Message -----

From: Wolfe Stephen S Civ GS-11 <Stephen.Wolfe@macdill.af.mil>

To: Multiple recipients of ProFox <profox@leafe.com>

Sent: tisdag den 17 augusti 1999 14:24

Subject: VFP6: Parametric SQL Question

| SQL experts:

|

| I need to tighten up my SQL (I will do some studying toward this

| end, too, however, I need to deliver a 'cross the product line' query COB

| today. I could cut and paste a bunch of the following SQL code, but I

| rather do it smartly and learn something in the process.

|

| To that end I'll expose some of my raggedy ol' SQL code for your

| inspection and fruitful critique.

|

| Code:

|

| IF oCEObj.ByTarget

| SELECT Staff.*, Lifesupp.ssan, Lifesupp.staffid,

| Lifesupp.staffidc,;

| Lifesupp.pals_date, Lifesupp.pals_exp,

| Lifesupp.pals_type,;

| Lifesupp.pals_insts, Lifesupp.pals_sched;

| FROM ce!Staff INNER JOIN ce!Lifesupp ;

| ON Staff.ssan = Lifesupp.ssan;

| WHERE Staff.hosp = ?OCEobj.hosp;

| AND Staff.Squadron = ?THIS.Squadron;

| AND Staff.Off_Sym = ?THIS.OfficeSymbol;

| AND Lifesupp.pals_exp <= ?THIS.RPTT1;

| AND Lifesupp.pals_exp # {};

| ORDER BY Staff.Squadron, Staff.Off_Sym,

| Staff.last_name;

| INTO CURSOR palquery

| ELSE

| SELECT Staff.*, Lifesupp.ssan, Lifesupp.staffid,

| Lifesupp.staffidc,;

| Lifesupp.pals_date, Lifesupp.pals_exp,

| Lifesupp.pals_type,;

| Lifesupp.pals_insts, Lifesupp.pals_sched;

| FROM ce!Staff INNER JOIN ce!Lifesupp ;

| ON Staff.ssan = Lifesupp.ssan;

| WHERE Staff.hosp = ?OCEobj.hosp;

| AND Staff.Squadron = ?THIS.Squadron;

| AND Staff.Off_Sym = ?THIS.OfficeSymbol;

| AND Lifesupp.pals_exp >= ?THIS.RPTT1;

| AND Lifesupp.pals_exp <= ?THIS.RPTT2;

| ORDER BY Staff.Squadron, Staff.Off_Sym,

| Staff.last_name;

| INTO CURSOR palquery

| ENDIF

| SELECT palquery

|

| How can I optimize the above? Note one select does a to 'date'

| query while the other does a between date query.

|

| The 'cross product lines' portion of this question comes into play

| where I want to get more than just the PALS expiration date data. Look in

| the WHERE clause and see the element comparison on [AND Lifesupp.pals_exp

<=

| ?THIS.RPTT1;]; can I parametrically substitute or build additional

compares

| for the BLS, ACLS, ATLS, and NRP products? All of the expiration date

| elements have been named <product_name>_exp.

|

| Will I have to build my own SQL query command and execute it like a

| macro?

|

| TIA,

|

|

| Stephen S. Wolfe, GS-11, DAF

| Deputy Chief, Medical Information Services

| DSN 968-6930 Comm: (813) 828-6930

| Email: wolfess@macdill.af.mil

|

|

|