June 20, 2003 at 6:20 am
sql server 7
The following query works fine until I try to add a where clause (sub query) on any of the from tables. When I add a subquery such as (select * from dbo.PO_Custom_TextSmall1 where poct_uid = 10) the outer join fails and the result is as an inner join is occurring???????
SELECT poprj_UID,
pocst_Cost02 AS [Approved Budget],
poflg_Flag01 AS [Project Status Red],
poflg_Flag02 AS [Project Status Yellow],
poflg_Flag03 AS [Project Status Green],
CASE (poflg_Flag04)
WHEN 1 THEN 'Y' ELSE 'N' END AS [Regulatory/Core],
CASE (poflg_Flag05)
WHEN 1 THEN 'Y' ELSE 'N' END AS Contractual,
CASE (poflg_Flag07)
WHEN 1 THEN 'Y' ELSE 'N' END AS [Mgmt Visibility],
podat_Start01 AS [Date Status Updated],
potxt_Small01 AS [Sponsor Dept],
potxt_Small02 AS [IT Lead],
potxt_Small03 AS [Project State],
potxt_Small05 AS [Strategic Imperative],
potxt_Small06 AS [IT Project Team],
potxt_Small11 AS [Project Status Cost],
potxt_Small12 AS Sponsor,
potxt_Small14 AS [Issue Severity],
potxt_Small19 AS System,
potxt_Small20 AS [Risk Level],
potxt_Small29 AS [Project Status Resource],
potxt_Small30 AS [Project Status Schedule],
potxt_Small32 AS [Project Type],
potxt_Small58 AS [Project Threshhold],
potxt_Small59 AS [Project Next Step],
potxt_Small60 AS [Project Current Phase]
FROM
(select poprj_uid from dbo.PO_Projects) as po_projects,
(select * from dbo.PO_Custom_TextSmall2) as PO_Custom_TextSmall2,
dbo.PO_Custom_TextSmall1,
dbo.PO_Custom_StartDates,
dbo.PO_Custom_Flags,
dbo.PO_Custom_Costs
where
poprj_UID *= PO_Custom_TextSmall2.LinkID and
poprj_UID *= dbo.PO_Custom_TextSmall1.LinkID and
poprj_UID *= dbo.PO_Custom_StartDates.LinkID and
poprj_UID *= dbo.PO_Custom_Flags.LinkID and
poprj_UID *= dbo.PO_Custom_Costs.LinkId
June 20, 2003 at 7:33 am
Can't see a reason for this behaviour. Maybe somewhere down the line, one of the outer joins gets 'converted' to a cross join?
I would suggest advancing step by step, adding one table in the join at a time, to come up with the point where the results are erroneous. That might point you in the right direction.
PS: You should consider converting the query to SQL-92 standard using 'OUTER JOIN' in the FROM clause. In general, this format is less errorprone and more readable (not to mention portable to other RDBMSes).
June 20, 2003 at 11:01 am
Maybe you left out something when you pasted this query,
but I don't see this table joined with any of your other tables
(select poprj_uid from dbo.PO_Projects) as po_projects
The other thing I want to point out is that I don't see any subquery
its all derived tables
MW
MW
June 23, 2003 at 12:10 am
use this way to find out what exactly the problem is. Outerjoins in where clauses have troubled me more then once. Using the JOIN-sintax it solved my problems most of the time or showed me semantic errors.
FROM dbo.PO_Projects as po_projects
left join dbo.PO_Custom_TextSmall2 as PO_Custom_TextSmall2 on poprj_UID = PO_Custom_TextSmall2.LinkID
left join dbo.PO_Custom_TextSmall1 on poprj_UID = dbo.PO_Custom_TextSmall1.LinkID
left join dbo.PO_Custom_StartDates on poprj_UID = dbo.PO_Custom_StartDates.LinkID
left join dbo.PO_Custom_Flags on poprj_UID = dbo.PO_Custom_Flags.LinkID
left join dbo.PO_Custom_Costs on poprj_UID = dbo.PO_Custom_Costs.LinkId
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 23, 2003 at 3:26 am
Hi!
Looking at BOL you'll see that the *=/=* syntax is not anymore supported ("can lead to unpredicted results").
Use LEFT/RIGHT OUTER JOIN instead. In addition, you'll have to include a restricting predicate in the ON clause of the JOIN keyword to be sure your outer join will show up with all the data on the LEFT/RIGHT side of the query.
best regards,
chris.
June 23, 2003 at 6:48 am
Using the updated syntax I get the same results. I actually started with that syntax. I have found that it works if only one table is outer joined. As soon as I add the second table many of the records from the left (project) drop.
June 23, 2003 at 7:28 am
I'm afraid we're not going to solve your problem without any additional input.
Can you post some DDL and sample data on this problem...
June 23, 2003 at 12:36 pm
I don't know if this will work, but try to qualify the column poprj_UID as po_projects.poprj_UID.
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply