When to call query? | Notes | |
---|---|---|
Query Call During Loading | ||
Component 1 (Transaction List) | SELECT ID, DATE_SQL AS TRAN_DATE, POST_DATE, SUM(AMOUNT) AS AMOUNT, CATEGORY_TEXT AS CATEGORY, DESCRIPTION FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" GROUP BY ID, DATE_SQL, POST_DATE, CATEGORY_TEXT, DESCRIPTION ORDER BY ID; | |
Component 2 (Pie Chart) | SELECT SUM(AMOUNT) AS AMOUNT, CATEGORY_TEXT AS CATEGORY FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" GROUP BY CATEGORY_TEXT ORDER BY CATEGORY_TEXT | |
Component 3 (Static Transaction Report - 3, 6, and 12 months) | SELECT SUM(AMOUNT) AS AMOUNT, RANGE, OUTPUT_TYPE FROM \"_SYS_BIC\".\"psa/CAL_GET_AMOUNT_REPORT\" GROUP BY RANGE, OUTPUT_TYPE | |
Component 5 (Classification by Transactions) | SELECT COUNT(ID) AS ID, AVG(AMOUNT) AS AMOUNT, CLUSTER_ID FROM \"_SYS_BIC\".\"psa/CAL_GROUP_TRANSACTION\" GROUP BY CLUSTER_ID ORDER BY CLUSTER_ID | |
Event Driven Query Call | ||
Component 4 (Line Chart) | Radio button is selected | Three radio buttons are configured to link with different queries. Each radio button is linking to one query. Query 2: Get Monthly Average Query 3: Get Monthly Difference |
Component 6 (Search by ID) | Transaction is selected | SELECT ID, DATE_SQL AS TRAN_DATE, POST_DATE, SUM(AMOUNT) AS AMOUNT, CATEGORY_TEXT AS CATEGORY, DESCRIPTION FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" WHERE id = ? GROUP BY ID, DATE_SQL, POST_DATE, CATEGORY_TEXT, DESCRIPTION ORDER BY ID |
Component 7 (Search by Description) | Search button is clicked | SELECT ID, DATE_SQL AS TRAN_DATE, POST_DATE, SUM(AMOUNT) AS AMOUNT, CATEGORY_TEXT AS CATEGORY, DESCRIPTION FROM \"_SYS_BIC\".\"psa/ANA_TRANSACTION\" WHERE UPPER(description) LIKE '%" + description + "%' GROUP BY ID, DATE_SQL, POST_DATE, CATEGORY_TEXT, DESCRIPTION ORDER BY ID |
Average Difference in two Consecutive Months |
---|
var_out = SELECT SUM(T2.AMOUNT-T1.AMOUNT) AS AMOUNT, T1.MONTH_INT AS MONTH_INT FROM (SELECT AVG(AMOUNT) AS AMOUNT, MONTH_INT FROM "_SYS_BIC"."psa/ANA_TRANSACTION" GROUP BY MONTH_INT) AS T1, (SELECT AVG(AMOUNT) AS AMOUNT, MONTH_INT FROM "_SYS_BIC"."psa/ANA_TRANSACTION" GROUP BY MONTH_INT) AS T2 WHERE T1.MONTH_INT = T2.MONTH_INT - 1 GROUP BY T1.MONTH_INT; CREATE COLUMN TABLE DATE_RANGE(RANGE INT PRIMARY KEY); |
Prepare the time configuration for report |
---|
INSERT INTO DATE_RANGE VALUES(1); INSERT INTO DATE_RANGE VALUES(3); INSERT INTO DATE_RANGE VALUES(6); INSERT INTO DATE_RANGE VALUES(12); |
Generate transaction report |
---|
TIME_RANGE_OUT = SELECT L.DATE_SQL AS DATE_FROM, D.LAST_DATE AS DATE_TO, R.RANGE AS RANGE FROM "_SYS_BIC"."psa/ATT_TIME_VIEW" AS L, (SELECT MAX(TRAN_DATE) AS LAST_DATE FROM "SYSTEM"."PSA_TRANSACTION") AS D, "SYSTEM"."DATE_RANGE" AS R WHERE DAYS_BETWEEN(L.DATE_SQL, D.LAST_DATE) = R.RANGE * 30; CALL "_SYS_BIC"."psa/PRO_GET_TIME_RANGE"(TIME_RANGE);AVG_OUTPUT = SELECT R.RANGE AS RANGE, AVG(AMOUNT) AS AMOUNT, 'AVG' AS OUTPUT_TYPE FROM "_SYS_BIC"."psa/ANA_TRANSACTION" AS D, :TIME_RANGE AS R WHERE D.DATE_SQL >= R.DATE_FROM AND D.DATE_SQL <= R.DATE_TO GROUP BY R.RANGE;SUM_OUTPUT = SELECT R.RANGE AS RANGE, SUM(AMOUNT) AS AMOUNT, 'SUM' AS OUTPUT_TYPE FROM "_SYS_BIC"."psa/ANA_TRANSACTION" AS D, :TIME_RANGE AS R WHERE D.DATE_SQL >= R.DATE_FROM AND D.DATE_SQL <= R.DATE_TO GROUP BY R.RANGE; var_out = SELECT * FROM :AVG_OUTPUT union SELECT * FROM:SUM_OUTPUT; |
Create the R script procedure |
---|
DROP PROCEDURE "SYSTEM"."GROUP_TRAN"; DROP TYPE "SYSTEM"."DATA_TYPE"; CREATE TYPE "SYSTEM"."DATA_TYPE" AS TABLE( "ID" INTEGER not null, "TRAN_DATE" DATE null, "POST_DATE" DATE null, "DESCRIPTION" NVARCHAR (60) null, "AMOUNT" DOUBLE null, "CATEGORY_TEXT" NVARCHAR(20) null); DROP TYPE "SYSTEM"."DATA_OUTPUT_TYPE"; CREATE TYPE "SYSTEM"."DATA_OUTPUT_TYPE" AS TABLE ( "ID" INTEGER not null, "TRAN_DATE" DATE null, "POST_DATE" DATE null, "DESCRIPTION" NVARCHAR (60) null, "AMOUNT" DOUBLE null, "CATEGORY_TEXT" NVARCHAR(20) null, "CLUSTER_ID" INTEGER null); CREATE PROCEDURE "SYSTEM"."GROUP_TRAN"(IN data_input "SYSTEM"."DATA_TYPE", OUT result "SYSTEM"."DATA_OUTPUT_TYPE" ) LANGUAGE RLANG reads sql data AS BEGIN library(kernlab) model<-kmeans(data_input$AMOUNT, 3) result<-data.frame(data_input, CLUSTER_ID=model$cluster) END; |
temp_var = SELECT "ID","TRAN_DATE","POST_DATE","DESCRIPTION","AMOUNT","CATEGORY_TEXT" FROM "SYSTEM"."PSA_TRANSACTION"; CALL "SYSTEM"."GROUP_TRAN"( :temp_var, var_out); |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
43 | |
25 | |
17 | |
15 | |
10 | |
7 | |
7 | |
6 | |
6 | |
6 |