October 21, 2024 at 6:41 pm
I have this view [dbo].[Asset Metrics - Client(Rama -Client-Test)], which makes use of multiple tables/views Below:
The only change that was made in the underlying data was [Prod].[IMETA - Global Field Flows and Holdings (Period and client)_model_SAP BW]. The Value column in source contained a non numerical value "X", i removed it and replaced it with "Null".
Sample Data:
INSERT INTO [dbo].[YourTableName]
(
[Fiscal year period],
[Company Code],
[Company],
[Column 3],
[Account],
[Level 1],
[Level 2],
[Level 3],
[Level 4],
[Column 15],
[Column 17],
[Account Name],
[Level 1 Name],
[Level 2 Name],
[Level 3 Name],
[Level 4 Name],
[Material Code],
[Metric],
[Value],
[Loaddate],
[Month],
[Column 18]
)
VALUES
(
'001.2025', NULL, 'AE10', NULL, 100777858, 101131243, 100983124, 100983084, 100983084, NULL, NULL,
'Ali Rashid Al Amin Co B.S.C-Manama', 'Bahrain Distributor Parent', 'Retail TT Reporting Parent',
'ME Retail Reporting Grandparent', 'ME Retail Reporting Grandparent', 1,
'D Stock (D01+D02+D03+D04+D05+D06+D07+Admin+Other)', 2035, NULL, NULL, NULL
),
(
'001.2025', NULL, 'AE10', NULL, 100777858, 101131243, 100983124, 100983084, 100983084, NULL, NULL,
'Ali Rashid Al Amin Co B.S.C-Manama', 'Bahrain Distributor Parent', 'Retail TT Reporting Parent',
'ME Retail Reporting Grandparent', 'ME Retail Reporting Grandparent', 1,
'D Stock (PD+CD)', 2035, NULL, NULL, NULL
);
USE [IMETA_FINAL]
GO
/****** Object: View [dbo].[Asset Metrics - Client(Rama -Client-Test)] Script Date: 10/20/2024 5:07:29 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Asset Metrics - Client(Rama -Client-Test)]
AS
(
SELECT DISTINCT
Table_1.[Company Code],
Table_1.[Account],
Table_2.[Calendar day],
Table_1.[Period],
Table_1.[FY],
Table_1.[Service Line],
Table_1.[Metric],
Table_1.[Metric Value]
FROM
(
SELECT DISTINCT
[Company Code],
[Account],
[FY],
[Period],
[Service Line],
[Metric],
[Metric Value]
FROM [Prod].[IMETA - Asset Management Metrics (Client) - Field_Flow (All flows)_Table_SAP BW]
UNION ALL
SELECT DISTINCT
[Company Code],
[Account],
[FY],
[Period],
[Service Line],
[Metric],
[Metric Value]
FROM [Prod].[IMETA - Asset Management Metrics (Client) - Global_Stock_Table_SAP BW]
) AS Table_1
LEFT OUTER JOIN [Prod].[Asset Metrics - Reporting_Date_Date_match] AS Table_2
ON Table_2.[FY] = Table_1.[FY]
AND Table_2.[Period] = Table_1.[Period]
AND Table_2.[Company] = Table_1.[Company Code]
WHERE Table_2.[Calendar day] IS NOT NULL
)
GO
Tables/Views mentioned:
[Prod].[IMETA - Asset Management Metrics (Client) - Field_Flow (All flows)_Table_SAP BW]
[Prod].[Asset Metrics - Reporting_Date_Date_match]
[IMETA - Asset Management Metrics (Client) - Global_Stock_Table_SAP BW] Is a VIEW:
USE [IMETA_FINAL]
GO
/****** Object: View [Prod].[IMETA - Asset Management Metrics (Client) - Global_Stock_Table_SAP BW] Script Date: 10/21/2024 6:25:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [Prod].[IMETA - Asset Management Metrics (Client) - Global_Stock_Table_SAP BW]
AS
(
SELECT
S.[Company] AS [Company Code],
S.[Account],
S.[Period],
CAST(S.[FY] AS VARCHAR(6)) AS [FY],
S.[Material Code],
M.[Service Line],
S.[Stock] AS [Metric],
S.[Metric Value]
FROM
(
SELECT
[Company],
[Account],
[Material Code],
[FY],
[Period],
-- Calculated Fields
SUM(CASE WHEN [Metric] IN ('Cooperative Distributors - Stock','NCD (0 - 50%) - Stock','NCD (51 - 70%) - Stock','Non-Cooperative Distributors - Stock','Participative Distributors - Stock','Semi-Cooperative Distributors - Stock')
THEN [Value] END) AS [Distributor Stock],
SUM(CASE WHEN [Metric] IN ('E Stock')
THEN [Value] END) AS [Emitter Stock],
SUM(CASE WHEN [Metric] IN ('Cooperative Distributors - Stock','NCD (0 - 50%) - Stock','NCD (51 - 70%) - Stock','Non-Cooperative Distributors - Stock','Participative Distributors - Stock','Semi-Cooperative Distributors - Stock','E Stock')
THEN [Value] END) AS [Field Stock],
SUM(CASE WHEN [Metric] IN ('D Stock (D01+D02+D03+D04+D05+D06+D07+Admin+Other)','E Stock','P Stock')
THEN [Value] END) AS [Total Stock],
SUM(CASE WHEN [Metric] IN ('P Stock')
THEN [Value] END) AS [Plant Stock],
SUM(CASE WHEN [Metric] IN ('Admin (Out of Pool - 11) Stock','Admin (Unknown - 9) Stock','Admin (Variance - 10) Stock')
THEN [Value] END) AS [Admin Stock]
FROM
(
SELECT
t.[Fiscal year period],
t.Account,
t.Metric,
t.[Material Code],
t.Company,
t.Value,
CASE
WHEN SUBSTRING([Fiscal year period],1,3)='001' THEN 'P01'
WHEN SUBSTRING([Fiscal year period],1,3)='002' THEN 'P02'
WHEN SUBSTRING([Fiscal year period],1,3)='003' THEN 'P03'
WHEN SUBSTRING([Fiscal year period],1,3)='004' THEN 'P04'
WHEN SUBSTRING([Fiscal year period],1,3)='005' THEN 'P05'
WHEN SUBSTRING([Fiscal year period],1,3)='006' THEN 'P06'
WHEN SUBSTRING([Fiscal year period],1,3)='007' THEN 'P07'
WHEN SUBSTRING([Fiscal year period],1,3)='008' THEN 'P08'
WHEN SUBSTRING([Fiscal year period],1,3)='009' THEN 'P09'
WHEN SUBSTRING([Fiscal year period],1,3)='010' THEN 'P10'
WHEN SUBSTRING([Fiscal year period],1,3)='011' THEN 'P11'
WHEN SUBSTRING([Fiscal year period],1,3)='012' THEN 'P12'
END AS [Period],
SUBSTRING([Fiscal year period],5,8) AS [FY]
FROM
(
SELECT
R.[Fiscal year period],
R.Account,
R.[Metric],
R.[Material Code],
R.[Company],
R.[Value]
FROM
[Prod].[IMETA - Global Field Flows and Holdings (Period and client)_model_SAP BW] AS R
WHERE
R.Company IN('SA10','AE10','AE20')
) AS t
) AS t
LEFT OUTER JOIN
[Prod].[IMETA - BRACS Company Codes_Table_Other] AS s
ON t.[Company] = s.[Co Code]
GROUP BY
[Company],
[Account],
[Material Code],
[FY],
[Period]
) AS z
UNPIVOT
(
[Metric Value] FOR [Stock]
IN (
[Distributor Stock],
[Emitter Stock],
[Field Stock],
[Plant Stock],
[Total Stock],
[Admin Stock]
)
) AS S
LEFT OUTER JOIN
(
SELECT
MAST.[Generic Material],
MAST.[Service Line]
FROM
[Prod].[IMETA - Material Master_Table_SAP BW] AS MAST
) AS M
ON M.[Generic Material] = S.[Material Code]
)
GO
Tables/Views in this View:
[Prod].[IMETA - BRACS Company Codes_Table_Other]
[Prod].[IMETA - Material Master_Table_SAP BW]
[Prod].[IMETA - Global Field Flows and Holdings (Period and client)_model_SAP BW]
[Prod].[Asset Metrics - Reporting_Date_Date_match]
Where have i made the error, that prohibits my view from displaying?
October 21, 2024 at 6:48 pm
Haven't looked for your error, but you could replace all of this:
CASE
WHEN SUBSTRING([Fiscal year period],1,3)='001' THEN 'P01'
WHEN SUBSTRING([Fiscal year period],1,3)='002' THEN 'P02'
WHEN SUBSTRING([Fiscal year period],1,3)='003' THEN 'P03'
WHEN SUBSTRING([Fiscal year period],1,3)='004' THEN 'P04'
WHEN SUBSTRING([Fiscal year period],1,3)='005' THEN 'P05'
WHEN SUBSTRING([Fiscal year period],1,3)='006' THEN 'P06'
WHEN SUBSTRING([Fiscal year period],1,3)='007' THEN 'P07'
WHEN SUBSTRING([Fiscal year period],1,3)='008' THEN 'P08'
WHEN SUBSTRING([Fiscal year period],1,3)='009' THEN 'P09'
WHEN SUBSTRING([Fiscal year period],1,3)='010' THEN 'P10'
WHEN SUBSTRING([Fiscal year period],1,3)='011' THEN 'P11'
WHEN SUBSTRING([Fiscal year period],1,3)='012' THEN 'P12'
END AS [Period]
With this
[Period] = 'P' + SUBSTRING([Fiscal year period],2,2)
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
October 21, 2024 at 6:50 pm
Thank you.
October 21, 2024 at 7:42 pm
I suggest removing ALL non-numeric values in the query itself rather than trying to go thru the data. For example, by making this mod to the query:
...
FROM
(
SELECT
R.[Fiscal year period],
R.Account,
R.[Metric],
R.[Material Code],
R.[Company],
Value_adjusted AS [Value] --<<--
FROM
[Prod].[IMETA - Global Field Flows and Holdings (Period and client)_model_SAP BW] AS R
WHERE
R.Company IN('SA10','AE10','AE20')
) AS t
CROSS APPLY ( --<<--
SELECT CASE WHEN TRY_CAST(t.Value AS decimal(17, 3)) IS NULL AND TRY_CAST(t.Value AS int) IS NULL
THEN NULL ELSE [value] END AS Value_adjusted
) AS Fix_value_if_nonnumeric
) AS t
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 21, 2024 at 7:45 pm
I did it at first, which left me with some missing values where the "X" has been, still the view did not pick up the data and the same error was displayed.
October 22, 2024 at 6:07 am
did you try casting "value" to a numerical data type ?
e.g.
...
SUM(CASE WHEN [Metric] IN (...)
THEN cast([Value] as decimal(18,2)) END) AS [...],
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply