Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating Access tables in SQL

I'm using Coldfusion and trying to create a Access table with a autonumber field (SID).

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="createtable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>

The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"

Which is really helpful as you can see.

Thanks in advance for any help.Create Table #tbl.code#_Users
(
SID COUNTER PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)|||Thanks for that works a treat.sql

Creating a Yes/No field in SQL Server

hi,

i need to create a yes/no field in a SQL Server database table. I know that i could previously do this in Microsoft Access and i believe that SQL Server now uses Boolean(0 or 1) for this. Does anyone have any ideas as to how to convert these 0 or 1 to yes or no with a default of yes?

I am taking the output from this table and displaying in Excel as yes or no not 0 or 1.

Thanks

If you are using a SQL Server BIT datatype and you import data into excel, the provider will tell Excel to convert this into Yes / No Value, or the appropiate language equivalent for the machine.

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de|||

hi,

thanks for the reply...., excel converts this into true/false instead of yes/no.....can i amend this?

also can i change the default value in sql server from 0 to 1?

thanks

|||

If you want yes/no you either have to transform the data at the SQL Server side first, converting the data into VARCHAR columns with the static text yes/no or you have to use a expression in Excel which evaluates the value and displays the appopiate text.

The default value can be change by putting a default constraint on a table, like:

ALTER TABLE TableName
ADD CONSTRAINT NewConStraintName DEFAULT 1 FOR ColumnName

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi

where do i add the default constraint, is it Properties, Check Constraints?

when i added that text there an error validating came up.

Thanks

|||Hi,

via GUI you can do that by navigating to the column properties and the default constaint, per script you can do that either during creation of the table (Although you will get a automatic name assigned to the default constraint) or by altering the table:

CREATE TABLE #TempTable

(

SOMEColumn BIT DEFAULT 1

)

GO

DROP TABLE #TempTable

GO

CREATE TABLE #TempTable

(

SOMEColumn BIT

)

GO

ALTER TABLE #TempTable

ADD CONSTRAINT NewConStraintName DEFAULT 1 FOR SOMEColumn

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Creating a unique index on a table

Hi,
I'm trying to create an index on a newly created table. I want the index to
be on the field ref. I tried running:
CREATE INDEX ref ON U_segment (ref)
but it's just sitting there! The ref is unique anyway, and there are about
700,000 records.
Any help, or advice would be appreaciated.
Regards
Rob
Does it run and stop, or what?
I wonder if having the index name and the column name the same is the
issue...try
CREATE INDEX IX_ref ON U_segment (ref)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:9AF46DBF-1693-4DD3-A324-BB378FCEBB79@.microsoft.com...
> Hi,
> I'm trying to create an index on a newly created table. I want the index
> to
> be on the field ref. I tried running:
>
> CREATE INDEX ref ON U_segment (ref)
> but it's just sitting there! The ref is unique anyway, and there are about
> 700,000 records.
> Any help, or advice would be appreaciated.
> Regards
> Rob
|||Did you look to see if you are being blocked?
Andrew J. Kelly SQL MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:9AF46DBF-1693-4DD3-A324-BB378FCEBB79@.microsoft.com...
> Hi,
> I'm trying to create an index on a newly created table. I want the index
> to
> be on the field ref. I tried running:
>
> CREATE INDEX ref ON U_segment (ref)
> but it's just sitting there! The ref is unique anyway, and there are about
> 700,000 records.
> Any help, or advice would be appreaciated.
> Regards
> Rob
sql

Creating a Trigger on Table Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Hi
This is not possible through triggers, if you use a Stored procedure to
access the table you can add the code there. Doing this sort of thing may
incur a high performance penalty.
John
"iamalex84@.gmail.com" wrote:
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Do you mean using updates to cause a trigger to trigger or using stored
procedures? Would incur a high performance penalty, that is.|||Hi Alex
For every time you did a select from your table, there would be a subsequent
update of another table. If there was a reasonable load this may result in a
bottleneck and therefore reduce performance. This would be true of any
auditing system regardless of whether you are auditing select, insert, update
or delete statements through triggers or code. In general most systems quite
often do a significantly larger number of selects than other statements
therefore the impact of auditing select statements would be higher. The only
way will you really know the impact is to benchmark your system under heavy
load and volumes.
John
"Alex" wrote:
> Do you mean using updates to cause a trigger to trigger or using stored
> procedures? Would incur a high performance penalty, that is.
>

Creating a Trigger on Table Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Hi
This is not possible through triggers, if you use a Stored procedure to
access the table you can add the code there. Doing this sort of thing may
incur a high performance penalty.
John
"iamalex84@.gmail.com" wrote:

> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Do you mean using updates to cause a trigger to trigger or using stored
procedures? Would incur a high performance penalty, that is.|||Hi Alex
For every time you did a select from your table, there would be a subsequent
update of another table. If there was a reasonable load this may result in a
bottleneck and therefore reduce performance. This would be true of any
auditing system regardless of whether you are auditing select, insert, updat
e
or delete statements through triggers or code. In general most systems quite
often do a significantly larger number of selects than other statements
therefore the impact of auditing select statements would be higher. The only
way will you really know the impact is to benchmark your system under heavy
load and volumes.
John
"Alex" wrote:

> Do you mean using updates to cause a trigger to trigger or using stored
> procedures? Would incur a high performance penalty, that is.
>

Creating a Trigger on Table Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Never update a value just to call a trigger. Create a stored procedure and
call it when you need to. This is a job for a stored procedure, called by
the application, not a job for a trigger. Triggers are used to enforce
rules on your data, and sometimes for auditing changes to data, but not for
this.
<iamalex84@.gmail.com> wrote in message
news:1148415231.372278.151590@.g10g2000cwb.googlegroups.com...
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||There is no SELECT trigger. You could have the logon process and/or table
access be done only via a stored procedure.
HTH
Vern
"iamalex84@.gmail.com" wrote:

> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Thank you both for your responses.

Creating a Trigger on Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.I don't think a 'SELECT' trigger would be a good idea since data could be
access for reasons other than login. IMHO, a stored procedure would be the
best approach since you can include other login activities, like password
validation. For example:
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30)
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<iamalex84@.gmail.com> wrote in message
news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Thank you very much. I have customized and created the procedure. One
more problem though... How do I use it in ASP? I have never worked
with stored procedures before. I'm having trouble with passing the
username and password to it. Also, how do I retrieve the returned
values? Also, is there a way to grab the current value before it is
updated?
Dan Guzman wrote:[vbcol=seagreen]
> I don't think a 'SELECT' trigger would be a good idea since data could be
> access for reasons other than login. IMHO, a stored procedure would be th
e
> best approach since you can include other login activities, like password
> validation. For example:
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30)
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <iamalex84@.gmail.com> wrote in message
> news:1148414912.126785.4600@.j73g2000cwa.googlegroups.com...|||> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
Below is a VBScript example as well as a proc that will return data via an
output parameter.
CREATE PROCEDURE dbo.usp_LoginUser
@.UserName varchar(30),
@.Password varchar(30),
@.LastLoginTime datetime OUTPUT
AS
SET NOCOUNT ON
DECLARE @.ReturnCode int
UPDATE dbo.Users
SET
@.LastLoginTime = LastLoginTime,
LastLoginTime = GETDATE()
WHERE
UserName = @.UserName AND
Password = @.Password
IF @.@.ROWCOUNT > 0
BEGIN
--indicate login success
SET @.ReturnCode = 0
END
ELSE
BEGIN
--indicate login failure
SET @.ReturnCode = 1
END
RETURN @.ReturnCode
GO
'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
'methods to include ADO constants in ASP
connection.Open connectionString
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandType = adCmdStoredProcedure
command.CommandText = "dbo.usp_LoginUser"
Set returnCodeParameter = command.CreateParameter( _
"@.ReturnCode", adInteger, adParamReturnValue)
command.Parameters.Append returnCodeParameter
Set userNameParameter = command.CreateParameter( _
"@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
command.Parameters.Append userNameParameter
Set passwordParameter = command.CreateParameter( _
"@.Password", adVarChar, adParamInput, 30, Request("Password"))
command.Parameters.Append passwordParameter
Set lastLoginTimeParameter = command.CreateParameter( _
"@.LastLoginTime", adDBTimeStamp, adParamOutput)
command.Parameters.Append lastLoginTimeParameter
command.Execute()
If returnCodeParameter.Value = 0 Then
Response.Write "Login succeeded. Last login was " &
lastLoginTimeParameter.Value
Else
Response.Write "Login failed"
End If
connection.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <iamalex84@.gmail.com> wrote in message
news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...
> Thank you very much. I have customized and created the procedure. One
> more problem though... How do I use it in ASP? I have never worked
> with stored procedures before. I'm having trouble with passing the
> username and password to it. Also, how do I retrieve the returned
> values? Also, is there a way to grab the current value before it is
> updated?
>
> Dan Guzman wrote:
>|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...|||Ah, thank you so much. This is perfect.
Dan Guzman wrote:[vbcol=seagreen]
> Below is a VBScript example as well as a proc that will return data via an
> output parameter.
>
> CREATE PROCEDURE dbo.usp_LoginUser
> @.UserName varchar(30),
> @.Password varchar(30),
> @.LastLoginTime datetime OUTPUT
> AS
> SET NOCOUNT ON
> DECLARE @.ReturnCode int
> UPDATE dbo.Users
> SET
> @.LastLoginTime = LastLoginTime,
> LastLoginTime = GETDATE()
> WHERE
> UserName = @.UserName AND
> Password = @.Password
> IF @.@.ROWCOUNT > 0
> BEGIN
> --indicate login success
> SET @.ReturnCode = 0
> END
> ELSE
> BEGIN
> --indicate login failure
> SET @.ReturnCode = 1
> END
> RETURN @.ReturnCode
> GO
> 'see http://www.4guysfromrolla.com/webtech/110199-1.shtml for
> 'methods to include ADO constants in ASP
> connection.Open connectionString
> Set command = CreateObject("ADODB.Command")
> command.ActiveConnection = connection
> command.CommandType = adCmdStoredProcedure
> command.CommandText = "dbo.usp_LoginUser"
> Set returnCodeParameter = command.CreateParameter( _
> "@.ReturnCode", adInteger, adParamReturnValue)
> command.Parameters.Append returnCodeParameter
> Set userNameParameter = command.CreateParameter( _
> "@.UserName", adVarChar, adParamInput, 30, Request("UserName"))
> command.Parameters.Append userNameParameter
> Set passwordParameter = command.CreateParameter( _
> "@.Password", adVarChar, adParamInput, 30, Request("Password"))
> command.Parameters.Append passwordParameter
> Set lastLoginTimeParameter = command.CreateParameter( _
> "@.LastLoginTime", adDBTimeStamp, adParamOutput)
> command.Parameters.Append lastLoginTimeParameter
> command.Execute()
> If returnCodeParameter.Value = 0 Then
> Response.Write "Login succeeded. Last login was " &
> lastLoginTimeParameter.Value
> Else
> Response.Write "Login failed"
> End If
> connection.Close
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <iamalex84@.gmail.com> wrote in message
> news:1149801803.857235.271770@.f6g2000cwb.googlegroups.com...sql

Creating a sum for the year

Hi all I have to create a query that would give the users a a total amount of each field for the month or year.

I just need it to give me a total amount of each item for the month. So basically what happens is that the user enters in a beginning date and an ending date for the month or year and this report gives them the [INVESTIGATOR], [VIOLATION TYP], [DATE], [TOTAL LOSS].

I just want to show how many vilolationtypes per investigator so if Smith had 40 in one month I would need to reflect that in the report. does that make sense?

[VIOLATION TYPE](NVARCHAR)
[DATE] (DATETIME0
[TOTAL LOSS](MONEY)
[INVESTIGATOR](Nvarchar)Hi all I have to create a query that would give the users a a total amount of each field for the month or year.

I just need it to give me a total amount of each item for the month. So basically what happens is that the user enters in a beginning date and an ending date for the month or year and this report gives them the [INVESTIGATOR], [VIOLATION TYP], [DATE], [TOTAL LOSS].

I just want to show how many vilolationtypes per investigator so if Smith had 40 in one month I would need to reflect that in the report. does that make sense?

[VIOLATION TYPE](NVARCHAR)
[DATE] (DATETIME0
[TOTAL LOSS](MONEY)
[INVESTIGATOR](Nvarchar)

1. Create a column for YYYYMM using DatePart.
link here: http://msdn2.microsoft.com/en-us/library/aa258265(SQL.80).aspx
Be sure to CONVERT the results into VARCHAR so you can string them together (YYYY + MM).

2. Return that and the columns you want.

3. Put that query into a subquery.

4. On the outer query;
Group by:
Investigator, YYYYMM, ViolationType

5. Do an aggregate on the fields you want to tally
SUM(Money), ...

If you want actual code, would you be so kind as to do the typing for "create table" and "insert" statements to create a little test data. The concept seems pretty universally basic however. I'll see if I can find a table with dates to make an example.

EDIT: Here's a (meaningless) sample ... This returns a monthly tally of "Balance" by "Customer ID" from our Invoices table:
Note: It uses the "DateName" function for readability of the "Month" result ... not sure if that's ANSI Standard.

SELECT
CustomerID,
MM_YYYY,
SUM(Balance) AS Balance
FROM
( SELECT
CustomerID,
Balance,
CONVERT(varchar(10), DATENAME(MM, InvoiceDate)) + ' ' +
CONVERT(varchar(4), DATEPART(YYYY, InvoiceDate)) AS MM_YYYY
FROM tbInvoice
WHERE (InvoiceDate >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102))
) InnerSelect
GROUP BY CustomerID, MM_YYYY|||I'm sure it would help if I showed you all the DDL

CREATE TABLE [IncidentReports] (
[Notes] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IncidentReports_Notes] DEFAULT (N'Scanned Report'),
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IncidentReports_I/RDocument] DEFAULT (N'Scanned Reports'),
[Action Type] [int] NULL ,
[Action] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL CONSTRAINT [DF_IncidentReports_Loss] DEFAULT (0.00),
[Violation Type] [int] NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inspector] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_IncidentReports] PRIMARY KEY CLUSTERED
(
[IR Number]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO|||SELECT MONTH([DATE]) AS MNTH, COUNT([VIOLATION] AS VLT
FROM INCIDENTREPORTS
WHERE [DATE] BETWEEN @.BEGDATE AND @.ENDDATE
GROUP BY MONTH([DATE])

I'll let you fill in the restsql

Creating a Sum for the Year

Hi all I have to create a query that would give the users a a total amount of each field for the month or year.

I just need it to give me a total amount of each item for the month. So basically what happens is that the user enters in a beginning date and an ending date for the month or year and this report gives them the [INVESTIGATOR], [VIOLATION TYP], [DATE], [TOTAL LOSS].

I just want to show how many vilolationtypes per investigator so if Smith had 40 in one month I would need to reflect that in the report. does that make sense?

Code Snippet

[VIOLATION TYPE](NVARCHAR)
[DATE] (DATETIME0
[TOTAL LOSS](MONEY)
[INVESTIGATOR](Nvarchar)

Does this look right

Code Snippet

SELECT Month([Date]) AS Mnth, COUNT([Violation]) AS VlT
FROM Revised_MainTable
WHERE [Date] BETWEEN @.BeginDate AND @.EndDate
GROUP BY Month([Date])
GO

Try: GROUP BY Year([Date]), Month([Date])

If you only group on the month, then, for example, May 2006 would be grouped with May 2005.
|||

this is the error message I get when I exectute that

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'BETWEEN'.

Code Snippet

SELECT Month([Date]) AS Mnth, COUNT([Violation]) AS VlT
FROM Revised_MainTable
WHERE BETWEEN @.StartDate AND @.EndDate
GROUP BY Year([Date]), Month([Date])
GO

|||Looks like you're missing the [Date] column name before the BETWEEN keyword in that second excerpt.
|||

WHERE {what} BETWEEN @.StartDate AND @.EndDate

|||

Ok I did that now the error message is

Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@.StartDate'.

|||

In your first post, you used @.BeginDate, and then in a later post, you used @.StartDate.

Perhaps you need to change this to @.BeginDate...

|||

Now I'm getting

Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@.StartDate'.

Here's the DDL

Code Snippet

CREATE TABLE [Revised_MainTable] (
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Revised_MainTable_I/RDocument] DEFAULT (N'Scanned Report'),
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL CONSTRAINT [DF_Revised_MainTable_Loss] DEFAULT (0.0000),
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Revised_MainTable_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Revised_MainTable] PRIMARY KEY CLUSTERED
(
[IR Number]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Is this a stored procedure?

Do you have the parameters @.StartDate, @.BeginDate, or @.EndDate defined?

Do you provide those parameters with values anywhere?

|||

thank you so much it works no thank you

One more question please, how would I get it to calculate each violation. lets say if theres 6 Thefts how would I get it to tell me that plus the total amount that was lost?

Code Snippet

SELECT MONTH(Date) AS Mnth, COUNT([Violation Type]) AS [Violation Type], SUM(Loss) AS [Sum Of Loss], [Violation Type] AS Violation
FROM dbo.Revised_MainTable
WHERE (Date BETWEEN @.StartDate AND @.EndDate)
GROUP BY YEAR(Date), MONTH(Date), [Violation Type]

|||

Assuming that [Violation] and [Violation Type] are not the same thing, you could use the same query, substituting one for the other -and it already had the sum of loss included.

Otherwise, if [Violation] is a sub-set of [Violation Type], then add [Violation] to the GROUP BY.

If my assumption are incorrect, send more information.

|||

I would just like to mention one thing about your use of @.StartDate and @.EndDate.

Be aware that as these are datetimes they can mark a point in time. If you are using these as date only then you should make sure that they all indicate midnight at the start of the day (the default). The reason for this is that if the Date field ever manages to get time information in it as well then checking against an @.EndDate that is midnight at the start of the day will not catch any offences which are for times later in the day.

|||

See this is the query the guy built and based his reports off of, I inherited this database and I need to make it into an ADP where sQL server is the engine not access.

But the thing is that Jet SQL and SQL Server are a little different. this is the query he uses in access this the sql view of it. and I'm trying to turn it into SQL server but I'm not getting the same results he's getting.

Code Snippet

SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:

>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
>[StartDate] [datetime] NULL ,
>[EndDate] [datetime] NULL ,
>[Quantity] [int] NULL ,
>[Bar_Code] [varchar] (10) NULL ,
>[Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))
|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
sql

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row an
d
updating the Comment field with the reasons why test failed where appropriat
e.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update commen
t
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:

>Hi all,
>I need to perform a validation test on fields in stored data for each row a
nd
>updating the Comment field with the reasons why test failed where appropria
te.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comme
nt
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
<
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment = CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
--
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via SQLMonster.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via SQLMonster.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment => CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>

Creating a stored proc that references an optional column

Is it possible to create a stored proc which references a non-existent
field without getting the "invalid column name" error?
The field will only exist in one or two production databases (not in
all) but we still want the stored proc to exist in all databases so
that users in the future may add the new field to their own database
(using a Dictionary builder form that exists in the application) and
then be able to run the report (via the stored procedure) that
references the field.
The stored proc checks that the field exists before proceeding.
ThanksIt is possible only if the table does not exist when the procedure is
created. However, this is not a good idea. It would be better to create
two different procedures.
Razvansql

Creating a Select statement with subqueries to 3 other tables...

I have four total tables.

Table One (Documents)- List of Documents. Each record has two fields
related to this issue. First field (Document_ID) is the ID of the
document, second field is the ID of the record (Task_ID) it is
associated to in Table Two.

Table Two (Activities)- List of activities. Each record has two fields
related to this issue. First field (Activity_ID) is the ID of the
activity, the second field (Group_ID) is the ID of the record it is
associated to in Table Three.

Table Three (Groups) - List of groups. Each record has two fields
related to this issue. First field (Group_ID) is the ID of the group,
the second field (Stage_ID) is the ID of the record it is associated to
in Table four.

Table Four (Stages)- List of Event Stages. Each record has two fields
that is related to this issue. The first field (Stage_ID) is the ID of
the stage of an event, the second record is the ID number associated to
the event. This last ID is a known value.

20000024 = the Event ID

I'm trying to come up with a list of Documents from the first table
that is associated to an Event in the Fourth table.

Query Analyzer shows no errors within the script. It just doesn't
return any data. I know that it should, if it does what I'm wanting it
to do.

SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE
(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID
= A.Activity_ID))))))= '20000024')Wenin wrote:
> I have four total tables.
>
> Table One (Documents)- List of Documents. Each record has two fields
> related to this issue. First field (Document_ID) is the ID of the
> document, second field is the ID of the record (Task_ID) it is
> associated to in Table Two.
> Table Two (Activities)- List of activities. Each record has two
fields
> related to this issue. First field (Activity_ID) is the ID of the
> activity, the second field (Group_ID) is the ID of the record it is
> associated to in Table Three.
> Table Three (Groups) - List of groups. Each record has two fields
> related to this issue. First field (Group_ID) is the ID of the
group,
> the second field (Stage_ID) is the ID of the record it is associated
to
> in Table four.
> Table Four (Stages)- List of Event Stages. Each record has two
fields
> that is related to this issue. The first field (Stage_ID) is the ID
of
> the stage of an event, the second record is the ID number associated
to
> the event. This last ID is a known value.
> 20000024 = the Event ID
> I'm trying to come up with a list of Documents from the first table
> that is associated to an Event in the Fourth table.
> Query Analyzer shows no errors within the script. It just doesn't
> return any data. I know that it should, if it does what I'm wanting
it
> to do.
>
> SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
> Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C
WHERE
> (C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE
(B.Activity_ID
> = A.Activity_ID))))))= '20000024')

I'm thinking this is going to require the use of Joins, but I still
can't seem to wrap my head around how joins work exactly.|||First, to save Joe Celko from having to post...

Rows are not records, columns are not fields. You are thinking in the
old sequential file processing mentality. It will take you a year to
unlearn this and get your mind accustomed to thinking in a set-oriented
fashion.

Ok, now that that's out of the way...

You are correct, it will require the use of joins. Please don't take
this as an insult, as I'm sure that you are still learning SQL and
there was a time for all of us when we didn't know it, but this is a
pretty simple set of joins. You really do need to "get your head around
how joins work exactly" if you are going to be doing any SQL coding.
There are plenty of good books for SQL beginners out there. Even the
SQL for Dummies book should get you past this hurdle.

A join takes each table being joined, creates a cartesian product of
the two (i.e., every possible combination of records) then filters that
result based on the ON part of the join. As an example, if I have two
tables:

Customers
cust_id
1
2

Orders
ord_id cust_id
1 1
2 2
3 1

The cartesian product would be:
cust_id ord_id cust_id
1 1 1
1 2 2
1 3 1
2 1 1
2 2 2
2 3 1

If I joined these tables on cust_id = cust_id then it would give me:
cust_id ord_id cust_id
1 1 1
1 3 1
2 2 2

