SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
-------------------------------------------------------- -- DDL for Procedure SP_ASIA_BOC_SUMMARY_QUARTERLY_TEST -------------------------------------------------------- set define off; CREATE OR REPLACE EDITIONABLE PROCEDURE BERT.SP_ASIA_BOC_SUMMARY_QUARTERLY_TEST ( p_bfy in varchar2, p_fy in number, p_fm in number, p_fund in varchar2, p_fund_center in varchar2, p_appropriation in varchar2, p_program in varchar2, p_is_obligations in number, p_auth_fund_centers in varchar2, resultset out sys_refcursor ) AS p_sql varchar2(32767) := ''; p_quarter varchar2(32767) := ''; BEGIN if p_is_obligations = 1 then p_quarter := 'Obs Q' || to_char( ceil( p_fm/3 ) ); else p_quarter := 'Coms Q' || to_char( ceil( p_fm/3 ) ); end if; p_sql := 'FUND_CENTER,FUND , VALUE, CARRYOVER, VALUE_QUARTER, CARRYOVER_QUARTER from ( '; p_sql := p_sql || ' select FY, FM, APPN, d.FUND_CENTER AS FUND_CENTER, d.FUND AS FUND, FUND_SOURCE_YEAR, FUND_ACCOUNT, UNIQUE_FUND_ID, d.PROGRAM, d.BOC '; p_sql := p_sql || ' , fc.TITLE as FUND_CENTER_TITLE, f.TITLE as FUND_TITLE, p.TITLE as PROGRAM_TITLE, b.TITLE as BOC_TITLE '; p_sql := p_sql || ' , substr(BOC_GROUP, 1, 2) as BOC_GROUP_ORDER, substr(BOC_GROUP, 4) as BOC_GROUP '; p_sql := p_sql || ' , substr(BOC_CATEGORY, 1, 2) as BOC_CATEGORY_ORDER, substr(BOC_CATEGORY, 4) as BOC_CATEGORY , VALUE, CARRYOVER '; p_sql := p_sql || ' , case when ' || to_char(p_fm) || ' between 1 and 3 and FM between 1 and 3 then VALUE '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 4 and 6 and FM between 4 and 6 then VALUE '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 7 and 9 and FM between 7 and 9 then VALUE '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 10 and 12 and FM between 10 and 12 then VALUE '; p_sql := p_sql || ' else 0 end as VALUE_QUARTER '; p_sql := p_sql || ' , case when ' || to_char(p_fm) || ' between 1 and 3 and FM between 1 and 3 then CARRYOVER '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 4 and 6 and FM between 4 and 6 then CARRYOVER '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 7 and 9 and FM between 7 and 9 then CARRYOVER '; p_sql := p_sql || ' when ' || to_char(p_fm) || ' between 10 and 12 and FM between 10 and 12 then CARRYOVER '; p_sql := p_sql || ' else 0 end as CARRYOVER_QUARTER from ( '; p_sql := replace(replace(p_sql, ' ', ' '), ' ,', ','); p_sql := p_sql || ' select FISCAL_YEAR as FY, FISCAL_MONTH as FM, BEGIN_FISCAL_YEAR as BFY '; p_sql := p_sql || ' , APPROPRIATION as APPN, DIVISION as FUND_CENTER, FUND, substr(FUND, 1, 3) as FUND_SOURCE_YEAR '; p_sql := p_sql || ' , substr(FUND, 5, 4) as FUND_ACCOUNT, FUND_CLASS as UNIQUE_FUND_ID, PROGRAM, BOC '; p_sql := p_sql || ' , case when substr(BOC, 1, 2) in (''11'', ''12'', ''13'') then ''01-Pay'' else ''02-Non-Pay'' end BOC_GROUP '; p_sql := p_sql || ' , case when substr(BOC, 1, 1) in (''1'') then ''01-Pay (BOC 1*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''21'') then ''02-Travel (BOC 21*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''22'') then ''03-Transportation of Things (BOC 22*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 3) in (''231'', ''232'') then ''04-Rent (BOC 231* and 232*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 3) in (''233'') then ''05-Communications/Utilities (BOC 233*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''24'') then ''06-Printing (BOC 24*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''25'') then ''07-Other Contr. (BOC 25*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 2) in (''26'') then ''08-Supplies (BOC 26*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 1) in (''3'') then ''09-Equipment (BOC 3*)'' '; p_sql := p_sql || ' when substr(BOC, 1, 1) in (''4'') then ''10-Grants and Fixed Charges (BOC 4*)'' '; p_sql := p_sql || ' else ''11-Other'' end BOC_CATEGORY '; p_sql := p_sql || ' , case when to_char(FISCAL_YEAR) = BEGIN_FISCAL_YEAR or BEGIN_FISCAL_YEAR = ''XXX'' then case when ' || to_char(p_is_obligations) || ' = 1 then OBLIGATED else COMMITMENTS end '; p_sql := p_sql || ' else 0 '; p_sql := p_sql || ' end as VALUE '; p_sql := p_sql || ' , case when to_char(FISCAL_YEAR) = BEGIN_FISCAL_YEAR or BEGIN_FISCAL_YEAR = ''XXX'' then 0 '; p_sql := p_sql || ' else case when ' || to_char(p_is_obligations) || ' = 1 then OBLIGATED else COMMITMENTS end '; p_sql := p_sql || ' end as CARRYOVER '; p_sql := p_sql || ' from MV_ASIA_RESOURCE_FINAL r where ((' || to_char(p_is_obligations) || ' = 1 and OBLIGATED <> 0) or (' || to_char(p_is_obligations) || ' = 0 and COMMITMENTS <> 0)) '; p_sql := p_sql || ' and r.FISCAL_YEAR BETWEEN ' || to_char(p_fy - 3) || ' AND ' || to_char(p_fy) || ' AND (r.FISCAL_MONTH <= (ceil(' || to_char(p_fm) || '/3) * 3)) '; -- p_sql := p_sql || ' and ((''' || p_from_inception || ''' = ''0'' AND r.FISCAL_YEAR BETWEEN ' || to_char(p_fy - 3) || ' AND ' || to_char(p_fy) || ' AND (r.FISCAL_MONTH <= (ceil(' || to_char(p_fm) || '/3) * 3))) '; -- p_sql := p_sql || ' OR (''' || p_from_inception || ''' != ''0'' AND r.FISCAL_MONTH > 0 AND (r.FISCAL_YEAR < ' || to_char(p_fy) || ' OR (r.FISCAL_YEAR = ' || to_char(p_fy) || ' AND r.FISCAL_MONTH <= (ceil(' || to_char(p_fm) || '/3) * 3))))) '; p_sql := p_sql || ' AND (TRIM(''' || p_bfy || ''') IS NULL '; p_sql := p_sql || ' OR r.BEGIN_FISCAL_YEAR IN '; p_sql := p_sql || ' (SELECT REGEXP_SUBSTR(''' || p_bfy || ''', ''[^,]+'',1,ROWNUM) '; p_sql := p_sql || ' FROM dual CONNECT BY REGEXP_SUBSTR(''' || p_bfy || ''', ''[^,]+'', 1, LEVEL) IS NOT NULL)) '; p_sql := p_sql || ' AND ((r.TERM = 0) OR (r.TERM != 0 AND TO_NUMBER(r.BEGIN_FISCAL_YEAR) + (r.TERM - 1) + 5 >= TO_NUMBER(''' || p_fy || '''))) '; p_sql := p_sql || ' AND (''' || p_fund || ''' IS NULL OR r.FUND_CLASS = ''' || p_fund || ''') '; p_sql := p_sql || ' AND ((''' || p_appropriation || ''' IS NULL OR r.APPROPRIATION = ''' || p_appropriation || ''') OR (''' || p_appropriation || ''' IS NULL OR r.APPROPRIATION_GROUP = ''' || p_appropriation || ''')) '; p_sql := p_sql || ' AND (''' || p_fund_center || ''' IS NULL OR r.FUND_CENTER LIKE ''' || p_fund_center || ''' || ''%'') '; p_sql := p_sql || ' AND (''' || p_program || ''' is null or substr(program, 0, 7) in '; p_sql := p_sql || ' (select regexp_substr(''' || p_program || ''',''[^,]+'', 1, level) from dual connect by regexp_substr(''' || p_program || ''', ''[^,]+'', 1, level) is not null)) '; p_sql := p_sql || ' AND (''' || p_auth_fund_centers || ''' IS NULL OR EXISTS '; p_sql := p_sql || ' (SELECT 1 FROM (SELECT REGEXP_SUBSTR(''' || p_auth_fund_centers || ''',''[^,]+'', 1, LEVEL) AS fund_center '; p_sql := p_sql || ' FROM DUAL CONNECT BY REGEXP_SUBSTR(''' || p_auth_fund_centers || ''', ''[^,]+'', 1, LEVEL) IS NOT NULL) d '; p_sql := p_sql || ' WHERE r.fund_center LIKE d.fund_center || ''%'')) ) d '; p_sql := p_sql || ' left join MV_ASIA_FUND_CENTER fc on fc.FUND_CENTER = trim(d.FUND_CENTER) '; p_sql := p_sql || ' left join MV_ASIA_FUND f on f.FUND = trim(d.FUND) '; p_sql := p_sql || ' left join MV_ASIA_PROGRAM p on p.PROGRAM = trim(d.PROGRAM) '; p_sql := p_sql || ' left join MV_ASIA_BOC b on b.FISCAL_YEAR = d.FY and b.BOC = trim(d.BOC) and b.BOC_ROLLUP = least(4, length(trim(d.BOC))) '; p_sql := p_sql || ' ) d ) '; p_sql := replace(replace(p_sql, ' ', ' '), ' ,', ','); p_sql := p_sql || ' select decode(CATEGORY, '''', ''Total'', CATEGORY) as "Category",FUND as FUND ,FUND_CENTER as FUND_CENTER , sum(FY1_ACT_CY + FY1_ACT_CAR) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Actuals Total" '; p_sql := p_sql || ' , sum(FY1_ACT_CY) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Actuals CY" '; p_sql := p_sql || ' , sum(FY1_ACT_CAR) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Actuals Carryover" '; p_sql := p_sql || ' , sum(FY1_QUA_CY + FY1_QUA_CAR) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Only Total" '; p_sql := p_sql || ' , sum(FY1_QUA_CY) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Only CY" '; p_sql := p_sql || ' , sum(FY1_QUA_CAR) as "FY' || substr(to_char(p_fy - 0), -2) || ' ' || p_quarter || ' Only Carryover" '; p_sql := p_sql || ' , sum(FY2_ACT_CY + FY2_ACT_CAR) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Actuals Total" '; p_sql := p_sql || ' , sum(FY2_ACT_CY) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Actuals CY" '; p_sql := p_sql || ' , sum(FY2_ACT_CAR) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Actuals Carryover" '; p_sql := p_sql || ' , sum(FY2_QUA_CY + FY2_QUA_CAR) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Only Total" '; p_sql := p_sql || ' , sum(FY2_QUA_CY) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Only CY" '; p_sql := p_sql || ' , sum(FY2_QUA_CAR) as "FY' || substr(to_char(p_fy - 1), -2) || ' ' || p_quarter || ' Only Carryover" '; p_sql := p_sql || ' , sum(FY3_ACT_CY + FY3_ACT_CAR) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Actuals Total" '; p_sql := p_sql || ' , sum(FY3_ACT_CY) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Actuals CY" '; p_sql := p_sql || ' , sum(FY3_ACT_CAR) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Actuals Carryover" '; p_sql := p_sql || ' , sum(FY3_QUA_CY + FY3_QUA_CAR) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Only Total" '; p_sql := p_sql || ' , sum(FY3_QUA_CY) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Only CY" '; p_sql := p_sql || ' , sum(FY3_QUA_CAR) as "FY' || substr(to_char(p_fy - 2), -2) || ' ' || p_quarter || ' Only Carryover" '; p_sql := p_sql || ' , sum(FY4_ACT_CY + FY4_ACT_CAR) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Actuals Total" '; p_sql := p_sql || ' , sum(FY4_ACT_CY) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Actuals CY" '; p_sql := p_sql || ' , sum(FY4_ACT_CAR) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Actuals Carryover" '; p_sql := p_sql || ' , sum(FY4_QUA_CY + FY4_QUA_CAR) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Only Total" '; p_sql := p_sql || ' , sum(FY4_QUA_CY) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Only CY" '; p_sql := p_sql || ' , sum(FY4_QUA_CAR) as "FY' || substr(to_char(p_fy - 3), -2) || ' ' || p_quarter || ' Only Carryover" from ( '; p_sql := replace(replace(p_sql, ' ', ' '), ' ,', ','); p_sql := p_sql || ' select CATEGORY_ORDER, CATEGORY,FUND,FUND_CENTER '; p_sql := p_sql || ' , VALUE as FY1_ACT_CY, CARRYOVER as FY1_ACT_CAR, VALUE_QUARTER as FY1_QUA_CY, CARRYOVER_QUARTER as FY1_QUA_CAR '; p_sql := p_sql || ' , 0 as FY2_ACT_CY, 0 as FY2_ACT_CAR, 0 as FY2_QUA_CY, 0 as FY2_QUA_CAR '; p_sql := p_sql || ' , 0 as FY3_ACT_CY, 0 as FY3_ACT_CAR, 0 as FY3_QUA_CY, 0 as FY3_QUA_CAR '; p_sql := p_sql || ' , 0 as FY4_ACT_CY, 0 as FY4_ACT_CAR, 0 as FY4_QUA_CY, 0 as FY4_QUA_CAR '; p_sql := p_sql || ' from base where FY = (' || to_char(p_fy - 0) || ') '; p_sql := p_sql || ' union all select CATEGORY_ORDER, CATEGORY,FUND,FUND_CENTER '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , VALUE, CARRYOVER, VALUE_QUARTER, CARRYOVER_QUARTER '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' from base where FY = (' || to_char(p_fy - 1) || ') '; p_sql := p_sql || ' union all select CATEGORY_ORDER, CATEGORY ,FUND,FUND_CENTER'; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , VALUE, CARRYOVER, VALUE_QUARTER, CARRYOVER_QUARTER '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' from base where FY = (' || to_char(p_fy - 2) || ') '; p_sql := p_sql || ' union all select CATEGORY_ORDER, CATEGORY ,FUND,FUND_CENTER'; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , 0, 0, 0, 0 '; p_sql := p_sql || ' , VALUE, CARRYOVER, VALUE_QUARTER, CARRYOVER_QUARTER '; p_sql := p_sql || ' from base where FY = (' || to_char(p_fy - 3) || ') ) d '; -- p_sql := replace(replace(p_sql, ' ', ' '), ' ,', ','); -- OPEN resultset for ' with base as ( select FY, BOC_GROUP_ORDER as CATEGORY_ORDER, BOC_GROUP as CATEGORY ' || p_sql || ' group by ROLLUP( CATEGORY ) order by substr(CATEGORY, 2, 2) '; p_sql := replace(p_sql, 'decode(CATEGORY, '''', ''Total'', CATEGORY) as "Category"', 'CATEGORY as "Category"'); OPEN resultset for ' with base as ( select FY, BOC_CATEGORY_ORDER as CATEGORY_ORDER, BOC_CATEGORY as CATEGORY, FUND,FUND_CENTER, ' || p_sql || ' group by CATEGORY_ORDER, CATEGORY order by CATEGORY_ORDER, CATEGORY '; -- p_sql := replace(p_sql, 'CATEGORY as "Category"', 'CATEGORY as "BOC", TITLE as "Title"'); -- p_sql := replace(p_sql, 'CATEGORY_ORDER, CATEGORY', 'CATEGORY_ORDER, CATEGORY, TITLE'); -- OPEN resultset3 for ' with base as ( select FY, 1 as CATEGORY_ORDER, BOC as CATEGORY, BOC_TITLE as TITLE ' || p_sql || ' group by CATEGORY_ORDER, CATEGORY, TITLE order by CATEGORY_ORDER, CATEGORY, TITLE '; END; / GRANT EXECUTE ON BERT.SP_ASIA_BOC_SUMMARY_QUARTERLY_TEST TO BERT_VIEW;
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear