Invalid SQL Statement in Oracle when comparing two queries using $IF$

Question

I am new to Oracle DBMS and I want to try the following:

I have an entity "Flight" with an attribute "DepartureTime", I want to know the number of flights at 2 different times and compare the count, then print which time has more flights

My attempt:

IF (SELECT COUNT(DepartureTime)   
From Flight    
WHERE DepartureTime='23:58')>   
(SELECT COUNT(DepartureTime)   
From Flight  
WHERE DepartureTime='23:00')  
 BEGIN 
PRINT 'First is bigger'    
END   
ELSE  
 BEGIN   
PRINT 'Second is bigger'  
END;

I am getting "Invalid SQL Statement"

Any help would be appreciated.

This is not valid Oracle syntax. This looks like SQL Server. — GMB
– GMB, Commented Nov 21, 2020 at 15:24
@GMB would you mind helping? I am playing with iy on my own and just googled how I can do which resulted in this syntax — Sergio
– Sergio, Commented Nov 21, 2020 at 15:25
Yes, syntax incorrect. If this is a part of larger SQL procedure type thing, pls post it. — Koushik Roy
– Koushik Roy, Commented Nov 21, 2020 at 15:25
I will have access to oracle in around an hour so I didn't test it as of yet, I will let you know what happens when I do. — Sergio
– Sergio, Commented Nov 21, 2020 at 15:46
@KoushikRoy it is working, please put it as answer so I mark it! — Sergio
– Sergio, Commented Nov 22, 2020 at 15:50

Koushik Roy · Accepted Answer · 2020-11-22 16:55:52Z

1

You can try this -

SELECT case when subq1.cnt > subq2.cnt then  'First is bigger'  else 'Second is bigger' end as output 
FROM
(SELECT COUNT(DepartureTime) cnt    From Flight     WHERE DepartureTime='23:58') subq1, 
(SELECT COUNT(DepartureTime) cnt   From Flight   WHERE DepartureTime='23:00')  subq2;

I created two subqueries to count departure times and then compared those counts in outer query using case-when.
Its not the most efficient but it will meet your requirement.

answered Nov 22, 2020 at 16:55

Koushik Roy

7,4392 gold badges18 silver badges36 bronze badges

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

2 Comments

Sergio Over a year ago

thank you! btw if you are interested I posted another question that you might be able to answer too stackoverflow.com/questions/64956724/…

Sergio Over a year ago

accepted your edit, any further help would be appreciated :)

Collectives™ on Stack Overflow

Invalid SQL Statement in Oracle when comparing two queries using $IF$

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related