Join two tables on multiple corresponding flds/Null values=lost data

[cgeib]

Hi all,

New user here struggling with table joining! Novice with SQL and appologize if I've used the wrong terminology.

Using: CR XI & SQL Server 2000

Trying to Link two tables in CR on multiple corresponding fields (the combination of all fields creates unique condition for join). When field values are Null, no records are returned ...how do I get them?

Below is the portion of the SQL Query from CR that I'm trying to work with:

LEFT OUTER JOIN "abecas_cs"."dbo"."PRCOMMITAC" "PRCOMMITAC" ON (((((((("ACACCOUNTING"."COMPANYID"="PRCOMMITAC"."COMPANYID") AND ("ACACCOUNTING"."P_GLACCOUNT"="PRCOMMITAC"."P_GLACCOUNT")) AND ("ACACCOUNTING"."P_BATCH"="PRCOMMITAC"."P_BATCH")) AND ("ACACCOUNTING"."P_GLDEPARTMENT"="PRCOMMITAC"."P_GLDEPARTMENT")) AND ("ACACCOUNTING"."FISCALYEAR"="PRCOMMITAC"."FISCALYEAR")) AND ("ACACCOUNTING"."FISCALPERIOD"="PRCOMMITAC"."FISCALPERIOD")) AND ("ACACCOUNTING"."P_GLFUND"="PRCOMMITAC"."P_GLFUND")) AND ("ACACCOUNTING"."P_CAJOB"="PRCOMMITAC"."P_CAJOB")) AND ("ACACCOUNTING"."P_CAACTIVITY"="PRCOMMITAC"."P_CAACTIVITY"))

CompanyID, P_Batch, P_GLAccount, FiscalYear, FiscalPeriod are always populated (FiscalPeriod can = 0).

P_GLDepartment, P_GLFund, P_CAJob, & P_CAActivity may or may not be Null and in most cases atleast one of them is Null.

P_{Fields} are pointer fields containing Keysequence record numbers.

Below is entire CR SQL Query so far:


SELECT "ACACCOUNTING"."GLACCRUALAMOUNT", "PRCOMMITAC"."GLACCRUALAMOUNT", "ACBATCH"."KEYSEQUENCE", "ACBATCH"."DESCRIPTION", "ACACCOUNTING"."SOURCE", "ACACCOUNT"."ACCOUNT", "ACACCOUNTING"."FISCALYEAR", "ACACCOUNTING"."FISCALPERIOD", "ACACCOUNTING"."COMPANYID", "PRCOMMITAC"."P_GLACCOUNT", "ACACCOUNTING"."P_GLACCOUNT", "ACACCOUNT"."DESCRIPTION", "IDMASTER_1"."IDENTITYID", (CASE
WHEN "ACACCOUNTING"."SOURCE"<>'PR' THEN
CASE
WHEN "ACACCOUNTING"."GLACCRUALAMOUNT">0 THEN "ACACCOUNTING"."GLACCRUALAMOUNT"
END
WHEN "ACACCOUNTING"."SOURCE"='PR' THEN
CASE
WHEN "PRCOMMITAC"."GLACCRUALAMOUNT">0 THEN "PRCOMMITAC"."GLACCRUALAMOUNT"
END
END), (CASE
WHEN "ACACCOUNTING"."SOURCE"<>'PR' THEN
CASE
WHEN "ACACCOUNTING"."GLACCRUALAMOUNT"<0 THEN "ACACCOUNTING"."GLACCRUALAMOUNT"*-1
END
WHEN "ACACCOUNTING"."SOURCE"='PR' THEN
CASE
WHEN "PRCOMMITAC"."GLACCRUALAMOUNT"<0 THEN "PRCOMMITAC"."GLACCRUALAMOUNT"*-1
END
END)
FROM ((("abecas_cs"."dbo"."ACACCOUNTING" "ACACCOUNTING" INNER JOIN "abecas_cs"."dbo"."ACACCOUNT" "ACACCOUNT" ON "ACACCOUNTING"."P_GLACCOUNT"="ACACCOUNT"."KEYSEQUENCE") INNER JOIN "abecas_cs"."dbo"."ACBATCH" "ACBATCH" ON "ACACCOUNTING"."P_BATCH"="ACBATCH"."KEYSEQUENCE") LEFT OUTER JOIN "abecas_cs"."dbo"."PRCOMMITAC" "PRCOMMITAC" ON (((((((("ACACCOUNTING"."COMPANYID"="PRCOMMITAC"."COMPANYID") AND ("ACACCOUNTING"."P_GLACCOUNT"="PRCOMMITAC"."P_GLACCOUNT")) AND ("ACACCOUNTING"."P_BATCH"="PRCOMMITAC"."P_BATCH")) AND ("ACACCOUNTING"."P_GLDEPARTMENT"="PRCOMMITAC"."P_GLDEPARTMENT")) AND ("ACACCOUNTING"."FISCALYEAR"="PRCOMMITAC"."FISCALYEAR")) AND ("ACACCOUNTING"."FISCALPERIOD"="PRCOMMITAC"."FISCALPERIOD")) AND ("ACACCOUNTING"."P_GLFUND"="PRCOMMITAC"."P_GLFUND")) AND ("ACACCOUNTING"."P_CAJOB"="PRCOMMITAC"."P_CAJOB")) AND ("ACACCOUNTING"."P_CAACTIVITY"="PRCOMMITAC"."P_CAACTIVITY")) INNER JOIN "abecas_cs"."dbo"."IDMASTER" "IDMASTER_1" ON "PRCOMMITAC"."P_IDENTITY"="IDMASTER_1"."KEYSEQUENCE"
WHERE "ACACCOUNTING"."COMPANYID"='D2' AND "ACACCOUNTING"."FISCALYEAR"=2005
ORDER BY "ACACCOUNT"."ACCOUNT", "ACACCOUNTING"."FISCALYEAR", "ACACCOUNTING"."FISCALPERIOD", "ACBATCH"."KEYSEQUENCE

 

[synapsevampire]

When what field values are null?

What isn't being returned?

Do you understand SQL well enough to do this manually? If so you can paste in the proper SQL using a command object, or better yet, use a Stored Procedure or a View on the database.

A left outer on each table will assure that rows are returned from the parent if there isn't a match in the child, but you're post doesn't describe the data nor the requirement.

-k

 

[cgeib]

Thank you for your reply!

I appologize that I wasn't clear, I'll try to clarify.....


"When what field values are null?"
When any one of: P_GLDepartment, P_GLFund, P_CAJob, or P_CAActivity, has a Null value.

"What isn't being returned?"
Any rows from the left hand table nor the joined right hand table, if one of the above fields has a Null value.


"A left outer on each table will assure that rows are returned from the parent if there isn't a match in the child, but you're post doesn't describe the data nor the requirement."
I thought a left outer would always return the parent as well, however, it is not doing so!

The Crystal statement in my orginal post will return 3 rows from the example tables below:
=Row 1 from Table1 & Rows 1-3 from Table2

All remaining rows are excluded from the returned set, however, I want the remaining rows to "match" on the combination of these fields and return a row for each "match":
Table1, Row2=Table2, Rows4-7
Table1, Row3=Table2, Rows8-9
Table1, Row4=Table2, Rows10-11

Table1: ACAccounting
Field: P_GLAccount P_GLDepartment P_GLFund P_CAJob Amount
Row -------------------- ------------------------- ---------------- -------------- -----------
1 A1 D1 F1 J1 15
2 A1 D2 F1 <null> 12
3 A1 D3 <null> <null> 10
4 A1 <null> <null> <null> 20


Table2: PRCommitAC
Field: P_GLAccount P_GLDepartment P_GLFund P_CAJob Amount
Row -------------------- ------------------------- ---------------- -------------- -----------
1 A1 D1 F1 J1 5
2 A1 D1 F1 J1 5
3 A1 D1 F1 J1 5
4 A1 D2 F1 <null> 3
5 A1 D2 F1 <null> 3
6 A1 D2 F1 <null> 3
7 A1 D2 F1 <null> 3
8 A1 D3 <null> <null> 5
9 A1 D3 <null> <null> 5
10 A1 <null> <null> <null> 12
11 A1 <null> <null> <null> 8


Sorry, I cannot get the alignment above, I hope you can follow it.

"Do you understand SQL well enough to do this manually? If so you can paste in the proper SQL using a command object, or better yet, use a Stored Procedure or a View on the database."
I know how to use a command object, and I'm gradually learning some SQL ...syntax gets me plenty of times. I have created a view today that appears to be working thru cut-paste as the design interface did not care for the statements I was trying to add and kept undoing the visual linking and selections made. I do not understand why Stored Proc or View's are better, but seeing as they are, I'll proceed trying to learn and understand them.

