*I am trying to convert a column from ZAR Column "
Amount_in_Company_Code_Currency" " to USD.*
Table:
CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2"
(
"Company_Code" character varying(255) COLLATE pg_catalog."default",
"Posting_Period" integer,
"Fiscal_Year" integer,
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" integer,
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Amount_in_Company_Code_Currency" numeric,
"Company_Code_Currency" character varying(255) COLLATE
pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Loaddate" date DEFAULT CURRENT_DATE,
"Row_Hash" text COLLATE pg_catalog."default",
"LoadTime" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
"ZTBR_TransactionCode" integer NOT NULL GENERATED ALWAYS AS IDENTITY (
INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey2" PRIMARY KEY
("ZTBR_TransactionCode")
)
*Conversion Table :*
CREATE TABLE IF NOT EXISTS dim."IMETA_Master_Currency_Data_TA_BR"
(
"Currency" character varying(255) COLLATE pg_catalog."default",
"Currency name" character varying(255) COLLATE pg_catalog."default",
"Currency from" character varying(255) COLLATE pg_catalog."default",
"Scenario" character varying(255) COLLATE pg_catalog."default",
"Fiscal year" double precision,
"Fiscal period" character varying(255) COLLATE pg_catalog."default",
"Currency from value" double precision,
"Currency to value" double precision,
"Loaddate" date
)
TABLESPACE pg_default;
*I am getting no output for using:*
SELECT
z."Fiscal_Year",
SUM(z."Amount_in_Company_Code_Currency") AS Total_Amount,
ROUND(SUM(z."Amount_in_Company_Code_Currency" / CASE
WHEN c."Currency" = 'USD' THEN 1
ELSE c."Currency to value" END)::numeric, 2) AS Total_Amount_USD
FROM
system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2" z
LEFT JOIN
(SELECT
"Currency",
"Currency to value"
FROM
dim."IMETA_Master_Currency_Data_TA_BR"
WHERE
"Scenario" = 'Actual' -- Adjust the scenario as needed
AND "Fiscal year" = 2024) c -- Adjust the fiscal year as needed
ON
z."Company_Code_Currency" = c."Currency"
WHERE
z."Fiscal_Year" = 2024
GROUP BY
z."Fiscal_Year";
*In a previous calculation/join i used the code below and it worked, what
am i doing wrong?*
-- View: model.IMETA_ZTRB_BRACS_Model_TA_BW_View
-- DROP VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View";
CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View"
AS
SELECT t."ZTBR_TransactionCode",
t."Company_Code",
t."Posting_Period",
t."Fiscal_Year",
t."Profit_Center",
t."Account_Number",
t."Business_Process",
t."Internal_Order",
t."Amount_in_Company_Code_Currency",
t."Company_Code_Currency",
t."BRACS_FA",
t."Expense_Type",
t."Primary_ZTBR_TransactionCode",
t."DIM_BRACS_Account_Description" AS "Acct_Type",
t."DIM_Classification",
t."DIM_Direct_Primary_Key",
t."DIM_Order",
t."SDM_BRACSFA",
t."SDM_Function",
t."BRACS_Level_1",
t."BRACS_Level_2",
t."BRACS_Level_3",
t."Roll_Up_Currency",
t."Roll_Up_Account_Description",
t."BRACS_Account",
t."BRACS_Account_Description",
t."IS_BS",
t."Classification",
t."Roll_Up_Function",
t."Region",
t."Roll_Up",
t."Entity",
t."Entity_Name",
t."Entity_Level",
t."Entity_Level_1",
t."Entity_Level_2",
t."Entity_Level_3",
t."Entity_Level_4",
t."Entity_Level_5",
t."Entity_Level_6",
t."Region_Mapping_CoCd",
t."Region_Mapping_Sub_Region",
t."Region_Mapping_Region",
t."Region_Mapping_BRACS_Entity",
t."Region_Mapping_Consul",
t."Region_Mapping_Report",
t."Region_Mapping_Region_BRACS",
t."Region_Mapping_Group",
t."Region_Mapping_Group_BRACS",
round((t."Amount_in_Company_Code_Currency"::double precision /
curr."Conversion rate")::numeric, 2) AS "Amount in USD",
CASE
WHEN t."Fiscal_Year"::double precision =
date_part('year'::text, CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Current Period",
CASE
WHEN t."Fiscal_Year"::double precision =
(date_part('year'::text, CURRENT_DATE) - 1::double precision) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Prior Period",
CASE
WHEN t."Fiscal_Year"::double precision =
date_part('year'::text, CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
WHEN t."Fiscal_Year"::double precision =
(date_part('year'::text, CURRENT_DATE) - 1::double precision) THEN -
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Movement"
FROM model."IMETA_ZTRB_BRACS_Model_TA_BW3" t
LEFT JOIN ( SELECT "IMETA_Master_Currency_Data_TA_BR"."Currency",
"IMETA_Master_Currency_Data_TA_BR"."Currency name",
CASE
WHEN
"IMETA_Master_Currency_Data_TA_BR"."Currency"::text = 'USD'::text THEN
1::double precision
ELSE "IMETA_Master_Currency_Data_TA_BR"."Currency to
value"
END AS "Conversion rate"
FROM dim."IMETA_Master_Currency_Data_TA_BR"
WHERE "IMETA_Master_Currency_Data_TA_BR"."Scenario"::text =
'BUD'::text) curr ON t."Company_Code_Currency" = curr."Currency"::text;
