troubleshooting Question

SQL Conditional field return

Avatar of whorsfall
whorsfallFlag for Australia asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
2 Comments1 Solution379 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros