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

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[^]

How to set column value based on condition

1 solution

Solution 1

Add your solution here

Preview 0

How to set column value based on condition

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us