Report issue - SQL Server Forums

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Report issue

Author

Topic

Plaice
Starting Member

20 Posts

Posted - 2009-11-27 : 10:09:40

Hi new to the forum and looking for some help.

Having a problem with a report and wondering if anyone can help with the code.

Initially I have:

Select Table_C.A, Table_C.B, Table_C, Table_C.D
FROM Table_A inner join Table_B and Table_B inner join Table_C
WHERE Table_A.E = @Parameter AND Table_A.F = @Parameter

This query brings up the correct 10 lines of data. The problem I have is adding in the extra tables I need and filtering the results from that. I always need to show the ten lines but for those lines the extra tables may or may not have data to display.

I also need to have:

WHERE Table_D.G = 'R' or Table_E.H = 'R'

As from the additional tables data I only want to display some of it based on this addition in the WHERE clause.

Any help would be appreciated as it's now started to give me a headache.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-11-27 : 10:58:35

What it looks like you want are outer joins to tables E and F where you will always display data from A, B, C, and D but is there is no data to join return NULLS for the D&F otherwise display the data.

John
It's a small world (but I wouldn't want to paint it)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-28 : 02:08:17

so you want all ones which have matching data in Table_D & Table_E which satisfies conditions WHERE Table_D.G = 'R' or Table_E.H = 'R' and also all records which doesnt have data in Table_D & Table_E

Plaice
Starting Member

20 Posts

Posted - 2009-12-01 : 12:02:40

quote:
Originally posted by visakh16

so you want all ones which have matching data in Table_D & Table_E which satisfies conditions WHERE Table_D.G = 'R' or Table_E.H = 'R' and also all records which doesnt have data in Table_D & Table_E

Yes, that's right

Plaice
Starting Member

20 Posts

Posted - 2009-12-08 : 09:32:14

After having a week off still having issues with this report so thought I'd post up a reply to clarify the issue and try and get a hand with it.

The original sql is this:


SELECT        PART.ID, PART.DESCRIPTION, PART.FABRICATED, PART.PURCHASED, PART.BUYER_USER_ID, REQUIREMENT.ISSUED_QTY, REQUIREMENT.CALC_QTY
FROM            PART INNER JOIN
                         REQUIREMENT ON PART.ID = REQUIREMENT.PART_ID INNER JOIN
                         WORK_ORDER ON REQUIREMENT.WORKORDER_TYPE = WORK_ORDER.TYPE AND REQUIREMENT.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID AND 
                         REQUIREMENT.WORKORDER_LOT_ID = WORK_ORDER.LOT_ID AND REQUIREMENT.WORKORDER_SPLIT_ID = WORK_ORDER.SPLIT_ID AND 
                         REQUIREMENT.WORKORDER_SUB_ID = WORK_ORDER.SUB_ID
WHERE        (WORK_ORDER.BASE_ID = @WOBase) AND (WORK_ORDER.LOT_ID = @WOLot)

Now this brings up 10 lines of data (using the parameters I'm testing with) which is correct as there's 10 parts in the Work Order. However for each fabricated part there will also be a Work Order. Therefore:


SELECT        PART.ID, PART.DESCRIPTION, PART.FABRICATED, PART.PURCHASED, PART.BUYER_USER_ID, REQUIREMENT.ISSUED_QTY, REQUIREMENT.CALC_QTY
FROM            PART INNER JOIN
                         REQUIREMENT ON PART.ID = REQUIREMENT.PART_ID INNER JOIN
                         WORK_ORDER ON REQUIREMENT.WORKORDER_TYPE = WORK_ORDER.TYPE AND REQUIREMENT.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID AND 
                         REQUIREMENT.WORKORDER_LOT_ID = WORK_ORDER.LOT_ID AND REQUIREMENT.WORKORDER_SPLIT_ID = WORK_ORDER.SPLIT_ID AND 
                         REQUIREMENT.WORKORDER_SUB_ID = WORK_ORDER.SUB_ID LEFT OUTER JOIN
                         WORK_ORDER AS WORK_ORDER_1 ON PART.ID = WORK_ORDER_1.PART_ID
WHERE        (WORK_ORDER.BASE_ID = @WOBase) AND (WORK_ORDER.LOT_ID = @WOLot)

Adding in the Work_ORDER_1 table using a Left Outer join returns 15 lines of data which once again is correct as for the fabricated parts there multiple Work Orders. However for these Work Orders I only want to return the data where WORK_ORDER_1.STATUS = 'R' but if I add this on to my WHERE clause it only returns 1 line of data. How do I add this filter to the query while still displaying the 10 oringinal lines?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-08 : 10:25:07

Since you are doing a LEFT JOIN, Try adding this...

(WORK_ORDER_1.STATUS = 'R' OR WORK_ORDER_1.STATUS IS NULL)

Plaice
Starting Member

20 Posts

Posted - 2009-12-08 : 11:06:23

Thanks for the reply. Had already tried that nad in doing so it returns 9 lines of data although can see no reason why it drops off one part.

ETA: Can see why now, using that WHERE clause leaves out any fabricated parts that do not have any Work Orders with Status = 'R'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources