All,

(hey, everyone is urged to take a stab at this, but note that this is VFP3 and Outer Joins are simulated...and slower)

I have two tables and need to extract information from both to create table three.

Examples of the two data tables:

TABLE A (both fields are always populated, ID serves as a key)

ID POLICY

1 111111

2 222222

3 333333

4 444444

5 555555

TABLE B (answer field is a MEMO and may be blank; Question may be blank, ID is never blank)

ID QUESTION ANSWER

1 1 Bob

1 2

2 1

2 2

3

3 2 Alaska

4 1 Larry

4 2 Texas

5 1 Joe

5 2 Virginia

The desired result:

TABLE C

POLICY CLIENTNAME STATE ID

111111 Bob 1

222222 2

333333 Alaska 3

444444 Larry Texas 4

555555 Joe Virginia 5

Description of TABLE C:

Include one record for ALL Policies from Table A whether there exists a record in TABLE B or not.

Include only one record for each Policy from Table A.

Include ID and POLICY in each record, but CLIENTNAME (answer to Question 1) and STATE (answer to Q 2) may be blank.

My understanding:

I don't think this can be done without three to four steps. Because this is VFP3, I am using a simulated OUTER join. To keep this message short, I am not including code samples, but can do so easily. While the memo field is sure to contribute to some performance degradation, it is necessary due to some answers being free-form, unlimited length (such as a COMMENTS field). The 255 character limit was unacceptable for this application.

Attempts so far:

I've got indexes on all fields. I've tried with and without DELETED tags. The tables have virtually no deleted records due to a record-recycling approach I've built in.

The problem:

The above task is taking 3 to 4 minutes over a network with 30 to 40 users with tables that only have several thousand records. I need to get it down into the seconds (like 15 to 30).

Question(s):

Can this be done in one step? Is the problem the way the tables are set up?

HELP!

Michael Copeland

Genesis Group Software

mailto:mike.copeland@genesis-group.net

Mike, I see that you are using upper/lower case in your example - did you

pay attention to this with your indexes (for Rushmore)?

e.g. If I understand Rushmore clearly, if you operate on a field with

UPPER(field), it is not optimizable ... e.g. the indexes and the expressions

against them have to be identical ...

Another thought: if it turns out to be memo fields are causing the

performance problem, maybe you could replicate the 1st 255 chrs into a fixed

field just for this operation (I know that can lead to other headaches, but

if this performance problem *has* to be solved, and it *is* the memo field

causing it ...)

Bill

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

From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Michael

Copeland

Sent: Monday, July 26, 1999 1:01 PM

To: Multiple recipients of profox

Subject: VFP3 SQL question

All,

(hey, everyone is urged to take a stab at this, but note that this is VFP3

and Outer Joins are simulated...and slower)

I have two tables and need to extract information from both to create

table three.

Examples of the two data tables:

TABLE A (both fields are always populated, ID serves as a key)

ID POLICY

1 111111

2 222222

3 333333

4 444444

5 555555

TABLE B (answer field is a MEMO and may be blank; Question may be

blank, ID is never blank)

ID QUESTION ANSWER

1 1 Bob

1 2

2 1

2 2

3

3 2 Alaska

4 1 Larry

4 2 Texas

5 1 Joe

5 2 Virginia

The desired result:

TABLE C

POLICY CLIENTNAME STATE ID

111111 Bob 1

222222 2

333333 Alaska 3

444444 Larry Texas 4

555555 Joe Virginia 5

Description of TABLE C:

Include one record for ALL Policies from Table A whether there exists a

record in TABLE B or not.

Include only one record for each Policy from Table A.

Include ID and POLICY in each record, but CLIENTNAME (answer to Question

1) and STATE (answer to Q 2) may be blank.

My understanding:

I don't think this can be done without three to four steps. Because this

is VFP3, I am using a simulated OUTER join. To keep this message short, I am

not including code samples, but can do so easily. While the memo field is

sure to contribute to some performance degradation, it is necessary due to

