If else condition in PL SQL

Question

0.00/5 (No votes)

See more:

Below is my requirement:
if category = 16 and count < 5000 then print
and if category is (24 or 35) and count is < 1000 then print

This is my script/data

<pre>
select n.category, count(*)
  from Staff n
 where n.category in (16,24,35)
 group by n.category

What I have tried:

This is my script/data
<pre><pre>
select n.category, count(*)
  from Staff n
 where n.category in (16,24,35)
 group by n.category

Posted 26-Feb-21 18:26pm

varsh12

Updated 26-Feb-21 20:28pm

Add a Solution

2 solutions

Solution 1

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]

Posted 26-Feb-21 19:46pm

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2021-02-26T20:28:00

Solution 2

You can use IF...ELSE (Transact-SQL) - SQL Server | Microsoft Docs[^] or CASE (Transact-SQL) - SQL Server | Microsoft Docs[^]. The choice is yours.

CASE WHEN example:

SQL

SELECT category, cnt, CASE WHEN category = 16 AND cnt<5000 THEN 'category 16 less than 5000 cases'
    WHEN (category = 24 OR category =35) AND cnt<1000 THEN 'category 24, 35 less than 1000 cases'
    ELSE 'Other values' END AS myDescription
FROM
(
    SELECT n.category, count(*) cnt
    FROM Staff n
    WHERE n.category in (16,24,35)
    GROUP BY n.category
) A

Posted 26-Feb-21 20:28pm

Maciej Los

Comments

varsh12 27-Feb-21 7:45am

Thanks Maciej Los

Maciej Los 27-Feb-21 8:24am

You're very welcome.
If my answer was helpful, please accept it (green button).

varsh12 27-Feb-21 10:17am

Yes. if I want to show only those row which is fulfill not all. Any help.
Like if category 16 and 35 are fulfill, then return only those 2 rows.

varsh12 27-Feb-21 11:45am

Now i want to return only those records which satisfied the condition.
category 16 count < 5000 and category 24 count < 1000 then print both but not 35
if category 24 and 35 count is < 1000 but category 16 count > 5000, then return only 24 and 35. like this.
i tried below but multiple oracle errors happening.

DECLARE count_1 number := 5000; Count_2 number:= 1000 ; beginIF (count_1< (SELECT count(*)    FROM staff n    WHERE n.category = 16    GROUP BY n.category) and   (Count_2 < (SELECT count(*)    FROM staff n<pre lang="text">
    WHERE n.category in (2,3) GROUP BY n.category)))
    then 
    select category,count_1 as count from staff where category in (1,2,3);
    
    else 
    (count_1 > (SELECT count(*)
    FROM staff n
    WHERE n.category = 1 GROUP BY n.category) and   (Count_2< (SELECT count(*)
    FROM staff n
    WHERE n.category in (2,3) GROUP BY n.category)))
    --then
    select category,count_1 as count from staff where category in (1,2,3);
 end if;
 end;

Maciej Los 28-Feb-21 6:58am

This sounds like another question - use "Ask a question" widget.