Problem with links and returned data 1

[matpj]

Hi,
i'm using CR10 and am currently in the process of writing a report involving 5 finance tables.
One of these tables contains all of th actuals (ppa_WP)
the 'driving' table in this query is the projects table, with other tables linking to that.

I need to display ALL of the data being returned from all tables, even if there aren't any corresponding values in the WIP table. At the moment, if there aren't records in WIP that match the criteria of Type='L' and Status=0, then it doesn't return any values from the other tables for that project.

my SQL is here:
(can anyone suggest what I am doing wrong, or what I'm just not doing?)

SELECT
budgetheader.budgetheadertype, budgetheader.units, projects_PointOfSale.userdefined_label, clntsupp.clntcode, projects.clntcode, projects.projid, projects.title, projects.status, ppa_WIP.TransType, ppa_WIP.DepartCode, ppa_WIP.Quantity, ppa_WIP.Status
FROM
DBSfpmi.dbo.budgetheader budgetheader,
DBSfpmi.dbo.projects_PointOfSale projects_PointOfSale,
DBSfpmi.dbo.clntsupp clntsupp,
DBSfpmi.dbo.projects projects,
DBSfpmi.dbo.ppa_WIP ppa_WIP
WHERE
budgetheader.clntcode = projects_PointOfSale.clntcode AND
budgetheader.projid = projects_PointOfSale.projcode AND
budgetheader.clntcode = clntsupp.clntcode AND
budgetheader.clntcode = projects.clntcode AND
budgetheader.projid = projects.projid AND
budgetheader.projrevisionid = projects.currentrevision AND
projects.clntcode *= ppa_WIP.Client AND
projects.projid *= ppa_WIP.Project AND
ppa_WIP.TransType = 'L' AND
budgetheader.budgetheadertype = 'L' AND
ppa_WIP.Status = 0 AND
projects.projid = 'HKE00339S' AND
projects.clntcode = 'DST01UK'
ORDER BY
projects.projid ASC,
projects_PointOfSale.userdefined_label ASC,
clntsupp.clntcode ASC


thanks,

Matt
London (UK)

 

[kskid]

Whenever you have a selection criteria involving an outer joined table, you have to account for the absence of the record in your record selection formula

(
isnull({ppa_WIP.Project}) or
(
ppa_WIP.TransType = 'L' AND
ppa_WIP.Status = 0
)
)

Cheers,

-LW

 

[matpj]

Hi LW,

that is great! you have no idea how long I have been trying different link options etc to get that to work!
have a star!


Matt
London (UK)