Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185

    Unanswered: Conditional Computed Columns

    I have a table with three columns namely "A","B", anc "C".
    Column C is a computed column which is derived from Column "B".Something like " IF B < 100 then C=B*0.5 ELSE C=B*0.25"

    How do I make it possible in Sql Server?Is there a way of doing this?

    Thanks!

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Use the case when statement. So for your example:

    select a,b, case when b < 100 then b*.5 else b*.25 end as c
    from ...

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    To make ColC a computed column
    Code:
    drop table test
    go
    create table test
    	(
    	colA	int,
    	colB	int,
    	colC	as case when ColB < 100 then ColB*0.5 else ColB*0.25 end
    	)
    
    go
    insert test (ColA,ColB) values (100,80)
    insert test (ColA,ColB) values (100,100)
    go
    select	* 
    from	test
    
    Output
    
    colA        colB        colC            
    ----------- ----------- --------------- 
    100         80          40.00
    100         100         25.00
    MCDBA

  4. #4
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185

    Thanks everyone

    It's working now

  5. #5
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    humm.. This really new for me.

    Would this create a Trigger??


    Originally posted by achorozy
    To make ColC a computed column
    Code:
    drop table test
    go
    create table test
    	(
    	colA	int,
    	colB	int,
    	colC	as case when ColB < 100 then ColB*0.5 else ColB*0.25 end
    	)
    
    go
    insert test (ColA,ColB) values (100,80)
    insert test (ColA,ColB) values (100,100)
    go
    select	* 
    from	test
    
    Output
    
    colA        colB        colC            
    ----------- ----------- --------------- 
    100         80          40.00
    100         100         25.00
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  6. #6
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    No it doesn't create a trigger, however it does store the computed information in syscomments
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •