June 7, 2006 at 6:15 pm
I have a view which combines 3 tables JobsAllocated, MainJob, and Persons plus several other incidental tables which display fine.
the view works fine in that it displays the right numbers of rows and most of the details however the view occasionally dosn't pick up on one of the id fields
example the customer id in mainjob is a compulsory field but the view shows it as a <null> occasionally even though the underlying record is correct and consequently dosn't report that customers details
Any Ideas were to start looking. the view was built using access project graphically and has very little processing done to it
approx 100 fields and 1200 rows
Thanks Gerard
June 8, 2006 at 6:15 am
Can you post the query? It's hard to guess at what the problem is without seeing it.
But, just to toss out some guesses, I'd start by looking for outer joins (i.e., left or right joins).
Do you have any CASE, CAST, or CONVERT functions in your statement?
Have you stepped through your WHERE conditions, to make sure they all make sense, and there are no typos?
I'd also try building up the query in pieces, and watch to see what part breaks. If you just run a query on mainjob, does it return the null fields? When you join it to the customer table, do the null fields show up?
June 9, 2006 at 7:47 am
I agree, the most probable source of such problem is outer join. An example (only freely based on what you mentioned about the table structure, but shows how it works):
CREATE TABLE #mainjob(job_id int, jobname varchar(20), customer_id int)
CREATE TABLE #persons(person_id int, fname varchar(30), lname varchar(30), city varchar(20))
INSERT INTO #mainjob VALUES (1,'director',3)
INSERT INTO #mainjob VALUES (2,'manager',6)
INSERT INTO #mainjob VALUES (3,'operator',4)
INSERT INTO #persons VALUES (3, 'John','Quincy','New York')
INSERT INTO #persons VALUES (4, 'Peter','Dalton','Toronto')
INSERT INTO #persons VALUES (5, 'Eve','Amory','Houston')
INSERT INTO #persons VALUES (6, 'Susan','Croft','London')
SELECT mj.job_id, mj.jobname, p.person_id as customer_id, p.city
FROM #mainjob mj
LEFT JOIN #persons p ON mj.customer_id = p.person_id AND p.fname <> 'John'
job_id jobname customer_id city
----------- -------------------- ----------- --------------------
1 director NULL NULL
2 manager 6 London
3 operator 4 Toronto
(3 row(s) affected)
As you can see, for director the customer ID is NULL and the details are not reported - although they exist in the database. Why? Because the LEFT JOIN contains condition, that first name must not be John. All people with first name John will thus not be found in the table Persons (such condition is like a filter that is applied before joining the tables) and NULLs will be reported.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply