Dynamic Report--left outer join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Dynamic Report--left outer join

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2009-02-04 : 04:02:39

hi

can i join the 2 tables in the view as left outer join if thr is plz let me know

normally if i join the 2 tables it il be inner join right?

if i write the query as left outer the result il be correct if i implement that in the view that is it's working as inner join

how to change that into left outer join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 08:33:46

you can use inner as well as left join in view. it dependson your business rules. inner join is used when you want only matching records b/w two tables. left join is used when you want all records from left table regardless of presence of a match in right.
WHat's the problem you're facing? illustrate with some sample data

shm
Yak Posting Veteran

86 Posts

Posted - 2009-02-05 : 07:26:53

hi

SELECT PB.PBG_ID AS BUG_ID,
PB.PBG_ID_ORACLE AS OLD_BUG_ID, PT.PTK_tASK_NAME TASKNAME,
PB.IMPACTED_ITEMS,
PB.PBG_CREATED_DATE AS DATE_REPORTED,

FROM BUG AS PB
INNER JOIN MODULE PM ON PM.MOD_SEQ_NO = PB.MOD_SEQ_NO
INNER JOIN PHASE PP ON PP.PPH_SEQ_NO = PM.PPH_SEQ_NO
INNER JOIN PROJECT P ON P.PRJ_SEQ_NO = PP.PRJ_SEQ_NO
left outer JOIN TASK PT ON PT.PTK_SEQ_NO =PB.TASK_ID and pt.mod_seq_no = pm.mod_seq_no

this is the query in the dynamic report how to make a left outer join

data source view:

module(table) phase(table) project(table)
mod_seq_no----------->pph_seq_no
prj_seq_no----->prj_seq_no

bug (table) task (table)
pbg_seq_no
ptk_seq_no---------->ptk_seq_no
mod_seq_no mod_seq_no

the mod_seq_no both from the bug and the task is joined to module..this is the case in the data source view
wt happened is if i select the project filter cond while generating the report..one is from the heirarchy module-->phase--->project here i select the project the result i get some 711 rows

and if i select the project from the task that is the heirarchy is task-->module-->phase-->project here i get some 609 rows

but i shld get the same result from both the heirarchy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 09:46:41

sorry i cant understand what you're telling. please give some sample data to explain your problem rather than the query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources