NVarchar And NChar

Hi ,
What is the different I use NChar and Char as my database field datatype
? In what circumstance I should use NChar or NVarchar ? Kindly advised.
Travis Tan
The datatypes nvarchar, nchar, ntext are UNICODE data types.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6befd9de-9218-4a2c-826e-54f957ab6b4a.htm):
Storing data in multiple languages within one database is difficult to
manage when you use only character data and code pages. It is also difficult
to find one code page for the database that can store all the required
language-specific characters. Additionally, it is difficult to guarantee the
correct translation of special characters when being read or updated by
different clients running various code pages. Databases that support
international clients should always use Unicode data types instead of
non-Unicode data types.
For example, consider a database of customers in North America that must
handle three major languages:
Spanish names and addresses for Mexico
French names and addresses for Quebec
English names and addresses for the rest of Canada and the United States
When you use only character columns and code pages, you must take care to
make sure the database is installed with a code page that will handle the
characters of all three languages. You must also take care to guarantee the
correct translation of characters from one of the languages when read by
clients running a code page for another language.
With the growth of the Internet, it is even more important to support many
client computers that are running different locales. Selecting a code page
for character data types that will support all the characters required by a
worldwide audience would be difficult.
The easiest way to manage character data in international databases is to
always use the Unicode nchar, nvarchar, and ntext data types, instead of
their non-Unicode equivalents, char, varchar, and text......
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B5DBA6D6-7559-40CF-A13F-D2E7ADE98789@.microsoft.com...
> Hi ,
> What is the different I use NChar and Char as my database field datatype
> ? In what circumstance I should use NChar or NVarchar ? Kindly advised.
> --
> Travis Tan

NVarchar And NChar

Hi ,
What is the different I use NChar and Char as my database field datatype
? In what circumstance I should use NChar or NVarchar ? Kindly advised.
--
Travis TanThe datatypes nvarchar, nchar, ntext are UNICODE data types.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6befd9de-9218-4a2c-826e-54f957ab6b4a.htm):
Storing data in multiple languages within one database is difficult to
manage when you use only character data and code pages. It is also difficult
to find one code page for the database that can store all the required
language-specific characters. Additionally, it is difficult to guarantee the
correct translation of special characters when being read or updated by
different clients running various code pages. Databases that support
international clients should always use Unicode data types instead of
non-Unicode data types.
For example, consider a database of customers in North America that must
handle three major languages:
Spanish names and addresses for Mexico
French names and addresses for Quebec
English names and addresses for the rest of Canada and the United States
When you use only character columns and code pages, you must take care to
make sure the database is installed with a code page that will handle the
characters of all three languages. You must also take care to guarantee the
correct translation of characters from one of the languages when read by
clients running a code page for another language.
With the growth of the Internet, it is even more important to support many
client computers that are running different locales. Selecting a code page
for character data types that will support all the characters required by a
worldwide audience would be difficult.
The easiest way to manage character data in international databases is to
always use the Unicode nchar, nvarchar, and ntext data types, instead of
their non-Unicode equivalents, char, varchar, and text......
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B5DBA6D6-7559-40CF-A13F-D2E7ADE98789@.microsoft.com...
> Hi ,
> What is the different I use NChar and Char as my database field datatype
> ? In what circumstance I should use NChar or NVarchar ? Kindly advised.
> --
> Travis Tan

NVarchar And NChar

