Subquery - Can't sum - 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

General SQL Server Forums

New to SQL Server Programming

Subquery - Can't sum

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2007-10-16 : 13:27:48

Greetings, I am working on a SQL View - I previously posted in this forum and thought I had fixed my problem. Turns out that my view no longer works because I have multiple records where I want only one record.

I am working with 4 tables - here are the tables and my sample data:

USE YOURDATABASE
GO
CREATE TABLE ZTECSOP60100
(
SOPNUMBE char(21) NOT NULL,
PONUMBER char(17) NOT NULL,
ORD int NOT NULL,
RCPTCOST numeric (19,5) NOT NULL,
QTYONPO numeric (19,5) NOT NULL,
QTYRECVD numeric (19,5) NOT NULL
)

INSERT INTO ZTECSOP60100
values ('ORD001', 'PO2074', '16384', '0.00000', '0.00000', '1.00000')
INSERT INTO ZTECSOP60100
values ('ORD1003', 'PO2079', '16384', '425.00000', '0.00000', '5.00000')

USE YOURDATABASE
CREATE TABLE ZTECPOP10110
(PONUMBER char(17) NOT NULL,
ITEMNMBR char (31) NOT NULL,
ITEMDESC char (101) NOT NULL,
ORD int NOT NULL)
GO

INSERT INTO ZTECPOP10110
values ('PO2074', 'SERVICE1', 'Service item', '16384')
INSERT INTO ZTECPOP10110
values ('PO2079', 'SERVICE1', 'Service item', '16384')

USE YOURDATABASE
CREATE TABLE ZTECSOP30200
(
SOPNUMBE char(21) NOT NULL,
ORIGNUMBE char (21) NOT NULL,
SOPTYPE smallint NOT NULL,
)
GO

INSERT INTO ZTECSOP30200
values ('ORD001', '', '2')
INSERT INTO ZTECSOP30200
values ('INV001', 'ORD001', '3')
INSERT INTO ZTECSOP30200
values ('ORD1003', '', '2')
INSERT INTO ZTECSOP30200
values ('INV010', 'ORD1003', '3')

USE YOURDATABASE
CREATE TABLE ZTEC30310
(
PONUMBER char (17) NOT NULL,
UNITCOST numeric (19,5) NOT NULL,
TRXSORCE char (13) NOT NULL,
UMQTYINB numeric (19,5) NOT NULL,
EXTDCOST numeric (19,5) NOT NULL
)
GO

USE YOURDATABASE
INSERT INTO ZTEC30310
values ('PO2074', '125', 'RECVG00000001', '1', '125')
USE RELIA
INSERT INTO ZTEC30310
values ('PO2074', '125', 'POIVC00000001', '1', '125')
INSERT INTO ZTEC30310
values ('PO2079', '100', 'POIVC00000005', '1', '300')
INSERT INTO ZTEC30310
values ('PO2079', '75', 'RECVG00000007', '1', '375')
INSERT INTO ZTEC30310
values ('PO2079', '85', 'POIVC00000004', '1', '170')

Here is my VIEW -

CREATE view [dbo].[_tec_SOP_POP_link] as
select ZTECSOP60100.SOPNUMBE as SOP_ORDER_NUMBER,
ZTECSOP60100.PONUMBER as PONUMBER,
ZTECSOP60100.QTYRECVD as QtyReceivedSoFar,
ZTECSOP30200.sopnumbe as SOP_INV_NUMBER,
ZTECPOP10110.ITEMNMBR as ITEM_NUMBER,
ZTECPOP10110.ITEMDESC as itemdescription,
CASE WHEN ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.TRXSORCE like 'RECVG%'
then ZTEC30310.UNITCOST else '0' end as ReceivedCost,
CASE WHEN ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.TRXSORCE like 'POIVC%'
then ZTEC30310.UNITCOST else '0' end as VENDORINVOICE_Cost,
Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND
ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.EXTDCOST/ZTEC30310.UNITCOST else 0 end) as QTYINVOICEDMATCHED,
Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND
ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.extdcost else 0 end) as TOTALDOLLARSINVOICEDMATCHED
from ZTECsop60100 left outer join ZTECsop30200 on ZTECsop60100.sopnumbe = ZTECsop30200.ORIGNUMBE AND ZTECsop30200.soptype = 3
left outer join ZTECPOP10110 on ZTECSOP60100.PONUMBER = ZTECPOP10110.PONUMBER
left outer join ZTEC30310 on ZTECsop60100.ponumber = ZTEC30310.ponumber
where ZTECSOP60100.ORD = ZTECPOP10110.ORD
GROUP BY ZTECSOP60100.SOPNUMBE, ZTECSOP60100.QTYRECVD, ZTECSOP30200.sopnumbe, ZTECSOP60100.PONUMBER, ZTECSOP60100.QTYONPO,
ZTEC30310.TRXSORCE, ZTECPOP10110.ITEMNMBR, ZTECPOP10110.ITEMDESC, ZTEC30310.UNITCOST, ZTEC30310.umqtyinb, ZTEC30310.ponumber
GO

This View returns 5 Records when I want only 2 records.

I need 1 record for each SOP_ORDER_NUMBER AND PONUMBER combination.

Record 1
SOP_ORDER_NUMBER = 'ORD001'
PONUMBER = 'PO2074'
QtyReceivedSofar = '1'
SOP_INV_NUMBER = 'INV001'
ITEM_NUMBER = 'SERVICE1'
itemdescription = 'Service item'
ReceivedCost = '125'
VENDORINVOICED_COST = '125'
QTYINVOICEDMATCHED = '1'
TOTALDOLLARSINVOICEDMATCHED = '125'

Record 2
SOP_ORDER_NUMBER = 'ORD1003'
PONUMBER = 'PO2079'
QtyReceivedSofar = '5'
SOP_INV_NUMBER = 'INV010'
ITEM_NUMBER = 'SERVICE1'
itemdescription = 'Service item'
ReceivedCost = '75'
VENDORINVOICED_COST = '94' (calculated by taking TOTALDOLLARSINVOICEDMATCHED/QTYINVOICEDMATCHED)
QTYINVOICEDMATCHED = '5'
TOTALDOLLARSINVOICEDMATCHED = '470'

The unanticpated dilemna was multiple records in ZTEC30310 for TRXSORCE of POIVC or RECVG - I basically need to sum the records and return one record.

I attempted to fix by adding sum within my case statements but I get the SQL error message
'Cannot perform an aggregate function on an expression containing an aggregate or a subquery'

Any assistance would be appreciated......many thanks in advance...

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-10-17 : 02:42:51

hi,
you can modify ur view as..
CREATE view [dbo].[_tec_SOP_POP_link] as
select ZTECSOP60100.SOPNUMBE as SOP_ORDER_NUMBER,
ZTECSOP60100.PONUMBER as PONUMBER,
ZTECSOP60100.QTYRECVD as QtyReceivedSoFar,
ZTECSOP30200.sopnumbe as SOP_INV_NUMBER,
ZTECPOP10110.ITEMNMBR as ITEM_NUMBER,
ZTECPOP10110.ITEMDESC as itemdescription,
sum(CASE WHEN ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.TRXSORCE like 'RECVG%'
then ZTEC30310.UNITCOST else '0' end) as ReceivedCost,
Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND
ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.extdcost else 0 end) / Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND
ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.EXTDCOST/ZTEC30310.UNITCOST else 0 end) as VENDORINVOICED_COST,
Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND
ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.EXTDCOST/ZTEC30310.UNITCOST else 0 end) as QTYINVOICEDMATCHED,
Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND
ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.extdcost else 0 end) as TOTALDOLLARSINVOICEDMATCHED
from ZTECsop60100 left outer join ZTECsop30200 on ZTECsop60100.sopnumbe = ZTECsop30200.ORIGNUMBE AND ZTECsop30200.soptype = 3
left outer join ZTECPOP10110 on ZTECSOP60100.PONUMBER = ZTECPOP10110.PONUMBER
left outer join ZTEC30310 on ZTECsop60100.ponumber = ZTEC30310.ponumber
where ZTECSOP60100.ORD = ZTECPOP10110.ORD
GROUP BY
ZTECSOP60100.SOPNUMBE , ZTECSOP60100.PONUMBER , ZTECSOP60100.QTYRECVD , ZTECSOP30200.sopnumbe , ZTECPOP10110.ITEMNMBR , ZTECPOP10110.ITEMDESC
GO

btamulis
Yak Posting Veteran

64 Posts

Posted - 2007-10-17 : 04:51:34

Fantastic - works great. As an interim solution I had created a view to summarize this view - which worked (I did not realize a view could reference another view).

I am studying your changes - looks like you modified my case statements and my grouping -

absolutely brilliant..........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources