Stephen,

Bottom line, there's no harm in using the second query for both

instances. (Unless you count nano-seconds ;) The code below just

forces there to be a value in your second date. (If the ending date

is not required, the ending date will be set to the starting date.)

Here goes:

LOCAL ldDate1, ldDate2

ldDate1 = THIS.RPTT1

IF oCEObj.ByTarget

ldDate2 = THIS.RPTT1

ELSE

ldDate2 = THIS.RPTT2

ENDIF

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

HTH

Doug

> 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?