x

combining columns and findng values.

Hi, Please suggest me to right SQL statement for the below mentioned logic. I have five columns Q25A1,Q25A2,Q25A3,Q25A4 AND HQ_BUNDLE. First four columns can have values between 1 to 13 and fifth column can have only 1 to 4. Now, condition is when all first four columns have same value then HQ_BUNDLE will be 4 similarly when any and only three columns have same value then HQ_BUNDLE = 3. similarly when any and only two columns have same value then HQ_BUNDLE = 2. similarly when all the first four columns have different values row wise then HQ_BUNDLE = 1.
more ▼

asked Oct 02, 2012 at 10:13 AM in Default

sandeepdbrl gravatar image

sandeepdbrl
28 2 2

perfect guys. can you please write query using CHECK condition OR UPDATE? Here is the attached data file for your reference.link text

table.txt (105 B)
Oct 09, 2012 at 11:03 AM sandeepdbrl
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Well, what you could do is have the HQ_BUNDLE column as a COMPUTED COLUMN that uses a CASE statement

not providing a full answer because it feels like a homework question...

more ▼

answered Oct 02, 2012 at 10:58 AM

ThomasRushton gravatar image

ThomasRushton ♦
34k 18 20 44

(comments are locked)
10|1200 characters needed characters left

I agree with Thomas that this feels like homework, and I also agree that a computed column is the way to go.

With that said, it is good to have options. You can use an update command to make it so as a one time thing. If your table is such that it is rarely updated but HQ_Bundle is frequently selected, doing the update can be more effecient since a computed column will be frequently recalculated (unless the computed column is indexed, but there are some stringent requirements on whether or not a computed column can be indexed.) Again, I would use a case in the update much like Thomas suggested.

If you want, you could enforce it with a CHECK condition. The advantage is that the check condition only affects performance during write operations, and in most databases read operations are much more common and more time-sensitive than write operations are.

You could also use a series of update statements with where clauses for each possibility instead of using one update with a case, but the only situation I would do that in is if this were really homework and you haven't gotten to the CASE statements in your coursework yet.
more ▼

answered Oct 02, 2012 at 04:46 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x729

asked: Oct 02, 2012 at 10:13 AM

Seen: 484 times

Last Updated: Oct 09, 2012 at 12:13 PM