Column's default value in 2005

HI,
Dose column's default value can be set as 2 columns' "+, - , *, / " ?
I want ColumnC's default value as ColumnA/ColumnB.
Any idea? Thanks for any advice!
Angiangi a crit :
> HI,
> Dose column's default value can be set as 2 columns' "+, - , *, / " ?
> I want ColumnC's default value as ColumnA/ColumnB.
> Any idea? Thanks for any advice!
> Angi
>
NO... Default and Not Null constraint can only be set on a single row.
To do that, you must use a trigger :
CREATE TRIGGER <trig_name> ON <table_name>
FOR INSERT
AS
UPDATE <table_name>
SET ColumnC = ColumnA/ColumnB
FROM <table_name> T
INNER JOIN inserted i
ON T.<key_col> = i.<key_col>
WHERE ColumnC IS NULL
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Column's default value in 2005

HI,
Dose column's default value can be set as 2 columns' "+, - , *, / " ?
I want ColumnC's default value as ColumnA/ColumnB.
Any idea? Thanks for any advice!
Angiangi a écrit :
> HI,
> Dose column's default value can be set as 2 columns' "+, - , *, / " ?
> I want ColumnC's default value as ColumnA/ColumnB.
> Any idea? Thanks for any advice!
> Angi
>
NO... Default and Not Null constraint can only be set on a single row.
To do that, you must use a trigger :
CREATE TRIGGER <trig_name> ON <table_name>
FOR INSERT
AS
UPDATE <table_name>
SET ColumnC = ColumnA/ColumnB
FROM <table_name> T
INNER JOIN inserted i
ON T.<key_col> = i.<key_col>
WHERE ColumnC IS NULL
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Column Width

How can you widen or shorten a column's width when the report is run?
The width doesn't allow custom code or expressions.
Basically, based on a parameter I pass in, I want to widen or shrink a
column's width.Anyone?
"JSF" wrote:
> How can you widen or shorten a column's width when the report is run?
> The width doesn't allow custom code or expressions.
> Basically, based on a parameter I pass in, I want to widen or shrink a
> column's width.|||JSF,
It doesn't look like it's possible to dynamically change column width.
I just did another search through the group and found nothing new.

column heading centered when sort enabled

SQL Server 2005
I have a report with 20+ columns. Normally the text in each colum
heading is aligned at the top of the column heading. The column
heading is three lines high - there is so much text in some of the
columns - it takes three rows to display all of the column heading
text.
Without any sorting enabled - the column heading text is aligned to
the top of the heading row.
For columns with sorting enabled - it appears that it centers the
text. So column headings that are only one row - they are centered in
the heading row - whereas columns that are not sorted align at the
top.
The vertical align property for the row containing column headings is
set to top
It looks bad and I know my customer will object and might rather
disable sorting.
Any ideas?
Thanks!I believe I know what you are talking about. What is happening is that when
sorting is enabled it has to add the sorting "arrow" icon to the headers
and therefore moves the text around in the column header. If you want the
header to not change size (if the size of the cell is growing when you do
this) then turn the "CanGrow" property to "False" and see if that helps.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: GoogleGroups@.BaldwinNC.com
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: column heading centered when sort enabled
> Date: Tue, 02 Oct 2007 07:03:15 -0700
> SQL Server 2005
> I have a report with 20+ columns. Normally the text in each colum
> heading is aligned at the top of the column heading. The column
> heading is three lines high - there is so much text in some of the
> columns - it takes three rows to display all of the column heading
> text.
> Without any sorting enabled - the column heading text is aligned to
> the top of the heading row.
> For columns with sorting enabled - it appears that it centers the
> text. So column headings that are only one row - they are centered in
> the heading row - whereas columns that are not sorted align at the
> top.
> The vertical align property for the row containing column headings is
> set to top
> It looks bad and I know my customer will object and might rather
> disable sorting.
> Any ideas?
> Thanks!
>|||Thanks for the idea. I only had to change the very first column,
TextBox properties, Format Tab, - I unchecked the "Can increase to
accomodate contents".
Note I only modified the first column heading textbox properties - and
all column headings now appear to be aligned (correctly) to the top.
Thanks!|||I take my last post back. When I "previewed" the report changing just
the first column seemed to fix all columns. But when I deploy the
report - I had to change each column.|||Great. Glad we got it working for you at least :)
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: GoogleGroups@.BaldwinNC.com
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Re: column heading centered when sort enabled
> Date: Tue, 02 Oct 2007 10:37:17 -0700
> I take my last post back. When I "previewed" the report changing just
> the first column seemed to fix all columns. But when I deploy the
> report - I had to change each column.
>

Column constraint

Is it possible to limit a column's value to 0 or 1 and to allow zero or one
row to contain a value
of 1 for that column?
TIA, LarsNot 100% sure of your question but I think you want to look at CHECK
constraints and Unique Indexes in BooksOnLine. You can also enforce data
with triggers.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:po5f811s7pvik05doqb0roide0g2uvla32@.
4ax.com...
> Is it possible to limit a column's value to 0 or 1 and to allow zero or
> one row to contain a value
> of 1 for that column?
> TIA, Lars|||> Is it possible to limit a column's value to 0 or 1
Yes, using either a Bit field or a Check constraint on a TinyInt, SmallInt,
or
Integer column.

> and to allow zero or one row to contain a value
> of 1 for that column?
This part is a bit trickier. Based purely on the information given, you coul
d
solve this in a couple of ways:
1. A trigger that raises an error when on an Insert or Update, a value alrea
dy
exists in the table with a value of 1.
2. Create a View that selects all rows that have a value of 1 and put a uniq
ue
index on that view.
There are other ways to solve this problem if you give us more information a
bout
the schema and problem you are trying to solve. For example, in building a
system for an insurance company, each insurance quote could be associated wi
th
multiple insurance brokers. However, each quote also had one and only one
"primary" broker. Thus, we solved this problem by putting a PrimaryBrokerId
column on the quote itself as well as had a table of "associated" brokers. T
his
made it easy to ensure that each quote had a primary broker and that there w
as
one and only one.
Granted, this sort of problem is much easier to solve in an object model tha
n it
is with database schema but with more infomration about the actual problem,
there might be a solution in the database design.
Thomas|||CREATE TABLE Foobar
( ..
silly_col INTEGER DEFAULT 0 NOT NULL
CONSTRAINT silly_col_range
CHECK (silly_col IN (0,1)),
CONSTRAINT silly_col_single flag
CHECK (1 - (SELECT SUM(silly_col) FROMbar)),
.);
But in SQL Server, you cannot write that second constraint like you can
in other products with better SQL-92 conformance. You would never use
bit data types because they are proprietary.
if you want to flag one row for some purpose, I would recomment that
you use a unique numeric column and select the minimum value:
CREATE TABLE Foobar
( ..
silly_col INTEGER DEFAULT 0 NOT NULL UNIQUE
CONSTRAINT silly_col_range
CHECK (silly_col >= 0),
.);
CREATE VIEW NewFoobar
AS
SELECT .. CASE WHEN silly_col
= (SELECT MIN(silly_col) FROM Foobar)
THEN 1 ELSE 0 END AS silly_flag,
FROM Foobar;|||If you just want to set the state (col =1) of exactly one row in a table
then maybe you could use a second table to do that:
CREATE TABLE YourTable (key_col INTEGER PRIMARY KEY, ... /* other cols */)
CREATE TABLE foo (key_col INTEGER NOT NULL REFERENCES YourTable (key_col), x
INTEGER DEFAULT 1 NOT NULL CHECK (x=1) UNIQUE /* Permit only one row */)
David Portas
SQL Server MVP
--

column alias in select?

I would like to use the value calculated in one column in another colum by using it's alias. However, when I try that, I get an 'Invalid Column Name' error.

for example,

SELECT
sum(Price) as 'Sum_Price',
('Sum_Price' / 3) as 'One_Third_Sum_Price'The SELECT clause can only refer to columns from tables in the FROM clause, so that won't work. Of course, you can do this:

SELECT
sum(Price) as 'Sum_Price',
sum(Price) / 3 as 'One_Third_Sum_Price'

Or you can do this:

SELECT
Sum_Price
Sum_Price / 3 as One_Third_Sum_Price
FROM
( SELECT sum(Price) as 'Sum_Price'
FROM ...
)

column

Hey,
Is it possible to change a column's Allow Nulls property after the table is created?
Thanks,Yes - using alter table. But you have to fill in null values with something if you are going from NULL to NOT NULL.|||FYI - If you have access to bol (Books Online), most of your questions could be answered - including the one you had earlier on primary key constraints.|||What's this books online?|||Oh yeah, and how would I do it using Alter Table??
thanks,|||If you have access to bol (Books Online)... everybody with an internet connection does, it's, um, online

http://msdn.microsoft.com/library/en-us/dnanchor/html/sqlserver2000.asp

for those without a 24/7 internet connection, you can also download BOL in case you want to look stuff up without dialing up...

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

rudy|||Originally posted by vbgladiator
Oh yeah, and how would I do it using Alter Table??
thanks,

http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_3ied.asp|||Rudy - Thanks for posting those links.

vbgladiator - BOL is an online technical reference utility for SQL Server. I would download it - that gives you guaranteed access. You will find that it is an indispensable reference for your SQL Server questions.|||already did :)

Thanks a lot guys.

Colum Name - Alias

We are thinking of using general purpose column names in our application schema. We want to give the option to the end user to customize the filed names to fit their business . We want to build the functionality on the generic names so that it will work for multiple customes.

Example: We may want to have 10 Strings, 10 numbers and 5 booleans pre defined and reports running off of the table. The customer can name first number as pressure, second one for length and map their data to the table. Other customer can use the first number for temperature and the second one for width.

Is there a way to do it in SQL server w/o having a lookup table for column name aliasing?

Thanks

Option 1: Any Reporting application generally has provision to display a customized column names for the table reports.

Option 2: When you query the table - you can provide column alias for the columns queried for example:

SELECT Column1 AS Pressure, Column2 AS Length FROM TableName

Option 3: You can create multiple views over the base table and the created views can have appropriate column names.

Thanks,

Sankaranarayanan MG