How to use Created VAR from SELECT in WHERE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use Created VAR from SELECT in WHERE

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2009-09-11 : 14:24:51

In the code below is there away I can use the var I created "Category" in the select statment, down in the where statement?

declare @Category varchar(50)

select distinct a.HistoryDate
     , a.UserID
     , a.UserChangeComment
     , c.StdDeptDesc             as Department
     , b.chargecode
     , d.PrimaryDesc
     , Category= 
CASE 
 WHEN e.FieldID =17 and e.OldValue =0 and e.NewValue =1 THEN 'Reactivation'
  WHEN e.FieldID =17 and e.OldValue =1 and e.NewValue =0 THEN 'Inactivation'
   WHEN a.IsAddedtoCDM =1 THEN 'Additions'
    ELSE 'Changes'
END

from cdm_TransactionHistory          a
    ,cdm_CDMMaster                   b
    ,FusionCommon.dbo.osit_DeptInfo  c
    ,cdm_CDMCommon                   d
    ,cdm_TransactionHistoryDetail    e


where a.cdmid = 36680
and a.historydate >='2009-08-01 00:00:00.000' 
and a.historydate <='2009-08-31 23:59:59:997'
and a.CDMMasterID = b.CDMMasterID
and a.CDMID = b.CDMID
and a.CDMID = c.CDMID
and b.DeptID = c.DeptID
and a.CDMMasterID = d.CDMMasterID
and b.CDMMasterID = d.CDMMasterID
and a.Transactionhistoryid = e.transactionhistoryid  
--order by historydate, Category DESC
order by Category DESC

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 14:34:48

you mean to filter the rows based on the resolved value of the {category] expression? Like this?


where ....
and   CASE 
 WHEN e.FieldID =17 and e.OldValue =0 and e.NewValue =1 THEN 'Reactivation'
  WHEN e.FieldID =17 and e.OldValue =1 and e.NewValue =0 THEN 'Inactivation'
   WHEN a.IsAddedtoCDM =1 THEN 'Additions'
    ELSE 'Changes'
END = @category

Be One with the Optimizer
TG

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2009-09-11 : 15:46:09

Yes, but I'm not using the @category, I need it to be on the
'Category' = from the select statement

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2009-09-11 : 15:47:51

or is there a way I can populate @category based off an CASE?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 16:25:29

Sorry, I can't tell what you are trying to do. How do you want to use this variable in the WHERE clause? You mean like:
and @category = 'Additions'

If so you'll just need to repeat the CASE expression as I posted above and forget trying to use your variable.

Be One with the Optimizer
TG

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2009-09-11 : 16:32:32

yeah thats how I want to use it, but if you look closely Im actually not using the one I declare '@Category', but rather I'm defining it in the SELECT statement has just 'Category'= so I'm unable to reference it in the where statement

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 16:38:14

And that is why TG said use your CASE again in where clause...

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 17:01:22

Yeah, like this:


select distinct a.HistoryDate
     , a.UserID
     , a.UserChangeComment
     , c.StdDeptDesc             as Department
     , b.chargecode
     , d.PrimaryDesc
     , Category = CASE 
              WHEN e.FieldID =17 and e.OldValue =0 and e.NewValue =1 THEN 'Reactivation'
              WHEN e.FieldID =17 and e.OldValue =1 and e.NewValue =0 THEN 'Inactivation'
              WHEN a.IsAddedtoCDM =1 THEN 'Additions'
              ELSE 'Changes'
              END

from cdm_TransactionHistory          a
    ,cdm_CDMMaster                   b
    ,FusionCommon.dbo.osit_DeptInfo  c
    ,cdm_CDMCommon                   d
    ,cdm_TransactionHistoryDetail    e


where a.cdmid = 36680
and a.historydate >='2009-08-01 00:00:00.000' 
and a.historydate <='2009-08-31 23:59:59:997'
and a.CDMMasterID = b.CDMMasterID
and a.CDMID = b.CDMID
and a.CDMID = c.CDMID
and b.DeptID = c.DeptID
and a.CDMMasterID = d.CDMMasterID
and b.CDMMasterID = d.CDMMasterID
and a.Transactionhistoryid = e.transactionhistoryid  

AND    CASE 
       WHEN e.FieldID =17 and e.OldValue =0 and e.NewValue =1 THEN 'Reactivation'
       WHEN e.FieldID =17 and e.OldValue =1 and e.NewValue =0 THEN 'Inactivation'
       WHEN a.IsAddedtoCDM =1 THEN 'Additions'
       ELSE 'Changes'
       END 
              = 'Additions'

--order by historydate, Category DESC
order by Category DESC

Be One with the Optimizer
TG

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2009-09-14 : 13:24:31

Thanks TG, I get what your saying now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources