CODEWITHSUNDEEP
oracle-database
Rafiki
Rafiki

Reputation: 698

How do I set a variable as the return value of an Oracle SQL statement?

I'm using a reporting program in which I give it the SQL statement and the values selected, become the variables. For example in this case I'm trying to determine whether a record already exists or not. If it doesn't, return RecordExists with a value of 0; otherwise 1.

This is much simpler (to me) using SQL Server, but I'm trying to figure out why the below will not work. The syntax error generated suggests I need an "INTO" clause with my SELECT RecordExists FROM Dual; line even though I'm not trying to assign a value at that point.

DECLARE 
  RecordExists NUMBER;
BEGIN
 SELECT COUNT(*) INTO RecordExists FROM Times
 WHERE Times.OrderNumber = '123456789' AND Times.StopSequence = '1';
 IF (RecordExists >= 1)
THEN
 SELECT 1 INTO RecordExists FROM dual;
 SELECT RecordExists FROM dual;
ELSE
 SELECT 0 INTO RecordExists FROM dual;
 SELECT RecordExists FROM dual;
END IF;
END;

Upvotes: 0

Views: 56

Answers (1)

deanosaur
deanosaur

Reputation: 611

Could you use something a bit simpler, like this:

SELECT CASE 
  WHEN count(*) > 0 then 1 
  ELSE 0 END 
FROM times WHERE OrderNumber = '123456789' AND Times.StopSequence = '1' ;

Upvotes: 1

Related Questions