[ ]
Here is the solution to my problem with some comments.
First, what I started with which is the same as above except with the memo fields highlighted.
Green variables are numeric, red are logical, highlighted are memo, rest are character.
[tt]
SELECT a.vourptnym, a.vourptstyl, a.vyear, a.vourptnum, ;
b.vusrname, a.pi_m_tc, [COLOR=blue orange]a.vmiscinfo, a.pi_m_id, ;
a.vccode, a.voa_thick, a.vod_deep, a.vid_wide, ;
a.pipicnum, a.vfamname, a.vourwebgrp, ;
b.vusrptnum, [COLOR=blue yellow]b.vusrinfo,[/color] b.vusrptnym, ;
a.piprice, b.vusrprice, ;
b.vusrood, b.rb_luoem ;
FROM JFamPart a, JUsrInfo b ;
WHERE a.vourptnum = b.vourptnum ;
INTO CURSOR J_TEMP1 ;
ORDER BY 1, 2, 3, 4, 5 ;
UNION ;
SELECT a.vourptnym, a.vourptstyl, a.vyear, a.vourptnum, ;
'', a.pi_m_tc, [COLOR=blue orange]a.vmiscinfo,[/color] a.pi_m_id, ;
a.vccode, a.voa_thick, a.vod_deep, a.vid_wide, ;
a.pipicnum, a.vfamname, a.vourwebgrp, ;
'', [COLOR=blue yellow]'',[/color] '', ;
a.piprice, 0.00, ;
.F., .F. ;
FROM JFamPart a ;
WHERE a.vourptnum NOT IN ;
(SELECT b.vourptnum FROM JUsrInfo b)
[/color][/tt]
As you know, this did not work.
When I removed the YELLOW highlighted memo fields everything worked fine, even though I did NOT remove the ORANGE highlighted memo fields.
Based on this result, I concluded that memo fields CAN be included in UNIONs PROVIDED that empty dummy memo place holders were NOT required in the UNION clause. None of the documentation I have even mentions this requirement.
So, I changed the above to this and incorporated Borislav's changes:
[tt]
SELECT a.vourptnym, a.vourptstyl, a.vyear, a.vourptnum, ;
b.vusrname, a.pi_m_tc, [COLOR=blue orange]a.vmiscinfo, a.pi_m_id, ;
a.vccode, a.voa_thick, a.vod_deep, a.vid_wide, ;
a.pipicnum, a.vfamname, a.vourwebgrp, ;
b.vusrptnum, [COLOR=blue yellow]b.vusrinfo,[/color] b.vusrptnym, ;
a.piprice, b.vusrprice, ;
b.vusrood, b.rb_luoem ;
FROM JFamPart a, JUsrInfo b ;
WHERE a.vourptnum = b.vourptnum ;
UNION ;
SELECT a.vourptnym, a.vourptstyl, a.vyear, a.vourptnum, ;
'', a.pi_m_tc, [COLOR=blue orange]a.vmiscinfo,[/color] a.pi_m_id, ;
a.vccode, a.voa_thick, a.vod_deep, a.vid_wide, ;
a.pipicnum, a.vfamname, a.vourwebgrp, ;
'', [COLOR=blue orange]a.vmiscinfo,[/color] '', ;
a.piprice, 0.00, ;
.F., .F. ;
FROM JFamPart a ;
WHERE a.vourptnum NOT IN ;
(SELECT b.vourptnum FROM JUsrInfo b) ;
INTO CURSOR J_TEMP1 ;
ORDER BY 1, 2, 3, 4, 5
[/color][/tt]
Note that I included
[COLOR=blue orange]a.vmiscinfo,[/color] TWICE in the UNION clause. The second instance is a dummy place holder so that the second SELECT clause meets the requirement that it outputs exactly the same number of identically typed columns. This outputs the correct number of columns and works as expected, with the only problem being that I now have a memo column that contains junk in the resulting table.
To overcome that problem I did the following:
[tt]
&& Returns with CURSOR 'Jusrinfo' selected
DO QUsrInfo.QRY
INDEX ON vusrptnum TAG vusrptnum
= TDELETE('Jfamuser')
&& Returns with TABLE 'Jfamuser' selected
DO QFamUsr.QRY
REPLACE ALL vusrinfo WITH ""
SET RELATION TO vusrptnum INTO Jusrinfo
SCAN
REPLACE Jfamuser.vusrinfo WITH Jusrinfo.vusrinfo
ENDSCAN
SET RELATION TO
= TDELETE('Jfampart')
= TDELETE('Jusrinfo')
[/tt]
To save space here, the GREEN line is the corrected query as shown previously. I added the RED lines of code to fill in the
[COLOR=blue yellow]Jfamuser.vusrinfo[/color] column with the correct memo information. Now everything works as expected.
****************************************
The
Language Reference book states the following about UNION clauses;
UNION clauses follow these rules:
1) You cannot use UNION to combine subqueries.
2) Both SELECTs must output the same number of columns.
3) Each column in the query results of one SELECT must have the the same data type and width as the corresponding column in the other SELECT.
4) Only the final SELECT can have an ORDER BY clause, which must refer to output columns by number. If an ORDER BY clause is included, it affects the entire result.
This quote nowhere states that there are special conditions relating to memo fields, and this is where those special conditions should be noted.
Continuing on the next page I find the following quote about place holders:
Note The place holders must be the same type as the fields that they represent. If the field is a date type, the place holder should be { / / }. If the field is a character field, the place holder should be "".
According to this, place holders for character fields need not be the same length as the character field in the first SELECT. Note also that nothing is said about NUMERIC fields, LOGICAL fields, or MEMO fields here. This is the only place where place holders are expressly defined, yet much of the needed information is not there. And this is where the special conditions of memo fields should also be noted.
I am well aware of how flawed some of the information is that Bill Gates provides with his products. Even though nothing is stated in the book about UNIONs and MEMO fields, and even though this solution works for me, that does not mean that my code won't break later because I didn't follow rules that I did not know about. Nor does it mean that my solution will work for anyone else. Without proper documentation from the manufacturer, there is no way to know what is supposed to work and what is not supposed to work.
Because of this lack of documentation, I think it might be a good idea for others to try my solution on their platforms and VFP versions to see whether it works and then report back here with the results.
****************************************
StewartUK
According to the documentation that I have ("Language Reference"), I must be sure that both SELECTs have the same number of fields. Therefore the second SELECT needs dummy fields which can be empty.
ilyad
You earn a star for pointing me in the correct direction.
Do you know why using
a,
b,
c, etc would throw an error as opposed to
aa,
bb,
cc, etc.? The documentation I have shows the first ones in the examples.
Your pointing out the problems of using memo fields in the UNION clause got me off of dead zero. Thanks for that. However, I don't understand exactly how your workaround works, so I didn't use it. Instead, I opted for the above solution.
OlafDoschke
You also get a star for giving the reason why memo fields won't work the way I was using them.
By the time I saw your post, I had already solved the problem. However, I will keep your possible solution in mind should I have future problems with mine.
Imagincorp
COBOL? On a Mac? ROFLOL.
As noted previously my code is way to long to list on Tek-Tips, but you can go look at a preliminary prototype of the web pages generated by my FoxPro program here:
Those 499 webpages still need a LOT of work before I can put them up on a permanent website. Any comments would be welcome.
MikeLewis
Ok, so basically you are generating your whole site using FoxPro to write every page based on the contents of your template and the underlying data tables. And the FrontPage bit was just what is left hanging from your original use of FrontPage to generate the original template. Interesting.
I am basically doing something very similar, but I have to admit that I never thought of using using a template generated by FrontPage or whatever and then using the resulting code for a template. Neither did I even think of using a template to control the look and feel of the resulting pages. Instead, I have hardcoded the look and feel of the my pages into the main generator page which then calls a series of functions that, as a set, output the pages according to the hardcoded information. And, after giving it a bit of thought, I probably won't change to a template. My pages basically will contain only tables of information along with the usual fine print, so changing the look and feel generally won't be an option. If anything, I might put the color and font information in my BaseLink.DBF if I feel the need to experiment with them.
I am working with 9 basic information tables, about 25 dynamic lookup tables, several static lookup tables, a few dynamic program/system info tables, and a couple of static program/system info tables. The biggest table has 33 fields and currently has 7000 records with a potential of several million records. Just trying to keep track of the tables is hard enough that one could even consider writing a FoxPro routine to catalog them, their structure, and their contents. Worst part is that, until recently, I had not settled on a easily useable naming convention for the fields in those tables. Now that I have found a naming convention that works I am slowly changing all the field names to conform to it.
I generated a preliminary prototype of the resulting web pages with a LOT of dead links which can be found here:
FoxPro generated those 499 pages in 16 minutes on this 12-year-old Mac. It took me 18 minutes to upload them via FTP at this dialup connection.
As for your idea to change 'Microsoft FrontPage 4.0" to "Microsoft Visual FoxPro" I am not sure that is a good idea. VFP didn't generate the pages. The program Mike Lewis wrote while using VFP did generate those pages. So how about something like "Mike Lewis's Custom Visual FoxPro Application?" Or, something so that you or the program you wrote gets the credit, and not the program Bill Gates sold to you.
mmerlinn
"Political correctness is the BADGE of a COWARD!"
