how to combine multiple columns from two tables?

[khan82]

Hi I am trying to create a cyrstal report which is based of two tables. Both the tables have similar data in them. below is the query i wrote but it doesn't seem to be working....what am i doing wrong?

SELECT "Table_A"."Account", "Table_A"."Accounting_Date", "Table_B"."Account", Substring("Table_A"."Account",13,4), "Table_B"."Debit"+"Table_B"."Credit"
, Right("Table_A"."Account",9), "Table_A"."Debit" + "Table_A"."Credit" 


 FROM   "Table_B" "Table_B" INNER JOIN "Table_A" "Table_A" ON ((((((("Table_B"."Account"="Table_A"."Account") AND ("Table_B"."Accounting_Date"="Table_A"."Accounting_Date")) AND ("Table_B"."Journal"="Table_A"."Journal")) AND ("Table_B"."Run_Number"="Table_A"."Run_Number")) AND ("Table_B"."Sequence_Number"="Table_A"."Sequence_Number")) AND ("Table_B"."Application_of_Origin"="Table_A"."Application_of_Origin")) AND ("Table_B"."Batch"="Table_A"."Batch")) AND ("Table_B"."Source"="Table_A"."Source")

 WHERE  Right("Table_A"."Account",9)='1160.9890' AND Substring("Table_A"."Account",13,4)='1160' AND "Table_A"."Accounting_Date"<={d '2012-01-31'}

 ORDER BY "Table_A"."Account", "Table_B"."Account"

 

[kray4660]

The query looks okay, but you did not explain 'doesn't seem to be working'. Please clarify

 

[khan82]

it showing a blank page on crystal.

 

[pmax9999]

As kray4660 says, there is nothing obviously wrong with the code but without being familiar with the data it is impossible to give you a definitive answer. However, the general approach to troubleshooting this type of problem is:
[ol 1]
[li]Remove the WHERE clause. If the report now returns data, add back the 3 conditions one at a time, checking the result after each addition. If one of the 3 conditions causes the report to fail, that is the one you need to look at. If removing the WHERE clause makes no difference, the problem is in the join[/li]

[li]Test the Join by removing 7 of the 8 Join conditions, and adding each back one at a time, and testing after each addition. The one that causes the report to fail is the one that will need to be reviewed. [/li]

[/ol]

Hope this helps.

Cheers
Pete

 

[khan82]

i tried removing the joins but to no avail.

what i am trying to do is that create balances for accounts.

Transactions dating 01/01/2012 or after are in table A
Transactions dating 12/31/2011 or before are in table B

both tables have the same structure as to the columns in the tables i.e. account, debit & credit etc.

I need to design a report that will some up transactions for a certain account from both tables for a given cut off period. i.e. sum of all transactions posted on or before 12/31/2012 for account 1160.9000

the report breaks down when i group the tables. i.e. group 1 (table a) and then group 2 (table b)

can you think of a better way to write up the query?

 

[pmax9999]

I think you will need to use a Union. Try something like this:

SELECT 	"Table_A"."Account", 
	"Table_A"."Accounting_Date", 
	Substring("Table_A"."Account",13,4), 
	Right("Table_A"."Account",9), 
	"Table_A"."Debit" + "Table_A"."Credit" 

FROM 	"Table_A" "Table_A" 

WHERE	Right("Table_A"."Account",9)='1160.9890' AND 
	Substring("Table_A"."Account",13,4)='1160'


UNION

SELECT 	"Table_B"."Account", 
	"Table_B"."Accounting_Date", 
	Substring("Table_B"."Account",13,4), 
	Right("Table_B"."Account",9), 
	"Table_B"."Debit" + "Table_B"."Credit" 

FROM 	"Table_B" "Table_B" 

WHERE	Right("Table_B"."Account",9)='1160.9890' AND 
	Substring("Table_B"."Account",13,4)='1160'

Hope this helps

Cheers
Pete

 

[khan82]

the query seems to be working somewhat....for some reason its not pulling all the transactions for a particular date...

 

[pmax9999]

Please post some sample data (Accounting_Date & Account columns) of rows that you believe should be on the report but aren't.

It is likely that it is the WHERE clause is excluding these records.

Cheers
Pete

 

[khan82]

 

[khan82]

first picture is the crystal printout while the second one is and odbc link in excel.

 

[pmax9999]

That doesn't help. What I wanted to see is sample data, that is not returned by the report, that you believe should be on the report, and showing the Account field so I could check it against the WHERE clause. The second screenshot, which I am guessing are the ones that you believe should be on the report but aren't does not show the Account field.

Pete

 

[khan82]

Accounting_Date "Company" "Base" "Amount" Account
2/3/2010 8100 1160 (339.15) 8100-000-00-1160.9890
2/3/2010 8100 1160 339.15 8100-000-00-1160.9890
2/8/2010 8100 1160 9,898.34 8100-000-00-1160.9890
2/8/2010 8100 1160 (4,548.56) 8100-000-00-1160.9890
2/8/2010 8100 1160 (4,548.56) 8100-000-00-1160.9890
2/8/2010 8100 1160 (63.26) 8100-000-00-1160.9890
2/8/2010 8100 1160 (63.25) 8100-000-00-1160.9890
2/8/2010 8100 1160 (17.64) 8100-000-00-1160.9890
2/8/2010 8100 1160 (52.92) 8100-000-00-1160.9890
2/8/2010 8100 1160 (52.92) 8100-000-00-1160.9890
2/8/2010 8100 1160 (110.00) 8100-000-00-1160.9890
2/8/2010 8100 1160 (60.00) 8100-000-00-1160.9890
2/8/2010 8100 1160 (30.00) 8100-000-00-1160.9890
2/8/2010 8100 1160 (30.00) 8100-000-00-1160.9890
2/8/2010 8100 1160 (321.23) 8100-000-00-1160.9890

 

[pmax9999]

The only thing I can think of is that the Account field could have trailing spaces which would impact the WHERE clause. Without knowing what database you are using I can't be certain about the syntax but try this:

SELECT 	"Table_A"."Account", 
	"Table_A"."Accounting_Date", 
	Substring("Table_A"."Account",13,4), 
	Right("Table_A"."Account",9), 
	"Table_A"."Debit" + "Table_A"."Credit" 

FROM 	"Table_A" "Table_A" 

WHERE	Right(RTrim("Table_A"."Account"),9)='1160.9890' AND 
	Substring("Table_A"."Account",13,4)='1160'


UNION

SELECT 	"Table_B"."Account", 
	"Table_B"."Accounting_Date", 
	Substring("Table_B"."Account",13,4), 
	Right("Table_B"."Account",9), 
	"Table_B"."Debit" + "Table_B"."Credit" 

FROM 	"Table_B" "Table_B" 

WHERE	Right(RTrim("Table_B"."Account"),9)='1160.9890' AND 
	Substring("Table_B"."Account",13,4)='1160'

The 2nd part of the WHERE clause (the substring line) could be omitted because if the field passes the 1st test it would also pass the 2nd test.

Hope this helps,

Cheers
Pete

 

[khan82]

Tried the new code but still ended with the same result.

 

[pmax9999]

Not sure what else to suggest. If the data is as you describe I don't see why it would not work.

Maybe someone else will see something I have missed.

Pete