Problem with Derived Table Query

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 2000 Forums

SQL Server Development (2000)

Problem with Derived Table Query

Author

Topic

allend2010
Starting Member

28 Posts

Posted - 2003-04-09 : 09:33:29

Hello:

When I try to run the query below with a derived table I keep getting the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have seen this error before with subqueries but I am not quite sure why I get it when I plug it in as a virtual table. I can run the derived table query succesfully as well which returns the distinct set of rows properly! Only when I plug it in as a derived table do I get the error. Also, I tried to no avail to make the main select statement more simple by removing case statements and grouping etc... Any help anybody can provide would be greatly appreceiated.

Thanks,
Allen D.

SELECT CASE WHEN DT.ShipDate IS NOT NULL THEN DT.ShipDate ELSE 'TOTALS:' END AS ShipDate,
SUM(DT.PieceCount) AS Packages,
SUM(DT.Weight) AS Weight,
SUM(DT.FreightCharge) AS FreightCharges,
SUM(CASE WHEN DT.ExtraCharges IS NULL THEN 0 ELSE DT.ExtraCharges END) AS ExtraCharges,
SUM(DT.Incentive) AS Incentives,
SUM(DT.NetCharge) AS NetCharges
FROM (
SELECT P.PkgShipDate AS ShipDate,
P.PkgTrackingNumber AS TrackingNumber,
P.PkgWeight AS Weight,
P.PkgPieces AS PieceCount,
P.PkgNetCharges AS NetCharge,
(
SELECT C.PkgChargeAmt
FROM Charges C
WHERE C.PkgChargeCode = '001' AND C.PkgTrackingNumber = P.PkgTrackingNumber
)
AS FreightCharge,
(
SELECT C.PkgChargeAmt
FROM Charges C
WHERE C.PkgChargeCode = '005' AND C.PkgTrackingNumber = P.PkgTrackingNumber
)
AS Incentive,
(
SELECT C.PkgChargeAmt
FROM Charges C
WHERE C.PkgChargeCode <> '0001' AND C.PkgChargeCode <> '005' AND C.PkgTrackingNumber = P.PkgTrackingNumber
)
AS ExtraCharges
FROM InvoiceDetails P
) DT
GROUP BY DT.ShipDate
WITH ROLLUP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-09 : 09:39:36

I would use regular joins for this query ...

if it doubles up records, you know your relationships aren't sound and you need to filter your subqueries more. You can also make those INNER JOINs into LEFT OUTER joins.

SELECT P.PkgShipDate AS ShipDate,
P.PkgTrackingNumber AS TrackingNumber,
P.PkgWeight AS Weight,
P.PkgPieces AS PieceCount,
P.PkgNetCharges AS NetCharge,
C.PkgChargeAmt AS FreightCharge,
D.PkgChargeAmt AS Incentive,
E.PkgChargeAmt AS ExtraCharges
FROM InvoiceDetails P
INNER JOIN Charges C
ON C.PkgChargeCode = '001' AND
C.PkgTrackingNumber = P.PkgTrackingNumber
INNER JOIN Charges D
ON D.PkgChargeCode = '005' AND D.PkgTrackingNumber = P.PkgTrackingNumber
INNER JOIN Charges E
ON E.PkgChargeCode <> '0001' AND E.PkgChargeCode <> '005' AND E.PkgTrackingNumber = P.PkgTrackingNumber

- Jeff

allend2010
Starting Member

28 Posts

Posted - 2003-04-09 : 09:55:10

Thank you for your assistance! I need to brush up on JOINS, your feedback worked. It didn't return all the information but at least I didn't get any errors and I have data to work with to get the rest of it working properly :)

Allen D.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources