Creating a 'UNION' between a Credit and Debit table 5

[rier]

I have two tables where one stores DATE,CREDIT and the other stores DATE,DEBIT. I am trying to create a UNION where the result is one table that details the combined information. I can acheive the above using SQL server with the following query:

SELECT distinct
AccountCredits.Date,
AccountCredits.credit,
(NULL)as debit
FROM
Pegasus.dbo.AccountCredits AccountCredits, Pegasus.dbo.AccountDebits AccountDebits
WHERE
AccountCredits.CustomerID = 1 and accountcredits.credit=accountdebits.debit
union
SELECT
AccountDebits.Date,
(NULL)as Credit,
AccountDebits.Debit
FROM
Pegasus.dbo.AccountCredits AccountCredits, Pegasus.dbo.AccountDebits AccountDebits
WHERE
AccountCredits.CustomerID = 1 and accountcredits.credit=accountdebits.debit

The result:

Date credit debit
--------------------------- --------------------- ---------------------
2000-06-01 00:00:00.000 NULL 91.7000
2000-06-14 00:00:00.000 91.7000 NULL
2000-07-03 00:00:00.000 NULL 1.6900
2000-07-12 00:00:00.000 NULL 31.7300
2000-07-19 00:00:00.000 NULL 1.6900
2000-07-22 00:00:00.000 1.6900 NULL
2000-08-01 00:00:00.000 NULL 31.7300
2000-08-14 00:00:00.000 31.7300 NULL
2000-09-11 00:00:00.000 NULL 91.7000

Where columns match, I don't have a problem, but when I have a CREDIT column in one table and a DEBIT column in the other and use UNION, it nags because the there should CREDIT,DEBIT columns in both tables. I need to be able to use '(NULL) as Credit (or Debit) in CR7 but cannot.

Does anyone know a workaround?

Thanks

Richard

[sig][/sig]

 

[kenhamady]

You could use a view, stored procedure or a Query designed in the Crystal SQL designer. All of these will allow you to do what you want to do.

The problem you are probably running into is that you can't make any changes to the first line of the SQL statement in the report designer. So you can't put in your "Null" field on the first table. You could on the second table if it were already in the first.

The typical workaround is to write and then 'neuter' the first SQL statement with a where clause of "False". You can then union anything you want to that statement, including your complete union statement (both instances).

The problem is that you have to still match the fields in the 'neutered' select. If you only have two fields in that table this could be a problem. If you have a third field that can stand in for your third column, this will help. Or if your 'neutered' select can come from an empty table with three fields. [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[MalcolmW]

If you are determined to avoid easy routes, the &quot;neuter&quot; could be done with
SELECT
AccountCredits.Date,
AccountCredits.credit,
AccountDebits.debit
FROM
Pegasus.dbo.AccountCredits AccountCredits, Pegasus.dbo.AccountDebits AccountDebits
WHERE 0=1
UNION
{your SQL above}
The WHERE clause is obviously always false, and this query is evaluated quite quickly because of that.
You could have any table in the above, but it is a good idea to use representative tables so that the report is understandable.

Your SQL statement has a few curious features. One is that one of the Select statements is distinct, the other isn't.
The other curious thing is the table joins are based on the credit/debit amount, which seems unusual. Lastly, I wonder why those joins are even needed.

However, if it works, don't fix it. [sig][/sig]

 

[rier]

To both 'Ken Hamady' and 'MalcolmW', thank you both for your invaluable help - easy when you know how hey?

Thanks again

Richard [sig][/sig]