[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

SQL Conditional field return

Hi,

I have the following SQL query below.

Basically it only returns if the two conditions are met:
fn_rbac_PackageStatusDistPointsSumm.State = 1 and
                        vSMS_DistributionDPStatus.MessageCategory = 76      

So how can I change it so that if vSMS_DistributionDPStatus.MessageCategory = 76 it
returns vSMS_DistributionDPStatus.InsString3 for that column

however it the vSMS_DistributionDPStatus.MessageCategory <> 76
the it returns a ' ' for that field. (So I don't get its contents in
this case).

/*      
==============================================
Check the progress of distributing packages
==============================================

*/

Declare @UserSIDs nvarchar(8)

declare @current_time as nvarchar(20)

set @current_time = convert(varchar, getdate(), 3) + ' ' + convert(varchar, getdate(), 24)
Set @UserSIDs = 'Disabled'

select fn_rbac_PackageStatusDistPointsSumm.PackageID,
	   v_Package.Name as 'Package Name',
      'Package Type (Text)' =
		Case
			when v_Package.PackageType = 0 Then 'Software Distribution Package'
			when v_Package.PackageType = 3 Then 'Driver Package'
			when v_Package.PackageType = 4 Then 'Task Sequence Package'
			when v_Package.PackageType = 5 Then 'Software Update Package'
			when v_Package.PackageType = 6 Then 'Device Setting Package'
			when v_Package.PackageType = 7 Then 'Virtual Package'
			when v_Package.PackageType = 8 Then 'Application'
			when v_Package.PackageType = 257 Then 'Image Package'
			when v_Package.PackageType = 258 Then 'Boot Image Package'
			when v_Package.PackageType = 259 Then 'Operating System Install Package'
		Else
			'Unknown'
		End,
		vSMS_DistributionDPStatus.Name as 'Distribution Point',
		vSMS_DistributionDPStatus.InsString3 as 'Percent Complete',
		@current_time as 'Current Time'
		from fn_rbac_PackageStatusDistPointsSumm(@UserSIDs) INNER JOIN
                      vSMS_DistributionDPStatus ON 
                      fn_rbac_PackageStatusDistPointsSumm.PackageID = vSMS_DistributionDPStatus.ObjectID 
                      INNER JOIN
                      v_Package ON fn_rbac_PackageStatusDistPointsSumm.PackageID = v_Package.PackageID
		  where fn_rbac_PackageStatusDistPointsSumm.State = 1 and 
				vSMS_DistributionDPStatus.MessageCategory = 76

Open in new window

0
whorsfall
Asked:
whorsfall
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>change it so that
   if vSMS_DistributionDPStatus.MessageCategory = 76 it returns vSMS_DistributionDPStatus.InsString3
   it the vSMS_DistributionDPStatus.MessageCategory <> 76 the it returns a ' ' for that field.

SELECT blah, blah, blah, 
   CASE vSMS_DistributionDPStatus.MessageCategory  
      WHEN 76 THEN vSMS_DistributionDPStatus.InsString3 
      ELSE ' ' END, 
... blah, blah, blah..

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
however it the vSMS_DistributionDPStatus.MessageCategory <> 76
 the it returns a ' ' for that field. (So I don't get its contents in

That would never happen with your query because it only returns  vSMS_DistributionDPStatus.MessageCategory = 76 so we can always vSMS_DistributionDPStatus.InsString3 without testing the if vSMS_DistributionDPStatus.MessageCategory = 76
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now