CODEWITHSUNDEEP
sqldatabaseoracle-database
Sam
Sam

Reputation: 2065

SQL Subquery to get first record

I need to execute a query something like below.

SELECT TO_CHAR(ROWNUM),
A.Name,
B.Order,
(SELECT * FROM (
SELECT ROUND(LAST_ORDER_AMOUNT,5) FROM ORDERS WHERE ID=A.id AND REQUEST_LEVEL='N' ORDER BY O_DATE DESC)
WHERE ROWNUM =1) AS AMOUNT
FROM Table1 A LEFT JOIN Table2 B
ON A.TYPE_CODE = B.ENTITY_TYPE

But this gives me A.ID is invalid error in oracle. I need to get the first record from inner query as it will return multiple records.

Can someone please let me know how can i bind these tables to achieve my goal.

Thank you in advance.

Upvotes: 1

Views: 504

Answers (2)

user275683
user275683

Reputation:

You can rewrite subquery using WITH clause, not exactly sure on syntax but should be something like following.

WITH    AmountQuery
      AS (
           SELECT ID
               ,ROUND(LAST_ORDER_AMOUNT, 5) AS AmountValue
               ,ROW_NUMBER() OVER ( ORDER BY O_DATE DESC ) AS RN
            FROM ORDERS
            WHERE REQUEST_LEVEL = 'N'
         )
SELECT TO_CHAR(ROWNUM)
       ,A.Name
       ,B.Order
       ,C.AmountValue
    FROM Table1 A
    LEFT JOIN Table2 B
        ON A.TYPE_CODE = B.ENTITY_TYPE
    LEFT JOIN AmountQuery C
        ON a.ID = c.ID
           AND c.RN = 1

here is SQLFiddle to show how it works.

http://sqlfiddle.com/#!4/696b6/36

Upvotes: 1

vicsana1
vicsana1

Reputation: 369

Probably, LIMIT will do the job for you selecting just one record from the subquery (It worked for me in MySQL. I do not have Oracle, but I think it may be similar). Try something like this:

SELECT TO_CHAR(ROWNUM),
A.Name,
B.Order,
COALESCE( C.AMOUNT ) as AMOUNT,
FROM Table1 A LEFT JOIN Table2 B
ON A.TYPE_CODE = B.ENTITY_TYPE
LEFT JOIN ( SELECT ROUND(LAST_ORDER_AMOUNT,5) AS AMOUNT FROM ORDERS WHERE REQUEST_LEVEL='N' ORDER BY  O_DATE DESC ) C ON C.ID = A.id
group by A.id;

Upvotes: 0

Related Questions