How to get 'IS NULL' where clause values if initial condition returns none in oracle

where to watch how to get away season 6
when does season 5 of how to get away start on netflix
htgawm season 6 release date
htgawm cast season 5
htgawm season 6 cast
alfred enoch
cast of htgawm season 4
charlie weber

I have a table with values as follows

![ID S_ID NAME 1 James 2 2455 Patrick 3 2566 Sam 4 25988 Rick]1

My requirement is to get the NAME based on S_ID and if the given S_ID is not in the table then get the NAME with no S_ID

EX: SELECT NAME FROM STUDENTS WHERE S_ID=2455

The result would be Patrick

SELECT NAME FROM STUDENTS WHERE S_ID=2411

The result should be James

I have tried

select NAME from STUDENTS where S_ID=2455 or S_ID IS NULL

It gives be bot James and Patrick

I would really appreciate any help

Here's one option:

SQL> set ver off
SQL> with students (id, s_id, name) as
  2    (select 2, 2389, 'Patrick' from dual union all
  3     select 1, null, 'James'   from dual union all
  4     select 3, 2566, 'Sam'     from dual
  5    )
  6  select name from students where s_id = &&par_s_id
  7    and exists (select null from students where s_id = &&par_s_id)
  8  union all
  9  select name from students where s_id is null
 10    and not exists (select null from students where s_id = &&par_s_id);
Enter value for par_s_id: 2389

NAME
-------
Patrick                               --> because S_ID = 2389 exists in a table

SQL> undefine par_s_id
SQL> /
Enter value for par_s_id: 123456

NAME
-------
James                                 --> because S_ID = 123456 doesn't exist

SQL>

As you already have the table, you'd use code from line #6 onward.

How to Get Away with Murder (TV Series 2014–2020), S06 E13 � What If Sam Wasn't the Bad Guy This Whole Time? Created by Peter Nowalk. With Viola Davis, Billy Brown, Jack Falahee, Aja Naomi King. A group of ambitious law students and their brilliant criminal defense professor become involved in a twisted murder plot that promises to change the course of their lives.

@Joe,

As per my understanding if S_ID is found in the STUDENT table it should return the NAME of the student, if S_ID is not found the query should return a message as 'No #S_ID'.

Please let me know if I am making any mistake in understanding your question !

[Solution] Its a Tsql so we can use it as is in the sql or in any language. And note I have tested it in MSSQL but the approach remains the same.

declare @sidInput int set @sidInput = 2455 if exists (select NAME from STUDENTS where S_ID = @sidInput) begin select NAME from STUDENTS where S_ID = @sidInput end else begin declare @outputMsg varchar(20) set @outputMsg = 'No ' + convert( varchar(10), @sidInput) select top 1 @outputMsg as NAME from STUDENTS where S_ID not in ( @sidInput ) end

[Test Cases]

1) For Input @sidInput = 2455

Output - NAME as Patrick

2) For Input @sidInput = 2411

Output - NAME as James

3) For Input @sidInput = 2222 #S_ID value not found in STUDENT table

Output - NAME as No 2222

Let me know if it works for you.

Regards,

Arnab

How to Get Away With Murder - Trailer, S06 E03 � Do You Think I'm a Bad Man? GET asks for a User ID and password in order to sign in. These are based on your 4-digit PIN and your Campus Identification Number (CIN). The User ID for GET is your 9-digit CIN. Your CIN can be found on emails from the Admissions Office, or if available, your Golden Eagle Card (student ID card).

@Joe,

try this t-sql (and pardon me I was using maria db - so used it ) . basically it remains the same as for my above response its just that I have updated the else block. ( Note - If you are using MySQL can simply use this as it for testing )

DROP PROCEDURE IF EXISTS prod_studentName; DELIMITER $$ CREATE PROCEDURE`prod_studentName`()`

BEGIN

declare sidValue int;
set sidValue = 2389;
if exists (select NAME from STUDENT where S_ID = sidValue ) 
then
    select NAME from STUDENT where S_ID = sidValue;
else
    select NAME from STUDENT where S_ID is NULL;
end if;`

END$$

DELIMITER ;

refer to the attached image for the table inputs -

test outcomes

positive test - lookup for s_id = 2389

outcome -

negative test - lookup for s_id = 7777 this id doesn't even exist at all

outcome -

hope this helps you

Watch How to Get Away with Murder TV Show, S05 E15 � Please Say No One Else Is Dead To get the best Windows 10 experience and take advantage of features like passwordless sign-on through Windows Hello, you'll want to purchase a new Windows 10 PC (or one released after July 2015

How to Get Away With Murder, S05 E08 � I Want to Love You Until the Day I Die Get the best gaming deals, reviews, product advice, competitions, unmissable gaming news and more! Thank you for signing up to GamesRadar+. You will receive a verification email shortly.

How to Get a Break on College Tuition: Just Ask, S05 E05 � It Was the Worst Day of My Life How to get free hacks. As you know that there are classic methods to get free hacks, those methods can cost you a lot of your cash just follow the beneath simple ways to get free hacks. 1. From free Roblox hack generator, page open the free robux page. 2. From that page enter the necessary measures to get the robux 3. Enter the username / email

Free Credit Reports, S04 E04 � Was She Ever Good at Her Job? It's quick and easy. You don't need insurance and free testing is available. Anyone who wants a test, can now get one. It's especially important to get tested if: You are experiencing symptoms (fever, cough, shortness of breath, chills, sore throat, muscle pain, shivering, headache, or new loss of taste or smell)

Comments
  • Thank you @Littlefoot
  • When s_id was not found, I need the name of the student who doesn't have the s-id. Even if there are more than 1 result, I need all of them.