MS SQL 2005 :: Delphi 7

Dear Experts,

Sometimes you need to select rows from a table which should be retrieved distinctively but fields in the select might have different values. How can you force a group by or a distinctive select on a fields.

Lets say this is what my returning rows look like:

customer#|   Date    |    PaidCash    |    PaidCreditCard     |     PaidCheque
---------------------------------------------------------------------------------------------------
601222      1/1/08           1                        
601222      1/1/08                                       1    
601222      1/1/08                                                                          1

I know this example is not very good but what I am trying to explain is that a person might make 3 purchases on the same day and each time use a different paymethod.
Now I would like to group by customer# and just return 1 line showing all the paymethods used....

My current query uses CASE WHEN statement to select the paymethod fields dynamically but understandably it would return a new row for each paymethod. What is the work around for this or how can I change my approach....

My actual scenario deals with part types (for vehicles).
I have a ProductionOrder# which will be the same for several part types. Part Types can be: "Flairs", "Bumpers" etc.... So I would like to return one row (distinct on Productionorder#) for each order and then simply indicating which part types are included on the order by simply flagging the field = "1" or "0". Fields for the part types are selected dynamically. Please see my actual query attached....

SELECT DISTINCT(P.POrderNumber), P.ProductionDate, COALESCE((SELECT A.CheckPointCode FROM tblAfterjig A WHERE A.POrderNumber = P.POrderNumber AND A.Deleted = 0 AND A.CheckPointCode = '0945'), 'Prejig')AS CheckPointCode, COALESCE((SELECT TOP 1 C.Description FROM tblPartTypes PT LEFT OUTER JOIN tblParts PART ON PART.PartTypeID = PT.PartTypeID LEFT OUTER JOIN tblColors C ON PART.ColorID = C.ColorID WHERE PT.IsBodyColor = 1 AND PART.PartNumber = P.ItemNumber), '***Part Number Missing Color***') AS Color ,CASE WHEN (PT.PartTypeNumber) = 'Flares' THEN '1'ELSE '' END AS '  Flares' ,CASE WHEN (PT.PartTypeNumber) = 'Bumper' THEN '1'ELSE '' END AS '  Bumper' ,CASE WHEN (PT.PartTypeNumber) = 'Side Gate' THEN '1'ELSE '' END AS '  Side_Gate' ,CASE WHEN (PT.PartTypeNumber) = 'Door Moulding' THEN '1'ELSE '' END AS '  Door_Moulding' ,CASE WHEN (PT.PartTypeNumber) = 'Tail Gate Garnish' THEN '1'ELSE '' END AS '  Tail_Gate_Garnish' FROM tblPrejig P LEFT OUTER JOIN tblParts PA ON P.ItemNumber = PA.PartNumber LEFT OUTER JOIN tblPartTypes PT ON PA.PartTypeID = PT.PartTypeID WHERE P.Deleted = 0 AND P.CalledOff = 0 AND P.ModelCode IN (SELECT M1.ModelCode FROM tblModels M1 WHERE M1.Deleted = 0) ORDER BY P.POrderNumber, P.ProductionDate ASC

Open in new window

Marius0188Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please try this:
SELECT POrderNumber, ProductionDate, CheckPointCode, Color 
, SUM([  Flares]) [  Flares]
, SUM([  Bumper]) [  Bumper] 
, SUM([  Side_Gate]) [  Side_Gate]
, SUM([  Door_Moulding]) [  Door_Moulding]
, SUM([  Tail_Gate_Garnish]) [  Tail_Gate_Garnish]
FROM (
SELECT P.POrderNumber
, P.ProductionDate
, COALESCE((SELECT A.CheckPointCode FROM tblAfterjig A WHERE A.POrderNumber = P.POrderNumber AND A.Deleted = 0 AND A.CheckPointCode = '0945'), 'Prejig') AS CheckPointCode
, COALESCE((SELECT TOP 1 C.Description FROM tblPartTypes PT LEFT OUTER JOIN tblParts PART ON PART.PartTypeID = PT.PartTypeID LEFT OUTER JOIN tblColors C ON PART.ColorID = C.ColorID WHERE PT.IsBodyColor = 1 AND PART.PartNumber = P.ItemNumber), '***Part Number Missing Color***') AS Color 
,CASE WHEN (PT.PartTypeNumber) = 'Flares' THEN 1 END AS [  Flares]
,CASE WHEN (PT.PartTypeNumber) = 'Bumper' THEN 1 END AS [  Bumper] 
,CASE WHEN (PT.PartTypeNumber) = 'Side Gate' THEN 1 END AS [  Side_Gate]
,CASE WHEN (PT.PartTypeNumber) = 'Door Moulding' THEN 1 END AS [  Door_Moulding]
,CASE WHEN (PT.PartTypeNumber) = 'Tail Gate Garnish' THEN 1 END AS [  Tail_Gate_Garnish] 
FROM tblPrejig P 
LEFT OUTER JOIN tblParts PA 
  ON P.ItemNumber = PA.PartNumber 
LEFT OUTER JOIN tblPartTypes PT 
  ON PA.PartTypeID = PT.PartTypeID 
WHERE P.Deleted = 0 
  AND P.CalledOff = 0 
  AND P.ModelCode IN (SELECT M1.ModelCode FROM tblModels M1 WHERE M1.Deleted = 0) 
) sq
GROUP BY P.POrderNumber, P.ProductionDate, Color
ORDER BY P.POrderNumber, P.ProductionDate ASC

