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