I have modified a similar joining example I found in a view that appeared to be addressing the same issue I was having. I think it is working, although I haven't fully verified the results 100%. Not certain, but I believe this syntax tests for a Null value and supplies a value if true:

left outer join PRCommitAC on ACAccounting.P_GLAccount = PRCommitAC.P_GLAccount and
ACAccounting.P_Batch = PRCommitAC.P_Batch and
isnull(ACAccounting.P_GLDepartment, 99999999) = isnull(PRCommitAC.P_GLDepartment, 99999999)and
isnull(ACAccounting.P_GLFund, 99999999) = isnull(PRCommitAC.P_GLFund, 99999999) and
isnull(ACAccounting.P_CAJob, 99999999) = isnull(PRCommitAC.P_CAJob, 99999999) and
isnull(ACAccounting.P_CAActivity, 99999999) = isnull(PRCommitAC.P_CAActivity, 99999999)and
ACAccounting.FiscalYear=PRCommitAC.FiscalYear and
AcAccounting.FiscalPeriod=ACAccounting.FiscalPeriod and
ACAccounting.CompanyID=PRCommitAC.CompanyID

Is there an issue with this logic or a better way?

Thank you for the assistance!





Veiw so far:

CREATE VIEW dbo.VW_A_CMS_ACAccountingDetail
AS
SELECT

IdCode= case when ACAccounting.Source='PR' then EmpID.IdentityID
when ACAccounting.Source<>'PR' then IDMaster.IdentityID
end,
ACAccount.Account,
ACAccount.Description,
ACAccounting.FiscalYear,
ACAccounting.FiscalPeriod,
ACAccounting.Source,
ACAccounting.TransactionDate,
ACAccounting.TransactionNumber,
ACAccounting.GLAccrualAmount,
TranDesc= case when ACAccounting.Source <> 'PR' then ACAccounting.Description
when ACAccounting.Source = 'PR' then PRCommitAC.Description
end,
ACAccounting.Reference,
/*ACAccounting.Description,*/
ACBatch.Description as BatchDesc,
ACBatch.Keysequence as BatchRec,
PRCommitAC.Keysequence as PRCommitRec,
Debit = case when ACAccounting.Source <> 'PR' and ACAccounting.GLAccrualAmount > 0 then isnull(ACAccounting.GLAccrualAmount,0.0)
when ACAccounting.Source = 'PR' and PRCommitAC.GLAccrualAmount > 0 then isnull(PRCommitAC.GLAccrualAmount,0.0)
end,
Credit = case when ACAccounting.Source <> 'PR' and ACAccounting.GLAccrualAmount < 0 then isnull(ACAccounting.GLAccrualAmount*-1,0)
when ACAccounting.Source = 'PR' and PRCommitAC.GLAccrualAmount < 0 then isnull(PRCommitAC.GLAccrualAmount*-1,0)
end


FROM ACAccounting
inner join ACAccount on ACAccounting.P_GLAccount = ACAccount.Keysequence
left outer join ACDepartment on ACAccounting.P_GLDepartment = ACDepartment.Keysequence
left outer join FAFund on ACAccounting.P_GLFund = FAFund.Keysequence
left outer join CAJob on ACAccounting.P_CAJob = CAJob.Keysequence
left outer join CAActivity on ACAccounting.P_CAActivity = CAActivity.Keysequence
inner join ACBatch on ACAccounting.P_Batch = ACBatch.Keysequence
left outer join IDMaster on ACAccounting.P_Identity = IDMaster.Keysequence
left outer join PRCommitAC on ACAccounting.P_GLAccount = PRCommitAC.P_GLAccount and
ACAccounting.P_Batch = PRCommitAC.P_Batch and
isnull(ACAccounting.P_GLDepartment, 99999999) = isnull(PRCommitAC.P_GLDepartment, 99999999)and
isnull(ACAccounting.P_GLFund, 99999999) = isnull(PRCommitAC.P_GLFund, 99999999) and
isnull(ACAccounting.P_CAJob, 99999999) = isnull(PRCommitAC.P_CAJob, 99999999) and
isnull(ACAccounting.P_CAActivity, 99999999) = isnull(PRCommitAC.P_CAActivity, 99999999)and
ACAccounting.FiscalYear=PRCommitAC.FiscalYear and
AcAccounting.FiscalPeriod=ACAccounting.FiscalPeriod and
ACAccounting.CompanyID=PRCommitAC.CompanyID
left outer join IDMaster as EmpId on PRCommitAC.P_Identity=EmpID.Keysequence