March 20, 2016 at 8:32 pm
I'm working on this SP and am getting an error at the end, Msg 102, Level 15, State 1, Procedure p_adw_OperationalFact_Load_V2, Line 142
Incorrect syntax near ')'.
Here's the Proc:
INSERT INTO ADW.fact.OperationalFact_V2 WITH (HOLDLOCK)
Select * from
(SELECT
CSN.CustomerID,
COALESCE(A.Assetid,0) AS AssetID,
COALESCE(CSN.CustomerServiceNumberID,0) AS CustomerServiceNumberID,
(SELECT P.DateID FROM ADW.dim.Period P WHERE P.DateID = convert(varchar, Servicedate, 112)) AS DateID,
(SELECT P.DateID FROM ADW.dim.Period P WHERE P.DateID = convert(varchar,PostDate, 112)) AS PostDate,
COALESCE(CSN.FeeStructureID,0) AS FeeStructureID,
COALESCE(M.ModalityID,0) AS ModalityID,
0 as OrgUnitID,
0 as CompanyID,
--COALESCE(OU.OrgUnitID,0) AS OrgUnitID,
--COALESCE(C.CompanyID,0) AS CompanyID,
(SELECT ScenarioID FROM ADW.DIM.Scenario WHERE Scenario = 'Actual' )AS ScenarioID,
0 AS DepartmentID,
--COALESCE(D.DepartmentID,0) AS DepartmentID,
--S.[retail_scan_count] AS RetailScans,
--S.wholesale_scan_count AS WholesaleScans,
S.ScanCount As RetailScans,
0 as WholesaleScans,
S.WBScanCount,
S.SourceSystem,
getdate() AS [Z_InsertDate],
getdate() AS [Z_LastModDate],
suser_sname() AS [Z_LastModBy]
FROM ADS.dbo.ScansODS S
INNER JOIN ADW.dim.CustomerServiceNumber CSN
ON CSN.CustomerServiceNumber = s.ServiceNbr
INNER Join ADW.Dim.Modality M
ON M.ModalityCode = S.ModalityCode
--INNER JOIN ADW.DIM.OrgUnit OU
--ON OU.SubLatCode = S.Region
--INNER JOIN ADW.DIM.Company C
--ON C.CompanyCode = S.Company
INNER JOIN ADW.dim.v_CurrentAsset a
ON a.UnitNbr = s.UnitSegment
--INNER JOIN ADW.dim.Department D
--ON S.DepartmentCode = D.departmentCode
WHERE
--(S.PostDate BETWEEN @StartDateID AND @EndDateID) AND REFER HISTORY #1
CSN.Is_Current = 1
AND
S.ScanType = 'Retail'
Union ALL
SELECT
CSN.CustomerID,
COALESCE(A.Assetid,0) AS AssetID,
COALESCE(CSN.CustomerServiceNumberID,0) AS CustomerServiceNumberID,
(SELECT P.DateID FROM ADW.dim.Period P WHERE P.DateID = convert(varchar, Servicedate, 112)) AS DateID,
(SELECT P.DateID FROM ADW.dim.Period P WHERE P.DateID = convert(varchar,PostDate, 112)) AS PostDate,
COALESCE(CSN.FeeStructureID,0) AS FeeStructureID,
COALESCE(M.ModalityID,0) AS ModalityID,
0 as OrgUnitID,
0 as CompanyID,
--COALESCE(OU.OrgUnitID,0) AS OrgUnitID,
--COALESCE(C.CompanyID,0) AS CompanyID,
(SELECT ScenarioID FROM ADW.DIM.Scenario WHERE Scenario = 'Actual' )AS ScenarioID,
0 AS DepartmentID,
--COALESCE(D.DepartmentID,0) AS DepartmentID,
--S.[retail_scan_count] AS RetailScans,
--S.wholesale_scan_count AS WholesaleScans,
0 as RetailScans,
S.ScanCount As WholeSaleScans,
S.WBScanCount,
S.SourceSystem,
getdate() AS [Z_InsertDate],
getdate() AS [Z_LastModDate],
suser_sname() AS [Z_LastModBy]
FROM ADS.dbo.ScansODS S
INNER JOIN ADW.dim.CustomerServiceNumber CSN
ON CSN.CustomerServiceNumber = s.ServiceNbr
INNER Join ADW.Dim.Modality M
ON M.ModalityCode = S.ModalityCode
--INNER JOIN ADW.DIM.OrgUnit OU
--ON OU.SubLatCode = S.Region
--INNER JOIN ADW.DIM.Company C
--ON C.CompanyCode = S.Company
INNER JOIN ADW.dim.v_CurrentAsset a
ON a.UnitNbr = s.UnitSegment
--INNER JOIN ADW.dim.Department D
--ON S.DepartmentCode = D.departmentCode
WHERE
--(S.PostDate BETWEEN @StartDateID AND @EndDateID) AND REFER HISTORY #1
CSN.Is_Current = 1
AND
S.ScanType = 'Wholesale')
The selects work fine on their own and If I do the insert separately for each section it works fine.
But the problem arises when I try to run the full insert with the Union ALL, or the full select. starting with the Select * From...
What am I missing?
Thanks
March 20, 2016 at 10:59 pm
You need to give your
select *
from (
.
.
.
)
an alias
select *
from (
.
.
.
) aliasHere
March 21, 2016 at 12:23 am
I tried that and still got the error . This time it said the error was at the alias not the ).
matak (3/20/2016)
You need to give your
select *from (
.
.
.
)
an alias
select *from (
.
.
.
) aliasHere
March 21, 2016 at 4:23 am
craig.bobchin (3/21/2016)
I tried that and still got the error . This time it said the error was at the alias not the ).matak (3/20/2016)
You need to give your
select *from (
.
.
.
)
an alias
select *from (
.
.
.
) aliasHere
Does it have to be so complex and expensive? Try this:
INSERT INTO ADW.fact.OperationalFact_V2 WITH (HOLDLOCK) -- use a column list!!!!!
--SELECT *
--FROM (
SELECT
CSN.CustomerID,
AssetID = COALESCE(A.Assetid,0),
CustomerServiceNumberID = COALESCE(CSN.CustomerServiceNumberID,0),
DateID = (
SELECT P.DateID
FROM ADW.dim.Period P
WHERE P.DateID = CONVERT(VARCHAR,Servicedate, 112)),
PostDate = (
SELECT P.DateID
FROM ADW.dim.Period P
WHERE P.DateID = CONVERT(VARCHAR,PostDate,112)),
FeeStructureID = COALESCE(CSN.FeeStructureID,0),
ModalityID = COALESCE(M.ModalityID,0),
OrgUnitID = 0,
CompanyID = 0,
ScenarioID = (
SELECT ScenarioID
FROM ADW.DIM.Scenario
WHERE Scenario = 'Actual' ),
DepartmentID = 0,
RetailScans = CASE WHEN S.ScanType = 'Retail' THEN S.ScanCount ELSE 0 END,
WholesaleScans = CASE WHEN S.ScanType = 'Wholesale' THEN S.ScanCount ELSE 0 END,
S.WBScanCount,
S.SourceSystem,
[Z_InsertDate] = GETDATE(),
[Z_LastModDate] = GETDATE(),
[Z_LastModBy] = SUSER_SNAME()
FROM ADS.dbo.ScansODS S
INNER JOIN ADW.dim.CustomerServiceNumber CSN
ON CSN.CustomerServiceNumber = s.ServiceNbr
INNER JOIN ADW.Dim.Modality M
ON M.ModalityCode = S.ModalityCode
INNER JOIN ADW.dim.v_CurrentAsset a
ON a.UnitNbr = s.UnitSegment
WHERE CSN.Is_Current = 1
AND S.ScanType IN ('Retail','Wholesale')
--) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply