(T-SQL)-VIEW Combined tables into one but wrong positions 1

[markajem]

Present results below:

[tt]
ITEMNMBR QtyAlloc CMPTITNM BOMAlloc Grand_Tot_Pcs
<NULL> <NULL> 51130 48 <NULL>
<NULL> <NULL> 51480 72 <NULL>
10100 54 10100 72 126
65436 24 <NULL> <NULL> 24
72273 34 72273 20 54
[/tt]

Present code as below:
[tt]
SELECT TOP 100 PERCENT AIA.ITEMNMBR
, AIA.QtyAlloc
, CAWB.CMPTITNM
, SUM(CAWB.Total_Pcs_Needed) AS BOMAlloc
, ISNULL(SUM(CAWB.Total_Pcs_Needed), 0)
+ AIA.QtyAlloc AS Grand_Tot_Pcs

FROM dbo.[View_All_Items Allocated] AIA FULL OUTER JOIN
dbo.VIEW_CorrectAllocWithBOM CAWB ON AIA.ITEMNMBR = CAWB.CMPTITNM

GROUP BY AIA.ITEMNMBR
, AIA.QtyAlloc
, CAWB.CMPTITNM

ORDER BY AIA.ITEMNMBR
[/tt]

My desired results below:

[tt]
REFNM ITEMNMBR QtyAlloc CMPTITNM BOMAlloc Grand_Tot_Pcs
51130 <NULL> <NULL> 51130 48 48
51480 <NULL> <NULL> 51480 72 72
10100 10100 54 10100 72 126
65436 65436 24 <NULL> <NULL> 24
72273 72273 34 72273 20 54
[/tt]

Since I will use this view to feed many different cyrstal reports I want to try and avoid as much logic as possible when trying to pull the proper records from this view. Reason is because many of the reports that this view will feed this data is complicated enough as it is and do not want to have to add extra logic to the report just to pull the proper data from this VIEW. I will also need to pull the item number(REFNM) and grand total pieces (Grand_Tot_Pcs)

My fear is that since I am new to creating views it is so difficult for me to make changes to a present view and compare to past results to not only be sure that my new columns appear as needed but that my values have not changed. I basically have the logic but just don't have a strong enough SQL background yet.

Any help would be very much appreciated.

Thanks so much
Mark







Mark
Email: markanas333@hotmail.com

 

[SamGarland]

Aside from the REFNM field the two sets of results differ when Grand_Tot_Pcs is NULL. When you add a non-null integral value to NULL, you will get NULL, so maybe ISNULL should be applied to the result of the addition instead of SUM(CAWB.Total_Pcs_Needed):

ISNULL(SUM(CAWB.Total_Pcs_Needed) + AIA.QtyAlloc, 0) AS Grand_Tot_Pcs

 

[markajem]

Thanks for your reply Sam.

After giving it more thought I believe it would make more sense to have the ITEMNMBR = to CMPTITNM if ITEMNMBR is NULL instead of adding another column and making REFNM = to ITEMNMBR or CMPTITNM depending on the NULL values.

Would that make more sense. And then of course the next important part is being sure that Grand_Tot_Pcs takes the value of either BOMAlloc if ITEMNMBR is NULL or if ITEMNMBR is not NULL then the sum of both QtyAlloc and BOMAlloc as below.

DESIRED
[tt]
ITEMNMBR QtyAlloc CMPTITNM BOMAlloc Grand_Tot_Pcs
51130 <NULL> 51130 48 48
51480 <NULL> 51480 72 72
10100 54 10100 72 126
65436 24 <NULL> <NULL> 24
72273 34 72273 20 54
[/tt]

PRESENT
[tt]
ITEMNMBR QtyAlloc CMPTITNM BOMAlloc Grand_Tot_Pcs
<NULL> <NULL> 51130 48 <NULL>
<NULL> <NULL> 51480 72 <NULL>
10100 54 10100 72 126
65436 24 <NULL> <NULL> 24
72273 34 72273 20 54
[/tt]

Because then I can link to the ITEMNMBR and pull the field Grand_Tot_Pcs for all of my report needs.

Myself and the MIS director have programming backgrounds but unfortunately not that much experience in SQL coding so it makes it quite difficult for us to get our results when the problem can be as simple as a syntax error such as &quot;,&quot;. We know what our logic must be but it gets so frustrating when we don't know the langauge so well. We have created so many views to get to this point which feed one another and really feel we have come a long way with this project.

We understand it is not necc to have as many columns as the final output result when we only need ITEMNMBR and Grand_Tot_Pcs for our final result but seeing the columns helps us understand where we are going and where we need to go with this project.

Thanks again for everyone's help; it's really appreciated.

Mark
Email: markanas333@hotmail.com

 

[SamGarland]

OK, I think I understand what you are trying to do now.
I think you want to include the contents of the view and the table in one resultset and the QtyAlloc and Total_Pcs_Needed totals are used in the same way.
If this is true, rather than doing a FULL OUTER JOIN and adding a whole lot of conditional logic to create duplicate columns, you really should do a UNION ALL of the table and the view:

SELECT ItemNumber, SUM(Quantity)
FROM
(
SELECT ITEMNMBR, QtyAlloc
FROM dbo.[View_All_Items Allocated]

UNION ALL

SELECT CMPTITNM, Total_Pcs_Needed
FROM dbo.VIEW_CorrectAllocWithBOM
)
AS combined( ItemNumber, Quantity )
GROUP BY ItemNumber, Quantity
ORDER BY ItemNumber

Is this what you are trying to achieve or have I misunderstood you?

 

[markajem]

Sam thanks for the suggestion. We were able to obtain the results we were looking for using other code. Although your code may have produced the same results.

We were able to get from:
[tt]
ITEMNMBR QtyAlloc CMPTITNM BOMAlloc Grand_Tot_Pcs
<NULL> <NULL> 51130 48 <NULL>
<NULL> <NULL> 51480 72 <NULL>
10100 54 10100 72 126
65436 24 <NULL> <NULL> 24
72273 34 72273 20 54
[/tt]

To this:

[tt]
REFNM ITEMNMBR QtyAlloc CMPTITNM BOMAlloc Grand_Tot_Pcs
51130 <NULL> <NULL> 51130 48 48
51480 <NULL> <NULL> 51480 72 72
10100 10100 54 10100 72 126
65436 65436 24 <NULL> <NULL> 24
72273 72273 34 72273 20 54
[/tt]

We do want to thank you though and we will review the code that you supplied. We have already done our testing with our present code and compared to the database tables and they are correct. But reviewing your code will help us learn more.

Thanks
Again.

Mark







Mark
Email: markanas333@hotmail.com