How to set column value based on condition

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Dear All members,

I have the following problem in the SQL Query
Table Structure with Values are:

RequestId(Bigint) FileType(Varchar)            FileName(varchar) Status
1                 DRA                          1_DRA.pdf         Available  
1                 DRA Training

Problem is, I have to set the Column value to 'Available' or 'Not Available' based on the following condition:

1) If File Type is 'DRA' and File name is present or File Type is 'DRA Training' and Filename is not present I have to update set the column status as 'Available'

2) If File Type is 'DRA' and File name is not present or File Type is 'DRA Training' and Filename is present I have to update set the column status as 'Available'

3) If File Type is 'CRA' and File name is not present or File Type is 'CRA Training' and Filename is not present I have to update set the column status as ' Not Available'

What I have tried:

I have written the following code for the same which is not working:
1)

SQL

(SELECT TOP 1 CASE WHEN COALESCE([FileName],'') <> '' THEN 'AVAILABLE'    
            ELSE 'NOT AVAILABLE'     
            END     
            FROM tblScannedFileDetails     
            WHERE RequestID  = 6904
            AND FileType = 'DRA' or FileType='CRA Training'
           ) AS 'CRA Upload Status'

The above always shows Not Available.
2)

SQL

SELECT (CASE 
			WHEN ((COALESCE(C.FileName,'') <> '' and C.FileType = 'DRA') OR (COALESCE(C.FileName,'') = '' and C.FileType='DRA Training'))
			THEN 'AVAILABLE' 
			WHEN ((COALESCE(C.FileName,'') = '' and C.FileType = 'DRA') OR (COALESCE(C.FileName,'') <> '' and C.FileType='DRA Training'))
			THEN 'AVAILABLE'
			WHEN ((COALESCE(C.FileName,'') = '' and C.FileType = 'DRA') OR (COALESCE(C.FileName,'') = '' and C.FileType='DRA Training'))
			THEN 'NOT AVAILABLE'
            --ELSE 'NOT AVAILABLE'     
            END )    
            FROM tblScannedFileDetails C     
            WHERE RequestID  = 6904

This is also not working and returns null or Not Available.

Posted 2-Mar-16 19:40pm

Nischal Bhatt

Updated 2-Mar-16 21:04pm

Maciej Los

v2

Add a Solution

Comments

CHill60 3-Mar-16 5:35am

Are you sure you have stated your requirements correctly? Requirements 1 and 2 boil down to "If File Type is 'DRA' or File Type is 'DRA Training' then set Available" - it doesn't matter if FileName is present or not.
What about the condition where File Type is CRA or CRA Training and FileName IS present? Should that be Available?

Nischal Bhatt 3-Mar-16 5:52am

Chill60: Sorry, that's a Typo from my end it's suppose to be DRA and DRA Training.

1 solution

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 · Answer 1 · 2016-03-02T21:04:00

Check this:

SQL

DECLARE @tblScannedFileDetails TABLE(RequestId Bigint,  FileType Varchar(155), [FileName] Varchar(155), [Status] VARCHAR(30))

INSERT INTO @tblScannedFileDetails (RequestId, FileType, [FileName], [Status]) 
VALUES(1, 'DRA', '1_DRA.pdf', 'Available'),
(1, 'DRA Training', NULL, NULL),
(2, 'CRA', '1_CRA.pdf', NULL),
(2, 'CRA', NULL, NULL)

SELECT RequestId, FileType, [FileName], [Status], [NewStatus] = CASE
	WHEN (FileType = 'DRA' AND NOT [FileName] IS NULL) OR (FileType = 'DRA Training' AND [FileName] IS NULL) THEN 'Available'
	WHEN (FileType = 'DRA' AND [FileName] IS NULL) OR (FileType = 'DRA Training' AND NOT [FileName] IS NULL) THEN 'Available'
	WHEN (FileType = 'CRA' AND NOT [FileName] IS NULL) OR (FileType = 'CRA Training' AND NOT [FileName] IS NULL) THEN 'Available'
	ELSE 'Not Available' END
FROM @tblScannedFileDetails

Returned values:

RequestId	FileType		FileName	Status		NewStatus
1			DRA				1_DRA.pdf	Available	Available
1			DRA Training	NULL		NULL		Available
2			CRA				1_CRA.pdf	NULL		Available
2			CRA				NULL		NULL		Not Available

Is that what you're looking for?

If you would like to update table in single statement, check this: UPDATE from SELECT using SQL Server - Stack Overflow[^]