Embedded Queries

SELECT "Local_GL_Account",
   "Local_GL_Description",
   "Master_Account",
  "Invoice_Number",
   "Invoice_Date",
   "Customer/Supplier",
   amount
FROM  (
       SELECT campo1 AS "Year",
          campo14 AS "Scenario",
          campo2 AS "Month",
          campo3 AS "Entity",
          campo4 AS "Local_GL_Account",
          campo15 AS "Master_Account",
          campo5 AS "Local_GL_Description",
          campo6 AS "Cost_Center",
          campo7 AS "Product",
          campo11 AS "Customer/Supplier",
          campo9 AS "Invoice_Number",
  				CASE WHEN LENGTH(campo10) > 0 THEN       
  					TO_CHAR(TO_DATE('01011900','MMDDYYYY')+TO_NUMBER(campo10,'99999')*INTERVAL '1 Day', 'MM/DD/YYYY')
  				ELSE ''
  				END AS "Invoice_Date",
          campo11 AS "Supplier",
          CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$Entity.code} 
       AND  campo6='CC_500'
       AND  campo7='PD_500'
       AND  campo8='CM_500'
       AND  campo15=${$Account.code}
       AND  campo14= ${$Scenario.code} 
) a
UNION ALL
SELECT 'Total',
   '',
   '',
   '',
   '',
   '',
   SUM(amount) AS amount
FROM  (
       SELECT CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$Entity.code} 
       AND  campo6='CC_500'
       AND  campo7='PD_500'
     AND  campo8='CM_500'
     AND  campo15=${$Account.code}
       AND  campo14= ${$Scenario.code} 
) a

SELECT COMMENTI AS "Comment", DESC_AZIENDA0 as "Entity", DESC_DEST10 as "Cost Center", DATI_COMMENTI.USERUPD AS "Edited by", DATI_COMMENTI.dateupd as "Time" FROM DATI_COMMENTI 
INNER JOIN DEST1 
ON DATI_COMMENTI.DEST1=DEST1.COD_DEST1
INNER JOIN AZIENDA
ON DATI_COMMENTI.AZIENDA=AZIENDA.COD_AZIENDA
WHERE COD_SCENARIO = ${$Scenario.code}
and COD_PERIODO = ${$Period.code}
AND CONTO = ${$Account.code}
AND AZIENDA = ${$Entity.code}
AND FLAG_ATTIVO = 1

SELECT 'Rejected' as Status union
select 'Approved' as Status

SELECT COUNT(*) FROM DEST3 WHERE COD_DEST3 LIKE 'CM%'

SELECT COUNT(*) FROM DEST2 WHERE COD_DEST2 LIKE 'PD%'

SELECT 'Expense' AS ExpenseType UNION
SELECT 'Capitalisable' AS ExpenseType

SELECT 'Baseline' as Product union
SELECT 'East Coast Exp.' union
SELECT 'West Coast Exp.'

SELECT 'Baseline' as Product union
SELECT 'Boost Local Sales' union
SELECT 'Inc. Distributors'

SELECT 'Baseline' as Product union
SELECT 'Cost Cutting' union
SELECT 'R&D Outsourcing'

SELECT 'Baseline' as Product union
SELECT 'High' union
SELECT 'Low'

SELECT 'Baseline' as Product union
SELECT 'Q1 Push' union
SELECT 'Q4 Push'

Select 'Yes' as YesNo
union select 'No' as YesNo

Select 'Senior VP' as Type union
Select 'Vice Pres.' as Type union
Select 'Director' as Type union
Select 'Manager' as Type union
Select 'Senior Associate' as Type union
Select 'Associate' as Type union
Select 'Assistant' as Type union
Select 'Partner' as Type union
Select 'Attorney' as Type

Select 'Yes' as Deci union
Select 'No' as Deci

SELECT 'Plan 1' as Retire union
SELECT 'Plan 2' as Retire union
SELECT 'Plan 3' as Retire union
SELECT 'Plan 4' as Retire 
order by Retire

SELECT 'Ind. Basic' as Dental union
SELECT 'Ind. Premium' as Dental union
SELECT 'Fam. Basic' as Dental union
SELECT 'Fam. Premium' as Dental

SELECT 'Ind. Basic' as Medical union
SELECT 'Ind. Premium' as Medical union
SELECT 'Fam. Basic' as Medical union
SELECT 'Fam. Premium' as Medical

SELECT DESC_azienda0 
FROM azienda

select DESC_PERIODO0 from periodo
order by cod_periodo

SELECT 'Senior VP' as title union
SELECT 'Vice Pres.' as title union
SELECT 'Director' as title union
SELECT 'Manager' as title union
SELECT 'Senior Associate' as title union
SELECT 'Associate' as title union
SELECT 'Assistant' as title union
SELECT 'Partner' as title union
SELECT 'Attorney' as title

Select 'Hourly-Based' as Type union
Select 'Salary-Based' as Type union
Select 'Contracted' as Type

SELECT 'Active' as Status union
SELECT 'Paid Leave' as Status union
SELECT 'Terminated' as Status union
SELECT 'Unpaid Leave' as Status union
SELECT 'Transfer' as Status

select cod_conto as "Account", cod_dest1 as "Cost Center", importo as "Amount", Conto_prov as "Sender Account", dest1_prov as "Sender Cost Center", NUMERATORE as "Numerator", DENOMINATORE as "Denominator", userupd as "User", dateupd as "Date"from CL_DATI_MOVIMENTO
where cod_regola_versione ='020'
and cod_regola = '15'

and cod_scenario = ${$Scenario.code}
and cod_periodo = ${$Period.code}
and cod_conto = ${$Account.code}
and cod_dest1 = ${$Cust_Dim1.code}

SELECT "Local_GL_Account",
   "Local_GL_Description",
   "Master_Account",
  "Invoice_Number",
   "Invoice_Date",
   "Customer/Supplier",
   amount
FROM  (
       SELECT campo1 AS "Year",
          campo14 AS "Scenario",
          campo2 AS "Month",
          campo3 AS "Entity",
          campo4 AS "Local_GL_Account",
          campo15 AS "Master_Account",
          campo5 AS "Local_GL_Description",
          campo6 AS "Cost_Center",
          campo7 AS "Product",
          campo11 AS "Customer/Supplier",
          campo9 AS "Invoice_Number",
  				CASE WHEN LENGTH(campo10) > 0 THEN       
  					TO_CHAR(TO_DATE('01011900','MMDDYYYY')+TO_NUMBER(campo10,'99999')*INTERVAL '1 Day', 'MM/DD/YYYY')
  				ELSE ''
  				END AS "Invoice_Date",
          campo11 AS "Supplier",
          CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$Entity.code} 
       AND  campo6='CC_500'
       AND  campo7='PD_500'
       AND  campo8='CM_500'
       AND  campo15=${$Account.code}
       AND  campo14= ${$Scenario.code} 
) a
UNION ALL
SELECT 'Total',
   '',
   '',
   '',
   '',
   '',
   SUM(amount) AS amount
FROM  (
       SELECT CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$Entity.code} 
       AND  campo6='CC_500'
       AND  campo7='PD_500'
     AND  campo8='CM_500'
     AND  campo15=${$Account.code}
       AND  campo14= ${$Scenario.code} 
) a

SELECT DESC_DEST30 FROM DEST3 WHERE COD_DEST3 IN(SELECT COD_DEST3

as CODE FROM DEST3_GERARCHIA_ABBI

WHERE COD_DEST3_GERARCHIA = '500'

AND COD_DEST3_ELEGER = 'ALL')

SELECT 'Yes' as Option union
Select 'No'

SELECT 'Exclude' code

select DESC_ELEGER0 AS DESCR
from ed_channel_gerarchia
where cod_gerarchia = 'H01'
  and cod_eleger_padre = 'H01'
order by DESC_ELEGER0

SELECT "Local_GL_Account",
   "Local_GL_Description",
   "Master_Account",
  "Invoice_Number",
   "Invoice_Date",
   "Customer/Supplier",
   amount
FROM  (
       SELECT campo1 AS "Year",
          campo14 AS "Scenario",
          campo2 AS "Month",
          campo3 AS "Entity",
          campo4 AS "Local_GL_Account",
          campo15 AS "Master_Account",
          campo5 AS "Local_GL_Description",
          campo6 AS "Cost_Center",
          campo7 AS "Product",
          campo11 AS "Customer/Supplier",
          campo9 AS "Invoice_Number",
  				CASE WHEN LENGTH(campo10) > 0 THEN       
  					TO_CHAR(TO_DATE('01011900','MMDDYYYY')+TO_NUMBER(campo10,'99999')*INTERVAL '1 Day', 'MM/DD/YYYY')
  				ELSE ''
  				END AS "Invoice_Date",
          campo11 AS "Supplier",
          CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$Entity.code} 
       AND  campo6='CC_500'
       AND  campo7='PD_500'
       AND  campo8='CM_500'
       AND  campo15=${$Account.code}
       AND  campo14= ${$Scenario.code} 
) a
UNION ALL
SELECT 'Total',
   '',
   '',
   '',
   '',
   '',
   SUM(amount) AS amount
FROM  (
       SELECT CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$Entity.code} 
       AND  campo6='CC_500'
       AND  campo7='PD_500'
     AND  campo8='CM_500'
     AND  campo15=${$Account.code}
       AND  campo14= ${$Scenario.code} 
) a

SELECT 'Sales Exec.' CODE union
select 'Price List' union
select 'Predictive'

select id, pr.desc0 prospect, it.descr item, st.desc0 stage, cp.desc0 competitor, close_date, units, value, predictive_fct
from AW_004_000001_000002 t
inner join ed_prospect pr on pr.cod = t.prospect
inner join AW_004_000013_000001 it on it.item = t.item
inner join ed_stage st on st.cod = t.stage
inner join ed_competitor cp on cp.cod = t.competitor
where scenario = '2019BDG'
   and entity = ${$Entity.code}
   and t.product = ${$Cust_Dim2.code}
   and t.bdg_sce = ${$Scenario.code}
   and t.bdg_per =  case ${$Period.code} when '' then t.bdg_per else ${$Period.code} end
   and t.stage <> '05'
order by close_date, id

SELECT "Local_GL_Account",
   "Local_GL_Description",
   "Master_Account",
  "Invoice_Number",
   "Invoice_Date",
   "Customer/Supplier",
   amount
FROM  (
       SELECT campo1 AS "Year",
          campo14 AS "Scenario",
          campo2 AS "Month",
          campo3 AS "Entity",
          campo4 AS "Local_GL_Account",
          campo15 AS "Master_Account",
          campo5 AS "Local_GL_Description",
          campo6 AS "Cost_Center",
          campo7 AS "Product",
          campo11 AS "Customer/Supplier",
          campo9 AS "Invoice_Number",
  				CASE WHEN LENGTH(campo10) > 0 THEN       
  					TO_CHAR(TO_DATE('01011900','MMDDYYYY')+TO_NUMBER(campo10,'99999')*INTERVAL '1 Day', 'MM/DD/YYYY')
  				ELSE ''
  				END AS "Invoice_Date",
          campo11 AS "Supplier",
          CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$Entity.code} 
       AND  campo6='CC_500'
       AND  campo7='PD_500'
       AND  campo8='CM_500'
       AND  campo15=${$Account.code}
       AND  campo14= ${$Scenario.code} 
) a
UNION ALL
SELECT 'Total',
   '',
   '',
   '',
   '',
   '',
   SUM(amount) AS amount
FROM  (
       SELECT CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$Entity.code} 
       AND  campo6='CC_500'
       AND  campo7='PD_500'
     AND  campo8='CM_500'
     AND  campo15=${$Account.code}
       AND  campo14= ${$Scenario.code} 
) a

SELECT DESC_azienda0 
FROM azienda

SELECT 'Ind. Basic' as Medical union
SELECT 'Ind. Premium' as Medical union
SELECT 'Fam. Basic' as Medical union
SELECT 'Fam. Premium' as Medical

SELECT 'Ind. Basic' as Dental union
SELECT 'Ind. Premium' as Dental union
SELECT 'Fam. Basic' as Dental union
SELECT 'Fam. Premium' as Dental

SELECT 'Plan 1' as Retire union
SELECT 'Plan 2' as Retire union
SELECT 'Plan 3' as Retire union
SELECT 'Plan 4' as Retire 
order by Retire

SELECT 'Active' as Status union
SELECT 'Paid Leave' as Status union
SELECT 'Terminated' as Status union
SELECT 'Unpaid Leave' as Status union
SELECT 'Transfer' as Status

Select 'Hourly-Based' as Type union
Select 'Salary-Based' as Type union
Select 'Contracted' as Type

SELECT 'Senior VP' as title union
SELECT 'Vice Pres.' as title union
SELECT 'Director' as title union
SELECT 'Manager' as title union
SELECT 'Senior Associate' as title union
SELECT 'Associate' as title union
SELECT 'Assistant' as title union
SELECT 'Partner' as title union
SELECT 'Attorney' as title

select DESC_PERIODO0 from periodo
order by cod_periodo

SELECT * FROM (SELECT B.desc_conto0 from CONTO_GERARCHIA_ABBI A 
INNER JOIN conto B
ON A.cod_conto = B.cod_conto
where A.COD_CONTO_ELEGER = '2100' or A.COD_CONTO_ELEGER = '2200'
and A.COD_CONTO_GERARCHIA = '010') AS U
UNION ALL SELECT '$ Value' AS U 
UNION ALL SELECT 'Calculation' AS U

SELECT * FROM (
select 01 as Num,'January' as Period union
select 02 as Num,'February' union
select 03 as Num,'March' union
select 04 as Num,'April' union
select 05 as Num,'May' union
select 06 as Num,'June' union
select 07 as Num,'July' union
select 08 as Num,'August' union
select 09 as Num,'September' union
select 10 as Num,'October' union
select 11 as Num,'November' union
select 12 as Num,'December' union
select 13 as Num,'Every Month'
) A 
ORDER BY NUM

SELECT "Year",
   "Month",
   "Entity",
   "GL_Account_ID",
   "GL_Account_Desc",
   "Master_Account",
   "Cost_Center",
   "Invoice_Number",
   "Invoice_Date",
   "Supplier",
   amount
FROM  (
       SELECT campo1 AS "Year",
          campo14 AS "Scenario",
          campo2 AS "Month",
          campo3 AS "Entity",
          campo4 AS "GL_Account_ID",
          campo15 AS "Master_Account",
          campo5 AS "GL_Account_Desc",
          campo6 AS "Cost_Center",
          campo7 AS "Product",
          campo8 AS "Customer",
          campo9 AS "Invoice_Number",
  				CASE WHEN LENGTH(campo10) > 0 THEN       
  					TO_CHAR(TO_DATE('01011900','MMDDYYYY')+TO_NUMBER(campo10,'99999')*INTERVAL '1 Day', 'MM/DD/YYYY')
  				ELSE ''
  				END AS "Invoice_Date",
          campo11 AS "Supplier",
          CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$entity(hierarchy("010")).code}
       AND  campo6=${$cust_dim1(hierarchy("010")).code}
       AND  campo7='PD_500'
       AND  campo8='CM_500'
       AND  campo15=${$account(hierarchy("010")).code}
       AND  campo14=${$scenario(hierarchy("$")).code}
) a
UNION ALL
SELECT 'Total',
   '',
   '',
   '',
   '',
   '',
   '',
   '',
   '',
   '',
   SUM(amount) AS amount
FROM  (
       SELECT CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$entity(hierarchy("010")).code}
       AND  campo6=${$cust_dim1(hierarchy("010")).code}
       AND  campo7='PD_500'
       AND  campo8='CM_500'
       AND  campo15=${$account(hierarchy("010")).code}
       AND  campo14=${$scenario(hierarchy("$")).code}
) a

SELECT "Year",
   "Month",
   "Entity",
   "GL_Account_ID",
   "GL_Account_Desc",
   "Master_Account",
   "Cost_Center",
   "Invoice_Number",
   "Invoice_Date",
   "Supplier",
   amount
