QUERY - 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 2000 Forums

SQL Server Development (2000)

QUERY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-04 : 08:14:02

WILSON8802 writes "HI THERE HOPE YOU CAN HELP,
I am trying to keep all items in ITEM_NO that have duplicates.
but it doesn't work. this is my query and my results

Thanks a bunch

SELECT DISTINCT ITEM_NO,LOC_ID,ORDER_UM_CD,UM_CD,SUBSTRING(DESCR,1,8)AS DESCR
FROM SLOC_ITEM,ITEM
WHERE LOC_ID < 1002
AND ITEM.ITEM_ID = SLOC_ITEM.ITEM_ID
AND ITEM_NO NOT IN(
SELECT ITEM_NO
FROM ITEM
GROUP BY ITEM_NO
HAVING COUNT(ITEM_NO)> 1
)
GROUP BY ITEM_NO,LOC_ID,ORDER_UM_CD,UM_CD,DESCR

ITEM_NO         LOC_ID      ORDER_UM_CD      UM_CD            DESCR    
--------------- ----------- ---------------- ---------------- -------- 
1001            1000        UNIT02RL         UNIT02RL         BAGS PRO 
1001            1001        UNIT02RL         UNIT02RL         BAGS PRO 
1002            1000        UNIT02CA         UNIT02CA         BAGS BIO 
1002            1001        UNIT02CA         UNIT02EA         BAGS BIO 
1003            1000        UNIT02PK         UNIT02EA         BAGS ZIP 
1003            1001        UNIT02PK         UNIT02PK         BAGS ZIP 
1004            1000        UNIT02PK         UNIT02CS         BAGS ZIP 
1004            1001        UNIT02PK         UNIT02PK         BAGS ZIP 
10059           1000        UNIT02CS         UNIT02EA         ELECTROD 
1006            1001        UNIT02GL         UNIT02GL         CLEANER  
1009            1000        UNIT02CS         UNIT02EA         BAGS PER 
1009            1001        UNIT02CS         UNIT02EA         BAGS PER 
1010            1000        UNIT02PK         UNIT02PK         CUPS PLA 
1011            1000        UNIT02PK         UNIT02PK         STRAWS F 
1011            1001        UNIT02PK         UNIT02PK         STRAWS F 
10122           1001        UNIT02BX         UNIT02EA         BLADE EX 
10125           1000        UNIT02EA         UNIT02EA         ANCHOR M"

Nazim
A custom title

1408 Posts

Posted - 2002-04-04 : 08:21:57

SELECT DISTINCT ITEM_NO,LOC_ID,ORDER_UM_CD,UM_CD,SUBSTRING(DESCR,1,8)AS DESCR
FROM SLOC_ITEM,ITEM
WHERE LOC_ID < 1001
AND ITEM.ITEM_ID = SLOC_ITEM.ITEM_ID
AND ITEM_NO NOT IN(
SELECT ITEM_NO
FROM ITEM
GROUP BY ITEM_NO
HAVING COUNT(ITEM_NO)> 1
)

HTH

--------------------------------------------------------------

Jay99

468 Posts

Posted - 2002-04-04 : 09:26:02

quote:

WILSON8802 writes "HI THERE HOPE YOU CAN HELP,
I am trying to keep all items in ITEM_NO that have duplicates.
but it doesn't work. this is my query and my results

I am confused ... 'keep' implies a delete; did you mean 'select'? Also, was does the LOC_ID have to do with dupes in Item_no


delete Item
where
    item_no in (
        select
            item_no
        from
            Item
        group by 
            item_no
        having
            count(*) = 1 )

Jay
<O>

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-04 : 09:45:52

problem may be with the group by clause....

GROUP BY ITEM_NO,LOC_ID,ORDER_UM_CD,UM_CD,DESCR
should read

GROUP BY ITEM_NO,LOC_ID,ORDER_UM_CD,UM_CD, SUBSTRING(DESCR,1,8)AS DESCR

also NOT A GOOD IDEA to have a column ALIAS to be the SAME as the COLUMN NAME....it's confuses the reader, and it MAY confuse the SQL query processer.

in which case this should read
GROUP BY ITEM_NO,LOC_ID,ORDER_UM_CD,UM_CD, SUBSTRING(DESCR,1,8)AS SHORT_DESCR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources