Assistance with a join 1

[Bryan - Gendev]

I am trying to modify the following join to add an additional table

the existing code works

Select &myflagfield As flagval,;
C.idexhibit,;
etc more fields
A.PDEATH;
FROM ;
(TEMP_N As N inner Join;
(TEMP_A As A inner Join;
TEMP_C As C;
ON C.id_person = A.per_no;
AND C.RLTYPE = '_' ;
);
ON N.nper = A.per_no;
AND N.Primary = .T.;
);
INTO cursor mycursor1

However, the new version does n ot


Select &myflagfield As flagval,;
C.idexhibit,;
etc more fields
A.PDEATH;
S.subsource as citdetail; > added field
FROM ;
(TEMP_N As N inner Join;
(TEMP_A As A inner Join;
(TEMP_S As S inner Join; > added table
TEMP_C As C;
ON C.id_person = A.per_no;
AND C.RLTYPE = '_' ;
);
ON S.Recno = C.id_cit;
AND S.dsid = C.dsid;
);
ON N.nper = A.per_no;
AND N.Primary = .T.;
);
INTO cursor mycursor1

Can anyone sort this out for me, my knowledge is not great in this area?

Thanks

Gendev

 

[bborissov]

Two things:

Join condition ALWAYS should follow the joined table. I know that you could put it everywhere, but that make code unreadable.
Never alias table with ONE letter (I left it one letter here just because I'm a lazy person).

Select &myflagfield As flagval,;
       C.idexhibit,;
       ...
       A.PDEATH;
       S.subsource as citdetail,;
       added field
FROM TEMP_N As N
inner Join TEMP_A As A ON N.nper      = A.per_no AND N.Primary;
inner Join TEMP_C As C ON C.id_person = A.per_no AND C.RLTYPE = '_';
inner Join TEMP_S As S ON S.Recno     = C.id_cit AND S.dsid   = C.dsid;
inner Join AdditionalTable As SomeAlias ON ???????????????????
INTO cursor mycursor1

Borislav Borissov
VFP9 SP2, SQL Server

 

[mjcmkrsr]

Hi Gendev,

I suspect that S.Recno is NOT a field but the FUNCTION - hence it should be S.Recno()
Furthermore and in order to make your code more readable you might want to separate the JOIN conditions from the WHERE conditions

Select &myflagfield As flagval, C.idexhibit, ... A.PDEATH, S.subsource as citdetail ; 
   FROM TEMP_C As C ;
      JOIN TEMP_A AS A ON C.id_person = A.per_no ;
      JOIN TEMP_S AS S ON S.RECNO() = C.ID_CIT and S.DSID = C.DSID ;
      JOIN TEMP_N AS N ON N.NPER = A.PER_NO ;
   WHERE C.RLTYPE = '_' AND N.Primary = .T. ;
   INTO cursor mycursor1

hth

MK

 

[Mike Lewis]

Another point: I was brought up to avoid using single-letter local aliases in a SELECT. This refers to the letters such as A, C, N and S that you are using as aliases for your tables (as in A.PDeath, for example). The reason is that these could clash with VFP's default work area names, A to J.

This is probably unrelated to your present problem, but it's something to keep in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

...

and DON'T ever use single letters as local aliases

From Hacker's Guide to Visual FroxPro 7 by Tamar e. Granor and Ted Roche

The Single Letter Blues

On the whole, the designers of Visual FoxPro have done a tremendous job marrying object-orientation to Xbase. But there are places where the marriage seems a little rocky. The use of single-letter identifiers is one of them.

Traditionally, the letters A through J are alternate names for the first 10 work areas. (When the number of work areas went up to 25, Fox Software didn't extend this convention. It's just as well—they'd have a heckuva time finding 32,767 different characters to represent the work areas in Visual FoxPro.) In addition, the letter M was reserved to indicate that what followed was a memory variable.

So, how would this cause you any trouble? Well, what if you have a table named C.DBF? When you open it with USE C, you'd expect it to have the alias of "C", right? Only if you're really lucky. Because "C" is reserved for the third work area, the only way C.DBF will have an alias of "C" is if you open it in the third work area. Otherwise, it'll have an alias of the work area it's opened in ("A" through "J" if it's opened in one of the first 10 work areas, or "W" followed by the work area number if not). So, when you use code like SELECT C to select the table, you'll really be selecting the third work area, which may have an entirely different table open or no table open at all.

OOP makes it even worse. Object-orientation uses the same type of "dot" notation that fields in tables do to spell out the complete name of an object, like frmMyForm.grdMainGrid.colName.txtName. So what's the problem? There is none, unless you try to use one of the letters A-J or M for the name of an object. Code like the following:

a=CreateObject("form")
a.Caption="My Form"
is doomed to failure. You can create the form, but the assignment blows up on you as VFP goes looking for a field named Caption in the first work area.

Of course, single-letter variable and table names are a lousy idea anyway (most of the time—we're still fond of variables like x, y, z, and o for quick and dirty testing), so the workaround for this isn't terribly painful. It's like the old joke—"Doctor, it hurts when I do this."—"Then don't do that." Use names longer than one character and you'll never run into this problem.

hth

MK

 

[Olaf Doschke]

See the parenthesis on your initial code:

FROM ;
(TEMP_N As N inner Join;
(TEMP_A As A inner Join;
TEMP_C As C;
ON C.id_person = A.per_no;
AND C.RLTYPE = '_' ;
);
ON N.nper = A.per_no;
AND N.Primary = .T.;
);

The last ON refers to the N table, which becomes more visible, if you blend out the inner code:

FROM ;
(TEMP_N As N inner Join;
(...);
ON N.nper = A.per_no;
AND N.Primary = .T.;
);

You better get rid of the complex syntax, as Borislav suggests. But if you want to work as before, you can't add the S aliased table there, as the C alias is joined to the A table and you hook in at the wrong level.

The best maintainable code is joining one after another, no complex nesting, as you only do inner joins the order will not change much anyway.

Bye, Olaf.

 

[Bryan - Gendev]

Sadly I have not got anywhere yet.

Should this (shortened) form work? or is there a better way?

Select &myflagfield As flagval,;
TEMP_C.idexhibit,;
TEMP_A.PDEATH,;
TEMP_S.subsource as citdetail, ;
FROM TEMP_C ;
JOIN TEMP_A ON TEMP_C.id_person = TEMP_A.per_no ;
JOIN TEMP_S ON TEMP_S.RECNO = TEMP_C.ID_CIT and TEMP_S.DSID = TEMP_C.DSID ;
JOIN TEMP_N ON TEMP_N.NPER = TEMP_A.PER_NO ;
WHERE TEMP_C.RLTYPE = '_' AND TEMP_N.Primary = .T. ;
INTO cursor mycursortest

Regards
GenDev

 

[Olaf Doschke]

What if you first check without joining TEMP_S, to see if the rearrangement of the original query works?

Select &myflagfield As flagval,;
TEMP_C.idexhibit,;
...etc more fields...;
TEMP_A.PDEATH;
FROM TEMP_C ;
JOIN TEMP_A ON TEMP_C.id_person = TEMP_A.per_no ;
JOIN TEMP_N ON TEMP_N.NPER = TEMP_A.PER_NO ;
WHERE TEMP_C.RLTYPE = '_' AND TEMP_N.Primary = .T. ;
INTO cursor mycursortest

First see if that gives the same result. It should, as you only do inner joins, join order isn't much of importance, but may influence performance.

Bye, Olaf.

 

[Bryan - Gendev]

Hi Olaf,

Thanks

Yes I got a cursor with that code - now to add the TEMP_S table?

Gendev

 

[Bryan - Gendev]

But this doesn't work...

   Select &myflagfield As flagval,;
TEMP_C.idexhibit,;
TEMP_A.PDEATH;
TEMP_S.subsource as citdetail, ; 
FROM TEMP_C ;
JOIN TEMP_A ON TEMP_C.id_person = TEMP_A.per_no ;
JOIN TEMP_N ON TEMP_N.NPER = TEMP_A.PER_NO ;
JOIN TEMP_S ON TEMP_S.RECNO = TEMP_C.ID_CIT;
WHERE TEMP_C.RLTYPE = '_' AND TEMP_N.Primary = .T. ;
INTO cursor mycursortest

I'm a bit lost now and frustrated.

Gendev

 

[Olaf Doschke]

So what doesn't work with the final join? Do you really need a further inner join, or a left join? Or a union? Is your result too small? No record? Then check the data and the join condition.

Bye, Olaf.

 

[Bryan - Gendev]

Oh sorry, my error - this does create a cursor but without data in any of the fields.

TEMP_C.idexhibit,;
TEMP_A.PDEATH,;
TEMP_S.subsource as citdetail ; 
FROM TEMP_C ;
JOIN TEMP_A ON TEMP_C.id_person = TEMP_A.per_no ;
JOIN TEMP_N ON TEMP_N.NPER = TEMP_A.PER_NO ;
JOIN TEMP_S ON TEMP_S.RECNO = TEMP_C.ID_CIT;
WHERE TEMP_C.RLTYPE = '_' AND TEMP_N.Primary = .T. ;
INTO cursor mycursortest

 

[Olaf Doschke]

So your result is empty. Then check the data, no combination of records fullfills the last introduced join confition, there is no TEMP_S.Recno matching any ID_CIT. Either your data is wrong or your conditions are too restrictive.

So if you look into the result without joining TEMP_S, with the added TEMP_C.ID_CIT field, do you see a value matching any TEMP_S.RECNO?

So if you do this first:

TEMP_C.idexhibit,;
TEMP_A.PDEATH,;
TEMP_C.ID_CIT; 
FROM TEMP_C ;
JOIN TEMP_A ON TEMP_C.id_person = TEMP_A.per_no ;
JOIN TEMP_N ON TEMP_N.NPER = TEMP_A.PER_NO ;
WHERE TEMP_C.RLTYPE = '_' AND TEMP_N.Primary = .T. ;
INTO cursor mycursortest_withouts

Do you see any ID_CIT there, which can be matched with any TEMP_S.RECNO? IF not, you don't have any result.

Bye, Olaf.

 

[mjcmkrsr]

Hi,
I still suspect that TEMP_S.RECNO is NOT a field and that you call the FUNCTION RECNO() = RECNO with ()

Select &myflagfield As flagval,;
TEMP_C.idexhibit,;
TEMP_A.PDEATH;
TEMP_S.subsource as citdetail, ; 
FROM TEMP_C ;
JOIN TEMP_A ON TEMP_C.id_person = TEMP_A.per_no ;
JOIN TEMP_N ON TEMP_N.NPER = TEMP_A.PER_NO ;
JOIN TEMP_S ON TEMP_S.[b][COLOR=#CC0000]RECNO()[/color][/b] = TEMP_C.ID_CIT;
WHERE TEMP_C.RLTYPE = '_' AND TEMP_N.Primary = .T. ;
INTO cursor mycursortest

hth
MK

 

[Olaf Doschke]

MK,

1. That wouldn't work, you would do RECNO() or RECNO("TEMP_S"), but RECNO is no function of a table, a table just has fields, no code.
2. That would be against all rules, even if it would work. RECNOs are very VFP specific, other databases can number rows within the result, but won't even know a physical record, even if we just talk VFP, RECNOs are subject to change, if you PACK. They remain if you SET ORDER, but are then not in that index order.

RECNO() would almost only work in single table queries, it wouldn't work in this one.

See it from the other side, a field ID_CIT doesn't look to me in being found as a recno elsewhere.

Besides all that thoughts, as the query works and just lacks a result, the RECNO field exists. It's a bad practice to name fields as any reserved word, but it's not illegal.

Bye, Olaf.

 

[mjcmkrsr]

Hi Olaf,

You're absolutely right. I must have had sand in my brain when writing this BS. Sorry.

MK

 

[Olaf Doschke]

I somewhere read brain researchers say, that our brain dumbs down for a few minutes everyday, something like GC.collect happens then. You can blame it on that

Anyway, even a single table query would get unexpected results, if using Recno("aliasname"), see:

Create Cursor curTest (iid I)
Insert into curTest values (1)
Insert into curTest values (10)
Insert into curTest values (100)
Goto 2
Select Recno("curTest"), iid from curTest into cursor Result1
* not using the alias name helps:
Select Recno(), iid from curTest into cursor Result2
* why? see what alias is selected during the query:
Select Alias(), iid from curTest into cursor Result3

VFP always leaves any workarea as is and looks which DBFs it needs to reopen, it always reads data from those filea reopened, that's even valid for cursors created by CREATE CURSOR. We have sqlbuffering since VFP9 to determine VFP should read from the buffer of the specified workarea, too, but aside of that you see Alias() will show you a single letter for the workarea, in which curTest is reopened in. That's why recno("curTest") is 2 for all result records, it's whatever record you go to beforehand, the query doesn't influence that. Only the second query works, as it returns the recno of whatever current alias, which is the alias the sql engine reads from while executing. In queries with multiple tables you get unpredictable results or varying with every record, depending on sub queries and join conditions, as you can't use Recno("alias") and you can't control what alias Recno() will choose.

You can view how each single record is created in yet another workarea, by calling a function which suspends vfp:

Index on iid Tag xid
Select iid from curTest where iid>1 into cursor Result4 where break()
Procedure break()
   suspend
   Return .T.

Now start the debugger and resume with F8, which will be creating the next result record, until the query is done. You can debug what VFP does during a query, not down to any detail level, eg the rushmore optimisation is a step you won't interrupt, but you can see some details. Rushmore will be done already at the first or second step, the following steps then just fetch the records optimized by indexes and only non optimizable conditions are checked after fetching the records.

Bye, Olaf.

 

[Bryan - Gendev]

MK said
TEMP_S.RECNO()

My app deals with tables produced by another application. There is indeed a field recno in a number of tables ( there are more than 20) within that application. It was written over 10 years ago and the table structures can not be changed without causing chaos to other related applications.

My investigations proceed today...

Gendev

 

[Olaf Doschke]

As I said: "It's a bad practice to name fields as any reserved word, but it's not illegal."

Of course you don't rename a table field, when several code would need to be adjusted to handle this renaming. So that's not a big deal, the big deal is you have no join match.

What you need to investigate is, why there is no result record. Inner Joins are restrictive, they only put matching data into the result, the join condition must be matched not only for the joined table, if the join condition isn't matched, even the initial record isn't put into the result. So eg you have a table with primary key values 1 and 2 and a child table with foreign key value 1 only, then an inner join matching these keys will remove id=2 from the result, not only in regard of the child table, also the head data of the parent table is not put into the result. A left outer join starting from the parent table, joining the child table, will add parent data, even if any detail record is missing, a full outer join would even show orphaned child records, so eg a child table with foreign keys 1 and 3 full outer joined would leave parent record id=2 and chjild record id=3 in the result and put NULL values in the other fields.

I already gave you the partial query you can use to investigate which ID_CIT values are in the result BEFORE joining TEMP_S:

TEMP_C.idexhibit,;
TEMP_A.PDEATH,;
TEMP_C.ID_CIT; 
FROM TEMP_C ;
JOIN TEMP_A ON TEMP_C.id_person = TEMP_A.per_no ;
JOIN TEMP_N ON TEMP_N.NPER = TEMP_A.PER_NO ;
WHERE TEMP_C.RLTYPE = '_' AND TEMP_N.Primary = .T. ;
INTO cursor mycursortest_without_s

You could also do a LEFT OUTER join of TEMP_S, to see, that you get something, but no match in TEMP_S:

TEMP_C.idexhibit,;
TEMP_A.PDEATH,;
TEMP_S.subsource as citdetail ; 
FROM TEMP_C ;
JOIN TEMP_A ON TEMP_C.id_person = TEMP_A.per_no ;
JOIN TEMP_N ON TEMP_N.NPER = TEMP_A.PER_NO ;
LEFT JOIN TEMP_S ON TEMP_S.RECNO = TEMP_C.ID_CIT;
WHERE TEMP_C.RLTYPE = '_' AND TEMP_N.Primary = .T. ;
INTO cursor mycursortest_leftjoined_s

Take a look at all the ID_CIT values and see why they don't match TEMP_S.RECNO.

Bye, Olaf.