Without the join criteria the cartesian product is useless, but it is
the starting point.

Another way to think of it is this... I know my two tables are related
and I know the column that relates them. Therefore I can join on that
column. That is a VERY simplistic approach to joins, but it will
hopefully point you in the right direction.

Good luck,
-Tom.

Creating a filter on a Dataset

Can I create a report parameter label list to only show the values
that have been extracted wthin a field in my dataset?
i.e. in the same way you add an autofilter on a column in excel, to
return the values in that column.You would need to have a second dataset. Make it exactly the same except use
the distinct and just the single field you care about.
Of course if you are doing this, then your main dataset should take this and
use it as a query parameter so return as little data as possible. Stay away
from filters as much as possible.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andy" <andywilliams1971@.msn.com> wrote in message
news:17287b26-fbc2-4442-a6f2-ba6cfdd83718@.u10g2000prn.googlegroups.com...
> Can I create a report parameter label list to only show the values
> that have been extracted wthin a field in my dataset?
> i.e. in the same way you add an autofilter on a column in excel, to
> return the values in that column.

Creating a copy of a record

What is the simplest way to create a copy of a row?
The table in question has a primary key that is an identity field, which
will obviously need to have a different value, but otherwise I want to be
able to create a row that is identical to another one.
AFAIK Select Into only copies rows into a new table.
I could obviously retrieve each field from the original row (bar the primary
key) and INSERT a new record with this information, but since there are a
fair few columns, I wondered if there might be a simpler one-line SQL
instruction that will do the job...
Thanks in advance
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Think again. Why would you ever want to duplicate a row in a table?
(even with a different IDENTITY key).
In principle:
INSERT INTO YourTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE /* some row */ ;
However, this ought to fail on a key violation because IDENTITY should
never be the only key of a table. Rethink your requirement and your
table design.
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
pk INT NOT NULL PRIMARY KEY,
col CHAR(1),
col1 INT
)
INSERT INTO #Test VALUES (1,'a',20)
--Copy Row
INSERT INTO #Test
SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
col,col1 FROM #Test WHERE pk=1 --change to variable
SELECT * FROM #test
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23cT1aMdvFHA.3688@.tk2msftngp13.phx.gbl...
> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the
> primary key) and INSERT a new record with this information, but since
> there are a fair few columns, I wondered if there might be a simpler
> one-line SQL instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127214710.180280.324430@.g43g2000cwa.googlegroups.com...
> Think again. Why would you ever want to duplicate a row in a table?
> (even with a different IDENTITY key).
> In principle:
> INSERT INTO YourTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE /* some row */ ;
> However, this ought to fail on a key violation because IDENTITY should
> never be the only key of a table. Rethink your requirement and your
> table design.
>
I can see where you are coming from, but I'm afraid my requirement is
genuine & valid.
The table in question hold orderlines. In this example an orderline is being
closed off, but a new copy of the line is to be added to the order.
Therefore, I hoped to copy original orderline (with a different PK
obviously) and then Update the original to close it off. [Obviously this is
a simplistic explanation).
As you can see, in this case the table design is fine. And the requirement,
IS the requirement. But how we satisfy the requirement is the question...
and hence my post.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> CREATE TABLE #Test
> (
> pk INT NOT NULL PRIMARY KEY,
> col CHAR(1),
> col1 INT
> )
> INSERT INTO #Test VALUES (1,'a',20)
> --Copy Row
> INSERT INTO #Test
> SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
> col,col1 FROM #Test WHERE pk=1 --change to variable
> SELECT * FROM #test
Uri,
Thanks for this.
Q. Will this still work if my PK is an Identity field?
Chris|||> As you can see, in this case the table design is fine.
I don't see that. There are serious problems with tables that have only
an IDENTITY key. The consequences of storing duplicate data make it
very difficult (perhaps impossible in some conditions) to validate and
integrate data without logical keys. Also, there are practical
programming issues in TSQL because of the way IDENTITY values are
assigned in multiple row INSERTs.
Putting that aside, why store redundant data? If the row is going to be
modified anyway as you have said then why not insert the modified row
instead of duplicating an existing one and incurring the overhead of an
extra update? Seems like you are paying a high price in integrity and
performance for no discernable benefit.
David Portas
SQL Server MVP
--|||CJM
I think David has
already answered this question , however you are free to try it.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:u4WSrhdvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Uri,
> Thanks for this.
> Q. Will this still work if my PK is an Identity field?
> Chris
>|||> I can see where you are coming from, but I'm afraid my requirement is
> genuine & valid.
> The table in question hold orderlines. In this example an orderline is
> being closed off, but a new copy of the line is to be added to the order.
WHY? What is different about the row? Why does it need to be deleted and
re-inserted?
A|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127217457.000463.24290@.g44g2000cwa.googlegroups.com...
> I don't see that. There are serious problems with tables that have only
> an IDENTITY key. The consequences of storing duplicate data make it
> very difficult (perhaps impossible in some conditions) to validate and
> integrate data without logical keys. Also, there are practical
> programming issues in TSQL because of the way IDENTITY values are
> assigned in multiple row INSERTs.
>
Well I'll have to reserve judgement until I have digested all this... (which
I will).
What alternative would you offer instead of using an identity field?
I've not noticed any significant issues with Identity fields, but I'm always
open to improving my knowledge and my techniques...

