help with UNION please - am I doing the right thing?

[CarolineS]

Hi,

I'm trying to write a SELECT statement for use in Crystal Reports 8.5, reporting on some tables from Great Plains. I need to access the exact same fields in two Posted Documents tables, and two Unposted Documents tables, and I'm told that UNION is the way to achieve this. But I'm having some trouble. Sorry if my questions are obvious; I haven't had to write much in SQL before.
I'm saying:

SELECT
UnpostedHDR.CustNum, UnpostedHDR.InvNum, UnpostedLINE.ItemNum
FROM
UnpostedHDR inner join UnpostedLINE on UnpostedHDR.InvNum = UnpostedLINE.InvNum
UNION
SELECT
PostedHDR.CustNum, PostedHDR.InvNum, PostedLINE.ItemNum
FROM
PostedHDR inner join PostedLINE on PostedHDR.InvNum = PostedLINE.InvNum

(my syntax may be a little screwy in the example but the query does work except ….)
This query returns all the posted docs, and none of the unposted docs. Now I just tried flipping the query (identical except that I put the Select statement for the Posted first, then UNION, then the Select for the Unposted) - - and it returned only all the Unposted.

My question is two-fold actually: why isn't this working to bring back Posted and Unposted, and further – what I really want to do when I get the UNION working is group all this info by Customer. Customer# exists on each of these tables, but a more detailed Customer table will need to be joined in. Will I use it as part of this UNION statement or separate from it?

What I'm trying to end up with is a list, grouped by Customer, with some fields from the Customer table, and all the items the Customer had purchased, posted and unposted.


Thanks in advance,
Caro

 

[Guest_imported]

This is just a guess, but the Union should be working on like columns. Perhaps making sure the names don't reflect the tables from whence they came:

SELECT
UnpostedHDR.CustNum As CustNum, UnpostedHDR.InvNum As InvNum, UnpostedLINE.ItemNum As ItemNum
FROM
UnpostedHDR inner join UnpostedLINE on UnpostedHDR.InvNum = UnpostedLINE.InvNum
UNION
SELECT
PostedHDR.CustNum As CustNum, PostedHDR.InvNum As InvNum, PostedLINE.ItemNum As ItemNum
FROM
PostedHDR inner join PostedLINE on PostedHDR.InvNum = PostedLINE.InvNum

Bev

 

[CarolineS]

Thanks for the idea Bev. I tried it, and it didn't solve my problem; same records were returned. But perhaps it's part of the solution I need?

 

[Andel]

Try this...

SELECT 'Unposted',
UnpostedHDR.CustNum, UnpostedHDR.InvNum, UnpostedLINE.ItemNum
FROM
UnpostedHDR inner join UnpostedLINE on UnpostedHDR.InvNum = UnpostedLINE.InvNum
UNION ALL
SELECT 'Posted',
PostedHDR.CustNum, PostedHDR.InvNum, PostedLINE.ItemNum
FROM
PostedHDR inner join PostedLINE on PostedHDR.InvNum = PostedLINE.InvNum

Notice that I use UNION ALL.

Andel
andel@barroga.net

http://andyb.8m.com

 

[CarolineS]

Thanks Andel,

That seems to have done it! The first part anyway, which is getting Posted and Unposted together.

But now I need to have two other tables that must be referenced - Customer table and one other called Holds.
Do I need to include these in the union as in
(added text is bolded)

SELECT 'Unposted',
Customer.CustName, Customer.Phone, Holds.Reserved, UnpostedHDR.CustNum, UnpostedHDR.InvNum, UnpostedLINE.ItemNum
FROM
UnpostedHDR inner join UnpostedLINE on UnpostedHDR.InvNum = UnpostedLINE.InvNum (plus join info for other tables)
UNION ALL
SELECT 'Posted',
Customer.CustName, Customer.Phone, Holds.Reserved, PostedHDR.CustNum, PostedHDR.InvNum, PostedLINE.ItemNum
FROM
PostedHDR inner join PostedLINE on PostedHDR.InvNum = PostedLINE.InvNum (plus join info for other tables)

..... or is there some nifty way where I can do a query which takes fields from Customer table and Holds table, and THEN goes to fetch the UNIONed info?

Thanks in advance for any help out there.

 

[CarolineS]

I tried it as above, making all requests from all tables identical on either side of the UNION ALL, and it worked fine.

Whether it's the most efficient, since I only need to hit some of those tables once, I'm not sure. But it's working for now, and since I've now got boolean values (0 or 1, or 0 and 2) coming in from SQL to Crystal as numbers like 42,213,120 - -I've got enough to worry about!

-Caro

 

[CarolineS]

I thought I'd just update since this was an interesting problem - in case someone comes up against a similar thing.

The problem I refer to at the end of the post above
I've now got boolean values (0 or 1, or 0 and 2) coming in from SQL to Crystal as numbers like 42,213,120

was a bit of a stumper. It was only occuring in Crystal Reports and not in Crystal's SQL Designer. It turns out the two products use different .dlls to read the data, and somehow SCR was reading a line number which would count to some astronomical number, reset to 0 and then count up again.

There are many SQL queries that you cannot build in Crystal Reports. You're meant to build them in Designer and then bring them over. You can (a)export from Designer to SCR, you can (b)point a blank Crystal Report at the saved SQL query, or you can (c)use Database>Show SQL query and make changes in the window. The last -(c)-is messy, and not very helpful for large changes, and you cannot add to the SELECT section of the query.

But it turns out that (a), exporting from Designer, may not be good either, as it was not in my case. The Help folks at Crystal didn't seem to have encountered a problem like this before, so it may be unique to Great Plains's SQL tables.

In any case, in Crystal Reports using the Create a New Report>Blank Report> and then from Data Explorer choosing the very last option in the window, "Metadata/Query", you can point to the query you have previously saved in SQL Designer, and boolean values should read fine.

-Caro