Results 1 to 4 of 4

Thread: Logic problem

  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Logic problem

    Hi,

    This might just be my brain not working after the weekend, but I'm having problems working out just how to do this.

    The table:

    Code:
    CREATE TABLE [dbo].[tblQuiz] (
    	[id] [int] IDENTITY (1, 1) NOT NULL ,
    	[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    	[quizdate] [datetime] NULL ,
    	[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[score] [int] NULL 
    )


    The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".

    I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then:
    if [q5]= 'Happy' then change it to 'Unhappy'
    if [q5]= 'Unhappy' then change it to 'Happy'

    The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this an abstraction of a real world problem?
    Anyway - check out CASE in BoL - it is just the ticket for you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Quote Originally Posted by pootle flump
    Is this an abstraction of a real world problem?
    Anyway - check out CASE in BoL - it is just the ticket for you.
    I'm not sure what you mean by abstraction? It IS a real-world problem: "Real" as in "my boss is swearing at me".

    But you're right: CASE does the job perfectly. Thankyou very much

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Generally it's better to use a lookup table to store your Happy/Unhappy/Neither strings and refer to them through fk ids in tblQuiz. That way you don't waste space storing the same string over and over.

    If there are truly only 3 possible values, you could be using a tinyint in the q5 column instead of nvarchar(100), which is a pretty big space savings. this begins to matter pretty quickly in large databases with millions of rows.

Posting Permissions

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