> Putting that aside, why store redundant data? If the row is going to be
> modified anyway as you have said then why not insert the modified row
> instead of duplicating an existing one and incurring the overhead of an
> extra update? Seems like you are paying a high price in integrity and
> performance for no discernable benefit.
>
What data is redundant? The original record? No, not so. It remains an
essential part of the order. It would take too long to truly put this into
the right context, but suffice to say that the new row is NOT replacing the
old row. Both will co-exist and both are essential.
I've adapted the INSERT INTO statement that you suggested, and I now have
one command that achieves what I need. I don't see how it could get any more
efficient.|||-- if ur table has huge no of columns, u can try
select * into #t from tbl_name where ...
insert into tbl_name
select * from #t
drop table #t
Rakesh
"CJM" wrote:

> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the prima
ry
> key) and INSERT a new record with this information, but since there are a
> fair few columns, I wondered if there might be a simpler one-line SQL
> instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>

Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
Ivan
Ivan
Take a look at this example posted by Steve Kass long time ago
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>
|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> CREATE TABLE dupNulls (
> pk int identity(1,1) primary key,
> X int NULL,
> nullbuster as (case when X is null then pk else 0 end),
> CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
> )
> INSERT INTO dupNulls(X) VALUES (1)
> INSERT INTO dupNulls(X) VALUES (NULL)
> INSERT INTO dupNulls(X) VALUES (NULL)
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 1 WHERE pk = 2
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 2 WHERE pk = 2
> SELECT pk, X, nullbuster FROM dupNulls
> DROP TABLE dupNulls
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>
|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>

Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
IvanIvan
Take a look at this example posted by Steve Kass long time ago
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> CREATE TABLE dupNulls (
> pk int identity(1,1) primary key,
> X int NULL,
> nullbuster as (case when X is null then pk else 0 end),
> CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
> )
> INSERT INTO dupNulls(X) VALUES (1)
> INSERT INTO dupNulls(X) VALUES (NULL)
> INSERT INTO dupNulls(X) VALUES (NULL)
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 1 WHERE pk = 2
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 2 WHERE pk = 2
> SELECT pk, X, nullbuster FROM dupNulls
> DROP TABLE dupNulls
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>