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

 

[bborissov]

try:

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 ;
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);
;
ORDER BY 1, 2, 3, 4, 5 ;
INTO CURSOR J_TEMP1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

Hi Mmerlinn,

I don't know if this is the cause of your problem, but the ORDER BY clause in a union must be after the final SELECT.

Also, it would help to know which version of VFP you are using as the rules for unions changed slightly in 9.0.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[mmerlinn]

[ ]

Hmmm. Will try moving the ORDER BY clause and see what happens.

I have several other SELECT/UNION statements in the program in this exact format and they work just fine. So if the ORDER BY needs to be after everything else, I will also need to change them to avoid weird problems in the future.

VFP3.0, and, no, I cannot upgrade.

Borislav, is the blank line with the semicolon required before the ORDER BY clause?


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[bborissov]

No, that blank line I included only to show you what i moved from first part of the SELECT to the end of the whole query.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mmerlinn]

[ ]
Moved the ORDER BY and INTO statement as Borislav suggested.

Still get same error message.

Any more ideas?




mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[ilyad]

1. Change a and b to something more meaningful.
2. Put full length for charachter fields, e.g. space(20) as FName, etc. You may also put exact number of digits for numerical fields (alternatively you may use CAST function, which is new in VFP9).

 

[mmerlinn]

[ ]
ilyad

1) a and b are defined in the SELECT: ([TT]FROM JFamPart a, JUsrInfo b ;[/TT])

2) I will try full length for character & numeric fields and see what happens. But what do I use for the memo fields?

3) CAST is not available in VFP 3.0 ("VFP3.0, and, no, I cannot upgrade.")


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Mike Lewis]

MMerlinn,

Are you sure that the fields in each of the SELECTs match exactly in both length and data type? That was a requirement of a UNION in early versions of VFP.

It wasnt't enough for them to be similar data types; they had to be the same. For example, if a field in the first SELECT was an integer, the corresponding field in the second SELECT also had to be an integer, not a numeric.

Similarly, if a field in one SELECT was a character, the corresponding field had to be a character (and the same length of character), not a memo.

Also, you might need to truncate the memo fields so that their lengths match. Use PADR() to do that.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[StewartUK]

Picking up on Mike's post, I did notice that in the first select, you have the lines

a.vourptnym, a.vourptstyl, a.vyear, a.vourptnum, b.vusrname,

AND

b.vusrptnum, b.vusrinfo, b.vusrptnym,

but in the second select you have

a.vourptnym, a.vourptstyl, a.vyear, a.vourptnum, '',

AND

'', '', '',

which I think could cause the error.

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ilyad]

mmerlinn,

I know that you defined a and b in your select statement, I'm just saying, that it is not a good idea to use a and b at all. Use aa and bb if you want to shorten the code, otherwise sometimes you may have weird results.

Now, for the memo field the only choice pre-VFP9 would be to create an extra temp cursor with memo field and add it into the select statement, e.g.
select 0
create cursor curTempMemo (myMemoField M)

select MyTable.myFields,... curTempMemo.myMemoField, etc.

same would apply to integer fields.

In VFP9 this trick is no longer needed.

 

[mmerlinn]

[ ]
Thanks all.

I will do some more experimenting and see if I can get this problem solved.




mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Olaf Doschke]

Hi,

all in all it seems you are joining JFamPart and JUsrInfo, then add a record with the head data from JFamPart, for which there is no record in JUstInfo.

Why not make it

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 ;
ORDER BY 1, 2, 3, 4, 5 ;
INTO CURSOR J_TEMP2

* open result readwritable:
use dbf('J_TEMP2') in 0 again alias J_TEMP1
use in j_temp1

* set a relation between FamPart und UsrInfo
select JUsrInfo
set order to vourptnum
selcet JFamPart
set relation to vourptnum into JUsrInfo

* append records of FamPart with no correspnding record in UsrInfo
select j_temp1
append from dbf('JFamPart') for eof('JUsrInfo')

besides that a union with memo fields will never work, even if they are memofields in both selects. I also believe you rather want a Union ALL, not a Union. Although it would be the same in his case, it could make a little difference in that the SQL engine won't try to find an eliminate double records, UNION is a union of DISTINCT records, DISTINCT also does not allow memo fields.

Bye, Olaf.

 

[mmerlinn]

[ ]
Thanks all.

Problem has been solved.

Will post the results with my comments and award the appropriate stars in a few days.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Imaginecorp]

mmerlinn;

I know you have over emphasized that you cannot upgrade. I am just curious as to why. I am sure you have your reasons, but believe me, and I am sure you know this already, VFP3 was the worst , that’s why it was replaced in record time by VFP5.

Make life a little easier for yourself by upgrading. Reading your solutions and posts, you seem extremely talented; by moving up to VFP9 you could accomplish so much more and in half the time it takes in VFP3.

Just my two cents…

 

[mmerlinn]

[ ]
Imaginecorp

There are a number of reasons that it just does not make any sense for me to upgrade to VFP 9.

First and foremost, all of my programming has been done on Apple products starting with Apple BASIC and assembly on the Apple II+ in 1980. I am currently running FP & VFP on Macs with OS 7.1 thru OS 9.2. As far as I know, FP won't run on OS X, so even the Mac is now a limiting factor.