Hi ,
What is the different I use NChar and Char as my database field datatype
? In what circumstance I should use NChar or NVarchar ? Kindly advised.
Travis TanThe datatypes nvarchar, nchar, ntext are UNICODE data types.
From SQL Server 2005 BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6befd9de-9218-4a2c-826e-54f
957ab6b4a.htm):
Storing data in multiple languages within one database is difficult to
manage when you use only character data and code pages. It is also difficult
to find one code page for the database that can store all the required
language-specific characters. Additionally, it is difficult to guarantee the
correct translation of special characters when being read or updated by
different clients running various code pages. Databases that support
international clients should always use Unicode data types instead of
non-Unicode data types.
For example, consider a database of customers in North America that must
handle three major languages:
Spanish names and addresses for Mexico
French names and addresses for Quebec
English names and addresses for the rest of Canada and the United States
When you use only character columns and code pages, you must take care to
make sure the database is installed with a code page that will handle the
characters of all three languages. You must also take care to guarantee the
correct translation of characters from one of the languages when read by
clients running a code page for another language.
With the growth of the Internet, it is even more important to support many
client computers that are running different locales. Selecting a code page
for character data types that will support all the characters required by a
worldwide audience would be difficult.
The easiest way to manage character data in international databases is to
always use the Unicode nchar, nvarchar, and ntext data types, instead of
their non-Unicode equivalents, char, varchar, and text......
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B5DBA6D6-7559-40CF-A13F-D2E7ADE98789@.microsoft.com...
> Hi ,
> What is the different I use NChar and Char as my database field datatype
> ? In what circumstance I should use NChar or NVarchar ? Kindly advised.
> --
> Travis Tan

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Please include DDL with your questions so that we don't have to guess at what
your tables might look like.
Here's an example. Suppose you have a denormalized structure like this:
CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
You can convert this to a more usable form as follows:
CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
INSERT INTO accounts (account_no, dt, amount)
SELECT account_no, '20040101', jan
FROM monthly_accounts
WHERE jan IS NOT NULL
UNION ALL
SELECT account_no, '20040201', feb
FROM monthly_accounts
WHERE feb IS NOT NULL
UNION ALL
SELECT account_no, '20040301', mar
FROM monthly_accounts
WHERE mar IS NOT NULL
...
Notice that you will usually add at least one column to the key when you do
this.
David Portas
SQL Server MVP
|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E5E28C97-6FF6-4EBA-8152-33063A6C813C@.microsoft.com...
> Please include DDL with your questions so that we don't have to guess at
what
> your tables might look like.
> Here's an example. Suppose you have a denormalized structure like this:
> CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
> NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
> You can convert this to a more usable form as follows:
> CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
> CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
> INSERT INTO accounts (account_no, dt, amount)
> SELECT account_no, '20040101', jan
> FROM monthly_accounts
> WHERE jan IS NOT NULL
> UNION ALL
> SELECT account_no, '20040201', feb
> FROM monthly_accounts
> WHERE feb IS NOT NULL
> UNION ALL
> SELECT account_no, '20040301', mar
> FROM monthly_accounts
> WHERE mar IS NOT NULL
> ...
> Notice that you will usually add at least one column to the key when you
do
> this.
> --
> David Portas
> SQL Server MVP
> --
|||Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>
sql

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Please include DDL with your questions so that we don't have to guess at what
your tables might look like.
Here's an example. Suppose you have a denormalized structure like this:
CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
You can convert this to a more usable form as follows:
CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
INSERT INTO accounts (account_no, dt, amount)
SELECT account_no, '20040101', jan
FROM monthly_accounts
WHERE jan IS NOT NULL
UNION ALL
SELECT account_no, '20040201', feb
FROM monthly_accounts
WHERE feb IS NOT NULL
UNION ALL
SELECT account_no, '20040301', mar
FROM monthly_accounts
WHERE mar IS NOT NULL
...
Notice that you will usually add at least one column to the key when you do
this.
David Portas
SQL Server MVP
|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E5E28C97-6FF6-4EBA-8152-33063A6C813C@.microsoft.com...
> Please include DDL with your questions so that we don't have to guess at
what
> your tables might look like.
> Here's an example. Suppose you have a denormalized structure like this:
> CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
> NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
> You can convert this to a more usable form as follows:
> CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
> CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
> INSERT INTO accounts (account_no, dt, amount)
> SELECT account_no, '20040101', jan
> FROM monthly_accounts
> WHERE jan IS NOT NULL
> UNION ALL
> SELECT account_no, '20040201', feb
> FROM monthly_accounts
> WHERE feb IS NOT NULL
> UNION ALL
> SELECT account_no, '20040301', mar
> FROM monthly_accounts
> WHERE mar IS NOT NULL
> ...
> Notice that you will usually add at least one column to the key when you
do
> this.
> --
> David Portas
> SQL Server MVP
> --
|||Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advanceHi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (
A)
> with numerous numeric fields to a new table (B) with just one numeric fiel
d.
> Thus the number of records in the table (B) would be the number or record
s
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns
)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason wh
y
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
>|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
--|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegroups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegroups.com...
>
>

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>