Open in new window

0
 
Marius0188Author Commented:
Maybe the attached screenshot will explain little better.
Triton.jpg
0
 
Marius0188Author Commented:
Hi there,

Thanks for the comment.
Your query returns the following error when executing:

"Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.POrderNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.ProductionDate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.POrderNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.ProductionDate" could not be bound."
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Daniel WilsonCommented:
In AngelIII's query, the position of the GROUP BY and the alias used are not getting along.  Try this:

SELECT POrderNumber, ProductionDate, CheckPointCode, Color 
, SUM([  Flares]) [  Flares]
, SUM([  Bumper]) [  Bumper] 
, SUM([  Side_Gate]) [  Side_Gate]
, SUM([  Door_Moulding]) [  Door_Moulding]
, SUM([  Tail_Gate_Garnish]) [  Tail_Gate_Garnish]
FROM (
SELECT P.POrderNumber
, P.ProductionDate
, COALESCE((SELECT A.CheckPointCode FROM tblAfterjig A WHERE A.POrderNumber = P.POrderNumber AND A.Deleted = 0 AND A.CheckPointCode = '0945'), 'Prejig') AS CheckPointCode
, COALESCE((SELECT TOP 1 C.Description FROM tblPartTypes PT LEFT OUTER JOIN tblParts PART ON PART.PartTypeID = PT.PartTypeID LEFT OUTER JOIN tblColors C ON PART.ColorID = C.ColorID WHERE PT.IsBodyColor = 1 AND PART.PartNumber = P.ItemNumber), '***Part Number Missing Color***') AS Color 
,CASE WHEN (PT.PartTypeNumber) = 'Flares' THEN 1 END AS [  Flares]
,CASE WHEN (PT.PartTypeNumber) = 'Bumper' THEN 1 END AS [  Bumper] 
,CASE WHEN (PT.PartTypeNumber) = 'Side Gate' THEN 1 END AS [  Side_Gate]
,CASE WHEN (PT.PartTypeNumber) = 'Door Moulding' THEN 1 END AS [  Door_Moulding]
,CASE WHEN (PT.PartTypeNumber) = 'Tail Gate Garnish' THEN 1 END AS [  Tail_Gate_Garnish] 
FROM tblPrejig P 
LEFT OUTER JOIN tblParts PA 
  ON P.ItemNumber = PA.PartNumber 
LEFT OUTER JOIN tblPartTypes PT 
  ON PA.PartTypeID = PT.PartTypeID 
WHERE P.Deleted = 0 
  AND P.CalledOff = 0 
  AND P.ModelCode IN (SELECT M1.ModelCode FROM tblModels M1 WHERE M1.Deleted = 0) 
) sq
GROUP BY sq.POrderNumber, sq.ProductionDate, Color
ORDER BY sq.POrderNumber, sq.ProductionDate ASC

Open in new window

0
 
Daniel WilsonCommented:
Or if that doesn't work, try:

