Sets Query.. Help

Question

select pack_item,compoment_itm,qty from openquery(MFIPROD,'select * from PACKAGE')
WHERE Pack_ITem ='64W17630'

PacK_item CMPNT_ITM 	 	QTY
64W17630	62M17630	 	 	1
64W17630	62L19620	 	 	1

when i run the above query i see for every pack_item, if there are components will get in above order.... 

When i am running the above query against the below data set i can only see one record even if there`s more than one component item

select pack_item,compoment_itm,qty from openquery(MFIPROD,'select * from PACKAGE')
gr

inner join  (SELECT distinct --RIGHT(ITM_CD,5) AS [Product Number]
CASE WHEN  M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'ADJUSTABLE' THEN itm.new_product_number
                                                WHEN M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'REGULAR' THEN itm.new_product_number
                                                WHEN M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'LOW PRO' THEN itm.new_product_number
                                ELSE isnull(old_product_number,product_number) END  AS [Product Number]
,CASE WHEN  M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'ADJUSTABLE' THEN left(M.ITM_CD,3)+ cast(itm.new_product_number as nvarchar(5))
                                                WHEN M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'REGULAR' THEN left(M.ITM_CD,3)+ cast(itm.new_product_number as nvarchar(5))
                                                WHEN M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'LOW PRO' THEN left(M.ITM_CD,3)+ cast(itm.new_product_number as nvarchar(5))
                                ELSE M.ITM_CD END AS [Old gers Number]
                ,CASE WHEN M.SIZ_ID = 'EACH' THEN NULL ELSE REPLACE(M.SIZ, SPACE(1),'') END AS Size
                ,CASE WHEN  M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'ADJUSTABLE' THEN 'ADJ' 
                                                WHEN M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'REGULAR' THEN 'REG' 
                                                WHEN M.SIZ_ID = 'BOX SPRING' AND M.FINISH = 'LOW PRO' THEN 'LOW'
                                ELSE NULL END AS Color
                ,CASE WHEN ITM_TP_CD = 'INV' THEN 
                                                CASE WHEN  M.FLOOR = 'Y' THEN 'FLOOR' ELSE 'PRIME' END
                                ELSE '' END AS Style  
                ,CASE WHEN M.UDF7_CD = 'N' THEN 'Y' ELSE 'N' END AS [Blocked for purchasing]   
                ,M.itm_cd 
                ,itm.product_name
FROM MFI_MASTER_PRODUCT M
                                JOIN DBO.ITEMAXDM I ON I.MODEL=M.FINISH_ID
                                JOIN DBO.VENDORDMAX V ON V.VE_CD=M.VE_CD
left outer join 

			(select r.new_product_number,i.product_number, r.product_number old_product_number,
			--Product_Number, 
			Display_Product_Name, Search_Name, Product_Type, Product_Name
			, Item_Model_Group, Tracking_Dimension_Group, Storage_Dimension_Group
			, Product_Dimension_Group, Item_Group, Hierarchy, Category_Hierarchy
			, Color_Group, Size_group, Style_group, Segment, BRAND, CATEGORY, COMFORT
			, CONSTRUCTN, ACTIVE, PRICEPOINT, Vendor_Name
			from dbo.item_master i
			inner join dbo.Replaced_Items r on i.product_number = r.new_product_number
			)  itm
 on RIGHT(ITM_CD,5) = isnull(itm.old_product_number,itm.product_number)
WHERE I.COMPLETE='Y' AND 
ITM_TP_CD = 'INV' AND
 VERSION = 'GERS LIVE'
                                                                AND (DROP_CD <> 'DEL' OR DROP_CD IS NULL) AND SIZ_ID IN ('MATTRESS', 'BOX SPRING')
                                                                AND M.FINISH_ID <> 'CLEARANCE' AND M.SIZ NOT IN ('FULL XL', 'DUAL KING', 'OLYMPIC')
        )pd 
        on gr.CMPNT_ITM_CD = pd.itm_cd
where pd.[Product Number] is not null  
order by pkg_itm_cd desc       

Can someone help me out ??????????

---

FM

Answer 1

Your post have remained unanswered for a couple of hours, and there may be a reason for it.

You post is not entirely simple to understand. It seems that when you run the simple query:

select pack_item,compoment_itm,qty
from openquery(MFIPROD,'select * from PACKAGE')
                       WHERE Pack_ITem ='64W17630'

You get back two rows, but when you join it with a complex query which I have not deciphered in full, you only get one row back. I would assume that  the condition

  on gr.CMPNT_ITM_CD = pd.itm_cd

only matches one row in the big query.

I would encourage you to spend some time of cleaning up the format your big query, then it may easier for us to read. Sometimes I do that but I have an appointment with my pillow in just a few minutes, so I abstain. And who knows, if you fix the formatting and look closer, maybe you find the error in the query!

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

How many rows are returning from your inner query?

did you try remove the "where pd.[Product Number] is not null" condition to check the number of records?

Please narrow down your analysis to inner query first and incase you are sure of the two columns are retruned for "62M17630" and "62L19620", then you join with the OPENROWSET.

Apart, I would suggest you to populate the record from the OPENQUERYquery to a temp table and do the join with the temp table.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

Actually i found the answer thank you.. it was not correctly explained by me.. going to delete the post

---

FM

Answer 4

Hi Farhan1,

Thanks for your question!

If you have answer to this question, could you please post it for helping others who have the similar problem? It's a good chance for us to learn more about SQL Server.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.