some answers being free-form, unlimited length (such as a COMMENTS field).

The 255 character limit was unacceptable for this application.

Attempts so far:

I've got indexes on all fields. I've tried with and without DELETED tags.

The tables have virtually no deleted records due to a record-recycling

approach I've built in.

The problem:

The above task is taking 3 to 4 minutes over a network with 30 to 40 users

with tables that only have several thousand records. I need to get it down

into the seconds (like 15 to 30).

Question(s):

Can this be done in one step? Is the problem the way the tables are set

up?

HELP!

Michael Copeland

Genesis Group Software

mailto:mike.copeland@genesis-group.net

On 7/26/99 1:01 PM, Michael Copeland supposedly said:

>I have two tables and need to extract information from both to create

>table three.

Looks like you need to check out cross-tabs. There is a check box in

the query designer which triggers crosstab generation, although I don't

remember exactly where it is located in 3.0. Check that out and if you

get stuck, give a holler.

___/

/

__/

/

____/

Ed Leafe

http://leafe.com/

If you set:

SET COLLATE TO GENERAL

and then create your indexes, you can perform case-insensitive searches

in VFP, but note that the default is:

SET COLLATE TO MACHINE

Ê

Therefore, your second para is correct assuming that the index was not

created with the expression UPPER(field) or SET COLLATE TO GENERAL.

Ê

Mike Antonovich

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

From: Bill Arnold [mailto:bill@wjarnold.com]

Sent: Monday, July 26, 1999 1:19 PM

To: Multiple recipients of profox

Subject: RE: VFP3 SQL question

Mike, I see that you are using upper/lower case in your example - did

you pay attention to this with your indexes (for Rushmore)?

Ê

e.g. If I understand Rushmore clearly, if you operate on a field with

UPPER(field), it is not optimizable ... e.g. the indexes and the

expressions against them have to be identical ...

Ê

Another thought: if it turns out to be memo fields are causing the

performance problem, maybe you could replicate the 1st 255 chrs into a

fixed field just for this operation (I know that can lead to other

headaches, but if this performance problem *has* to be solved, and it

*is* the memo field causing it ...)

Ê

Ê

Ê

Bill

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

From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Michael

Copeland

Sent: Monday, July 26, 1999 1:01 PM

To: Multiple recipients of profox

Subject: VFP3 SQL question

All,

(hey, everyone is urged to take a stab at this, but note that this is

VFP3 and Outer Joins are simulated...and slower)

Ê

I have two tables and need to extract information from both to create

table three.

Examples of the two data tables:

ÊÊÊ TABLE A (both fields are always populated, ID serves as a key)

IDÊÊÊÊÊÊÊÊÊÊÊÊÊÊ POLICY

1ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 111111

2ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 222222

3ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 333333

4ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 444444

5ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 555555

Ê

ÊÊÊ TABLE BÊ (answer field is a MEMO and may be blank; Question may be

blank, ID is never blank)

IDÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ QUESTIONÊÊÊÊÊÊÊÊÊÊÊ ANSWER

1ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 1ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ Bob

1ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 2ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ

2ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 1ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ

2ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 2ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ

3ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ

3ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 2ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ Alaska

4ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 1ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ Larry

4ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 2ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ Texas

5ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 1ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ Joe

5ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 2ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ Virginia

Ê

The desired result:

ÊÊÊ TABLE C

POLICYÊÊÊÊÊÊÊ CLIENTNAMEÊÊÊÊÊÊÊ STATEÊÊÊÊÊÊÊÊÊÊÊ IDÊÊÊÊÊÊÊÊÊÊ

111111ÊÊÊÊÊÊÊÊÊ BobÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 1

222222ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ 2

333333ÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ AlaskaÊÊÊÊÊÊÊÊÊÊÊÊ 3

444444ÊÊÊÊÊÊÊÊÊ LarryÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ TexasÊÊÊÊÊÊÊÊÊÊÊÊÊ 4

555555ÊÊÊÊÊÊÊÊÊ JoeÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊÊ VirginiaÊÊÊÊÊÊÊÊÊÊÊ 5

Ê

ÊÊÊ Description of TABLE C:

Include one record for ALL Policies from Table A whether there exists a

record in TABLE B or not.

Include only one record for each Policy from Table A.

Include ID and POLICY in each record, but CLIENTNAME (answer to Question

1) and STATE (answer to Q 2) may be blank.

Ê

ÊÊÊ My understanding:

I don't think this can be done without three to four steps. Because this

is VFP3, I am using a simulated OUTER join. To keep this message short,

I am not including code samples, but can do so easily. While the memo

field is sure to contribute to some performance degradation, it is

necessary due to some answers being free-form, unlimited length (such as

a COMMENTS field). The 255 character limit was unacceptable for this

application.

Ê

ÊÊÊ Attempts so far:

I've got indexes on all fields. I've tried with and without DELETED

tags. The tables have virtually no deleted records due to a

record-recycling approach I've built in.

Ê

ÊÊÊ The problem:

The above task is taking 3 to 4 minutes over a network with 30 to 40

users with tables that only have several thousand records. I need to get

it down into the seconds (like 15 to 30).

Ê

ÊÊÊ Question(s):

Can this be done in one step? Is the problem the way the tables are set

up?

Ê

HELP!

Michael Copeland

Genesis Group Software

mailto:mike.copeland@genesis-group.net

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

one of the things that we have learned over here at RSA (See Foxpro Advisor May

1999 issue) is that having multiple Rushmore optimizable expressions in the

WHERE from the same table can wreak havoc on performance (local or across the

LAN). I would have three suggestions for you:

1. Move all but one of the qualifications from the Staff table to the HAVING

clause (leave the one that will narrow the results down the most in the WHERE

clause)

2. Always set values of This.Rpt1 and This.Rpt2 and change the <= to always use

This.Rpt2 and the >= This.Rpt1 so that in the case of oCEOjb.ByTarget, you can

set this.Rpt1 = {} (thus accomplishing the same as the extra filter condition on

# {})

3. Use the SQL BETWEEN for your date range comparision

Wolfe Stephen S Civ GS-11 <Stephen.Wolfe@macdill.af.mil> on 08/17/99 08:24:02 AM

Please respond to profox@leafe.com

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

cc: (bcc: Mary Hintermeier/RSA)

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

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?

On 8/17/99 8:24 AM, Wolfe Stephen S Civ GS-11 supposedly said:

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

>inspection and fruitful critique.

I'll try to be gentle. ;-)

> WHERE Staff.hosp = ?OCEobj.hosp;

> AND Staff.Squadron = ?THIS.Squadron;

> AND Staff.Off_Sym = ?THIS.OfficeSymbol;

I would be wary of using "This." is a view parameter. It forces the

view to only be used from within a method of a particular class.

Referring to a particular object name is also problematic, for the same

reason. Instead, I would write those lines:

WHERE Staff.hosp = ?vp_hosp;

AND Staff.Squadron = ?vp_Squadron;

AND Staff.Off_Sym = ?vp_OfficeSymbol;

and then in the method where the view is opened/requeried, define the

view parameter memvars from the corresponding props:

LOCAL vp_hosp, vp_Squadron, vp_OfficeSymbol

vp_hosp = OCEobj.hosp

vp_Squadron = This.Squadron

vp_OfficeSymbol = This.OfficeSymbol

USE MyView

This way, the view isn't tightly coupled with that particular object

in that particular context, but can be re-used in other situations.

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

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

Same thing applies here. If you create view parameter variables, you

can define them conditionally based upon your initial IF statement. Then

you only need one copy of the SQL to handle both situations.

___/

/

__/

/

____/

Ed Leafe

http://leafe.com/

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

|

|

|

Table A (primary table)

-------

icontainerid

iadditiveiid

Table B (lookup table)

-------

iid

cinformation

Both fields in "table a" relate to "table b" via the "iid" field in "table

b".

In an SQL statement, I want to gather "cinformation" from "table b" twice

per record; once for the "icontainerid" and once for the "iadditiveid".

How?

--Brad