SELECT'S are not UNION compatible 2

[mmerlinn]

[ ]
I get a "SELECT'S are not UNION compatible" error when I run this. What am I missing here? Green variables are numeric, red are logical, rest are character or memo.

[tt]
SELECT a.vourptnym, a.vourptstyl, a.vyear, a.vourptnum, b.vusrname, ;
a.pi_m_tc, 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, b.vusrinfo, 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, a.vmiscinfo, a.pi_m_id, a.vccode, a.voa_thick, ;
a.vod_deep, a.vid_wide, a.pipicnum, a.vfamname, a.vourwebgrp, ;
'', '', '', ;
a.piprice, 0.00, ;
.F., .F. ;
FROM JFamPart a ;
WHERE a.vourptnum NOT IN ;
(SELECT b.vourptnum FROM JUsrInfo b)
[/tt]


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

[ ]
Program sure won't be listed on Tek-Tips. It is way too long. Line count is approaching 100,000 lines and the function/procedure/program count is currently at 903 symbols.

What I will continue to do, as I find the time, is to write more FAQs for those functions I use that I think would be valuable to the FoxPro community. So far the only function FAQs that I have written are for ATXLEFT() & ATXRIGHT() {faq184-5975} and three ISO date calculation functions {faq184-6340}.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Imaginecorp]

Line count is approaching 100,000 lines and the function/procedure/program count is currently at 903 symbols.

Sure it's Fox and not COBOL? Did that stint many many moons ago before I discovered something on a Floppy disk called FoxBASE.

mmerlinn: Good Luck to you... and Success

 

[Mike Lewis]

The current program I am developing generates all of my eBay pages ...

Line count is approaching 100,000 lines and the function/procedure/program count is currently at 903 symbols.

Very impressive.

I wrote a VFP application to generate and maintain pages for a fairly large site. I would guess the whole app ran to about 2,000 lines of code (but it also used a lot of my standard classes).

If anyone's interested, you can see the result at

http://www.veg-world.com

- not that you can tell anything just by looking at the web pages.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

Mike: Nice work on the site... "Vegans" ... God help us {BG}
Strictly Meat & potatoes here…

 

[Mike Lewis]

Imaginecorp,

Strictly Meat & potatoes here…

That's OK. I won't hold it againt you <g>.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[mmerlinn]

[&nbsp;]
MikeLewis

I wrote a VFP application to generate and maintain pages for a fairly large site.

I went and looked at your great site. It looks like a lot of work to maintain it and VFP must be a great aid in keeping it running. But, I am confused. Does your VFP write the actual pages? Or is VFP some kind of a backend support?

Every page I looked at had this line in it:

<meta name="GENERATOR" content="Microsoft FrontPage 4.0">

mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Mike Lewis]

Mmerlinn,

Thanks for your interest.

In brief ...

The application maintains two main tables: one for book reviews and one for recipes. The recipes table, for example, has fields for the recipe name, author, keywords, main ingredients, category, whether it's vegan, whether it's a "quick dish", and the actual text of the recipe. Similarly with the book reviews.

There are the usual data-maintenance forms, reports and the like.

The clever bit (well, not all that clever really) is the Generate module. Essentially, this merges the data from a table with a template. The template is an HTML file that defines the page titles, fonts, colours, and general layout. So, when I run Generate on the recipes, I end up with one page per recipe, all with the same look and feel as the template.

This makes it very easy to change the overall look of the site. I just have to edit the template and run Generate again. I do that quite often, typically when I want to experiment with different ad placements.

I wondered if anyone would pick up the reference to Microsoft FrontPage. That's simply because I created the original templates in FrontPage, and never got round to editing the Generator tag. Maybe I should change it to "Microsoft Visual FoxPro". I'll get round to that one day.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[mmerlinn]

[&nbsp;]

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:

http://home.teleport.com/~tsco/catalog/catalog.htm.

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:

http://home.teleport.com/~tsco/catalog/catalog.htm.

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!"

 

[Mike Lewis]

Mmerlinn,

you are generating your whole site using FoxPro ... the FrontPage bit was just what is left hanging from your original use of FrontPage to generate the original template.

Yes, that's correct (except for things like the home page, contacts page, etc., which I just created by hand).

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.

I've just visited your eBay store. I can see that you have a huge amount of information to present and to keep track of. I'll bet your application is a lot more sophisticated than mine.

For me, the template is a vital element of the application. It's not just a question of look and feel, but more one of overall layout, especially the ads. For example, I eventually discovered that the ads for the books that accompany the recipes work much better placed at the right side of the page rather than at the foot. Once I had decided to change them, I only had to edit the template and re-generate, which was easy.

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

That's an excellent idea. As you say, I might as well give myself the extra publicity. Microsoft don't need it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

The problem with using single-letters as local aliases in a query is that the letters a through j are also aliases for the first 10 work areas. In some versions of FoxPro, the query engine can get confused if there are table open in those work areas.

Tamar

 

[mmerlinn]

[&nbsp;]
MikeLewis

I eventually discovered that the ads for the books that accompany the recipes work much better placed at the right side of the page rather than at the foot.

To me placing the ads on the right side should be the best place to put them. The scroll bars are on the right, so every time someone needs to scroll, they have to look right at the ads. The bottom would seem to me to be the worst place simply because much of the time people do not scroll to the bottom of the page.

I think the placement of ads would follow this scenario, from best to worst: Right side, Top, Left side, Bottom. Bottom line is higher on page and further to the right would seem to me to be best. So a right top should be better than a right bottom. Likewise, top right versus top left, left top versus left bottom, and a bottom right versus a bottom left. Does your experimentation bear this out?




mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Mike Lewis]

Mmerlinn,

Does your experimentation bear this out?

Yes, it pretty well does bear it out.

Of course, it's not just the ad placement that's important. I've also experimented with the type of ad, whether to host the ad myself or rely on the advertiser to serve it, and similar issues. Using a template in the way I described makes it easy to try different combinations.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk