How to declare and assign value to a variable before query?

Question

I have a complicated query that has to use a number called SubUnitRate. This variable comes from another table with special condition. in short we have:

DECLARE
SUBUNITRATE   NUMBER;
BEGIN
SELECT NVL (NULLIF (CU.SUBUNITRATE, 0), 1)
 INTO SUBUNITRATE
 FROM CURRENCYS CU
      JOIN ACCOUNTS ACC ON CU.ID = ACC.CURRENCY
WHERE ACC.ID = :ACCOUNTID;
END;
SELECT SUBUNITRATE * 100 FROM DUAL;

My goal is to acquire the result of(in simple case):

SELECT SUBUNITRATE * 100 FROM DUAL;

But how is that possible?

mayby you could create a stored fucntion submitRate(varchar2 accountID) — Turo
– Turo, Commented Apr 3, 2016 at 12:29
Or don't use PL/SQL at all and make this a subquery instead. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 3, 2016 at 12:48
By "simplifying" your query you have made it hard for us to understand what you are trying to achieve and so harder for use to help you. Please provide an actual explanation of your actual needs. — APC
– APC, Commented Apr 3, 2016 at 12:49

APC · Accepted Answer · 2016-04-03 12:53:03Z

2

Assuming you want to use the value of SUBUNITRATE multiple times in the same query you could use the WITH clause:

with cte as ( 
   select case 
            when CU.SUBUNITRATE = 0 then 1 
            else CU.SUBUNITRATE 
          end as SUBUNITRATE
   FROM CURRENCYS CU
   JOIN ACCOUNTS ACC ON CU.ID = ACC.CURRENCY
   WHERE ACC.ID = :ACCOUNTID
    )
select cte.SUBUNITRATE * 100
from cte;

answered Apr 3, 2016 at 12:53

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-04-03 12:16:05Z

2

A PL/SQL block cannot return the results of a query as a query. Instead, you can print the results out.

So, does this do what you want?

DECLARE
    SUBUNITRATE   NUMBER;
BEGIN
    SELECT NVL(NULLIF(CU.SUBUNITRATE, 0), 1)
    INTO SUBUNITRATE
    FROM CURRENCYS CU JOIN
         ACC
         ON CU.ID = ACC.CURRENCY
    WHERE ACC.ID = :ACCOUNTID;

    DBMS_OUTPUT.PUT_LINE(SUBUNITRATE * 100)
END;

answered Apr 3, 2016 at 12:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Khoda Over a year ago

As I said, the variable has to be used in more complicated query that simplified as 'SUBUNITRATE * 100'. so I need to use it multiple of times afterwards.

score 0 · Accepted Answer · 2016-04-03 22:39:43Z

0

No need for PL. APC' solution, simplified and in a form you can use directly in your query (wherever you would say ... = subunitrate, say ... = (select sur from cte) instead - including the parentheses):

with cte_prelim as (select subunitrate from ... etc.),
cte (sur) as select case when subunit rate is null or subunitrate = 0 then 100
                    else subunitrate * 100 end from cte_prelim)
select...  (your query where you need to use the value)

edited Apr 3, 2016 at 22:39

answered Apr 3, 2016 at 18:03

user5683823

Collectives™ on Stack Overflow

How to declare and assign value to a variable before query?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related