Oracle SQL : table Joining

Question

For Oracle Database, suppose I have two tables here (Similar structure, but much larger amount of data) Definition below:

create table payments(
    record_no INTEGER;
    cust_no INTEGER;
    amount NUMBER;
    date_entered DATE;
);
insert into payments values(1,3,34.5,sysdate-1);
insert into payments values(2,2,34.5,sysdate-2);
insert into payments values(3,3,34.5,sysdate-18/1440);
insert into payments values(4,1,34.5,sysdate-1);
insert into payments values(5,2,34.5,sysdate-2/24);
insert into payments values(6,3,34.5,sysdate-56/1440);
insert into payments values(7,4,34.5,sysdate-2);
insert into payments values(8,2,34.5,sysdate-1);

create table customer(
    cust_no INTEGER;
    name VARCHAR2;
    zip VARCHAR2;
);
insert into customer values(1,'Tom',90001);
insert into customer values(2,'Bob',90001);
insert into customer values(3,'Jack',90001);
insert into customer values(4,'Jay',90001);

Now I want to generate a report with those columns (Get the first two payment amount and date for each customer order by paydate) :

Cust_no | pay_amount1 | pay_date1 |pay_amount2 | pay_date2

Sample report I want

CUST_NO PAYMENT1    PAYDATE1     PAYMENT2      PAYDATE2
1   34.5    October, 09 2013     0             null
2   34.5    October, 08 2013     34.5      October, 09 2013
3   34.5    October, 09 2013     34.5      October, 10 2013
4   34.5    October, 08 2013     0             null

Can anybody make a correct and efficient Query ? Thanks ahead.

Not only is it slow, the rownum predicate as used in your query will give you a random row. Consult the docs for the correct way to use rownum -- googling for Oracle rownum will probably just throw a load of examples up. — David Aldridge, Oct 10 '13 at 16:23
This query is wrong (at leat on Oracle), two last subqueries give an error `c.cust_no - invalid identifier` - take a look at this demo: http://www.sqlfiddle.com/#!4/bf6e1/5 — krokodilko, Oct 10 '13 at 16:29
@kordirko I noticed that and modified my question, thanks for pointing out, so what's your solution ? — Frank, Oct 10 '13 at 16:32
You don't need the `;` **and** the `/` for DDL statements. You only need the `/`for PL/SQL blocks. In fact your `CREATE TABLE` statements will be executed twice (you probably wondered why you always get a `table already exists error). See here for details: http://stackoverflow.com/a/10207695/330315 — , Oct 10 '13 at 16:39
It is still wrong --> http://www.sqlfiddle.com/#!4/bf6e1/6, this time gives a syntax error: `ORA-00907: missing right parenthesis`. It's hard to optimize q query that doesn't compile, please correct it. — krokodilko, Oct 10 '13 at 16:39
It's not clear what you are trying to achieve. You may want to post sample data and the desired result based on it. Also please indicate which Oracle version you are using. Effectiveness often depends on proper indexing. — PM 77-1, Oct 10 '13 at 16:48

score 3 · Accepted Answer · answered Oct 10 '13 at 16:57

First you need to get your creation script right. The ; terminates a statement not a line inside a statement. Secondly the varchar2 data types needs a length specification: name VARCHAR2(20) instead of name VARCHAR2. Also character literals need to be enclosed in single quotes. '90001' is a character literal, 90001 is a number. Those are two different things.

So this results in the following script:

create table payments(
    record_no INTEGER,
    cust_no INTEGER,
    amount NUMBER,
    date_entered DATE
);

insert into payments values(1,3,34.5,sysdate-1);
insert into payments values(2,2,34.5,sysdate-2);
insert into payments values(3,3,34.5,sysdate-18/1440);
insert into payments values(4,1,34.5,sysdate-1);
insert into payments values(5,2,34.5,sysdate-2/24);
insert into payments values(6,3,34.5,sysdate-56/1440);
insert into payments values(7,4,34.5,sysdate-2);
insert into payments values(8,2,34.5,sysdate-1);

