CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
RoyalSwish
RoyalSwish

Reputation: 1573

Oracle - Using variables in SELECT statement

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.

Upvotes: 0

Views: 1765

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

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;

Upvotes: 1

Related Questions