replacing a subquery with a join
-
Can anyone please tell me if it is (and if so how) possible to replace a query like this:
Select sum(CS_SHPCHG) As FreightCost From CS_Package where CS_PackingSlipNum in
(
Select distinct B.PackingSlipID from CustInvoiceTrans A
Inner Join CustPackingSlipTrans B ON A.InventTransID=B.InventTransID
And A.ItemID=B.ItemID And A.SalesID=B.SalesID
Where A.InvoiceID like '%Si00038%'
)
With a join of some sort. I'm trying to use this query in a dts package
with the InvoiceID provided as a parameter. I get an error saying i cant have parameters inside of a subquery so i need to convert this to something like join. The *Trans tables are detail tables and Package is a header table.
I came up with lame replacement like this:
SELECT Top 1 sum(A.CS_SHPCHG) From CS_PACKAGE A
INNER JOIN
CustPackingSlipJour B ON A.CS_PackingSlipNUM=B.PackingSlipID
Inner Join CustPackingSlipTrans C ON B.PackingSlipID=C.PackingSlipID
Inner Join CustInvoiceTrans D ON C.InventTransID=D.InventTransID
AND C.ItemID=D.ItemID
AND C.SalesID=D.SalesID
AND D.InvoiceID like '%SI00038%'
Group By C.InventTransID
But i'm not sure if it would be reliable 100% of the time, not too mentioned the fact that it looks like and most certainly is a hack.
Any help is appreciated. Thanks!
-
Try this:
Select sum(CS_SHPCHG) As FreightCost
From CS_Package p,
(
Select distinct B.PackingSlipID from CustInvoiceTrans A
Inner Join CustPackingSlipTrans B ON A.InventTransID=B.InventTransID
And A.ItemID=B.ItemID And A.SalesID=B.SalesID
Where A.InvoiceID like '%Si00038%'
) dp
where p.CS_PackingSlipNum = dp.PackingSlipID
-
Thanks for your help.
It works great in query analyser but... when i try to stick it into a lookup task on my dts package (which is what i need this for)
i get a weirdo,non-descriptive error that says
ADO ERROR: Syntax error or access violation.
-
DTS Global Variables as parameters do not work very well with SQL statements due too many limitations such as you have indicated. But Global Variables work fine with stored procedures.
Using the Northwind database:
create procedure dbo.Employee_Cnt_Customers
(@EmployeeIdinteger
)
as
set nocount on
select count(*) as CustomerCount
from dbo.Customers
whereCustomerId in
(select distinct CustomerId
from dbo.Orders
where EmployeeID = @EmployeeId
)
go
Then in the DTS Transform Data Task, have:
exec dbo.Employee_Cnt_Customers @EmployeeId = ?
SQL = Scarcely Qualifies as a Language
-
Alternatively, you can pre-select into a temp table and join to it:
Select distinct B.PackingSlipID
Into #PackingSlips
from CustInvoiceTrans A
Inner Join CustPackingSlipTrans B
ON (A.InventTransID=B.InventTransID And A.ItemID=B.ItemID And A.SalesID=B.SalesID)
Where A.InvoiceID like '%Si00038%'
Select sum(CS_SHPCHG) As FreightCost
From CS_Package
Inner Join #PackingSlips
ON (CS_Package.CS_PackingSlipNum = #PackingSlips.PackingSlipID
-
Thanks to all who gave their input.
I will use the strange join statement i came up with. Unfortunately creating a stored procedure is not an option at this point.
Thanks again.
martin
-
>>I will use the strange join statement i came up with.
I suspect you will end up double-counting and generating an incorrect SUM() value.
Look at your original sub-query - why did you have to use the DISTINCT keyword ? If the reason is cardinality and repeating values in CustInvoiceTrans, you will get the wrong result with your "strange" join query.
-
Crap, when i started responding saying that it works ok i realized that it will not work in all cases after all. It'd work ok in a situation where i have a single packing slip and a single invoice. however if i have multiple packing slips to an invoice i will only get the freight listed on the first packing slip (top 1). Man, this is really starting to tick me off.
-
I know very little about DTS, so this is just on a chance that the error is raised because of the DISTINCT keyword... quite probably it will fail, too, but it could be worth trying. Please, let me know whether I understood the structure of tables and their relations correctly, or what's wrong... maybe we'll yet be able to find some other way.
/*create testing environment*/
create table CS_Package(CS_PackingSlipNum int, CS_SHPCHG money)
create table CustPackingSlipTrans (InventTransID int, ItemID int, SalesID int, PackingSlipID int)
create table CustInvoiceTrans (InventTransID int, ItemID int, SalesID int, InvoiceID varchar(40))
insert into CS_Package (CS_PackingSlipNum, CS_SHPCHG) values (1, 9.45)
insert into CS_Package (CS_PackingSlipNum, CS_SHPCHG) values (2, 13.00)
insert into CS_Package (CS_PackingSlipNum, CS_SHPCHG) values (3, 1.33)
insert into CustPackingSlipTrans (InventTransID, ItemID, SalesID, PackingSlipID) values (91, 1, 1, 1)
insert into CustPackingSlipTrans (InventTransID, ItemID, SalesID, PackingSlipID) values (91, 2, 1, 1)
insert into CustPackingSlipTrans (InventTransID, ItemID, SalesID, PackingSlipID) values (92, 1, 2, 2)
insert into CustPackingSlipTrans (InventTransID, ItemID, SalesID, PackingSlipID) values (92, 2, 2, 2)
insert into CustPackingSlipTrans (InventTransID, ItemID, SalesID, PackingSlipID) values (92, 3, 2, 2)
insert into CustPackingSlipTrans (InventTransID, ItemID, SalesID, PackingSlipID) values (92, 4, 2, 2)
insert into CustPackingSlipTrans (InventTransID, ItemID, SalesID, PackingSlipID) values (93, 1, 3, 3)
insert into CustInvoiceTrans (InventTransID, ItemID, SalesID, InvoiceID) values (91,1,1,'ABC')
insert into CustInvoiceTrans (InventTransID, ItemID, SalesID, InvoiceID) values (91,2,1,'ABC')
insert into CustInvoiceTrans (InventTransID, ItemID, SalesID, InvoiceID) values (92,1,2,'CDE')
insert into CustInvoiceTrans (InventTransID, ItemID, SalesID, InvoiceID) values (92,2,2,'CDE')
insert into CustInvoiceTrans (InventTransID, ItemID, SalesID, InvoiceID) values (92,3,2,'CDE')
insert into CustInvoiceTrans (InventTransID, ItemID, SalesID, InvoiceID) values (92,4,2,'CDE')
insert into CustInvoiceTrans (InventTransID, ItemID, SalesID, InvoiceID) values (93,1,3,'ABC')
/*the actual SQL comes now*/
SELECT SUM(Q.freight)
FROM
(
SELECT CSP.CS_PackingSlipNum, MIN(CSP.CS_SHPCHG) as freight
FROM CustInvoiceTrans A
JOIN CustPackingSlipTrans B ON A.InventTransID=B.InventTransID
AND A.ItemID=B.ItemID AND A.SalesID=B.SalesID
JOIN CS_Package CSP ON CSP.CS_PackingSlipNum = B.PackingSlipID
WHERE A.InvoiceID LIKE '%ABC%'
GROUP BY CSP.CS_PackingSlipNum
) as Q
/*cleanup*/
drop table CS_Package
drop table CustPackingSlipTrans
drop table CustInvoiceTrans
Good luck, Vladan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply