can it be overdone with check statements?

Hi,
I would assume most columns in a db would have a known valid range of values
that it makes sense for the business logics. For example, most counting
columns shouldn't have negative value even though it makes some mathetical
sense but high level application code typically doesn't handle it well. So
I'm wondering if experienced db designers would think it's worth adding a
huge check constraint to make sure all values entered into the table are
within the valid (or expected) range. thank you very much.
I'm a great fan of CHECK constraints!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well. So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>
|||Just like Tibor, I am a great fan of Check constraints and use them
generously. However you can probably overdo anything...The reason I use so
many check constraints is that it protects the database against faulty code,
and users who bypass the application using Excel, QA, SEM or any of the
other tools easily obtainable.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of
> values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well.
> So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>
|||is there any advantage of having one for each isolated column in a table or
a huge one per table (with the use of AND and OR operator)?
Thanks!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uKtOgJCfFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Just like Tibor, I am a great fan of Check constraints and use them
> generously. However you can probably overdo anything...The reason I use so
> many check constraints is that it protects the database against faulty
code,[vbcol=seagreen]
> and users who bypass the application using Excel, QA, SEM or any of the
> other tools easily obtainable.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
mathetical[vbcol=seagreen]
a
>

can it be overdone with check statements?

Hi,
I would assume most columns in a db would have a known valid range of values
that it makes sense for the business logics. For example, most counting
columns shouldn't have negative value even though it makes some mathetical
sense but high level application code typically doesn't handle it well. So
I'm wondering if experienced db designers would think it's worth adding a
huge check constraint to make sure all values entered into the table are
within the valid (or expected) range. thank you very much.I'm a great fan of CHECK constraints!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl..
.
> Hi,
> I would assume most columns in a db would have a known valid range of valu
es
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well. S
o
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>|||Just like Tibor, I am a great fan of Check constraints and use them
generously. However you can probably overdo anything...The reason I use so
many check constraints is that it protects the database against faulty code,
and users who bypass the application using Excel, QA, SEM or any of the
other tools easily obtainable.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of
> values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well.
> So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>|||is there any advantage of having one for each isolated column in a table or
a huge one per table (with the use of AND and OR operator)?
Thanks!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uKtOgJCfFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Just like Tibor, I am a great fan of Check constraints and use them
> generously. However you can probably overdo anything...The reason I use so
> many check constraints is that it protects the database against faulty
code,
> and users who bypass the application using Excel, QA, SEM or any of the
> other tools easily obtainable.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
mathetical[vbcol=seagreen]
a[vbcol=seagreen]
>

can it be overdone with check statements?

Hi,
I would assume most columns in a db would have a known valid range of values
that it makes sense for the business logics. For example, most counting
columns shouldn't have negative value even though it makes some mathetical
sense but high level application code typically doesn't handle it well. So
I'm wondering if experienced db designers would think it's worth adding a
huge check constraint to make sure all values entered into the table are
within the valid (or expected) range. thank you very much.I'm a great fan of CHECK constraints!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zeng" <Zeng5000@.hotmail.com> wrote in message news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well. So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>|||Just like Tibor, I am a great fan of Check constraints and use them
generously. However you can probably overdo anything...The reason I use so
many check constraints is that it protects the database against faulty code,
and users who bypass the application using Excel, QA, SEM or any of the
other tools easily obtainable.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> I would assume most columns in a db would have a known valid range of
> values
> that it makes sense for the business logics. For example, most counting
> columns shouldn't have negative value even though it makes some mathetical
> sense but high level application code typically doesn't handle it well.
> So
> I'm wondering if experienced db designers would think it's worth adding a
> huge check constraint to make sure all values entered into the table are
> within the valid (or expected) range. thank you very much.
>|||is there any advantage of having one for each isolated column in a table or
a huge one per table (with the use of AND and OR operator)?
Thanks!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uKtOgJCfFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Just like Tibor, I am a great fan of Check constraints and use them
> generously. However you can probably overdo anything...The reason I use so
> many check constraints is that it protects the database against faulty
code,
> and users who bypass the application using Excel, QA, SEM or any of the
> other tools easily obtainable.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:%23BEpd4AfFHA.640@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I would assume most columns in a db would have a known valid range of
> > values
> > that it makes sense for the business logics. For example, most counting
> > columns shouldn't have negative value even though it makes some
mathetical
> > sense but high level application code typically doesn't handle it well.
> > So
> > I'm wondering if experienced db designers would think it's worth adding
a
> > huge check constraint to make sure all values entered into the table are
> > within the valid (or expected) range. thank you very much.
> >
> >
>

