|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
I'm confused need some help ... with UnionI trying to get data using a UNION ( so I can have all the values, because in the main table in one column 'PARENT_PROD_ID' the value is missing for one particular record). However, it is repeating the rows for that particular record where the row is empty. I'm getting the same row twice. Any Ideas will be highly appreciated. Thanks in advance Here is my query. -- VALUES for PARENT_PROD_ID BEING NULL and NOT NULL SELECT DISTINCT AB.ORDER_NO, AB.ORDER_INT_LINE_NO, AB.SCHED_LINE_NO, AB.INV_ITEM_ID, CD.DESCR, AB.DEMAND_LINE_NO, AB.DEMAND_STATUS, AB.DEMAND_DATE, AB.PRODUCT_ID, AB.PARENT_PROD_ID, AB.SHIP_TO_CUST_ID, AB.SHIP_CUST_NAME1, AB.QTY_REQUESTED, BB.QTY_AVAILABLE, AB.DEMAND_SOURCE, CC.OPTIONAL_SHIP, CC.PRODUCT_ID, CC.PROD_COMPONENT_ID FROM PS_DEMAND_INF_INV AB, PS_BU_ITEMS_INV BB, PS_MASTER_ITEM_TBL CB, PS_PRODKIT_COMPS CC, PS_PROD_ITEM CD WHERE AB.PARENT_PROD_ID <> '' AND AB.PRODUCT_ID = CD.PRODUCT_ID AND AB.INV_ITEM_ID = CC.PROD_COMPONENT_ID AND AB.PARENT_PROD_ID = CC.PRODUCT_ID AND AB.INV_ITEM_ID = CD.INV_ITEM_ID AND AB.INV_ITEM_ID = BB.INV_ITEM_ID AND CC.PRODUCT_ID IN (SELECT DISTINCT A.PARENT_PROD_ID FROM (PS_DEMAND_INF_INV A LEFT OUTER Join PS_PRODKIT_COMPS DD On A.PARENT_PROD_ID = DD.PRODUCT_ID)) UNION SELECT DISTINCT AB.ORDER_NO, AB.ORDER_INT_LINE_NO, AB.SCHED_LINE_NO, AB.INV_ITEM_ID, CD.DESCR, AB.DEMAND_LINE_NO, AB.DEMAND_STATUS, AB.DEMAND_DATE, AB.PRODUCT_ID, AB.PARENT_PROD_ID, AB.SHIP_TO_CUST_ID, AB.SHIP_CUST_NAME1, AB.QTY_REQUESTED, BB.QTY_AVAILABLE, AB.DEMAND_SOURCE, CC.OPTIONAL_SHIP, CC.PRODUCT_ID, CC.PROD_COMPONENT_ID FROM PS_DEMAND_INF_INV AB, PS_BU_ITEMS_INV BB, PS_MASTER_ITEM_TBL CB, PS_PRODKIT_COMPS CC, PS_PROD_ITEM CD WHERE AB.PARENT_PROD_ID = '' AND AB.INV_ITEM_ID = CC.PROD_COMPONENT_ID AND AB.INV_ITEM_ID = CB.INV_ITEM_ID AND AB.INV_ITEM_ID = CD.INV_ITEM_ID AND AB.INV_ITEM_ID = BB.INV_ITEM_ID AND CC.PRODUCT_ID IN (SELECT DISTINCT A.PARENT_PROD_ID FROM (PS_DEMAND_INF_INV A LEFT OUTER Join PS_PRODKIT_COMPS DD On A.PARENT_PROD_ID = DD.PRODUCT_ID)) It looks like it is nothing to do with UNION. Your LEFT JOIN Return some
duplicates it seems. -- Show quoteThanks Ravi "riaz.has***@gmail.com" wrote: > Hi every1, > I trying to get data using a UNION ( so I can have all the values, > because in the main table in one column 'PARENT_PROD_ID' the value is > missing for one particular record). > > However, it is repeating the rows for that particular record where the > row is empty. > I'm getting the same row twice. > Any Ideas will be highly appreciated. > Thanks in advance > > Here is my query. > > > -- VALUES for PARENT_PROD_ID BEING NULL and NOT NULL > SELECT DISTINCT > AB.ORDER_NO, > AB.ORDER_INT_LINE_NO, > AB.SCHED_LINE_NO, > AB.INV_ITEM_ID, > CD.DESCR, > AB.DEMAND_LINE_NO, > AB.DEMAND_STATUS, > AB.DEMAND_DATE, > AB.PRODUCT_ID, > AB.PARENT_PROD_ID, > AB.SHIP_TO_CUST_ID, > AB.SHIP_CUST_NAME1, > AB.QTY_REQUESTED, > BB.QTY_AVAILABLE, > AB.DEMAND_SOURCE, > CC.OPTIONAL_SHIP, > CC.PRODUCT_ID, > CC.PROD_COMPONENT_ID > FROM PS_DEMAND_INF_INV AB, PS_BU_ITEMS_INV BB, PS_MASTER_ITEM_TBL CB, > PS_PRODKIT_COMPS CC, PS_PROD_ITEM CD > WHERE AB.PARENT_PROD_ID <> '' > AND AB.PRODUCT_ID = CD.PRODUCT_ID > AND AB.INV_ITEM_ID = CC.PROD_COMPONENT_ID > AND AB.PARENT_PROD_ID = CC.PRODUCT_ID > AND AB.INV_ITEM_ID = CD.INV_ITEM_ID > AND AB.INV_ITEM_ID = BB.INV_ITEM_ID > AND CC.PRODUCT_ID IN (SELECT DISTINCT A.PARENT_PROD_ID > FROM (PS_DEMAND_INF_INV A > LEFT OUTER Join PS_PRODKIT_COMPS DD > On A.PARENT_PROD_ID = DD.PRODUCT_ID)) > UNION > SELECT DISTINCT > AB.ORDER_NO, > AB.ORDER_INT_LINE_NO, > AB.SCHED_LINE_NO, > AB.INV_ITEM_ID, > CD.DESCR, > AB.DEMAND_LINE_NO, > AB.DEMAND_STATUS, > AB.DEMAND_DATE, > AB.PRODUCT_ID, > AB.PARENT_PROD_ID, > AB.SHIP_TO_CUST_ID, > AB.SHIP_CUST_NAME1, > AB.QTY_REQUESTED, > BB.QTY_AVAILABLE, > AB.DEMAND_SOURCE, > CC.OPTIONAL_SHIP, > CC.PRODUCT_ID, > CC.PROD_COMPONENT_ID > FROM PS_DEMAND_INF_INV AB, PS_BU_ITEMS_INV BB, PS_MASTER_ITEM_TBL CB, > PS_PRODKIT_COMPS CC, PS_PROD_ITEM CD > WHERE AB.PARENT_PROD_ID = '' > AND AB.INV_ITEM_ID = CC.PROD_COMPONENT_ID > AND AB.INV_ITEM_ID = CB.INV_ITEM_ID > AND AB.INV_ITEM_ID = CD.INV_ITEM_ID > AND AB.INV_ITEM_ID = BB.INV_ITEM_ID > AND CC.PRODUCT_ID IN (SELECT DISTINCT A.PARENT_PROD_ID > FROM (PS_DEMAND_INF_INV A > LEFT OUTER Join PS_PRODKIT_COMPS DD > On A.PARENT_PROD_ID = DD.PRODUCT_ID)) > > riaz.has***@gmail.com wrote:
> Hi every1, If you using UNION, which you are, you should never see duplicate rows > I trying to get data using a UNION ( so I can have all the values, > because in the main table in one column 'PARENT_PROD_ID' the value is > missing for one particular record). > > However, it is repeating the rows for that particular record where the > row is empty. > I'm getting the same row twice. > Any Ideas will be highly appreciated. > Thanks in advance > in the output since UNION always removes duplicates. You probably want to use UNION ALL in any case, since using UNION causes much more overhead as SQL Server looks for and removes duplicates. Also you are using "AB.PARENT_PROD_ID <> '' ", which does not handle NULL values. If the value can be NULL you need to check for NULLs using IS NULL or IS NOT NULL. You also have multiple FROM statements in the both parts of the query... Can you explain in a little more detail what you are trying to do? David thanks for your input.
You are right the UNION should eliminate the duplicates, but this is what confusing me because it's not eliminating the duplicates from the second set. What I'm trying to do is to generate a report based on this query which includes 4 different tables. In the main table (PS_DEMAND_INF_INV), in one column (PARENT_PROD_ID) is missing for one record and that record is not showing up on the report. So that is why I tried to use left join and then use union (result set in which the field is not null UNION result set in which the field is null). I should be able to get both result sets with no duplicate. However, the first result set is fine but in the second resultset where the record is empty it looks like it is repeating that one for some odd reason. (In the second result set all the values are same both rows). here is the sample data ORDER_NO INV_ITEM_ID PARENT_PROD_ID 514683 123456 654321 514683 235685AB 654321 514683 123456 514683 123456 As you can see from the above sample that only two records have the PARENT_PROD_ID, and the third one & forth one doesn't. What I'm trying to do is just to get only three records back since the forth one is the duplicate. Any ideas!.... Thanks riaz.has***@gmail.com wrote:
> ORDER_NO INV_ITEM_ID PARENT_PROD_ID A LEFT OUTER JOIN should be sufficient. I don't understand why you need > 514683 123456 654321 > 514683 235685AB 654321 > 514683 123456 > 514683 123456 the UNION in the first place. If those parent_prod_id values are NULL and not an empty string, then you would need to code for them as such. But I don't think you need the UNION in any case. Your current outpuer join is in the IN clause, not the main query. So I'm not really sure what you intended. If it's as simple as returning all rows from one table that may not appear in another, you want to make the table with the values the LEFT table and the table with the missing values the RIGHT table and use a LEFT OUTER JOIN. For example: create table #a (col1 int) create table #b (col1 int) insert into #a values (1) insert into #a values (2) insert into #a values (3) insert into #b values (1) insert into #b values (2) select a.col1, b.col1 from #a a left outer join #b b on a.col1 = b.col1 drop table #a drop table #b col1 col1 ----------- ----------- 1 1 2 2 3 NULL Missed the IN clause in the post which is why I mentioned the mupltiple FROM clauses?  |
|||||||||||||||||||||||
Other interesting topics