CODEWITHSUNDEEP
sqloracle-databasecommon-table-expression
Matt
Matt

Reputation: 15071

Oracle SQL get values on the same row using CTE

I have a small table balance:

TYPE    BNO    AMT
DEB-AA  1111   50
CRED-AA 2222   -50
CRED-AA 3333   -20
DEB-AA  4444   20

Currently my output is this:

DEBNUM        DEBAMT   CREDNUM       CREDAMT
DEB-AA 1111   50       NULL          NULL
NULL          NULL     CRED-AA 2222  -50
NULL          NULL     CRED-AA 3333  -20
DEB-AA 4444   20       NULL          NULL

From this query:

SELECT 
CASE WHEN SUBSTR(TYPE, 1, 3) = 'DEB' THEN TYPE||' '|| BNO ELSE NULL END AS "DEBNUM",
CASE WHEN SUBSTR(TYPE, 1, 3) = 'DEB' THEN AMT ELSE NULL END AS "DEBAMT",
CASE WHEN SUBSTR(TYPE, 1, 4) = 'CRED' THEN TYPE||' '|| BNO ELSE NULL END AS "CREDNUM",
CASE WHEN SUBSTR(TYPE, 1, 4) = 'CRED' THEN AMT ELSE NULL END AS "CREDAMT"
FROM balance

I want my output to look like this:

DEBNUM        DEBAMT   CREDNUM       CREDAMT
DEB-AA 1111   50       CRED-AA 2222  -50
DEB-AA 4444   20       CRED-AA 3333  -20

Now I think it requires a CTE but I cant get it to run with one as i dont have that much experience creating them.

EDIT

When introducing this to larger sets of data that dont have both a CRED and a DEB it is pulling the data back correctly. but it is not linking the rows that do have both a deb and cred values. For example

Current Output:

DEBNUM        DEBAMT   CREDNUM       CREDAMT
DEB-AA 6666   80       CR-QS 2222   -50
DEB-AA 5555   150      CR-QS 4444   -20
DEB-AA 7777   70        
DEB-AA 8888   200       
DEB-AA 9999   60    
DEB-AA 1111   50    
DEB-AA 3333   20

Desired Output:

DEBNUM        DEBAMT   CREDNUM       CREDAMT
DEB-AA 1111   50       CR-QS 2222   -50
DEB-AA 3333   20       CR-QS 4444   -20
DEB-AA 7777   70       NULL         NULL
DEB-AA 8888   200      NULL         NULL
DEB-AA 9999   60       NULL         NULL
DEB-AA 6666   80       NULL         NULL
DEB-AA 5555   150      NULL         NULL

Now i think this can be achieved by using an extra field which links a CRED to a DEB. Here is an updated sample data:

TYPE    BNO   AMT  DEBBNO
DEB-AA  1111  50   NULL
CRED-AA 2222  -50  1111
CRED-AA 3333  -20  4444
DEB-AA  4444  20   NULL
DEB-AA  7777  70   NULL
DEB-AA  8888  200  NULL
DEB-AA  9999  60   NULL 
DEB-AA  6666  80   NULL  
DEB-AA  5555  150  NULL

Upvotes: 1

Views: 156

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

Data:

CREATE TABLE balance(
   TYPE VARCHAR(18) NOT NULL 
  ,BNO  INTEGER  NOT NULL
  ,AMT  INTEGER  NOT NULL
);
INSERT INTO balance(TYPE,BNO,AMT) VALUES ('DEB-AA',1111,50);
INSERT INTO balance(TYPE,BNO,AMT) VALUES ('CRED-AA',2222,-50);
INSERT INTO balance(TYPE,BNO,AMT) VALUES ('CRED-AA',3333,-20);
INSERT INTO balance(TYPE,BNO,AMT) VALUES ('DEB-AA',4444,20);

Query:

WITH deb AS
(
  SELECT 
     TYPE || ' ' ||  BNO AS DEBNUM
    ,AMT AS DEBAMT
    ,ROW_NUMBER() OVER(ORDER BY BNO ASC) AS rn
  FROM balance
  WHERE TYPE LIKE 'DEB%'
), cred AS
(
  SELECT 
     TYPE || ' ' ||  BNO AS CREDNUM
    ,AMT AS CREDAMT
    ,ROW_NUMBER() OVER(ORDER BY BNO ASC) AS rn
  FROM balance
  WHERE TYPE LIKE 'CRED%'
)
SELECT d.DEBNUM, d.DEBAMT, c.CREDNUM, c.CREDAMT
FROM deb d
JOIN cred c
  ON d.rn = c.rn;

SqlFiddle Demo

Keep in mind that if table contains different number of DEB/CRED you may need FULL OUTER JOIN.

EDIT:

WITH deb AS
(
  SELECT 
     TYPE || ' ' ||  BNO AS DEBNUM
    ,AMT AS DEBAMT
    ,BNO
  FROM balance
  WHERE TYPE LIKE 'DEB%'
), cred AS
(
  SELECT 
     TYPE || ' ' ||  BNO AS CREDNUM
    ,AMT AS CREDAMT
    ,DEBBNO
  FROM balance
  WHERE TYPE LIKE 'CRED%'
)
SELECT d.DEBNUM, d.DEBAMT, c.CREDNUM, c.CREDAMT
FROM deb d
LEFT JOIN cred c
  ON d.BNO = c.DEBBNO;

SqlFiddleDemo2

Upvotes: 1

Related Questions