CODEWITHSUNDEEP
sqldatabaseoracle-database
Stikoun
Stikoun

Reputation: 105

Oracle SQL - creating "unstored" procedure

I have this SQL query

SELECT ACCBAL_DATE, ACCBAL_AMOUNT
FROM ACCOUNT_BALANCES t
WHERE ACC_KEY = '964570223'
AND ACCBAL_KEY = '16'
ORDER BY ACCBAL_DATE DESC
FETCH FIRST 1 ROWS ONLY;

It returns one row but I need to use this query for many ACC_KEYS (about 600).

So first way to do that is to run this query about 600x with different ACC_KEY parameter.

The second one is creating a procedure I think.

Procedure which will use variable acc_key and move it to WHERE statement. Issue is that I can't create procedure stored on server because of permissions.

Is there some way to solve it without storing procedure on server?

EDIT: I know the IN clause but that is not what I need. I need something which will run the query about 600x, each execution with another ACC_KEY in WHERE clause and the output should be 600 rows.

Upvotes: 0

Views: 114

Answers (3)

Alex Poole
Alex Poole

Reputation: 191425

when I used them in clause IN, then it will still return only one row. I want to return only one row because without limitations it returns about 100 rows, so I want only the first row which has needed data. For each ACC_KEY it should return only one row

You can still do that with an IN() clause listing all 600 key values:

select acc_key,
  max(accbal_date) as accbal_date,
  max(accbal_amount) keep (dense_rank last order by accbal_date) as accbal_amount
from account_balances t
where acc_key in ('964570223', '964570224', ...) -- up to 1000 allowed
and accbal_key = '16'
group by acc_key
order by acc_key;

This is using aggregate functions and grouping by the key, so you will get one row per key, with the data for the most recent date.

Read more about keep/last.

It would still be better to use a collection or a table - maybe an external table loaded from your Excel sheet, saved as a CSV; not least because you can only supply 1000 entries to a single IN() clause - or any expression list - but also for performance and readability/maintenance reasons.

Upvotes: 1

Esjay IT
Esjay IT

Reputation: 85

Step-1 : CREATE TABLE WITH 1 COLUMN ACC_KEY STORES ALL LIST OF ACC_KEY.

Step-2 : Code Run.

SELECT T.ACCBAL_DATE, T.ACCBAL_AMOUNT
FROM ACCOUNT_BALANCES t
WHERE EXISTS(SELECT A.ACC_KEY FROM <TABLENAME> A WHERE A.ACC_KEY=T.ACC_KEY)
AND T.ACCBAL_KEY = '16'
ORDER BY T.ACCBAL_DATE DESC
FETCH FIRST 1 ROWS ONLY;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can store the keys in a table or use a derived table in the query. I would recommend something more like this:

WITH keys as (
      SELECT '964570223' as ACC_KEY FROM DUAL UNION ALL
      . . . 
     )
SELECT k.ACC_KEY, MAX(ab.ACCBAL_DATE) as ACCBAL_DATE,
       MAX(ab.ACCBAL_AMOUNT) KEEP (DENSE_RANK FIRST ORDER BY ab.ACCBAL_DATE DESC) as ACCBAL_AMOUNT
FROM keys k LEFT JOIN
     ACCOUNT_BALANCES ab
     ON ab.ACC_KEY = k.ACC_KEY AND
        ab.ACCBAL_KEY = '16'
GROUP BY k.ACC_KEY;

Of course the CTE keys could be replaced with a table that has the accounts of interest.

Note that this replaces your logic with aggregation logic. You just want the most recent date and balance, which Oracle supports using the KEEP keyword.

Upvotes: 1

Related Questions