Oracle - Using variables in SELECT statement

Question

My background in SQL is SQL Server, so forgive me for using Oracle in a similar way to it. I need to use a variable so I can use the value stored in it to perform a separate SELECT query. My aim is to find a percentage value and the variable will hold the total value of a table.

DECLARE
  v_Count INT;

BEGIN

--get total rows
SELECT COUNT(OrderID)
INTO v_Count
FROM OrderedEquipment;

--find percentage of equipment ordered
SELECT a.Equip_Name, COUNT(b.EquipmentID), ((COUNT(b.EquipmentID)*1.0)/(v_Count*1.0)*100)
FROM Equipment a
LEFT OUTER JOIN OrderedEquipment b ON a.EquipmentID = b.EquipmentID
GROUP BY a.Equip_Name;

END;

SQL Developer will then throw this error:

Error report -
ORA-06550: line 10, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

I tried looking for solutions to this, and stumbled on to this post:

How do I use variables in Oracle SQL Developer?

But none of the answers really solved my problem. I tried using the bind variables example but that didn't work.

In Oracle 11g one has always to use select into. There is no exceptions and no automatic result set printing. The good starting point is PL/SQL Static SQL. — user272735
– user272735, Commented Feb 21, 2015 at 19:13

Ponder Stibbons · Accepted Answer · 2015-02-21 19:48:08Z

The simplest way is not using variable at all

SELECT a.Equip_Name, COUNT(b.EquipmentID), 
    (COUNT(b.EquipmentID)*1.0)/((SELECT COUNT(OrderID) cnt FROM OrderedEquipment)*1.0)*100
  FROM Equipment a
  LEFT OUTER JOIN OrderedEquipment b ON a.EquipmentID = b.EquipmentID
  GROUP BY a.Equip_Name;

You can also in your block select data into 3 variables

... a.Equip_Name into v1, COUNT(b.EquipmentID) into v2,
  ((COUNT(b.EquipmentID)*1.0)/(v_Count*1.0)*100) into v3 ...

and list them with

dbms_output.put_line(v1||' '||v2||' '||v3);

Edit - this query should be faster:

with counter as (select count(OrderID) cnt from OrderedEquipment)
select a.Equip_Name, count(b.EquipmentID), 
    (count(b.EquipmentID)*1.0)/(max(counter.cnt)*1.0)*100
  from Equipment a
    left join OrderedEquipment b ON a.EquipmentID = b.EquipmentID
    cross join counter
  group by a.Equip_Name;

Collectives™ on Stack Overflow

Oracle - Using variables in SELECT statement

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related