Problem with Script - 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

General SQL Server Forums

New to SQL Server Programming

Problem with Script

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-03-06 : 13:54:45

Getting this error when trying to run the script below. Not quite sure what is wrong:
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ','.


select distinct imrefdes_sql.imrefdes_pkg, imrefdes_sql.imrefdes_doc_type,
imrefdes_sql.imrefdes_parent_item, imrefdes_sql.imrefdes_item_no, imrefdes_sql.imrefdes_ord_type,
imrefdes_sql.imrefdes_ord_no,imrefdes_sql.imrefdes_seq_no, imrefdes_sql.imrefdes_note
into imrefdes2_sql
from imrefdes_sql
group by imrefdes_sql.imrefdes_pkg, imrefdes_sql.imrefdes_doc_type,
imrefdes_sql.imrefdes_parent_item, imrefdes_sql.imrefdes_item_no, imrefdes_sql.imrefdes_ord_type,
imrefdes_sql.imrefdes_ord_no,imrefdes_sql.imrefdes_seq_no, imrefdes_sql.imrefdes_note
hAVING COUNT((imrefdes_sql.imrefdes_pkg,imrefdes_sql.imrefdes_doc_type,
imrefdes_sql.imrefdes_parent_item, imrefdes_sql.imrefdes_item_no,imrefdes_sql.imrefdes_ord_type,
imrefdes_sql.imrefdes_ord_no, imrefdes_sql.imrefdes_seq_no,imrefdes_sql.imrefdes_note)) > 1

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-06 : 14:10:02

You should just count on 1 column

Mike
"oh, that monkey is going to pay"

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-06 : 14:11:48

Try ..

select imrefdes_sql.imrefdes_pkg,
imrefdes_sql.imrefdes_doc_type,
imrefdes_sql.imrefdes_parent_item,
imrefdes_sql.imrefdes_item_no,
imrefdes_sql.imrefdes_ord_type,
imrefdes_sql.imrefdes_ord_no,
imrefdes_sql.imrefdes_seq_no,
imrefdes_sql.imrefdes_note
into imrefdes2_sql
from imrefdes_sql
group by imrefdes_sql.imrefdes_pkg,
imrefdes_sql.imrefdes_doc_type,
imrefdes_sql.imrefdes_parent_item,
imrefdes_sql.imrefdes_item_no,
imrefdes_sql.imrefdes_ord_type,
imrefdes_sql.imrefdes_ord_no,
imrefdes_sql.imrefdes_seq_no,
imrefdes_sql.imrefdes_note
HAVING COUNT(*) > 1

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-03-06 : 14:38:40

That did not quite work how I thought it should. Let me give you some sample data.

In the data below there should only be one record where the comment reads "Customer supplied" and there should only be one record where the comment reads "Pack 2 each plate and screw with"

What I was attempting to do above is select distinct records into a new table, then move them back into the original. The script above only moved the records that had duplicates.

[code]
Comments
PP P 02183 8580337 P 42 2 1 Customer supplied.
PP P 02183 8580337 P 42 2 2 Customer supplied.
PP P 02183 8580337 P 42 2 3 Customer supplied.
PP P 02183 8580337 P 42 2 4 Customer supplied.
PP P 02183 WS53 P 42 4 1 Pack 2 each plate and screw with
PP P 02183 WS53 P 42 4 2 one folded instruction sheet per bag
PP P 02183 WS53 P 42 4 3 and pack 250 bags per carton
PP P 02183 WS53 P 42 4 4 and label.
PP P 02183 WS53 P 42 4 5 Pack 2 each plate and screw with
PP P 02183 WS53 P 42 4 6 Pack 2 each plate and screw with
PP P 02183 WS53 P 42 4 7 Pack 2 each plate and screw with
[#/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 14:49:06

Now run this:


select imrefdes_sql.imrefdes_pkg,
  imrefdes_sql.imrefdes_doc_type,
  imrefdes_sql.imrefdes_parent_item,
  imrefdes_sql.imrefdes_item_no,
  imrefdes_sql.imrefdes_ord_type,
  imrefdes_sql.imrefdes_ord_no,
  imrefdes_sql.imrefdes_seq_no,  imrefdes_sql.imrefdes_note
into imrefdes2_sql
from imrefdes_sql
group by imrefdes_sql.imrefdes_pkg, 
         imrefdes_sql.imrefdes_doc_type,
         imrefdes_sql.imrefdes_parent_item, 
         imrefdes_sql.imrefdes_item_no, 
         imrefdes_sql.imrefdes_ord_type,
         imrefdes_sql.imrefdes_ord_no,
         imrefdes_sql.imrefdes_seq_no,         imrefdes_sql.imrefdes_note
HAVING COUNT(*) > 1

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-03-06 : 14:54:30

That did the same thing.
IN the example data above it only brought over

PP P 02183 WS53 P 42 4 7 Pack 2 each plate and screw with
PP P 02183 8580337 P 42 2 1 Customer supplied

What I want it to bring over into the new table is:
PP P 02183 8580337 P 42 2 1 Customer supplied.
PP P 02183 WS53 P 42 4 1 Pack 2 each plate and screw with
PP P 02183 WS53 P 42 4 2 one folded instruction sheet per bag
PP P 02183 WS53 P 42 4 3 and pack 250 bags per carton
PP P 02183 WS53 P 42 4 4 and label.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 15:02:14

[code]

Select imrefdes_sql.imrefdes_pkg,
imrefdes_sql.imrefdes_doc_type,
imrefdes_sql.imrefdes_parent_item,
imrefdes_sql.imrefdes_item_no,
imrefdes_sql.imrefdes_ord_type,
imrefdes_sql.imrefdes_ord_no,
imrefdes_sql.imrefdes_seq_no,
imrefdes_sql.imrefdes_note
into imrefdes2_sql
from (Select imrefdes_sql.imrefdes_pkg,
imrefdes_sql.imrefdes_doc_type,
imrefdes_sql.imrefdes_parent_item,
imrefdes_sql.imrefdes_item_no,
imrefdes_sql.imrefdes_ord_type,
imrefdes_sql.imrefdes_ord_no,
imrefdes_sql.imrefdes_seq_no,
imrefdes_sql.imrefdes_note,
ROW_NUMBER()OVER (PARTITION BY imrefdes_sql.imrefdes_pkg,
imrefdes_sql.imrefdes_doc_type,
imrefdes_sql.imrefdes_parent_item,
imrefdes_sql.imrefdes_item_no,
imrefdes_sql.imrefdes_ord_type,
imrefdes_sql.imrefdes_ord_no,
imrefdes_sql.imrefdes_note ORDER BY imrefdes_sql.imrefdes_seq_no)
AS SEQ
FROM imrefdes_sql)Z
WHERE Z.SEQ =1[/code]

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-03-06 : 15:03:52

I am using SQL 2000
I get this when I run the above script:
Server: Msg 195, Level 15, State 10, Line 18
'ROW_NUMBER' is not a recognized function name.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 15:05:40