Can Matrix Control handle this? -- Revised Again and Again

I don't know how to solve this reporting services requirements using
Matrix control.
Can someone help me please?
1. The number of columns is unknown until run-time.
2. % Of Grand Total = Row Total / Grand Total
3. Display grouping of stores by city without using a separate
column, only padded by spaces.
4. Display sub-total for each city without using a separate column,
only padded by spaces.
Col1 Col2 Row Total % Of Grand Total
City1
Store1 11 12 23 10.85%
Store2 21 22 43 20.28%
Total 32 34 66 31.13%
City2
Store3 31 32 63 29.72%
Store4 41 42 83 39.15%
Total 72 74 146 68.87%
Grand Total 104 108 212 100.00%
Thanks a million!On Jan 31, 7:53 pm, bo_d...@.yahoo.com wrote:
> I don't know how to solve this reporting services requirements using
> Matrix control.
> Can someone help me please?
> 1. The number of columns is unknown until run-time.
> 2. % Of Grand Total = Row Total / Grand Total
> 3. Display grouping of stores by city without using a separate
> column, only padded by spaces.
> 4. Display sub-total for each city without using a separate column,
> only padded by spaces.
> Col1 Col2 Row Total % Of Grand Total
> City1
> Store1 11 12 23 10.85%
> Store2 21 22 43 20.28%
> Total 32 34 66 31.13%
> City2
> Store3 31 32 63 29.72%
> Store4 41 42 83 39.15%
> Total 72 74 146 68.87%
> Grand Total 104 108 212 100.00%
> Thanks a million!
Please refer to my previous response.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec85ea4fc96df4ac#72a05c6e103948d0
Regards,
Enrique Martinez
Sr. Software Consultant

Can Matrix Control handle this? -- Revised Again

I don't know how to solve this reporting services requirements using
Matrix control.
Can someone help me please?
1. The number of columns is unknown until run-time.
2. PctOfGrandTotal = RowTotal / GrandTotal
3. Display grouping of stores by city without using a separate
column,
only padded by spaces.
4. Display sub-total for each city without using a separate column,
only padded by spaces.
Col1 Col2 Col3 RowTotal
PctOfGrandTotal
City1
Store1 11 12 13 36 11.11%
Store2 21 22 23 66 20.37%
Total 32 34 36 102 31.48%
City2
Store3 31 32 33 96 29.63%
Store4 41 42 43 126 38.89%
Total 72 74 76 222 68.52%
GrandTotal 104 108 112 324 100.00%
Thanks a million!On Jan 31, 7:43 pm, bo_d...@.yahoo.com wrote:
> I don't know how to solve this reporting services requirements using
> Matrix control.
> Can someone help me please?
> 1. The number of columns is unknown until run-time.
> 2. PctOfGrandTotal = RowTotal / GrandTotal
> 3. Display grouping of stores by city without using a separate
> column,
> only padded by spaces.
> 4. Display sub-total for each city without using a separate column,
> only padded by spaces.
> Col1 Col2 Col3 RowTotal
> PctOfGrandTotal
> City1
> Store1 11 12 13 36 11.11%
> Store2 21 22 23 66 20.37%
> Total 32 34 36 102 31.48%
> City2
> Store3 31 32 33 96 29.63%
> Store4 41 42 43 126 38.89%
> Total 72 74 76 222 68.52%
> GrandTotal 104 108 112 324 100.00%
> Thanks a million!
Please refer to my previous response, since this question has been
posted multiple times.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec85ea4fc96df4ac#72a05c6e103948d0
Regards,
Enrique Martinez
Sr. Software Consultant

Can Matrix Control handle this? -- Revised

