We help IT Professionals succeed at work.

SQL Conditional field return

377 Views
Last Modified: 2014-10-27
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

Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.