FROM  (
       SELECT campo1 AS "Year",
          campo14 AS "Scenario",
          campo2 AS "Month",
          campo3 AS "Entity",
          campo4 AS "GL_Account_ID",
          campo15 AS "Master_Account",
          campo5 AS "GL_Account_Desc",
          campo6 AS "Cost_Center",
          campo7 AS "Product",
          campo8 AS "Customer",
          campo9 AS "Invoice_Number",
  				CASE WHEN LENGTH(campo10) > 0 THEN       
  					TO_CHAR(TO_DATE('01011900','MMDDYYYY')+TO_NUMBER(campo10,'99999')*INTERVAL '1 Day', 'MM/DD/YYYY')
  				ELSE ''
  				END AS "Invoice_Date",
          campo11 AS "Supplier",
          CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$entity(hierarchy("010")).code}
       AND  campo6=${$cust_dim1(hierarchy("010")).code}
       AND  campo7='PD_500'
       AND  campo8='CM_500'
       AND  campo15=${$account(hierarchy("010")).code}
       AND  campo14=${$scenario(hierarchy("$")).code}
) a
UNION ALL
SELECT 'Total',
   '',
   '',
   '',
   '',
   '',
   '',
   '',
   '',
   '',
   SUM(amount) AS amount
FROM  (
       SELECT CAST (REPLACE(campo12,',','.') AS NUMERIC(20,2)) AS amount
       FROM  map_dati_trasformati
       WHERE cod_mappatura='05'
       AND  cod_import='010'
       AND  campo1='2018'
       AND  campo2='12'
       AND  campo3=${$entity(hierarchy("010")).code}
       AND  campo6=${$cust_dim1(hierarchy("010")).code}
       AND  campo7='PD_500'
       AND  campo8='CM_500'
       AND  campo15=${$account(hierarchy("010")).code}
       AND  campo14=${$scenario(hierarchy("$")).code}
) a

SELECT * FROM (
select 01 as Num,'January' as Period union
select 02 as Num,'February' union
select 03 as Num,'March' union
select 04 as Num,'April' union
select 05 as Num,'May' union
select 06 as Num,'June' union
select 07 as Num,'July' union
select 08 as Num,'August' union
select 09 as Num,'September' union
select 10 as Num,'October' union
select 11 as Num,'November' union
select 12 as Num,'December' union
select 13 as Num,'Every Month'
) A 
ORDER BY NUM

SELECT * FROM (SELECT B.desc_conto0 from CONTO_GERARCHIA_ABBI A 
INNER JOIN conto B
ON A.cod_conto = B.cod_conto
where A.COD_CONTO_ELEGER = '2100' or A.COD_CONTO_ELEGER = '2200'
and A.COD_CONTO_GERARCHIA = '010') AS U
UNION ALL SELECT '$ Value' AS U 
UNION ALL SELECT 'Calculation' AS U

SELECT COD_PROSPETTO, CASE WHEN HIERARCHIE_UTILISEE <> 0 THEN 'Oui' END as "HIERARCHIE UTILISEE"
FROM 
(SELECT
COD_PROSPETTO
,POSITION('<s key="dim">' || ${@DIMENSION.code} || '_' || ${@HIERARCHIE.code} || '</s>' IN CONVERT_FROM(XML, 'UTF8')) AS HIERARCHIE_UTILISEE
 
FROM PROSPETTO_XML) Q
WHERE HIERARCHIE_UTILISEE <> 0

select cod_conto as "Account", cod_dest1 as "Cost Center", importo as "Amount", Conto_prov as "Sender Account", dest1_prov as "Sender Cost Center", NUMERATORE as "Numerator", DENOMINATORE as "Denominator", userupd as "User", dateupd as "Date"from CL_DATI_MOVIMENTO
where cod_regola_versione ='010'
and cod_regola = '10'

and cod_scenario = ${$Scenario.code}
and cod_periodo = ${$Period.code}
and cod_conto = ${$Account.code}
and cod_dest1 = ${$Cust_Dim1.code}