I don't know how to solve this reporting services requirements using
Matrix control.
Can someone help me please?
1. The number of columns is unknown until run-time.
2. % of Grand Total = Row Total / Grand Total
3. Display grouping of stores by city without using a separate
column,
only padded by spaces.
4. Display sub-total for each city without using a separate column,
only padded by spaces.
Column1 Column2 Column3 Row Total % of Grand Total
City1
Store1 11 12 13 36
11.11%
Store2 21 22 23 66
20.37%
Total 32 34 36 102
31.48%
City2
Store3 31 32 33 96
29.63%
Store4 41 42 43 126
38.89%
Total 72 74 76 222
68.52%
Grand Total 104 108 112 324
100.00%
Thanks a million!On Jan 31, 7:37 pm, bo_d...@.yahoo.com wrote:
> I don't know how to solve this reporting services requirements using
> Matrix control.
> Can someone help me please?
> 1. The number of columns is unknown until run-time.
> 2. % of Grand Total = Row Total / Grand Total
> 3. Display grouping of stores by city without using a separate
> column,
> only padded by spaces.
> 4. Display sub-total for each city without using a separate column,
> only padded by spaces.
> Column1 Column2 Column3 Row Total % of Grand Total
> City1
> Store1 11 12 13 36
> 11.11%
> Store2 21 22 23 66
> 20.37%
> Total 32 34 36 102
> 31.48%
> City2
> Store3 31 32 33 96
> 29.63%
> Store4 41 42 43 126
> 38.89%
> Total 72 74 76 222
> 68.52%
> Grand Total 104 108 112 324
> 100.00%
> Thanks a million!
Please refer to my previous response.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec85ea4fc96df4ac#72a05c6e103948d0
Regards,
Enrique Martinez
Sr. Software Consultant

Can Matrix Control handle this?

I don't know how to solve this reporting services requirements using
Matrix control.
Can someone help me please?
1. The number of columns is unknown until run-time.
2. % of Grand Total = Row Total / Grand Total
3. Display grouping of stores by city without using a separate column,
only padded by spaces.
4. Display sub-total for each city without using a separate column,
only padded by spaces.
Column1 Column2 Column3 Row Total % of
Grand Total
City1
Store1 11 12 13
36 11.11%
Store2 21 22 23
66 20.37%
Total 32 34 36
102 31.48%
City2
Store3 31 32 33
96 29.63%
Store4 41 42 43
126 38.89%
Total 72 74 76
222 68.52%
Grand Total 104 108 112
324 100.00%
Thanks a million!On Jan 31, 7:27 pm, bo_d...@.yahoo.com wrote:
> I don't know how to solve this reporting services requirements using
> Matrix control.
> Can someone help me please?
> 1. The number of columns is unknown until run-time.
> 2. % of Grand Total = Row Total / Grand Total
> 3. Display grouping of stores by city without using a separate column,
> only padded by spaces.
> 4. Display sub-total for each city without using a separate column,
> only padded by spaces.
> Column1 Column2 Column3 Row Total % of
> Grand Total
> City1
> Store1 11 12 13
> 36 11.11%
> Store2 21 22 23
> 66 20.37%
> Total 32 34 36
> 102 31.48%
> City2
> Store3 31 32 33
> 96 29.63%
> Store4 41 42 43
> 126 38.89%
> Total 72 74 76
> 222 68.52%
> Grand Total 104 108 112
> 324 100.00%
> Thanks a million!
1. This is inherent with a matrix report.
2. Row Total can either be determined in the stored procedure/query
that is sourcing the report via a while loop or cursor -or- whatever
group comprises a row can be unioned as a select statement and summed.
I'm assuming that the Grand Total is the overall total in the report/
query; so, you can just sum the entire result -or- return the sum of
the entire result set to the report as a separate column with repeated
values and use an expression like the following to display it in the
report.
=Max(Fields!GrandTotal.Value)
Of course the % of Grand Total would be the division of the two of
these.
3. You might be able to create the City group and then move the extra
column expression in the same row into the same vertical column and
remove the new column.
4. You should be able to union the same query together in the stored
procedure/query that is sourcing the report and group by city. Then
include this data in the report; however, solve it in the same way as
for item 3.
In SQL Server 2008 SSRS, the introduction of the tablix control
(combination of table and matrix control) should resolve the work
around in Items 3 and 4 above.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Can I use OUTER JOIN on 2 columns at the same time?

I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.

I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.

I have following query which does not produce desired result

SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)

The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.

I'm pretty sure it's simple solution but can not figure out myself.

The query is correct ,it has to give the expected result.

Could you provide us with sample data?

|||

That's true it works with scenario I specified but I simplified my real life challenge. Table2 actually contains an extra column called Game of type int. It's nullable. I want the same thing I specified originally but only if Game=15, so my tables are

Table1

label_id athlete_id

1 1 2 1 3 1 1 2 2 2 3 2

Table2

label_id athlete_id data game

1 2 5 15 2 3 4 15 1 7 7 17

I want all rows from Table1 corresponding to label_id and athlete_id between tables for Game=15

Query below (

SELECT Table1.label_id, Table1.athlete_id, data, game FROM Table1

LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)

where game=15

)

returns single row. How can I return all rows with null values where no matches are found and only for game_id

|||

What is your expected result?

I think that following is the expected results from the records you given

labelid athleteid data game

1 1 NULL NULL

2 1 NULL NULL

3 1 NULL NULL

1 2 5 15

2 2 NULL NULL

3 2 NULL NULL

The following is the query

Code Snippet

Select * from

(SELECT Table1.labelid, Table1.atheleteid, data, game FROM Table1

LEFT OUTER JOIN Table2 on (Table1.labelid = Table2.labelid AND Table1.atheleteid = Table2.atheleteid)) as TempTable

where Game=15 or Game is null

|||

Use Full Outer Join:

Code Snippet

SELECT

isnull(table1.label_id,table2.label_id),

isnull(table1.athlete_id,table2.athlete_id),

data,

game

FROM

table1

FULL OUTER JOIN table2 ON

(

table1.label_id = table2.label_id

and table1.athlete_id = table2.athlete_id

)

WHERE

game=15

/*

label_idathlete_iddatagame

-- -- -- --

12515

23415

*/

Move the Where clause to ON itself

Code Snippet

SELECT

Table1.label_id,

Table1.athlete_id,

data,

game

FROM

Table1 Table1

LEFT OUTER JOIN Table2 table2 ON

(

Table1.label_id = Table2.label_id

AND Table1.athlete_id = Table2.athlete_id

) and game=15

/*

label_idathlete_iddatagame

-- -- -- --

11NULLNULL

21NULLNULL

31NULLNULL

12515

22NULLNULL

32NULLNULL

*/

Can I use OUTER JOIN on 2 columns at the same time?

I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.

I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.

I have following query which does not produce desired result

SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)

The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.

I'm pretty sure it's simple solution but can not figure out myself.

The query is correct ,it has to give the expected result.

Could you provide us with sample data?

|||

That's true it works with scenario I specified but I simplified my real life challenge. Table2 actually contains an extra column called Game of type int. It's nullable. I want the same thing I specified originally but only if Game=15, so my tables are

Table1

label_id athlete_id

1 1 2 1 3 1 1 2 2 2 3 2

Table2

label_id athlete_id data game

1 2 5 15 2 3 4 15 1 7 7 17

I want all rows from Table1 corresponding to label_id and athlete_id between tables for Game=15

Query below (

SELECT Table1.label_id, Table1.athlete_id, data, game FROM Table1

LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)

where game=15

)

returns single row. How can I return all rows with null values where no matches are found and only for game_id

|||

What is your expected result?

I think that following is the expected results from the records you given

labelid athleteid data game

1 1 NULL NULL

2 1 NULL NULL

3 1 NULL NULL

1 2 5 15

2 2 NULL NULL

3 2 NULL NULL

The following is the query

Code Snippet

Select * from

(SELECT Table1.labelid, Table1.atheleteid, data, game FROM Table1

LEFT OUTER JOIN Table2 on (Table1.labelid = Table2.labelid AND Table1.atheleteid = Table2.atheleteid)) as TempTable

where Game=15 or Game is null

|||

Use Full Outer Join:

