rows are repeated - 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 2012 Forums

Transact-SQL (2012)

rows are repeated

Author

Topic

puppy
Starting Member

5 Posts

Posted - 2013-12-17 : 06:10:41

Hi,
For the below mentioned query the values/output appears more than once

SELECT
co.order_reference_id as iSell_Order_Number,
co.order_id as Pick_ID,
ct.art as Article,
b.adsc as Article_Name,
(CASE
WHEN co.order_destination = 0 then 'Customer 1'
WHEN co.order_destination = 1 then 'Transport 1'
WHEN co.order_destination = 2 then 'Customer 2'
WHEN co.order_destination = 3 then 'Transport 2'
ELSE co.order_destination
END)as Handout_area,
(CASE
WHEN ce.reason_code = 1 then 'Out of stock'
WHEN ce.reason_code = 2 then 'Last item damaged'
WHEN ce.reason_code = 3 then 'Wait for replenishment'
WHEN ce.reason_code = 4 then 'Confirmed oversales'
WHEN ce.reason_code = 5 then 'Others'
WHEN ce.reason_code = 9 then 'Return exception'
ELSE ce.reason_code
END) as Exception,
substr(ce.upd_time, 1, 22) as Time_of_status_change,max(
ce.upd_by_user) as User,
sum(a.nopa) as Item_per_Article,
substr(co.order_create_time, 1, 10) as Date_of_Payment,
substr(co.order_create_time, 12, 16) as Time_of_Payment

FROM
sgf_cps_order_hist co,
sgf_cps_task_hist ct,
sgf_cps_exception_correction ce,
iwaab b,
iwaaa a

WHERE
co.order_id = ct.order_id
and ct.art = b.art
and b.art = a.art
and co.order_id = ce.order_id
and ct.art = ce.art
and ce.anomaly_type='E'
and ce.reason_code <>0
and substr(co.order_create_time, 1, 10) >= '2013-12-09'
and substr(co.order_create_time, 1, 10) <= '2013-12-16'
and ce.reason_code in ('0','1','2','3','4','5','9%')

GROUP BY
co.order_reference_id,
co.order_id,
ct.art,
b.adsc,
co.order_destination,
ce.reason_code,
ce.upd_time,
ce.upd_by_user,
co.order_create_time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 07:35:34

whicn values? how do want them to appear as per your rule? show us sample values and also your expected output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

puppy
Starting Member

5 Posts

Posted - 2013-12-17 : 23:33:14

504082058 27266 87212500 MÅLA KRITOR BL FGR 24ST Transport 1 Out of stock 2013-12-11 17:58:28 ALHID1 1 11/12/2013 32:56.5
504120960 27273 50238858 MA PÅBYGGSIDA LD F HÖG 60 GLAS 2ST Transport 1 Out of stock 2013-12-11 21:13:59 ALHID1 1 11/12/2013 09:18.6
504286718 27350 60205800 LAXARBY DÖRR 40X60 VIT Customer 1 Out of stock 2013-12-12 13:42:31 MAKOM1 1 12/12/2013 22:19.2
504861696 27623 90191118 KARLSTAD KLÄD H23/32 ISUNDA GRÅ Customer 1 Out of stock 2013-12-14 13:00:53 MNSNN 1 14/12/2013 44:34.2
505280232 27953 20237756 HELANDE DEKORFRONT RFR STÅL Customer 1 Out of stock 2013-12-15 19:52:41 DAFOF 1 15/12/2013 29:56.6
505314413 27970 20237756 HELANDE DEKORFRONT RFR STÅL Transport 1 Out of stock 2013-12-15 20:41:29 DAFOF 1 15/12/2013 47:31.8
505314413 27970 80221451 UTRUSTA LÅDFRNT LÅG 40 VIT Transport 1 Out of stock 2013-12-15 20:41:42 DAFOF 1 15/12/2013 47:31.8
505314857 27972 80221451 UTRUSTA LÅDFRNT LÅG 40 VIT Transport 1 Out of stock 2013-12-15 20:47:30 DAFOF 1 15/12/2013 47:33.1
505699665 28115 40228812 IDOLF STOL VIT Transport 1 Out of stock 2013-12-17 15:52:49 SESEN2 1 17/12/2013 50:24.7
505699665 28115 40228812 IDOLF STOL VIT Transport 1 Wait for replenishment 2013-12-17 12:48:28 HAPUK 1 17/12/2013 50:24.7

from the above result some of the values i.e the articles numbers are repeated twice so i would like it to be displayed only once.please help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-18 : 01:06:09

are you using Microsoft SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

puppy
Starting Member

5 Posts

Posted - 2013-12-18 : 01:29:13

No i connect via MS access and then generate the report via a third party tool...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 06:37:36

quote:
Originally posted by puppy

No i connect via MS access and then generate the report via a third party tool...

then please post in access related forums. This forum is for MS SQL Server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources