如何根据条件设置列值 [英] How to set column value based on condition

问题描述

亲爱的所有成员，



我在SQL查询中遇到以下问题

表结构的值为：

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                     

问题是，我必须根据以下条件将Column值设置为'Available'或'Not Available'：



1）如果文件类型是'DRA'并且文件名存在或文件类型是'DRA培训'且文件名不存在我必须更新将列状态设置为'可用'



2）如果文件类型为'DRA'且文件名不存在或文件类型为'DRA培训'且文件名存在，我必须更新将列状态设置为'可用'



3）如果文件类型为'CRA'且文件名不存在或文件类型是'CRA培训'而文件名不存在我必须更新将列状态设置为'不可用'



我尝试过：



我为同样的代码编写了以下代码：

1）

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)

(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'

上面总是显示不可用。

2）

The above always shows Not Available.
2)

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

这也不起作用并返回null或Not Available。

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

推荐答案

检查：

Check this:

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

这就是你要找的东西吗？ />


如果您想在单个语句中更新表，请检查：使用SQL Server从SELECT更新 - Stack Overflow [ ^ ]

这篇关于如何根据条件设置列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！