Code Snippet

SELECT

isnull(table1.label_id,table2.label_id),

isnull(table1.athlete_id,table2.athlete_id),

data,

game

FROM

table1

FULL OUTER JOIN table2 ON

(

table1.label_id = table2.label_id

and table1.athlete_id = table2.athlete_id

)

WHERE

game=15

/*

label_idathlete_iddatagame

-- -- -- --

12515

23415

*/

Move the Where clause to ON itself

Code Snippet

SELECT

Table1.label_id,

Table1.athlete_id,

data,

game

FROM

Table1 Table1

LEFT OUTER JOIN Table2 table2 ON

(

Table1.label_id = Table2.label_id

AND Table1.athlete_id = Table2.athlete_id

) and game=15

/*

label_idathlete_iddatagame

-- -- -- --

11NULLNULL

21NULLNULL

31NULLNULL

12515

22NULLNULL

32NULLNULL

*/

Can I update several columns with CASE expressions using SQL at the same time?

Hi, all here,

I am having a question about update several columns with CASE expression in SQL Server 2005 database engine. Is it possible to do that in SQL Server 2005 database engine?

Thanks a lot in advance for any help and guidance.

Can you be more specific about what you're trying to achieve? Using CASE in an update statement is perfectly legal. For instance you can set the value of a column based on a case statement:

declare @.var int

set @.var = 10

update T1 set Col1 = case @.var when 1 then 'Something' when 2 then 'Something2' else 'SomethingElse' end

where Col1 = 1

Can I update several columns by CASE expressions?

Hi, all here,

I am having a question-is it possible to update several columns using CASE expression in SQL language? like if I wanna set each CASE for each column.

Thanks a lot in advance for any help and guidance.

I think so. Have you tried something and it didn't work? A CASE expression just returns a scalar value, so you can do:

update tablename
set column1 = case when .... end,
column2 = case when ... end,

If you want. If the case statements are completely independent of one another this might be the best way to do something, but if they are from the same data set then you might want to use a join, and possibly a derived table.

Post an example and someone will give you an idea

|||Yes, you can.
Few times, i had to create Stored Procedures that work on just one table and all fields did not allow "NULL" values.
E.g.)

CREATE TABLE NewsTitle (
newstitle_id INT IDENTITY PRIMARY KEY
, title NVARCHAR(512)
, page SMALLINT NOT NULL
, media SMALLINT NOT NULL
)

If you would like to create a simple Sproc that will update a record in that table you might do something like(using CASE) the following:

CREATE PROCEDURE UpdateNewsTitle
@.id INT
, @.title NVARCHAR(512)
, @.page SMALLINT
, @.media SMALLINT
AS
BEGIN
UPDATE NewsTitle
SET title = CASE WHEN @.title IS NULL THEN title ELSE @.title END
, page = CASE WHEN @.page IS NULL THEN page ELSE @.page END
, media = CASE WHEN @.media IS NULL THEN media ELSE @.media END
WHERE newstitle_id = @.id
END
GO

But if you are just checking if passed argument is NULL or not and, if argument is not null then try to use that value, you can use COALESCE function to rid of CASE statement like the following:

ALTER PROCEDURE UpdateNewsTitle
@.id INT
, @.title NVARCHAR(512)
, @.page SMALLINT
, @.media SMALLINT
AS
BEGIN
UPDATE NewsTitle
SET title = COALESCE(@.title, title)
, page = COALESCE(@.page, page)
, media = COALESCE(@.media, media)
WHERE newstitle_id = @.id
END
GO

If you are not familiar with COALESCE, you can look it up on BOL(Books Online). In short, COALESCE returns FIRST non-null value.

can i update GUIDS in sysmergepubliction and sysmergeAricles in sqlserver 2005 ?

Hi fellows ,

there is a senerio where i want to update GUIDS in columns "pubid" and "artid" generate in sysmergepubliction and sysmergeAricles respectivly in sqlserver 2005 ?

2nd is there any easy way to generate scripts of triggers of all replicated tables in subscriber at one .

You should not be updating the GUIDs (pubid and artid) manually in the system tables. Almost all of the replication working is based on these IDs and editing of it is neither recommended nor supported.

You should be able to use the UI to gerenate scripts for all the objects.

Can I Merge a table with itself ?

Hi,
I have a table which contains several integer columns that represent
similiar date related information. ie
RenewalMonthDay,HomeMonthDay,MotorMonthD
ay etc.
I need a view of the table where these different columns have been merged
into a single column, so where there was one row with x number of integers,
I would have X number of rows with a single integer. Is this possible ?
What I am actually wanting to do is sort the rows based on the value that is
highest / lowest from the values in the row.
Can this be done ?
Thanks,
Jon.Bishman,
select
a.c1,
case b.c1
when 1 then a.c2
when 2 then a.c3
when 3 then a.c4
end as new_c2
from
dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
select 3) as b
go
-- or
select c1, c2 as new_c2
from dbo.t1
union all
select c1, c3
from dbo.t1
union all
select c1, c4
from dbo.t1
go
AMB
"Bishman" wrote:

> Hi,
> I have a table which contains several integer columns that represent
> similiar date related information. ie
> RenewalMonthDay,HomeMonthDay,MotorMonthD
ay etc.
> I need a view of the table where these different columns have been merged
> into a single column, so where there was one row with x number of integers
,
> I would have X number of rows with a single integer. Is this possible ?
> What I am actually wanting to do is sort the rows based on the value that
is
> highest / lowest from the values in the row.
> Can this be done ?
> Thanks,
> Jon.
>
>|||Bishman,
If you are working with 2005, then you can also use new operator "unpivot".
create table dbo.t1 (
a int not null identity unique,
b int,
c int,
d int
)
go
insert into dbo.t1(b, c, d) values(10, 20, 30)
insert into dbo.t1(b, c, d) values(40, 50, 60)
go
select
a, val
from
(
select a, b as c1, c as c2, d as c3
from dbo.t1
) as pvt
unpivot
(val for c in (c1, c2, c3)) as unpvt
go
drop table dbo.t1
go
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Bishman,
> select
> a.c1,
> case b.c1
> when 1 then a.c2
> when 2 then a.c3
> when 3 then a.c4
> end as new_c2
> from
> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
> select 3) as b
> go
> -- or
> select c1, c2 as new_c2
> from dbo.t1
> union all
> select c1, c3
> from dbo.t1
> union all
> select c1, c4
> from dbo.t1
> go
>
> AMB
> "Bishman" wrote:
>|||Alejandro,
Thanks for these responses.
I like the unpivot one particuarly. I am just seeing if I can get it to work
now !!
I will let you know.
Thanks again,
Jon
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...[vbcol=seagreen]
> Bishman,
> If you are working with 2005, then you can also use new operator
> "unpivot".
> create table dbo.t1 (
> a int not null identity unique,
> b int,
> c int,
> d int
> )
> go
> insert into dbo.t1(b, c, d) values(10, 20, 30)
> insert into dbo.t1(b, c, d) values(40, 50, 60)
> go
> select
> a, val
> from
> (
> select a, b as c1, c as c2, d as c3
> from dbo.t1
> ) as pvt
> unpivot
> (val for c in (c1, c2, c3)) as unpvt
> go
> drop table dbo.t1
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||This is working well except I am trying to go past 4 columns ( I need 6)
I get this error when I go to 5 columns....
The type of column "cole" conflicts with the type of other columns specified
in the UNPIVOT list.
with this SQL
USE CastleProspect_Dev
select
ExtractNumber,rowID, RenewalDate
from
(select ExtractNumber,rowID,HomeRenewalMonthDay as a,ContentsRenewalMonthDay
as b,MotorRenewalMonthDay as c, BreakdownRenewalMonthDay as d ,
PetRenewalMonthDay as e from dbo.ExtractDetail) as pvt
unpivot
(RenewalDate for aa in (a, b, c, d, e)) as unpvt
where extractnumber = 99
go
Any Ideas '
Thanks again. This looks like just what I need.
Jon
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
> Alejandro,
> Thanks for these responses.
> I like the unpivot one particuarly. I am just seeing if I can get it to
> work now !!
> I will let you know.
> Thanks again,
> Jon
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>|||Go It !
The two remaining columns have different data type to the other 4, Sorted.
This works really well. Thanks again.
Jon.
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:%23vWauL0iHHA.1388@.TK2MSFTNGP05.phx.gbl...
> This is working well except I am trying to go past 4 columns ( I need 6)
> I get this error when I go to 5 columns....
> The type of column "cole" conflicts with the type of other columns
> specified in the UNPIVOT list.
> with this SQL
> USE CastleProspect_Dev
> select
> ExtractNumber,rowID, RenewalDate
> from
> (select ExtractNumber,rowID,HomeRenewalMonthDay as
> a,ContentsRenewalMonthDay as b,MotorRenewalMonthDay as c,
> BreakdownRenewalMonthDay as d , PetRenewalMonthDay as e from
> dbo.ExtractDetail) as pvt
> unpivot
> (RenewalDate for aa in (a, b, c, d, e)) as unpvt
> where extractnumber = 99
> go
>
> Any Ideas '
> Thanks again. This looks like just what I need.
> Jon
> "Bishman" <jonathan.bishop@.btinternet.com> wrote in message
> news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
>

Can I Merge a table with itself ?

Hi,
I have a table which contains several integer columns that represent
similiar date related information. ie
RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
I need a view of the table where these different columns have been merged
into a single column, so where there was one row with x number of integers,
I would have X number of rows with a single integer. Is this possible ?
What I am actually wanting to do is sort the rows based on the value that is
highest / lowest from the values in the row.
Can this be done ?
Thanks,
Jon.Bishman,
select
a.c1,
case b.c1
when 1 then a.c2
when 2 then a.c3
when 3 then a.c4
end as new_c2
from
dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
select 3) as b
go
-- or
select c1, c2 as new_c2
from dbo.t1
union all
select c1, c3
from dbo.t1
union all
select c1, c4
from dbo.t1
go
AMB
"Bishman" wrote:
> Hi,
> I have a table which contains several integer columns that represent
> similiar date related information. ie
> RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> I need a view of the table where these different columns have been merged
> into a single column, so where there was one row with x number of integers,
> I would have X number of rows with a single integer. Is this possible ?
> What I am actually wanting to do is sort the rows based on the value that is
> highest / lowest from the values in the row.
> Can this be done ?
> Thanks,
> Jon.
>
>|||Bishman,
If you are working with 2005, then you can also use new operator "unpivot".
create table dbo.t1 (
a int not null identity unique,
b int,
c int,
d int
)
go
insert into dbo.t1(b, c, d) values(10, 20, 30)
insert into dbo.t1(b, c, d) values(40, 50, 60)
go
select
a, val
from
(
select a, b as c1, c as c2, d as c3
from dbo.t1
) as pvt
unpivot
(val for c in (c1, c2, c3)) as unpvt
go
drop table dbo.t1
go
AMB
"Alejandro Mesa" wrote:
> Bishman,
> select
> a.c1,
> case b.c1
> when 1 then a.c2
> when 2 then a.c3
> when 3 then a.c4
> end as new_c2
> from
> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
> select 3) as b
> go
> -- or
> select c1, c2 as new_c2
> from dbo.t1
> union all
> select c1, c3
> from dbo.t1
> union all
> select c1, c4
> from dbo.t1
> go
>
> AMB
> "Bishman" wrote:
> > Hi,
> >
> > I have a table which contains several integer columns that represent
> > similiar date related information. ie
> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> >
> > I need a view of the table where these different columns have been merged
> > into a single column, so where there was one row with x number of integers,
> > I would have X number of rows with a single integer. Is this possible ?
> >
> > What I am actually wanting to do is sort the rows based on the value that is
> > highest / lowest from the values in the row.
> >
> > Can this be done ?
> >
> > Thanks,
> >
> > Jon.
> >
> >
> >|||Alejandro,
Thanks for these responses.
I like the unpivot one particuarly. I am just seeing if I can get it to work
now !!
I will let you know.
Thanks again,
Jon
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
> Bishman,
> If you are working with 2005, then you can also use new operator
> "unpivot".
> create table dbo.t1 (
> a int not null identity unique,
> b int,
> c int,
> d int
> )
> go
> insert into dbo.t1(b, c, d) values(10, 20, 30)
> insert into dbo.t1(b, c, d) values(40, 50, 60)
> go
> select
> a, val
> from
> (
> select a, b as c1, c as c2, d as c3
> from dbo.t1
> ) as pvt
> unpivot
> (val for c in (c1, c2, c3)) as unpvt
> go
> drop table dbo.t1
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >|||This is working well except I am trying to go past 4 columns ( I need 6)
I get this error when I go to 5 columns....
The type of column "cole" conflicts with the type of other columns specified
in the UNPIVOT list.
with this SQL
USE CastleProspect_Dev
select
ExtractNumber,rowID, RenewalDate
from
(select ExtractNumber,rowID,HomeRenewalMonthDay as a,ContentsRenewalMonthDay
as b,MotorRenewalMonthDay as c, BreakdownRenewalMonthDay as d ,
PetRenewalMonthDay as e from dbo.ExtractDetail) as pvt
unpivot
(RenewalDate for aa in (a, b, c, d, e)) as unpvt
where extractnumber = 99
go
Any Ideas '
Thanks again. This looks like just what I need.
Jon
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
> Alejandro,
> Thanks for these responses.
> I like the unpivot one particuarly. I am just seeing if I can get it to
> work now !!
> I will let you know.
> Thanks again,
> Jon
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>> Bishman,
>> If you are working with 2005, then you can also use new operator
>> "unpivot".
>> create table dbo.t1 (
>> a int not null identity unique,
>> b int,
>> c int,
>> d int
>> )
>> go
>> insert into dbo.t1(b, c, d) values(10, 20, 30)
>> insert into dbo.t1(b, c, d) values(40, 50, 60)
>> go
>> select
>> a, val
>> from
>> (
>> select a, b as c1, c as c2, d as c3
>> from dbo.t1
>> ) as pvt
>> unpivot
>> (val for c in (c1, c2, c3)) as unpvt
>> go
>> drop table dbo.t1
>> go
>>
>> AMB
>>
>> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible
>> > ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >
>|||Go It !
The two remaining columns have different data type to the other 4, Sorted.
This works really well. Thanks again.
Jon.
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:%23vWauL0iHHA.1388@.TK2MSFTNGP05.phx.gbl...
> This is working well except I am trying to go past 4 columns ( I need 6)
> I get this error when I go to 5 columns....
> The type of column "cole" conflicts with the type of other columns
> specified in the UNPIVOT list.
> with this SQL
> USE CastleProspect_Dev
> select
> ExtractNumber,rowID, RenewalDate
> from
> (select ExtractNumber,rowID,HomeRenewalMonthDay as
> a,ContentsRenewalMonthDay as b,MotorRenewalMonthDay as c,
> BreakdownRenewalMonthDay as d , PetRenewalMonthDay as e from
> dbo.ExtractDetail) as pvt
> unpivot
> (RenewalDate for aa in (a, b, c, d, e)) as unpvt
> where extractnumber = 99
> go
>
> Any Ideas '
> Thanks again. This looks like just what I need.
> Jon
> "Bishman" <jonathan.bishop@.btinternet.com> wrote in message
> news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
>> Alejandro,
>> Thanks for these responses.
>> I like the unpivot one particuarly. I am just seeing if I can get it to
>> work now !!
>> I will let you know.
>> Thanks again,
>> Jon
>>
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>> Bishman,
>> If you are working with 2005, then you can also use new operator
>> "unpivot".
>> create table dbo.t1 (
>> a int not null identity unique,
>> b int,
>> c int,
>> d int
>> )
>> go
>> insert into dbo.t1(b, c, d) values(10, 20, 30)
>> insert into dbo.t1(b, c, d) values(40, 50, 60)
>> go
>> select
>> a, val
>> from
>> (
>> select a, b as c1, c as c2, d as c3
>> from dbo.t1
>> ) as pvt
>> unpivot
>> (val for c in (c1, c2, c3)) as unpvt
>> go
>> drop table dbo.t1
>> go
>>
>> AMB
>>
>> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible
>> > ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >
>>
>