Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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

0
Marius0188
Asked:
Marius0188
  • 4
  • 3
2 Solutions
 
Marius0188Author Commented:
Maybe the attached screenshot will explain little better.
Triton.jpg
0
 
Guy Hengel [angelIII / a3]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:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 WilsonCommented:
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now