SQL 2005 Server Query

I have a query which produces a dataset showing details of my open sales orders

It's using a DISTINCT statement in order to only show the header information of each sales order and not every sales order line item.   However, for this query I now need to show something which is only detailed at row level but I don't want to every row.    Is there a way to include column  t2.POtrgnum (which is the linked purchase order code) only once for each row?    In every case there will only be one row of each sales order which has this field populated.


SELECT DISTINCT T1.[DocNum], t1.numatcard, T1.[CardCode]'Customer No.', t4.firstname+' '+t4.lastname as 'Owner', T1.[CardName]'Customer Name', T1.[DocDate], T1.[DocDueDate]'Due Date', 
CASE

WHEN ((select sum(t2.quantity) from rdr1 t2 inner join ordr t20 on T20.DocEntry = T2.DocEntry where (t2.itemcode = 'cmt') and t20.docnum = t1.docnum) IS NULL) THEN (select sum(t2.quantity) from rdr1 t2 inner join ordr t20 on T20.DocEntry = T2.DocEntry where ((t2.SWW = 'PATTERN') OR (t2.SWW = 'PRINT-PRESS')) and t20.docnum = t1.docnum)

WHEN ((select sum(t2.quantity) from rdr1 t2 inner join ordr t20 on T20.DocEntry = T2.DocEntry where (t2.itemcode = 'cmt') and t20.docnum = t1.docnum) IS NOT NULL AND (select sum(t2.quantity) from rdr1 t2 inner join ordr t20 on T20.DocEntry = T2.DocEntry where ((t2.SWW = 'PATTERN') OR (t2.SWW = 'PRINT-PRESS')) and t20.docnum = t1.docnum) IS NOT NULL) THEN (select sum(t2.quantity) from rdr1 t2 inner join ordr t20 on T20.DocEntry = T2.DocEntry where (t2.itemcode = 'cmt') and t20.docnum = t1.docnum)+(select sum(t2.quantity) from rdr1 t2 inner join ordr t20 on T20.DocEntry = T2.DocEntry where ((t2.SWW = 'PATTERN') OR (t2.SWW = 'PRINT-PRESS')) and t20.docnum = t1.docnum) 

ELSE (select sum(t2.quantity) from rdr1 t2 inner join ordr t20 on T20.DocEntry = T2.DocEntry where (t2.itemcode = 'cmt') and t20.docnum = t1.docnum)
END'Qty',

t6.name'Design Status', t1.u_designdate, t7.name'Dye Print Status', t1.u_dyeprintsch, t5.name'Dye Press Status', t1.u_dyepressdate,t10.name'Laser Cutting Status',t1.U_lasercutdate, t8.name'Dye Sewing Status',t1.u_dyesewdate,  t30.[Name]'Factory'

FROM OCRD T0  INNER JOIN ORDR T1 ON T0.CardCode = T1.CardCode INNER JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OCRG T3 ON T0.GroupCode = T3.GroupCode INNER JOIN OHEM T4 ON T1.OwnerCode = T4.empID left JOIN [dbo].[@DYEPRESSSTAT]  T5 ON T1.U_DyePressStat = t5.code left JOIN [dbo].[@DESIGN_STATUS] T6 ON T1.U_DesignStat = t6.code left JOIN [dbo].[@DYEPRINTSTAT]  T7 ON T1.U_DyePrintStat = t7.code left JOIN [dbo].[@DYESEWSTAT]  T8 ON T1.U_DyeSewStat = t8.code left JOIN [dbo].[@LASER_CUT] T10 ON T1.U_LaserCutStat = t10.code INNER JOIN OITM T9 ON T2.ItemCode = T9.ItemCode left join [dbo].[@dyesewfact] t30 on t1.[U_Dyesewfact]=t30.code

WHERE T1.[DocStatus] ='O' and t9.qrygroup42='Y' ORDER BY t1.u_designdate ASC, T1.[U_DyePrintSch] asc, T1.[U_DyePressDate] asc, T1.[U_DyeSewDate] asc,  T1.[DocNum] ASC

Open in new window

JonYenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Is this for a SQL Server 2005 database?
0
PortletPaulfreelancerCommented:
using "select distinct" on a query with 11 joined tables is almost certainly not an efficient way to achieve what you need.

to make a long story short, instead of joining to a table that causes "unwanted repetition" in the result, join to a "derived table" that only has one row for each one row in the outer query.

e.g.
/* NOT to do */
select distinct *
from orders as o
inner join order_items as oi on o.id = oi.orderid


/*instead*/
select *
from orders
inner join (
   select *, row_number() over(partition by orderid order by somedate DESC as rn
  from order_items
  ) as derived on o.id = derived.orderid and derived.rn = 1

The 2 most useful tools for making these "derived tables" supply only the wanted rows are:

GROUP BY, and

ROW_NUMBER() -- as shown in above example


So.  >>"Is there a way to include column  t2.POtrgnum (which is the linked purchase order code) only once for each row?"

Yes, I would suggest using ROW_NUMBER()
BUT

I have no idea which tables do what, and you may need more than one derived table. So I cannot be more specific.

Also the "Hail Mary Distinct" in this article, also the follow on references such as:

DISTINCT and GROUP BY... and why does it not work for my query?
Why I Hate DISTINCT
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JonYenAuthor Commented:
yes - it's SAP Business One verson 8.82 which is running on SQL Server 2005
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Just want to try to understand why adding t2.POtrgnum  to the SELECT list won't solve your issue?
0
JonYenAuthor Commented:
what seems to happen when I do this is I get a duplicate row everytime there is a value in the t2.potrgnum field
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post a sample result?
0
PortletPaulfreelancerCommented:
"select distinct" and " I get a duplicate row"

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique.
Please understand that the DEFINITION of "select distinct" is that EVERY ROW is UNIQUE
and it is IMPOSSIBLE for any ROW to be a "duplicate"

i.e. distinct is measured by THE WHOLE ROW, not one or some of the columns, but ALL the columns


do please refer to my article

Select Distinct is returning duplicates ...
0
JonYenAuthor Commented:
sample data attached.   it is only producing one row for the records where there is no PO Target but in  cases where there is a PO Target number it produces 2 rows for each record
0
PortletPaulfreelancerCommented:
there is no attachment
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.