create table customer(
    cust_no INTEGER,
    name VARCHAR2(20),
    zip VARCHAR2(20)
);

insert into customer values(1,'Tom','90001');
insert into customer values(2,'Bob','90001');
insert into customer values(3,'Jack','90001');
insert into customer values(4,'Jay','90001');

Note that it's bad coding practice to not specify the columns in an INSERT statement. It should be insert into customer (cust_no, name, zip) values(1,'Tom','90001'); instead of insert into customer values(1,'Tom','90001');

Now for your query, the following should do you wnat you need:

with numbered_payments as (
  select cust_no, 
         amount, 
         date_entered, 
         row_number() over (partition by cust_no order by date_entered) as rn
  from payments
) 
select c.cust_no,
       c.name, 
       p1.amount as pay_amount1,
       p1.date_entered as pay_date1,
       p2.amount as pay_amount2, 
       p2.date_entered as pay_date2
from customer c
  left join numbered_payments p1 
         on p1.cust_no = c.cust_no 
        and p1.rn = 1
  left join numbered_payments p2 
         on p2.cust_no = c.cust_no 
        and p2.rn = 2;

Note that I used an outer join to ensure that every customer is returned even if there is no or only a single payment for it.

Here is an SQLFiddle with all corrections and the query: http://sqlfiddle.com/#!4/74349/3

score 1 · Answer 2 · answered Oct 10 '13 at 17:17

SELECT * FROM (
    SELECT 
        c.cust_no,
        p.amount as payment,
        p.date_entered as paydate,
        ROW_NUMBER() OVER (PARTITION BY cust_no ORDER BY p.record_no ASC) AS rn
    FROM customer c
        JOIN payments p ON p.cust_no = c.cust_no
    ) t 
WHERE 
    rn <= 2
ORDER BY cust_no, rn;

Will show the 2 records per client you need, in 2 separate lines. If you prefer having it in the same line, then use this query:

SELECT
    cust_no,
    payment1,
    paydate1,
    CASE WHEN nextcli <> cust_no THEN 0 ELSE payment2 END AS payment2,
    CASE WHEN nextcli <> cust_no THEN SYSDATE ELSE paydate2 END AS paydate2
FROM (
    SELECT 
        c.cust_no,
        p.amount as payment1,
        p.date_entered as paydate1,
        ROW_NUMBER() OVER (PARTITION BY c.cust_no ORDER BY p.record_no ASC) AS rn,
        LEAD(c.cust_no, 1, -1) OVER (ORDER BY c.cust_no ASC) as nextcli,
        LEAD(p.amount, 1, 0) OVER (ORDER BY c.cust_no ASC) as payment2,
        LEAD(p.date_entered, 1, NULL) OVER (ORDER BY c.cust_no ASC) as paydate2
    FROM customer c
        JOIN payments p ON p.cust_no = c.cust_no
    ) t 
WHERE 
    rn <= 1
ORDER BY cust_no, rn;

score 0 · Answer 3 · answered Oct 10 '13 at 16:57

The analytic function ROW_NUMBER can help you give a number to each payment :

select cust_no, amount, date_entered,
   row_number() over(partition by cust_no order by date_entered ) rn
from payments ;

Using this I think we can get what you're looking for, something like :

With ordered_payments as (
    select cust_no, amount, date_entered,
       row_number() over(partition by cust_no order by date_entered ) rn
       from payments)
select customer.cust_no, p1.amount, p1.date_entered, p2.amount, p2.date_entered
from customer left join ordered_payments p1 
                     on customer.cust_no = p1.cust_no and p1.rn = 1
              left join ordered_payments p2 
                     on customer.cust_no = p2.cust_no and p2.rn = 2 ;

Oracle SQL : table Joining

3 Answers3