Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Cumulative sum and Lag Scenario
Hello All
, I have a scenario , I am trying to derive SQL to enable us calculate running balance.
oracle version : PL/SQL Release 12.2.0.1.0 - Production
CREATE TABLE ADJUSTMENT3
(
R_ORDER_NO NUMBER(9),
CORE_TRANSDATE DATE,
INSTALL_BILLING_LINE_ID NUMBER,
TXN_DATE TIMESTAMP(6),
ADJ_ORDER_NO NUMBER,
ADJ_AMOUNT NUMBER
);
SET DEFINE OFF;
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706800, TO_DATE('7/31/2019 11:39:54 AM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706800, TO_DATE('7/31/2019 11:39:54 AM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706801, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706802, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706803, TO_DATE('7/31/2019 12:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706804, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706805,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706805, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706806,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706805,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706806, TO_DATE('7/31/2019 1:39:54 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706805,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706806,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 2:39:55.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706807,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706807, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:00:47.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 3);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:01:12.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706806,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706805,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 1:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706804,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 2:39:55.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706807,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/18/2019 8:49:01.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145322061,
95.35);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706801,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706802,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:39:54.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706803,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:01:06.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 8);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 12:00:51.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 7);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 11:39:54.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706800,
-10.59);
Insert into ADJUSTMENT3
(R_ORDER_NO, CORE_TRANSDATE, INSTALL_BILLING_LINE_ID, TXN_DATE, ADJ_ORDER_NO,
ADJ_AMOUNT)
Values
(145706808, TO_DATE('7/31/2019 2:39:55 PM', 'MM/DD/YYYY HH:MI:SS AM'), 75987584, TO_TIMESTAMP('7/31/2019 2:39:55.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 145706808,
-10.63);
COMMIT;
----------------------------------------------------------------------------
Expected output
Answers
-
Thanks for posting the DDL and DML statements.
Don't forget to also post your full 4 digit database version (from the link on --> ) #5, as the answer you give will determine the answers you get.
5) Database Version and IDE Version
Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.If you're not sure what it is you can do the following:select * from v$version;in an SQL*Plus session and paste the results.
Also try to format your code from #10 in the link I posted
10) Formatting code and dataEnsure you format your code and data so that it's readable on the forum.On the new forum, this is now achieved using the Syntax Highlighting option, by copy/pasting your code to the editor, highlighting it, then clicking the "insert" (>>) button, selecting "Syntax Highlighting >" and choosing SQL (or the appropriate language). Your code should then appear formatted and highlighted. (although day 1 this still appears buggy)
DECLARE v_str VARCHAR2(100); BEGIN v_str := 'This is some sample code'; END;
The other option at the minute seems to be pasting your code/data and ensuring you change it to Courier New font, so that the indentation stays correctly.
In the meantime, here's what I mean
drop table adjust2;create table adjust2( ranked number, ret_order_no number(9), adj_order_no number, adj_amount number (10,2), debit number (10,2), net number (10,2), cr number(10,2));insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (1, 145706800, 145322061, 95.35);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (2, 145706800, 145706800, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (3, 145706801, 145322061, 95.35);insert into adjust2 (ranked, ret_order_no, adj_amount) values (6, 145706801, 8);insert into adjust2 (ranked, ret_order_no, adj_amount) values (5, 145706801, 7);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (4, 145706801, 145706800, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (7, 145706801, 145706801, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (8, 145706801, 145706803, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (9, 145706801, 145706802, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (10, 145706802, 145322061, 95.35);insert into adjust2 (ranked, ret_order_no, adj_amount) values (13, 145706802, 8);insert into adjust2 (ranked, ret_order_no, adj_amount) values (12, 145706802, 7);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (14, 145706802, 145706801, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (11, 145706802, 145706800, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (15, 145706802, 145706803, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (16, 145706802, 145706802, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (17, 145706803, 145322061, 95.35);insert into adjust2 (ranked, ret_order_no, adj_amount) values (20, 145706803, 8);insert into adjust2 (ranked, ret_order_no, adj_amount) values (19, 145706803, 7);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (18, 145706803, 145706800, -10.59);insert into adjust2 (ranked, ret_order_no, adj_order_no, adj_amount) values (23, 145706803, 145706802, -10.59);commit;
-
Hey I didn't provide an answer just information to update your thread. Please un-mark my reply as it is not answer to your question.
-
I thought the button was saying "correct answer" , i tried to unmark it ...
-
Playing around with it but either you'll need to do "hierarchical" query or a combo of that plus lead/lag. Also even just basic testing, indicated a loop in your data if I use
connect by prior adj_order_no = ret_order_no
-
Mr.Jondee wrote:I thought the button was saying "correct answer" , i tried to unmark it ...
No worries. It's back to being "unanswered". I'm done for the day at work but I'm sure other's will pick up the slack, based on my reply prior to this one.
-
You seem to assume that the rows are ordered somehow. What determines which row is first, which is second, etc.? Don't say "the order in which they are inserted in the table" - that answer is a failing answer in the first quiz in the first intro course in SQL.
Your image (Excel?) shows two date columns, which don't exist in your CREATE TABLE statement. Is the order of rows somehow related to those dates?
-
Hi,
Mr.Jondee wrote:Hello All
, I have a scenario , I am trying to derive SQL to enable us calculate running balance.oracle version : PL/SQL Release 12.2.0.1.0 - ProductionCREATE TABLE ADJUST2( RANKED NUMBER, RET_ORDER_NO NUMBER(9), ADJ_ORDER_NO NUMBER, ADJ_AMOUNT NUMBER (10,2), DEBIT NUMBER (10,2), NET NUMBER (10,2), CR NUMBER(10,2) ); Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (1, 145706800, 145322061, 95.35);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (2, 145706800, 145706800, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (3, 145706801, 145322061, 95.35);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_AMOUNT) Values (6, 145706801, 8);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_AMOUNT) Values (5, 145706801, 7);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (4, 145706801, 145706800, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (7, 145706801, 145706801, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (8, 145706801, 145706803, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (9, 145706801, 145706802, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (10, 145706802, 145322061, 95.35);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_AMOUNT) Values (13, 145706802, 8);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_AMOUNT) Values (12, 145706802, 7);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (14, 145706802, 145706801, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (11, 145706802, 145706800, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (15, 145706802, 145706803, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (16, 145706802, 145706802, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (17, 145706803, 145322061, 95.35);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_AMOUNT) Values (20, 145706803, 8);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_AMOUNT) Values (19, 145706803, 7);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (18, 145706803, 145706800, -10.59);Insert into ADJUST2 (RANKED, RET_ORDER_NO, ADJ_ORDER_NO, ADJ_AMOUNT) Values (23, 145706803, 145706802, -10.59);COMMIT;----------------------------------------------------------------------------Expected output
Business Requirement:
1. CR and ADJ_AMOUNT are the same , so we can populate CR with same values from ADJ_AMOUNT.
2. THE RET_ORDER_NO with 145706800 has the main amount 95.35 ( we make adjustment to it consequently)
3. To get 95.35 for DR in row 1 = 0 + 95.35
4. 8 and 7 are payments in row 6 and 55. 10.59 will be minus-ed from (row 6 + row 5) and nothing will be taken from net
6 subsequently , 4.41 is the remainder , and when another debit of 10.59 , then deduct net from 69.76 - (10.59 - 4.41) = 63.58
Sorry, I don't understand how you get those results from that data.
How do you get columns 3-5 (the dates and the number 75987584)?
When are the DR and NET columns NULL and when do you want them to have values?
Why don't you want any output for RANKED 17-23?
-
order of rows can be related to the dates , I ranked them using using the RANKED column , so when using analytical functions , i can order by RANKED.
-
@Frank Kulash
Sorry, I don't understand how you get those results from that data.75987584 --- from the spreadsheet can be discarded , it means unique number for all transactions relating to that account .
How do you get columns 3-5 (the dates and the number 75987584)?
As i mentioned above this can be discarded , the query i am working o can partition by column 2
When are the DR and NET columns NULL and when do you want them to have values?
DR and NET should be computed values , the first row tells us the amount in the plan , then sixth row in the spreadsheet (10.59) tells us that there was a payment of (8 + 7 ) and deduct from it , else deduct from the prior balance amount remaining.
Why don't you want any output for RANKED 17-23?
I took a snapshot of data , just to make it easier to copy and paste data
-
Hi,
Mr.Jondee wrote:...DR and NET should be computed values , the first row tells us the amount in the plan , then sixth row in the spreadsheet (10.59) tells us that there was a payment of (8 + 7 ) and deduct from it , else deduct from the prior balance amount remaining.Why don't you want any output for RANKED 17-23?I took a snapshot of data , just to make it easier to copy and paste data
Sorry, I still don't understand.