At my age (60), it just does not make any sense to change platforms so I can upgrade. The learning curve is simply too great. I would never have time to master a new platform, and would probably be very frustrated trying to do so.

For my take on Apple versus PCs, read my post near the bottom of this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1204174

As noted in the above link, I DO have PCs that I use as little as possible - only when my Macs cannot do the job. BUT, I have not done any programming on PCs since before Windows was released.

Secondly, even if it made sense, many of the programs that I have written do jobs that do not justify rewriting the programs in a modern language. Dollar for dollar it is much much cheaper to maintain them on the old computers than to bring them into the 21st century. I even have my original Apple II+ and still use it for a few jobs that I cannot justify the time or the money to move to newer computers.

Third, I cannot justify the cost of buying PCs, the cost of buying the programs to run on them, the cost of data conversion, the cost of paying someone to keep them running, and the multitudes of other costs (money, time, etc) that I do not have now. The cost of keeping things going on the Mac is minimal by comparison. I have enough backup Mac systems on hand (around 30 in storage) that I am not likely to ever have a problem finding computers to run my existing legacy programs. I also have numerous original FoxPro Mac programs (some still shrink-wrapped, and most costing under $25) should I lose or have any problems with those I have currently installed.

Since all of my programming has always been in-house for my own business, no one else is likely to be saddled with maintaining or upgrading any of my programs after I am gone. Even though it is likely no one else will ever maintain my programs, I still use most of the same rules I developed when I first started in Apple BASIC in 1980. By using those rules most of my code is fairly easy to follow and fairly easy to understand - no 'spaghetti' code here.

The most likely scenario is that this business will either close when I leave or will be bought out by a larger competitor with modern systems in place. Either way, any programs and equipment would become just more superfluous junk.

My major aim is to make sure that the data, tables, and structures are constructed so that the data can be moved to modern programs with minimal cost should someone after me decide to move it. Most likely the data will be moved simply because good data in my industry is just not available anywhere else. I have been collecting and publishing raw data now for 27 years - data that goes back at least 40 years - data that can NEVER be replaced - data that today's 20-somethings need to do their job efficiently.

When I originally bought FoxPro 2.5 for the Mac, one of the major reasons was that it was cross-platform and VERY powerful. I have never regretted spending that $600 shortly after it became available for sale. FoxPro has exceeded my expectations. I have yet to find ANY job that I need done that it won't do. I never expected to use it to write OTHER programs, but the MAJOR use today is using FP to automatically generate webpages from the underlying data tables - webpages that contain a mixture of HTML and Javascript. The current program I am developing generates all of my eBay pages and shortly will be able to generate thousands of interconnected webpages in minutes from the underlying tables of information.

For anyone that is interested in looking at the resulting eBay pages, my eBay name is the same as here - "mmerlinn". All of the pages I have on eBay were generated by FoxPro. Generating a brand new page takes about 15 minutes of data entry, then a few seconds to generate and upload. All thanks to FoxPro. I really doubt that I could do that with any other language since the same FoxPro program is handling ALL of the data entry, ALL of the data manipulation, as well as generating ALL of the requisite HTML and Javascript code needed to display the pages. FoxPro is truly the program of programs!!!!!

Finally, thank you for the compliment "you seem extremely talented". I am not sure how much is talent and how much is programming experience, but what you see in my posts is 27 years of programming experience in Apple BASIC, Apple assembly, Apple machine language, FoxPro, HTML, and Javascript at about 3 hours per day.

Well, I hope this gives you an insight into what I am posting on Tek-Tips.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Mike Lewis]

Mmerlinn,

You've made some valid points about the reasons for not upgrading. Most of us here treat VFP 3.0 with disdain, but we forget that it is the last true cross-platform version of Foxpro. Given that you are working in the Mac world, you're might well be better off with VFP 3.0 than with a Mac-specific, non-FoxPro alternative.

Personally, I hate it when I have to go back to using VFP 6.0, and would positively loath having to use an earlier version. Then again, I run other software on my computer that pre-dates Windows 95, and am perfectly happy with it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Imaginecorp]

Makes Perfect sense to me.
Thank you.

"...at about 3 hours per day..."
Maybe one day... it's more like 10 - 14 for me...

 

[mmerlinn]

[ ]
Imaginecorp

[tt]
"...at about 3 hours per day..."
Maybe one day... it's more like 10 - 14  for me...
[/tt]

There are days I spend 15 or more hours programming and there are days I don't even turn the computer on. Just depends on which of the many hats I wear that is most important at that particular time.

Taking care of the customers we sell to is the number one priority. Servicing our vendors is the second most pressing need. Without taking care of both of them first, any programming that I would do is a total waste of time.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Imaginecorp]

I meant: “Maybe one day I can ….,”, doesn’t seem likely though. I do wear those hats as well in addition to incorporating other programmers work into our application etc…. You know how it is.
But I have to admit… I love it. I built it from scratch and will still be working when they plant me 6 feet under…

The current program I am developing generates all of my eBay pages and shortly will be able to generate thousands of interconnected webpages in minutes from the underlying tables of information.

Please keep us informed… would love to see this program.