CODEWITHSUNDEEP
sqloracle-database
ERKSMTY
ERKSMTY

Reputation: 135

SELECT item, date WHERE date < sysdate - 60

Here's my sql script. I'm trying to pull all items that haven't been invoiced in 60 days. It's only returning one line. Any idea why? Thanks for your help!

SELECT oel.ordered_item Part_No,
       MAX(rca.trx_date) AS "Last Invoice Date"
FROM    oe_order_lines_all oel,
        ra_customer_trx_all rca
WHERE rca.trx_date < trunc(sysdate)-60
GROUP BY oel.ordered_item

Upvotes: 0

Views: 2124

Answers (2)

LONG
LONG

Reputation: 4620

You should add condition for this line for ',', which is inner join here,

SELECT oel.ordered_item Part_No,
       MAX(rca.trx_date) AS "Last Invoice Date"
FROM    oe_order_lines_all oel,
        ra_customer_trx_all rca  
WHERE rca.trx_date < trunc(sysdate)-60 --------shoud be a condition connects rca and oel
GROUP BY oel.ordered_item

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270633

I don't know why only one row is returned. But you are missing join conditions. This would be obvious if you used proper JOIN syntax.

Based on your description, the logic you want is more like this:

SELECT oel.ordered_item as Part_No,
       MAX(rca.trx_date) AS "Last Invoice Date"
FROM oe_order_lines_all oel LEFT JOIN
     ra_customer_trx_all rca
     ON oel.?? = rca.??
GROUP BY oel.ordered_item
HAVING MAX(rca.trx_date) < trunc(sysdate) - 60 OR MAX(rca.trx_date) IS NULL;

The ?? is for the columns used for joining the tables.

Upvotes: 1

Related Questions