Numeric value only for a character field

Hi
I have a character field (char ot varchar) that I want to force only to
contain numeric characters.
Can that be done by way of defining a constraint on the field ?
or by any other way in the field/table definition ?
What id the syntax ?
Anyone have examples ?

Thanks

David GreenbergOn Thu, 02 Aug 2007 10:05:40 +0200, David Greenberg wrote:

Quote:

Originally Posted by

>Hi
>I have a character field (char ot varchar) that I want to force only to
>contain numeric characters.
>Can that be done by way of defining a constraint on the field ?
>or by any other way in the field/table definition ?
>What id the syntax ?
>Anyone have examples ?

Hi David,

The obvious solution is to declare the column with one of the numeric
data types instead of char or varchar.

But if you insist on using character columns for numeric data, you can
add a CHECK constraint:

CREATE TABLE Example
(SomeCol char(9) CHECK (SomeCol NOT LIKE '%[^0-9]%'));

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

numeric conversion

I have a source table with a varchar field like 0000005467.
My target table has a numeric 18,2 column which I am trying to populate with 54.67 but it keeps rounding the last 2 digits to ZERO's.
Any ideas?
-KTry something likes that:

declare @.str varchar(25)
select @.str='00005667'
select @.str=left(@.str,len(@.str)-2)+'.'+right(@.str,2)
select convert(decimal(10,2),@.str),@.str|||The data field numeric(18,2) will support 2 decimal places but it doesn't assume that a value stored or converted has a 2 decimal value. So you need to tell the system that the converted value has a 2 decimal value, divide by 100.

declare @.x varchar(15)

set @.x = '0000005467'

select convert(numeric(18,2),@.x)/100

Numbering in SQL

I have table with 10-20 rows with field P6 which is empty. I want to
update numbers to P6 starting 1 and increasing by 1. I suppose it is
done by triggers but I don't know how to do that. Help :-)is this as 1 off or every time you INSERT a record?

--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.j33g2000cwa.googlegr oups.com...
> I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)|||Make the column a identity value, this way it is incremented with every
insert:

int IDENTITY (1, 1)|||lemes_m@.yahoo.com (lemes_m@.yahoo.com) writes:
> I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)

Without further knowledge about the table it is difficult to give
advice. And if the rest of the data is not unique, it's getting sort
of ugly.

For a one-off you could do:

DECLARE @.i int
SELECT @.i = 1
-- SET ROWCOUNT 1 Use this on SQL 2000.
WHILE EXISTS (SELECT * FROM tbl WHERE P6 IS NULL)
BEGIN
UPDATE /* TOP(1) */ tbl -- Remove comment for SQL 2005.
SET P6 = @.i

SELECT @.i = @.i + 1
END
-- SET ROWCOUNT 0 again, for SQL 2000.

But I would not like to see this code in a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> have table with 10-20 rows with field [sic] P6 which is empty [sic]. <<

You have NEVER written SQL before, have you? Columsn are not fields
and we have NULLs, not the empty spreadsheet cells you assume. TOTALLY
WRONG MINDSET!

>> I want to update numbers to P6 starting 1 and increasing by 1. <<

That is a SEQUENTAL MAGNETIC TAPE FILE and you are tryignto write
1950's code in SQL! It has northing whatsoever to do with RDBMS.
Tables have no ordering by definition. This is soooooo wrong ...|||Geeesh!!! You can insert a set at a time, so adding one to a previous
value makes no sense. Doesn't anyone go to RDBMS classes any more?|||>> Make the column a identity value, this way it is incremented with every insert: <<

Always go for the proprietary and most non-relational kludge? LET'S
FINBD OUT WHAT HE IS REALLY TRYIGN TO DO BEFORE WE POST ANYTHING ELSE.

Okay, lemes_m@.yahoo.com , why do you want to destroy the relational
model? What is your business goal?|||Hi Lemes,

Check out http://blogs.msdn.com/sqlcat/archiv.../10/572848.aspx it has
good examples of how to do this.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.j33g2000cwa.googlegr oups.com...
>I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.
How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.
|||That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
"Adam Machanic" wrote:

> How will you determine what the order should be? That is, should the row
> with AgencyID = 10 have a StatId of 109?
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
>
>
|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...[vbcol=seagreen]
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
row[vbcol=seagreen]

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
> > How will you determine what the order should be? That is, should the
row
> > with AgencyID = 10 have a StatId of 109?
> >
> >
> > "Jack" <Jack@.discussions.microsoft.com> wrote in message
> > news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> > > Hi,
> > > I have a table as follows:
> > >
> > > StatId AgencyID Value
> > > 1 10
> > > 2 47
> > > 3 38
> > > 4 59
> > > 5 60
> > > .. ..
> > > All the fields in the StatId field is blank. However,
> > > I have to fill up the field in statId starting from
> > > 109 with increment of 1 for each row. Altogether I have
> > > about 10,000 row in the above agency table.
> > >
> > > Any help as to how to proceed programmatically is highly
> > > appreciated. Thanks in advance.
> >
> >
> >

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
"Adam Machanic" wrote:

> How will you determine what the order should be? That is, should the row
> with AgencyID = 10 have a StatId of 109?
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
>
>|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...[vbcol=seagreen]
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
>
row[vbcol=seagreen]

NUMBER FORMATTING : round it to nearest whole number

HI,
CAN ANY ONE HELP me for this query.
i want to format the text field which takes numbers.I want to display the number as with no decimal places, round it to nearest whole number. For example,3.1 to 3.4 will round to 3 and 3.5 to 3.9 will round to 4.Have you tried using the Math.Round function?
Ex: Math.Round(YourVar, 0)
I think that'd do it, but it's been awhile since I've played with that function.
- T
"SRIRAM" wrote:
> HI,
> CAN ANY ONE HELP me for this query.
> i want to format the text field which takes numbers.I want to display the number as with no decimal places, round it to nearest whole number. For example,3.1 to 3.4 will round to 3 and 3.5 to 3.9 will round to 4.

num of checks written in 4 day period for more than $400

I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in
OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT )
between 400 & 500 # 20
> 501 # 10

NOV between 300 & 400 #30
between 400 & 500 # 20
> 501 # 10

and so on for a 6 month period.You will need to use a self join. Something like this:

select Table1.CustomerID, sum(Table2.CheckValue)
from YourTable Table1 inner join YourTable Table2
on Table1.CustomerID = Table2.CustomerID
and Table1.CheckDate > Table2.CheckDate
and datediff(day, Table2.CheckDate, Table1.CheckDate) <= 4
group by Table1.CustomerID
having sum(Table2.CheckValue) > 400

Nulls vs Blanks

Until yesterday it was imposible to delete a field that did not allow nulls
in Enterprise manager (SQL2000) the InfoPath Clients connected to such tables
saw red asterisks in all the required fields according to the information
provided by the SQL schema.
This has now changed on all instances and all servers without apparant
reason. now I can delete a field that does not allow nulls and it is
registered as a blank. Infopath clients are not forced to include all
required fields prior to submitting.
What could have changed?
Thanks
IT PHYTOSANThis is a multi-part message in MIME format.
--080302080803080309060001
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
This might be better off posted to an InfoPath newsgroup (like
microsoft.public.infopath).
--
*mike hodgson*
http://sqlnerd.blogspot.com
IT PHYTOSAN wrote:
>Until yesterday it was imposible to delete a field that did not allow nulls
>in Enterprise manager (SQL2000) the InfoPath Clients connected to such tables
>saw red asterisks in all the required fields according to the information
>provided by the SQL schema.
>This has now changed on all instances and all servers without apparant
>reason. now I can delete a field that does not allow nulls and it is
>registered as a blank. Infopath clients are not forced to include all
>required fields prior to submitting.
>What could have changed?
>Thanks
>IT PHYTOSAN
>
--080302080803080309060001
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>This might be better off posted to an InfoPath newsgroup (like
microsoft.public.infopath).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
IT PHYTOSAN wrote:
<blockquote cite="mid6513D024-96EB-4CF5-82B8-7134F4812D09@.microsoft.com"
type="cite">
<pre wrap="">Until yesterday it was imposible to delete a field that did not allow nulls
in Enterprise manager (SQL2000) the InfoPath Clients connected to such tables
saw red asterisks in all the required fields according to the information
provided by the SQL schema.
This has now changed on all instances and all servers without apparant
reason. now I can delete a field that does not allow nulls and it is
registered as a blank. Infopath clients are not forced to include all
required fields prior to submitting.
What could have changed?
Thanks
IT PHYTOSAN
</pre>
</blockquote>
</body>
</html>
--080302080803080309060001--

Nulls vs Blanks

Until yesterday it was imposible to delete a field that did not allow nulls
in Enterprise manager (SQL2000) the InfoPath Clients connected to such table
s
saw red asterisks in all the required fields according to the information
provided by the SQL schema.
This has now changed on all instances and all servers without apparant
reason. now I can delete a field that does not allow nulls and it is
registered as a blank. Infopath clients are not forced to include all
required fields prior to submitting.
What could have changed?
Thanks
IT PHYTOSANThis might be better off posted to an InfoPath newsgroup (like
microsoft.public.infopath).
*mike hodgson*
http://sqlnerd.blogspot.com
IT PHYTOSAN wrote:

>Until yesterday it was imposible to delete a field that did not allow nulls
>in Enterprise manager (SQL2000) the InfoPath Clients connected to such tabl
es
>saw red asterisks in all the required fields according to the information
>provided by the SQL schema.
>This has now changed on all instances and all servers without apparant
>reason. now I can delete a field that does not allow nulls and it is
>registered as a blank. Infopath clients are not forced to include all
>required fields prior to submitting.
>What could have changed?
>Thanks
>IT PHYTOSAN
>

Nulls in SQL 2005

Hello,

I have a varchar(20) field which stores a value or empty string.

I have noticed that if the value is NULL and I use is null in my query, the query runs very fast. However If I also include in my query a check for an empty string (len(rtrim(field)) = 0) the query is much slower.

Is it a good idea to force the empty strings to null and if Yes is there an automatic way in sql 2005 (maybe a trigger) to update all empty strings to null.

Thanks, I just need some advice.

Michael

The difference may be coming from the use of functions than checking for an empty string versus a null string. The optimizer cannot make use of an expression in the same way as it makes use of a field.

Using a trigger to turn blanks into null is not a bad idea, but it is not without overhead either. Also consider whether a blank string represents something different than a NULL. You might also want to revisit your data model and see whether it makes more sense to put the value in a separate table and remove the entry when it is not necessary.

|||One more option is to create computed column and index over it, but there are some requirements for that (refer for details: http://msdn2.microsoft.com/en-us/library/ms189292.aspx )|||

HI

U can also use this query

select * from table1 where col1 is null or col1 = ''

Nulls -conditional display of value in Visual Studio

I am trying to display one of 2 values based on an input parameter. I have
created a field which is supposed to evaluate the parameter and display the
appropriate field.
My code is as follows: =iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
Fields!Measures_M9N_MTD.Value)
When I preview my report and select "Y" as the parameter, my field displays
nothing. I have checked the data and know there is a value in the field
represented by the "true" portion of my statement.
If I change the staement to this:=iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value, 0)
I actually get the value I am expecting.
My conclusion is the data in the "false" portion of the statement is
actually null or non-existant. I think this is causing my statement to
malfunction. How do I account for this and make my statement work properly?
There will eventually be data in the field represented in the "false"
portion of the statement. The data will reside in either/or both depending
on the time of month.
Thank you... PB> My conclusion is the data in the "false" portion of the statement is
> actually null or non-existant. I think this is causing my statement to
> malfunction.
Assuming this conclusion is correct, you could use a nested IIF() test using
the ISNOTHING() function, like this:
=iif( Parameters!LastWeek.Value = "Y",
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
iif(ISNOTHING( Fields!Measures_M9N_MTD.Value), 0,
Fields!Measures_M9N_MTD.Value) )
... however (still assuming your conclusion about why it's not working is
correct) it might be safer to assume that *either* value could be missing,
so you could do the test in both places:
=iif( Parameters!LastWeek.Value = "Y",
iif(ISNOTHING(Fields!Measures_M30N_MTD_Last_Week_of_Month.Value),0,
Fields!Measures_M30N_MTD_Last_Week_of_Month.Value),
iif(ISNOTHING(Fields!Measures_M9N_MTD.Value), 0,
Fields!Measures_M9N_MTD.Value) )
... double-however <g> when you look at it this way, it might be simpler to
do the same work in your data query. IOW use ISNULL() or COALESCE() in your
SELECT statement, to provide a default value for values that might be
missing, before you get to the report level.
Regards,
>L<
"ppbedz" <ppbedz@.discussions.microsoft.com> wrote in message
news:7F96FEB5-806D-4761-8077-4C3A7B85973D@.microsoft.com...
>I am trying to display one of 2 values based on an input parameter. I have
> created a field which is supposed to evaluate the parameter and display
> the
> appropriate field.
> My code is as follows: =iif( Parameters!LastWeek.Value = "Y",
> Fields!Measures_M30N_MTD_Last_Week_of_Month.Value,
> Fields!Measures_M9N_MTD.Value)
> When I preview my report and select "Y" as the parameter, my field
> displays
> nothing. I have checked the data and know there is a value in the field
> represented by the "true" portion of my statement.
> If I change the staement to this:=iif( Parameters!LastWeek.Value = "Y",
> Fields!Measures_M30N_MTD_Last_Week_of_Month.Value, 0)
> I actually get the value I am expecting.
> My conclusion is the data in the "false" portion of the statement is
> actually null or non-existant. I think this is causing my statement to
> malfunction. How do I account for this and make my statement work
> properly?
> There will eventually be data in the field represented in the "false"
> portion of the statement. The data will reside in either/or both
> depending
> on the time of month.
> Thank you... PB

Nulls as 1.1.1900

I would be very thankful if anybody could help me with this:
If there is a date field with a null value it defaults to 1.1.1900
is it possible to change the default to the empty datevalue or ' / / '
Thanks
Bjarni SigurdssonIf the value is indeed NULL, you can detect that by binding an indicator
field for the row/parameter in question.
"gandalf" <anonymous@.discussions.microsoft.com> wrote in message
news:08ed01c4b1ca$4e9c6900$a401280a@.phx.gbl...[vbcol=seagreen]
> a date is internally stored as a number
> You could use a CASE-statement to show another value
>
> this:
> 1.1.1900
> datevalue or ' / / '