SELECT POrderNumber, ProductionDate, CheckPointCode, Color 
, SUM([  Flares]) [  Flares]
, SUM([  Bumper]) [  Bumper] 
, SUM([  Side_Gate]) [  Side_Gate]
, SUM([  Door_Moulding]) [  Door_Moulding]
, SUM([  Tail_Gate_Garnish]) [  Tail_Gate_Garnish]
FROM (
SELECT P.POrderNumber
, P.ProductionDate
, COALESCE((SELECT A.CheckPointCode FROM tblAfterjig A WHERE A.POrderNumber = P.POrderNumber AND A.Deleted = 0 AND A.CheckPointCode = '0945'), 'Prejig') AS CheckPointCode
, COALESCE((SELECT TOP 1 C.Description FROM tblPartTypes PT LEFT OUTER JOIN tblParts PART ON PART.PartTypeID = PT.PartTypeID LEFT OUTER JOIN tblColors C ON PART.ColorID = C.ColorID WHERE PT.IsBodyColor = 1 AND PART.PartNumber = P.ItemNumber), '***Part Number Missing Color***') AS Color 
,CASE WHEN (PT.PartTypeNumber) = 'Flares' THEN 1 END AS [  Flares]
,CASE WHEN (PT.PartTypeNumber) = 'Bumper' THEN 1 END AS [  Bumper] 
,CASE WHEN (PT.PartTypeNumber) = 'Side Gate' THEN 1 END AS [  Side_Gate]
,CASE WHEN (PT.PartTypeNumber) = 'Door Moulding' THEN 1 END AS [  Door_Moulding]
,CASE WHEN (PT.PartTypeNumber) = 'Tail Gate Garnish' THEN 1 END AS [  Tail_Gate_Garnish] 
FROM tblPrejig P 
LEFT OUTER JOIN tblParts PA 
  ON P.ItemNumber = PA.PartNumber 
LEFT OUTER JOIN tblPartTypes PT 
  ON PA.PartTypeID = PT.PartTypeID 
WHERE P.Deleted = 0 
  AND P.CalledOff = 0 
  AND P.ModelCode IN (SELECT M1.ModelCode FROM tblModels M1 WHERE M1.Deleted = 0) 
 
  GROUP BY P.POrderNumber, P.ProductionDate, Color
  ORDER BY P.POrderNumber, P.ProductionDate ASC
) sq

Open in new window

0
 
Daniel WilsonCommented:
btw, the solution is AngelIII's.  I have just (I hope) assisted.
0
 
Marius0188Author Commented:
Still the following error:

"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

0
 
Daniel WilsonConnect With a Mentor Commented:
OK, let's throw in a TOP 100 PERCENT

SELECT POrderNumber, ProductionDate, CheckPointCode, Color 
, SUM([  Flares]) [  Flares]
, SUM([  Bumper]) [  Bumper] 
, SUM([  Side_Gate]) [  Side_Gate]
, SUM([  Door_Moulding]) [  Door_Moulding]
, SUM([  Tail_Gate_Garnish]) [  Tail_Gate_Garnish]
FROM (
SELECT TOP 100 PERCENT P.POrderNumber
, P.ProductionDate
, COALESCE((SELECT A.CheckPointCode FROM tblAfterjig A WHERE A.POrderNumber = P.POrderNumber AND A.Deleted = 0 AND A.CheckPointCode = '0945'), 'Prejig') AS CheckPointCode
, COALESCE((SELECT TOP 1 C.Description FROM tblPartTypes PT LEFT OUTER JOIN tblParts PART ON PART.PartTypeID = PT.PartTypeID LEFT OUTER JOIN tblColors C ON PART.ColorID = C.ColorID WHERE PT.IsBodyColor = 1 AND PART.PartNumber = P.ItemNumber), '***Part Number Missing Color***') AS Color 
,CASE WHEN (PT.PartTypeNumber) = 'Flares' THEN 1 END AS [  Flares]
,CASE WHEN (PT.PartTypeNumber) = 'Bumper' THEN 1 END AS [  Bumper] 
,CASE WHEN (PT.PartTypeNumber) = 'Side Gate' THEN 1 END AS [  Side_Gate]
,CASE WHEN (PT.PartTypeNumber) = 'Door Moulding' THEN 1 END AS [  Door_Moulding]
,CASE WHEN (PT.PartTypeNumber) = 'Tail Gate Garnish' THEN 1 END AS [  Tail_Gate_Garnish] 
FROM tblPrejig P 
LEFT OUTER JOIN tblParts PA 
  ON P.ItemNumber = PA.PartNumber 
LEFT OUTER JOIN tblPartTypes PT 
  ON PA.PartTypeID = PT.PartTypeID 
WHERE P.Deleted = 0 
  AND P.CalledOff = 0 
  AND P.ModelCode IN (SELECT M1.ModelCode FROM tblModels M1 WHERE M1.Deleted = 0) 
 
  GROUP BY P.POrderNumber, P.ProductionDate, Color
  ORDER BY P.POrderNumber, P.ProductionDate ASC
) sq

Open in new window

0
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.

All Courses

From novice to tech pro — start learning today.