December 2, 2016 at 7:44 am
Good day friends, i have the query below:
There are two sub queries with joins, the first sub query requires a where clause to filter the results. the issue is a column is required in the second sub query which has an alias to it for referencing, how do i use the where clause in the first subquery to filter the data while also making sure the column referening the second sub query is returned. see script below:
SELECT StationName,AiredDateTime,AiredISCI,OrderNumber,Line,Length,FlexOrderRate AS FlexOrderRate,InvoiceNumber,CONVERT(varchar(20), InvoiceDate, 1) as InvoiceDate,AgencyRate,StationAddress,StationCityZip,
Product,BillingCycle,AdvertiserName,AgencyName,SalesPerson,Buyer,AgencyAddress,AgencyAddress2,AgencyStateCity,StationID
FROM (
select s.Name AS StationName,
ios.AiredDateTime,
ios.AiredISCI,
oh.id AS OrderNumber,
od.id AS Line,
cd.Length,
cd.FlexOrderRate,
ih.id AS InvoiceNumber,
ih.invoicedate AS InvoiceDate,
convert(int,od.AgencyRate) as AgencyRate,
'Westwood One, Inc' AS StationAddress,
'3544 Momentum Place
Chicago, IL 60689' AS StationCityZip,
/*sa.Address AS StationAddress,
ISNULL(sa.City,'')+', '+ISNULL(sa.state,'')+' '+ISNULL(sa.Zipcode,'') AS StationCityZip,*/
oh.ProductDescription AS Product,
CASE WHEN oh.BillingCycle='F' THEN 'End OF Flight'
WHEN oh.BillingCycle='M' THEN 'Monthly'
WHEN oh.BillingCycle='W' THEN 'Weekly'
ELSE NULL END AS BillingCycle,
c.name AS AdvertiserName,
cag.Name AS AgencyName,
osu.SalesPerson AS SalesPerson,---(column referencing the second sub query below )
cc.FirstName+' '+cc.LastName AS Buyer,
/*ca.Address1 AS AgencyAddress,
ISNULL(ca.Address2,'') AS AgencyAddress2,
ISNULL(ca.City,'')+', '+ISNULL(ast.Abrv,'')+' '+ISNULL(ca.Zip,'') AS AgencyStateCity,*/
'DIRECT RESULTS ' AS AgencyAddress,
'931 VILLAGE BLVD #905-507' AS AgencyAddress2,
'WEST PALM BEACH, FL 33409' AS AgencyStateCity,
s.ID AS StationID
from dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID=ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID=os.ID
INNER JOIN dbo.Inventory inv ON inv.id=ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id=inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id=cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id=os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id=odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id=od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID=s.ID
INNER JOIN dbo.Company c ON c.id=oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id=oh.AgencyID
Left JOIN dbo.CompanyAddress cc ON cc.id=oh.BuyerCompanyContactID--(the where clause should be here i.e(where active =1))
LEFT JOIN (SELECT usr.FirstName+' '+usr.LastName AS SalesPerson,os.orderHeaderID,MAX(os.id) AS orderSalesID
FROM dbo.OrderSales os
INNER JOIN dbo.[User] usr ON os.UserID = usr.ID
WHERE IsPrimary=1
GROUP BY usr.FirstName+' '+usr.LastName ,os.orderHeaderID
)osu ON osu.orderHeaderID=oh.ID
LEFT JOIN dbo.CompanyAddress ca ON ca.CompanyID=oh.AgencyID
LEFT JOIN dbo.AddressState ast ON ast.id=ca.AddressStateID
) AS P
WHERE InvoiceNumber=@InvoiceNumber AND StationName = @StationName
--AND (@StationName = 'ALL' OR StationName = @StationName)
AND AiredDateTime IS NOT NULL
order by StationName, AiredDateTime DESC
I learn from the footprints of giants......
December 2, 2016 at 7:50 am
For readability, please use IFCode formatting in future. Here is that code again:
SELECT
StationName
, AiredDateTime
, AiredISCI
, OrderNumber
, Line
, Length
, FlexOrderRate = FlexOrderRate
, InvoiceNumber
, InvoiceDate = CONVERT(VARCHAR(20), InvoiceDate, 1)
, P.AgencyRate
, P.StationAddress
, P.StationCityZip
, Product
, P.BillingCycle
, AdvertiserName
, AgencyName
, P.SalesPerson
, P.Buyer
, P.AgencyAddress
, P.AgencyAddress2
, P.AgencyStateCity
, StationID
FROM
(
SELECT
StationName = s.Name
, ios.AiredDateTime
, ios.AiredISCI
, OrderNumber = oh.id
, Line = od.id
, cd.Length
, cd.FlexOrderRate
, InvoiceNumber = ih.id
, InvoiceDate = ih.invoicedate
, AgencyRate = CONVERT(INT, od.AgencyRate)
, StationAddress = 'Westwood One, Inc'
, StationCityZip = '3544 Momentum Place
Chicago, IL 60689'
/*sa.Address AS StationAddress,
ISNULL(sa.City,'')+', '+ISNULL(sa.state,'')+' '+ISNULL(sa.Zipcode,'') AS StationCityZip,*/
, Product = oh.ProductDescription
, BillingCycle = CASE
WHEN oh.BillingCycle = 'F' THEN
'End OF Flight'
WHEN oh.BillingCycle = 'M' THEN
'Monthly'
WHEN oh.BillingCycle = 'W' THEN
'Weekly'
ELSE
NULL
END
, AdvertiserName = c.name
, AgencyName = cag.Name
, SalesPerson = osu.SalesPerson ---(column referencing the second sub query below )
, Buyer = cc.FirstName + ' ' + cc.LastName
/*ca.Address1 AS AgencyAddress,
ISNULL(ca.Address2,'') AS AgencyAddress2,
ISNULL(ca.City,'')+', '+ISNULL(ast.Abrv,'')+' '+ISNULL(ca.Zip,'') AS AgencyStateCity,*/
, AgencyAddress = 'DIRECT RESULTS '
, AgencyAddress2 = '931 VILLAGE BLVD #905-507'
, AgencyStateCity = 'WEST PALM BEACH, FL 33409'
, StationID = s.ID
FROM
dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID = ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID = os.ID
INNER JOIN dbo.Inventory inv ON inv.id = ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id = inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id = cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id = os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id = odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id = od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID = s.ID
INNER JOIN dbo.Company c ON c.id = oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id = oh.AgencyID
LEFT JOIN dbo.CompanyAddress cc ON cc.id = oh.BuyerCompanyContactID --(the where clause should be here i.e(where active =1))
LEFT JOIN
(
SELECT
SalesPerson = usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
, orderSalesID = MAX(os .id)
FROM
dbo.OrderSales os
INNER JOIN dbo.[User] usr ON os.UserID = usr.ID
WHERE IsPrimary = 1
GROUP BY
usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
) osu ON osu.orderHeaderID = oh.ID
LEFT JOIN dbo.CompanyAddress ca ON ca.CompanyID = oh.AgencyID
LEFT JOIN dbo.AddressState ast ON ast.id = ca.AddressStateID
) P
WHERE
InvoiceNumber = @InvoiceNumber AND
StationName = @StationName
--AND (@StationName = 'ALL' OR StationName = @StationName)
AND
AiredDateTime IS NOT NULL
ORDER BY
StationName
, AiredDateTime DESC;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2016 at 8:02 am
Kudos to Phil for the formatting. Now...what is the question??
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2016 at 8:10 am
thank you Phill for formatting.
i want to apply a where condition to a column in the inventory table -dbo.Inventory inv like this:
where inv.active=1
without causing conflicts for the columns in sub query 1 that depends on the 2nd sub query
I learn from the footprints of giants......
December 2, 2016 at 8:16 am
JALLY (12/2/2016)
thank you Phill for formatting.i want to apply a where condition to a column in the inventory table -dbo.Inventory inv like this:
where inv.active=1
without causing conflicts for the columns in sub query 1 that depends on the 2nd sub query
What do you mean by not causing conflicts? Why can't you simply add a where clause?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2016 at 8:29 am
if i use the where clause to filter return columns associated with inventory inv table to return inv.active = 1, it throws an error if i use it this way (where inv.active=1)
i.e
............FROM dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID = ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID = os.ID
INNER JOIN dbo.Inventory inv ON inv.id = ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id = inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id = cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id = os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id = odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id = od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID = s.ID
INNER JOIN dbo.Company c ON c.id = oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id = oh.AgencyID
LEFT JOIN dbo.CompanyAddress cc ON cc.id = oh.BuyerCompanyContactID
WHERE inv.active=1
............................
I learn from the footprints of giants......
December 2, 2016 at 9:27 am
JALLY (12/2/2016)
if i use the where clause to filter return columns associated with inventory inv table to return inv.active = 1, it throws an error if i use it this way (where inv.active=1)i.e
............FROM dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID = ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID = os.ID
INNER JOIN dbo.Inventory inv ON inv.id = ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id = inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id = cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id = os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id = odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id = od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID = s.ID
INNER JOIN dbo.Company c ON c.id = oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id = oh.AgencyID
LEFT JOIN dbo.CompanyAddress cc ON cc.id = oh.BuyerCompanyContactID
WHERE inv.active=1
............................
Perhaps you could share some details. Saying it throws an error does not provide any details. Sharing the error message (and at the very least the code you tried) would help. Without details of the problem there is nothing anybody can do. We are guessing based vague info.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2016 at 9:37 am
You can't have a WHERE clause in the middle of your JOIN clause. Just move your WHERE clause after ALL of the JOIN clauses.
FROM
dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID = ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID = os.ID
INNER JOIN dbo.Inventory inv ON inv.id = ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id = inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id = cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id = os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id = odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id = od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID = s.ID
INNER JOIN dbo.Company c ON c.id = oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id = oh.AgencyID
LEFT JOIN dbo.CompanyAddress cc ON cc.id = oh.BuyerCompanyContactID --(the where clause should be here i.e(where active =1))
/* This is the middle of your JOIN clause. You can't have a WHERE clause here. */
LEFT JOIN
(
SELECT
SalesPerson = usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
, orderSalesID = MAX(os .id)
FROM
dbo.OrderSales os
INNER JOIN dbo.[User] usr ON os.UserID = usr.ID
WHERE IsPrimary = 1
GROUP BY
usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
) osu ON osu.orderHeaderID = oh.ID
LEFT JOIN dbo.CompanyAddress ca ON ca.CompanyID = oh.AgencyID
LEFT JOIN dbo.AddressState ast ON ast.id = ca.AddressStateID
/* WHERE clause belongs after ALL of the JOIN clauses */
WHERE inv.active = 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 2, 2016 at 9:54 am
drew.allen (12/2/2016)
You can't have a WHERE clause in the middle of your JOIN clause. Just move your WHERE clause after ALL of the JOIN clauses.
FROM
dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID = ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID = os.ID
INNER JOIN dbo.Inventory inv ON inv.id = ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id = inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id = cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id = os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id = odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id = od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID = s.ID
INNER JOIN dbo.Company c ON c.id = oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id = oh.AgencyID
LEFT JOIN dbo.CompanyAddress cc ON cc.id = oh.BuyerCompanyContactID --(the where clause should be here i.e(where active =1))
/* This is the middle of your JOIN clause. You can't have a WHERE clause here. */
LEFT JOIN
(
SELECT
SalesPerson = usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
, orderSalesID = MAX(os .id)
FROM
dbo.OrderSales os
INNER JOIN dbo.[User] usr ON os.UserID = usr.ID
WHERE IsPrimary = 1
GROUP BY
usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
) osu ON osu.orderHeaderID = oh.ID
LEFT JOIN dbo.CompanyAddress ca ON ca.CompanyID = oh.AgencyID
LEFT JOIN dbo.AddressState ast ON ast.id = ca.AddressStateID
/* WHERE clause belongs after ALL of the JOIN clauses */
WHERE inv.active = 1
Drew
Thank you for your response drew:
Your solution above will create a conflict between columns that rely on the the sub queries for their source of data and the multi part identifier 'inv.active' will not be bound to the inventory table because it is outside of a sub query: for example :osu.SalesPerson is from the following subquery part identifier. if i add the where clause at the end of the script. the where clause will not be able to bind on to the inventory table due to the 2nd sub query bound by osu following it.
my question is , how can i add the where clause in the script for it to bind with the inventory table in the first sub query.
eg
SELECT
StationName
, AiredDateTime
, AiredISCI
, OrderNumber
, Line
, Length
, FlexOrderRate = FlexOrderRate
, InvoiceNumber
, InvoiceDate = CONVERT(VARCHAR(20), InvoiceDate, 1)
, P.AgencyRate
, P.StationAddress
, P.StationCityZip
, Product
, P.BillingCycle
, AdvertiserName
, AgencyName
, P.SalesPerson
, P.Buyer
, P.AgencyAddress
, P.AgencyAddress2
, P.AgencyStateCity
, StationID
FROM
(
SELECT
StationName = s.Name
, ios.AiredDateTime
, ios.AiredISCI
, OrderNumber = oh.id
, Line = od.id
, cd.Length
, cd.FlexOrderRate
, InvoiceNumber = ih.id
, InvoiceDate = ih.invoicedate
, AgencyRate = CONVERT(INT, od.AgencyRate)
, StationAddress = 'Westwood One, Inc'
, StationCityZip = '3544 Momentum Place
Chicago, IL 60689'
/*sa.Address AS StationAddress,
ISNULL(sa.City,'')+', '+ISNULL(sa.state,'')+' '+ISNULL(sa.Zipcode,'') AS StationCityZip,*/
, Product = oh.ProductDescription
, BillingCycle = CASE
WHEN oh.BillingCycle = 'F' THEN
'End OF Flight'
WHEN oh.BillingCycle = 'M' THEN
'Monthly'
WHEN oh.BillingCycle = 'W' THEN
'Weekly'
ELSE
NULL
END
, AdvertiserName = c.name
, AgencyName = cag.Name
, SalesPerson = osu.SalesPerson ---(column referencing the second sub query below )
, Buyer = cc.FirstName + ' ' + cc.LastName
/*ca.Address1 AS AgencyAddress,
ISNULL(ca.Address2,'') AS AgencyAddress2,
ISNULL(ca.City,'')+', '+ISNULL(ast.Abrv,'')+' '+ISNULL(ca.Zip,'') AS AgencyStateCity,*/
, AgencyAddress = 'DIRECT RESULTS '
, AgencyAddress2 = '931 VILLAGE BLVD #905-507'
, AgencyStateCity = 'WEST PALM BEACH, FL 33409'
, StationID = s.ID
FROM
dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID = ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID = os.ID
INNER JOIN dbo.Inventory inv ON inv.id = ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id = inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id = cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id = os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id = odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id = od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID = s.ID
INNER JOIN dbo.Company c ON c.id = oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id = oh.AgencyID
LEFT JOIN dbo.CompanyAddress cc ON cc.id = oh.BuyerCompanyContactID --(the where clause should be here i.e(where active =1))
LEFT JOIN
(
SELECT
SalesPerson = usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
, orderSalesID = MAX(os .id)
FROM
dbo.OrderSales os
INNER JOIN dbo.[User] usr ON os.UserID = usr.ID
WHERE IsPrimary = 1
GROUP BY
usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
) osu ON osu.orderHeaderID = oh.ID
LEFT JOIN dbo.CompanyAddress ca ON ca.CompanyID = oh.AgencyID
LEFT JOIN dbo.AddressState ast ON ast.id = ca.AddressStateID
) P
WHERE
InvoiceNumber = @InvoiceNumber AND
StationName = @StationName
--AND (@StationName = 'ALL' OR StationName = @StationName)
AND
AiredDateTime IS NOT NULL
ORDER BY
StationName
, AiredDateTime DESC;
I learn from the footprints of giants......
December 2, 2016 at 10:02 am
On which table is the column 'Active'?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2016 at 10:04 am
This question is clearly puzzling us all. Can I suggest that you create a 'dumbed-down' version, exemplifying the issue in consumable code, which we can run for ourselves? I think it might be faster than us slowly spiralling in on a solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2016 at 10:04 am
Phil Parkin (12/2/2016)
On which table is the column 'Active'?
'Inventory' table
I learn from the footprints of giants......
December 2, 2016 at 10:05 am
Phil Parkin (12/2/2016)
On which table is the column 'Active'?
Inventory
I learn from the footprints of giants......
December 2, 2016 at 10:14 am
You need to return inv.Active to the outer query, if you need it there.
SELECT
StationName
, AiredDateTime
, AiredISCI
, OrderNumber
, Line
, Length
, FlexOrderRate = FlexOrderRate
, InvoiceNumber
, InvoiceDate = CONVERT(VARCHAR(20), InvoiceDate, 1)
, P.AgencyRate
, P.StationAddress
, P.StationCityZip
, Product
, P.BillingCycle
, AdvertiserName
, AgencyName
, P.SalesPerson
, P.Buyer
, P.AgencyAddress
, P.AgencyAddress2
, P.AgencyStateCity
, StationID
FROM
(
SELECT
StationName = s.Name
, ios.AiredDateTime
, ios.AiredISCI
, OrderNumber = oh.id
, Line = od.id
, cd.Length
, cd.FlexOrderRate
, InvoiceNumber = ih.id
, InvoiceDate = ih.invoicedate
, AgencyRate = CONVERT(INT, od.AgencyRate)
, StationAddress = 'Westwood One, Inc'
, StationCityZip = '3544 Momentum Place
Chicago, IL 60689'
/*sa.Address AS StationAddress,
ISNULL(sa.City,'')+', '+ISNULL(sa.state,'')+' '+ISNULL(sa.Zipcode,'') AS StationCityZip,*/
, Product = oh.ProductDescription
, BillingCycle = CASE
WHEN oh.BillingCycle = 'F' THEN
'End OF Flight'
WHEN oh.BillingCycle = 'M' THEN
'Monthly'
WHEN oh.BillingCycle = 'W' THEN
'Weekly'
ELSE
NULL
END
, AdvertiserName = c.name
, AgencyName = cag.Name
, SalesPerson = osu.SalesPerson ---(column referencing the second sub query below )
, Buyer = cc.FirstName + ' ' + cc.LastName
/*ca.Address1 AS AgencyAddress,
ISNULL(ca.Address2,'') AS AgencyAddress2,
ISNULL(ca.City,'')+', '+ISNULL(ast.Abrv,'')+' '+ISNULL(ca.Zip,'') AS AgencyStateCity,*/
, AgencyAddress = 'DIRECT RESULTS '
, AgencyAddress2 = '931 VILLAGE BLVD #905-507'
, AgencyStateCity = 'WEST PALM BEACH, FL 33409'
, StationID = s.ID
, Active = inv.Active
FROM
dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID = ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID = os.ID
INNER JOIN dbo.Inventory inv ON inv.id = ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id = inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id = cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id = os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id = odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id = od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID = s.ID
INNER JOIN dbo.Company c ON c.id = oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id = oh.AgencyID
LEFT JOIN dbo.CompanyAddress cc ON cc.id = oh.BuyerCompanyContactID --(the where clause should be here i.e(where active =1))
LEFT JOIN
(
SELECT
SalesPerson = usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
, orderSalesID = MAX(os .id)
FROM
dbo.OrderSales os
INNER JOIN dbo.[User] usr ON os.UserID = usr.ID
WHERE IsPrimary = 1
GROUP BY
usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
) osu ON osu.orderHeaderID = oh.ID
LEFT JOIN dbo.CompanyAddress ca ON ca.CompanyID = oh.AgencyID
LEFT JOIN dbo.AddressState ast ON ast.id = ca.AddressStateID
) P
WHERE
InvoiceNumber = @InvoiceNumber AND
StationName = @StationName
--AND (@StationName = 'ALL' OR StationName = @StationName)
AND
AiredDateTime IS NOT NULL AND
p.Active = 1
ORDER BY
StationName
, AiredDateTime DESC;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2016 at 11:08 am
JALLY (12/2/2016)
Your solution above will create a conflict between columns that rely on the the sub queries for their source of data and the multi part identifier 'inv.active' will not be bound to the inventory table because it is outside of a sub query: for example :osu.SalesPerson is from the following subquery part identifier. if i add the where clause at the end of the script. the where clause will not be able to bind on to the inventory table due to the 2nd sub query bound by osu following it.
No, it won't. You have nested subqueries and inv is defined in the outer subquery, so it can be used ANYWHERE in the main subquery. The second subquery creates a new scope WITHIN the scope of the main subquery. It does not close the scope of the main subquery.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply