I want to create, alter and delete tables (not records) from ASP code. Does
anybody know of a class (preferably ASP or DLL) that hides the complexity of
the Data Definition Language?
CedricTake a look at the DMO API. However, first consider your reasons for wanting
to do this. DMO is a reasonable choice if you want to retrieve info about DB
objects or if you are developing a general-purpose database utility
(something like Enterprise Manager for example). For deployment of a
database application however, TSQL DDL is far more concise and much easier
to deploy in most environments.
At runtime, in a business process application, there should of course be no
general need to create and change database objects at all. Not if you have
the correct design anyway.
Hope this helps.
David Portas
SQL Server MVP
--|||Look at SQL-DMO in Books Online.
Paul
"Cedric" wrote:
> I want to create, alter and delete tables (not records) from ASP code. Doe
s
> anybody know of a class (preferably ASP or DLL) that hides the complexity
of
> the Data Definition Language?
> Cedric
>
>|||>I want to create, alter and delete tables (not records) from ASP code
Now I re-read that I am pretty alarmed! Why would you want to alter and
delete tables from ASP? Are you really writing a replacement for Enterprise
Manager? If so, you should first take a look at some of the similar third
party tools already available.
David Portas
SQL Server MVP
--
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Thursday, March 22, 2012
clarification on Reporting services
hi folks,
I want to display a message "no records retrieved" in the preview tab of the report in RS when no records are retrieved for a query specified in the data tab of the report in RS.Is there any custom code for this?How to accomplish this?Can anyone help me find a solution for this?Pls send me the procedure for this .
You could:
1. Set the message in the NoRows property of the data region bound to the dataset.
2. Display the message in a textbox which is hidden if the dataset has rows or visible otherwise.
|||Dear sir,
Thank you for your reply..I was successful with the first point.Can u tell me how to proceed with the second point?
How to display the message in the textbox?should i type something in the textbox value property?if so how?
Regards
Balaji
|||Drop a textbox in the report body with the desired text, e.g. "No data". Set the textbox Visibility Hidden expression to =IsNothing(First(Fields!<some field name>.Value, "<your dataset name>")) = Falsesqlsqlclarification on Reporting services
hi folks,
I want to display a message "no records retrieved" in the preview tab of the report in RS when no records are retrieved for a query specified in the data tab of the report in RS.Is there any custom code for this?How to accomplish this?Can anyone help me find a solution for this?Pls send me the procedure for this .
You could:
1. Set the message in the NoRows property of the data region bound to the dataset.
2. Display the message in a textbox which is hidden if the dataset has rows or visible otherwise.
|||Dear sir,
Thank you for your reply..I was successful with the first point.Can u tell me how to proceed with the second point?
How to display the message in the textbox?should i type something in the textbox value property?if so how?
Regards
Balaji
|||Drop a textbox in the report body with the desired text, e.g. "No data". Set the textbox Visibility Hidden expression to =IsNothing(First(Fields!<some field name>.Value, "<your dataset name>")) = False
Tuesday, March 20, 2012
Clarification on NULL values in the records.
Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>
>.
>|||Answered in .programming.
Please don't multi-post.
David Portas
SQL Server MVP
--
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>
>.
>|||Answered in .programming.
Please don't multi-post.
David Portas
SQL Server MVP
--
Clarification on NULL values in the records.
Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>> Hi All
>> Please write me the reason for
>> SQL:
>> --
>> select * from table1
>> where Required <> 'Y'
>> the result set is empty.
>> Table Schema is:
>> --
>> CREATE TABLE [dbo].[Table1] (
>> [Names] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Required] [nvarchar] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>> Insert Statement is is:
>> --
>> insert into table1 ([Names],Required)
>> values('Test1',null)
>> insert into table1 ([Names],Required)
>> values('Test2','Y')
>> insert into table1 ([Names],Required)
>> values('Test3',null)
>> insert into table1 ([Names],Required)
>> values('Test4','Y')
>> insert into table1 ([Names],Required)
>> values('Test5',null)
>> Is there any option I need to get the appropriate result
>> set?
>> B'coz my result set expected is
>> Test1 NULL
>> Test3 NULL
>> Test5 NULL
>
>.
>|||Answered in .programming.
Please don't multi-post.
--
David Portas
SQL Server MVP
--sqlsql
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>> Hi All
>> Please write me the reason for
>> SQL:
>> --
>> select * from table1
>> where Required <> 'Y'
>> the result set is empty.
>> Table Schema is:
>> --
>> CREATE TABLE [dbo].[Table1] (
>> [Names] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Required] [nvarchar] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>> Insert Statement is is:
>> --
>> insert into table1 ([Names],Required)
>> values('Test1',null)
>> insert into table1 ([Names],Required)
>> values('Test2','Y')
>> insert into table1 ([Names],Required)
>> values('Test3',null)
>> insert into table1 ([Names],Required)
>> values('Test4','Y')
>> insert into table1 ([Names],Required)
>> values('Test5',null)
>> Is there any option I need to get the appropriate result
>> set?
>> B'coz my result set expected is
>> Test1 NULL
>> Test3 NULL
>> Test5 NULL
>
>.
>|||Answered in .programming.
Please don't multi-post.
--
David Portas
SQL Server MVP
--sqlsql
Monday, March 19, 2012
Choosing one record from many
I have a table that lists user ID's and their last login times, and most of the records have duplicates with the only difference being the date field showing the last login time. How can I retrieve only the most current record for each ID from this table?
For example, I have ID's ABC and XYZ. Both are listed in the table 6 times each, but I want a resultant table of only 2 records, one for ABC and one for XYZ, and each of these records is the one with the most current (latest)date.
I am using CR 8.5
ThanksWhy not do this in your SQL query, Select Distinct(yourId) from...|||Note: You cannot change the SELECT clause of the SQL statement.
This note is from Crystal Reports Online Help. It seems that when I open the "Show SQL Query", I can edit anything else but the SELECT clause. So it seems that I can't use the DISTINCT from here.
Any other help please!!!
For example, I have ID's ABC and XYZ. Both are listed in the table 6 times each, but I want a resultant table of only 2 records, one for ABC and one for XYZ, and each of these records is the one with the most current (latest)date.
I am using CR 8.5
ThanksWhy not do this in your SQL query, Select Distinct(yourId) from...|||Note: You cannot change the SELECT clause of the SQL statement.
This note is from Crystal Reports Online Help. It seems that when I open the "Show SQL Query", I can edit anything else but the SELECT clause. So it seems that I can't use the DISTINCT from here.
Any other help please!!!
Choosing Lesser of Evils
I have the following SP that is the first step of cleaning bulk inserted
files before matching records against existing ones in the database:
UPDATE SampleSourceArchive
SET CompanyName = LTRIM(RTRIM(ISNULL(REPLACE(CompanyName,'
"',''),
''))),
CompanyPhoneArea = LTRIM(RTRIM(ISNULL(REPLACE(CompanyPhoneA
rea,'"',''),
''))),
CompanyPhoneNumber =
LTRIM(RTRIM(ISNULL(REPLACE(CompanyPhoneN
umber,'"',''), ''))),
CompanyAddress1 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s1,'"',''),
''))),
CompanyAddress2 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s2,'"',''),
''))),
CompanyAddress3 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s3,'"',''),
''))),
City = LTRIM(RTRIM(ISNULL(REPLACE(City,'"',''), ''))),
StateProvince = LTRIM(RTRIM(ISNULL(REPLACE(StateProvince
,'"',''),
''))),
PostalCode = LTRIM(RTRIM(ISNULL(REPLACE(PostalCode,'"',''), ''))),
Country = LTRIM(RTRIM(ISNULL(REPLACE(Country,'"',''), ''))),
SubIndustryCode = LTRIM(RTRIM(ISNULL(REPLACE(SubIndustryCo
de,'"',''),
''))),
SicCode = LTRIM(RTRIM(ISNULL(REPLACE(SicCode,'"',''), ''))),
RdhType = LTRIM(RTRIM(ISNULL(REPLACE(RdhType,'"',''), ''))),
RdhID = LTRIM(RTRIM(ISNULL(REPLACE(RdhID,'"',''), ''))),
WWCustomerNumber = LTRIM(RTRIM(ISNULL(REPLACE(WWCustomerNum
ber,'"',''),
''))),
DunsNumber = LTRIM(RTRIM(ISNULL(REPLACE(DunsNumber,'"',''), ''))),
EstablishmentSize =
LTRIM(RTRIM(ISNULL(REPLACE(Establishment
Size,'"',''), ''))),
ContactTitle = LTRIM(RTRIM(ISNULL(REPLACE(ContactTitle,
'"',''), ''))),
FirstName = LTRIM(RTRIM(ISNULL(REPLACE(FirstName,'"',''), ''))),
FullName = LTRIM(RTRIM(ISNULL(REPLACE(FullName,'"',''), ''))),
ContactJob = LTRIM(RTRIM(ISNULL(REPLACE(ContactJob,'"',''), ''))),
ContactPhoneNumber =
LTRIM(RTRIM(ISNULL(REPLACE(ContactPhoneN
umber,'"',''), ''))),
IbmContactNumber = LTRIM(RTRIM(ISNULL(REPLACE(IbmContactNum
ber,'"',''),
''))),
ProductVendor = LTRIM(RTRIM(ISNULL(REPLACE(ProductVendor
,'"',''),
''))),
ProductName = LTRIM(RTRIM(ISNULL(REPLACE(ProductName,'
"',''), ''))),
SoftwareVersion = LTRIM(RTRIM(ISNULL(REPLACE(SoftwareVersi
on,'"',''),
''))),
SoftwareOS = LTRIM(RTRIM(ISNULL(REPLACE(SoftwareOS,'"',''), ''))),
HardwareSeries = LTRIM(RTRIM(ISNULL(REPLACE(HardwareSerie
s,'"',''),
''))),
HardwareProductGroup =
LTRIM(RTRIM(ISNULL(REPLACE(HardwareProdu
ctGroup,'"',''), ''))),
SoftwareProductID =
LTRIM(RTRIM(ISNULL(REPLACE(SoftwareProdu
ctID,'"',''), ''))),
SoftwareComponentID =
LTRIM(RTRIM(ISNULL(REPLACE(SoftwareCompo
nentID,'"',''), ''))),
SoftwarePartNumber =
LTRIM(RTRIM(ISNULL(REPLACE(SoftwarePartN
umber,'"',''), ''))),
HardwareModel = LTRIM(RTRIM(ISNULL(REPLACE(HardwareModel
,'"',''),
''))),
HardwareMachineType =
LTRIM(RTRIM(ISNULL(REPLACE(HardwareMachi
neType,'"',''), ''))),
ContactEmail = LTRIM(RTRIM(ISNULL(REPLACE(ContactEmail,
'"',''), ''))),
CompanyNameLong = LTRIM(RTRIM(ISNULL(REPLACE(CompanyNameLo
ng,'"',''),
''))),
InterviewLanguage =
LTRIM(RTRIM(ISNULL(REPLACE(InterviewLang
uage,'"',''), ''))),
Salutation = LTRIM(RTRIM(ISNULL(REPLACE(Salutation,'"',''), ''))),
CompanyNameAbbreviation =
LTRIM(RTRIM(ISNULL(REPLACE(CompanyNameAb
breviation,'"',''), ''))),
UniversalCountryCode = LTRIM(RTRIM(ISNULL(REPLACE(EmailFlag,'"',''),
''))),
EmailFlag = LTRIM(RTRIM(ISNULL(REPLACE(EmailFlag,'"',''), ''))),
SurveyFlag = LTRIM(RTRIM(ISNULL(REPLACE(SurveyFlag,'"',''), '')))
WHERE SampleSourceKey = @.sampleSourceKey
Each of the columns is NVARCHAR(255) Don't be alarmed, the records are never
actually that big. The nature of the data is that certain records use
certain columns (not my fault).
Business Rule:
Each value has to get trimmed, replace nulls with ZLS's and drop any
existing double-quotes.
My Complaint:
Since a function gets invoked once for every record, then if there are 42
columns getting updated, and each one requires 4 nested function calls, that
seems to me that each record getting updated needs 168 function calls, and a
@.sampleSourceKey in the where clause could make it touch anywhere from a
half million records to over a million records.
My Worse Complaint:
The have just changed the business rules to also require me to remove any of
these characters from every value in the SampleSource block:
10, 13, 34, 39, 46, 145, 146, 147, 148
I *really* don't want to make this uglier than it already is, so I have
thought of three different ways to do it:
1.
ltrim(rtrim(replace(replace(replace(repl
ace(replace(...n...(isnull(column,
''))))))))) for each column in the set clause (I REALLY don't want to do
this)
2. Make a CleanGarbage(columnvalue) UDF that basically does #1 above, but at
least it encapsulates the mess (still has gobbles of function call
overhead)
3. Use sp_OACreate to get an instance of the Scripting.RegExp COM-based
regular expression object, hold onto its handle, and pass the handle and
value to be cleaned to a UDF. In this example all the UDF calls that run
the regular expression would be using the same instance of the same object,
have cleaner code, be more maintainable because when the character list to
be replaced changes, I'd only have to modify the regex pattern, and wouldn't
have the vast number of calls as #1 & #2, but on the other hand, this is
pretty obscure technique and I'm afraid that other people that come after me
might choke on it.
What are the comments about my three ideas? Feel free to suggest something
I haven't thought of -- I'd really like to have this be cleaner, and faster,
if possible. Speed is actually not a requirement, as this is about 3
percent of the work of this batch, and the stuff runs for hours at a time
because of other reasons.
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSenseiIs DTS an option in this situation?
Rick Sawtell
MCT, MCSD, MCDBA|||> 1.
> ltrim(rtrim(replace(replace(replace(repl
ace(replace(...n...(isnull(column,
> ''))))))))) for each column in the set clause (I REALLY don't want to do
> this)
urgg, i never ever want to maintain that !
> 2. Make a CleanGarbage(columnvalue) UDF that basically does #1 above, but
> at least it encapsulates the mess (still has gobbles of function call
> overhead)
As text base functions are very slow in 2000 that wouldnt make it better, I
working on a exmaple to comapre the effeciency of UDT and SQL Server 2005
.NET Assemblies, just checkout my sites from time to time, they will be on
in the next few w
s. perhaps ou can download, modify and try them with
your data.
3. Use sp_OACreate to get an instance of the Scripting.RegExp COM-based
> regular expression object, hold onto its handle, and pass the handle and
Wait a minute, that wouldnt be really nice at all. :-(
Nor of your suggestions really make me happy, if you are doing such
"immense" inserts in the database, I would write some code in .NET with some
kind of tracing. Using a serverside cursor and scrolling down the pages with
skipping the rows that doesnt really work with your translation and mark
them with some kind of (bit)flag that you can clean if some malicious data
was inserted into the database and coulnt be transformed. This application
could be run via commandprompt / winApp / WebApp or service.
If you cant code .NET well have to find something else.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
> I *really* don't want to make this uglier than it already is, so I have
> thought of three different ways to do it:
> 1.
> ltrim(rtrim(replace(replace(replace(repl
ace(replace(...n...(isnull(column,
> ''))))))))) for each column in the set clause (I REALLY don't want to do
> this)
> 2. Make a CleanGarbage(columnvalue) UDF that basically does #1 above, but
> at least it encapsulates the mess (still has gobbles of function call
> overhead)
> 3. Use sp_OACreate to get an instance of the Scripting.RegExp COM-based
> regular expression object, hold onto its handle, and pass the handle and
> value to be cleaned to a UDF. In this example all the UDF calls that run
> the regular expression would be using the same instance of the same
> object, have cleaner code, be more maintainable because when the character
> list to be replaced changes, I'd only have to modify the regex pattern,
> and wouldn't have the vast number of calls as #1 & #2, but on the other
> hand, this is pretty obscure technique and I'm afraid that other people
> that come after me might choke on it.
> What are the comments about my three ideas? Feel free to suggest
> something I haven't thought of -- I'd really like to have this be cleaner,
> and faster, if possible. Speed is actually not a requirement, as this is
> about 3 percent of the work of this batch, and the stuff runs for hours at
> a time because of other reasons.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||> Is DTS an option in this situation?
I personally am not that well versed in DTS. I would have to read up on
some of its different transform features.
Also in the end, the package would have to be invokable either from a stored
procedure, or .NET. Can that be done?
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23qP7bYNVFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||I would rather code an assembly which can be used by calling it from a
stored procedure (via XP_CMDSHELL) or just at thta time as you want to (via
WInApp, etc.). In DTS the great deal is doing things parallel, theres no
user for that in here because it will be updated as a whole.
HTH, Jens Suessmeyer.
http://www.sqlsever2005.de
--
"Mike Labosh" <mlabosh@.hotmail.com> schrieb im Newsbeitrag
news:uQcxufNVFHA.4092@.TK2MSFTNGP12.phx.gbl...
> I personally am not that well versed in DTS. I would have to read up on
> some of its different transform features.
> Also in the end, the package would have to be invokable either from a
> stored procedure, or .NET. Can that be done?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23qP7bYNVFHA.4092@.TK2MSFTNGP12.phx.gbl...
>|||I prefer to do all this in a pre-processing stage on the application side,
often via a .NET app. It has much better text processing/manipulation
capabilities.
Based on doing it in SQL 2K, I would think any of your possible solutions
would run pretty poorly... sp_OACreate especially... That seems like some
serious overkill. If you're stuck with doing this SQL Server-side, might as
well put it in a SP and/or UDF to at least keep some level of
maintainability... Can you imagine the mess in trying to update your #1
solution below when they decide they want to eliminate all ASCII character
code 9's from the data next w?
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OwFpG9MVFHA.2664@.TK2MSFTNGP15.phx.gbl...
>I have the following SP that is the first step of cleaning bulk inserted
>files before matching records against existing ones in the database:
> UPDATE SampleSourceArchive
> SET CompanyName = LTRIM(RTRIM(ISNULL(REPLACE(CompanyName,'
"',''),
> ''))),
> CompanyPhoneArea =
> LTRIM(RTRIM(ISNULL(REPLACE(CompanyPhoneA
rea,'"',''), ''))),
> CompanyPhoneNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(CompanyPhoneN
umber,'"',''), ''))),
> CompanyAddress1 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s1,'"',''),
> ''))),
> CompanyAddress2 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s2,'"',''),
> ''))),
> CompanyAddress3 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s3,'"',''),
> ''))),
> City = LTRIM(RTRIM(ISNULL(REPLACE(City,'"',''), ''))),
> StateProvince = LTRIM(RTRIM(ISNULL(REPLACE(StateProvince
,'"',''),
> ''))),
> PostalCode = LTRIM(RTRIM(ISNULL(REPLACE(PostalCode,'"',''), ''))),
> Country = LTRIM(RTRIM(ISNULL(REPLACE(Country,'"',''), ''))),
> SubIndustryCode = LTRIM(RTRIM(ISNULL(REPLACE(SubIndustryCo
de,'"',''),
> ''))),
> SicCode = LTRIM(RTRIM(ISNULL(REPLACE(SicCode,'"',''), ''))),
> RdhType = LTRIM(RTRIM(ISNULL(REPLACE(RdhType,'"',''), ''))),
> RdhID = LTRIM(RTRIM(ISNULL(REPLACE(RdhID,'"',''), ''))),
> WWCustomerNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(WWCustomerNum
ber,'"',''), ''))),
> DunsNumber = LTRIM(RTRIM(ISNULL(REPLACE(DunsNumber,'"',''), ''))),
> EstablishmentSize =
> LTRIM(RTRIM(ISNULL(REPLACE(Establishment
Size,'"',''), ''))),
> ContactTitle = LTRIM(RTRIM(ISNULL(REPLACE(ContactTitle,
'"',''),
> ''))),
> FirstName = LTRIM(RTRIM(ISNULL(REPLACE(FirstName,'"',''), ''))),
> FullName = LTRIM(RTRIM(ISNULL(REPLACE(FullName,'"',''), ''))),
> ContactJob = LTRIM(RTRIM(ISNULL(REPLACE(ContactJob,'"',''), ''))),
> ContactPhoneNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(ContactPhoneN
umber,'"',''), ''))),
> IbmContactNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(IbmContactNum
ber,'"',''), ''))),
> ProductVendor = LTRIM(RTRIM(ISNULL(REPLACE(ProductVendor
,'"',''),
> ''))),
> ProductName = LTRIM(RTRIM(ISNULL(REPLACE(ProductName,'
"',''), ''))),
> SoftwareVersion = LTRIM(RTRIM(ISNULL(REPLACE(SoftwareVersi
on,'"',''),
> ''))),
> SoftwareOS = LTRIM(RTRIM(ISNULL(REPLACE(SoftwareOS,'"',''), ''))),
> HardwareSeries = LTRIM(RTRIM(ISNULL(REPLACE(HardwareSerie
s,'"',''),
> ''))),
> HardwareProductGroup =
> LTRIM(RTRIM(ISNULL(REPLACE(HardwareProdu
ctGroup,'"',''), ''))),
> SoftwareProductID =
> LTRIM(RTRIM(ISNULL(REPLACE(SoftwareProdu
ctID,'"',''), ''))),
> SoftwareComponentID =
> LTRIM(RTRIM(ISNULL(REPLACE(SoftwareCompo
nentID,'"',''), ''))),
> SoftwarePartNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(SoftwarePartN
umber,'"',''), ''))),
> HardwareModel = LTRIM(RTRIM(ISNULL(REPLACE(HardwareModel
,'"',''),
> ''))),
> HardwareMachineType =
> LTRIM(RTRIM(ISNULL(REPLACE(HardwareMachi
neType,'"',''), ''))),
> ContactEmail = LTRIM(RTRIM(ISNULL(REPLACE(ContactEmail,
'"',''),
> ''))),
> CompanyNameLong = LTRIM(RTRIM(ISNULL(REPLACE(CompanyNameLo
ng,'"',''),
> ''))),
> InterviewLanguage =
> LTRIM(RTRIM(ISNULL(REPLACE(InterviewLang
uage,'"',''), ''))),
> Salutation = LTRIM(RTRIM(ISNULL(REPLACE(Salutation,'"',''), ''))),
> CompanyNameAbbreviation =
> LTRIM(RTRIM(ISNULL(REPLACE(CompanyNameAb
breviation,'"',''), ''))),
> UniversalCountryCode = LTRIM(RTRIM(ISNULL(REPLACE(EmailFlag,'"',''),
> ''))),
> EmailFlag = LTRIM(RTRIM(ISNULL(REPLACE(EmailFlag,'"',''), ''))),
> SurveyFlag = LTRIM(RTRIM(ISNULL(REPLACE(SurveyFlag,'"',''), '')))
> WHERE SampleSourceKey = @.sampleSourceKey
> Each of the columns is NVARCHAR(255) Don't be alarmed, the records are
> never actually that big. The nature of the data is that certain records
> use certain columns (not my fault).
> Business Rule:
> Each value has to get trimmed, replace nulls with ZLS's and drop any
> existing double-quotes.
> My Complaint:
> Since a function gets invoked once for every record, then if there are 42
> columns getting updated, and each one requires 4 nested function calls,
> that seems to me that each record getting updated needs 168 function
> calls, and a @.sampleSourceKey in the where clause could make it touch
> anywhere from a half million records to over a million records.
> My Worse Complaint:
> The have just changed the business rules to also require me to remove any
> of these characters from every value in the SampleSource block:
> 10, 13, 34, 39, 46, 145, 146, 147, 148
> I *really* don't want to make this uglier than it already is, so I have
> thought of three different ways to do it:
> 1.
> ltrim(rtrim(replace(replace(replace(repl
ace(replace(...n...(isnull(column,
> ''))))))))) for each column in the set clause (I REALLY don't want to do
> this)
> 2. Make a CleanGarbage(columnvalue) UDF that basically does #1 above, but
> at least it encapsulates the mess (still has gobbles of function call
> overhead)
> 3. Use sp_OACreate to get an instance of the Scripting.RegExp COM-based
> regular expression object, hold onto its handle, and pass the handle and
> value to be cleaned to a UDF. In this example all the UDF calls that run
> the regular expression would be using the same instance of the same
> object, have cleaner code, be more maintainable because when the character
> list to be replaced changes, I'd only have to modify the regex pattern,
> and wouldn't have the vast number of calls as #1 & #2, but on the other
> hand, this is pretty obscure technique and I'm afraid that other people
> that come after me might choke on it.
> What are the comments about my three ideas? Feel free to suggest
> something I haven't thought of -- I'd really like to have this be cleaner,
> and faster, if possible. Speed is actually not a requirement, as this is
> about 3 percent of the work of this batch, and the stuff runs for hours at
> a time because of other reasons.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||Thanks All.
After a "team meeting", the boss has decided to go with my #1
ltrim(rtrim(replace(replace(replace...
Because, "It's the only thing you're going to get a solution on my the end
of today. Just load the SP into notepad and change the expression with a
find-replace."
I have very well documented my concerns. This is one of those places where
there solution is to do it fast and dumb, and if it runs like death, we'll
just throw a bigger server at it. LOL!
Sometimes there just aren't enough drugs.
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||Reminds me of the relationshsip between Dilbert and his boss --> Just do it
quick and dirty, you will have enough time for rest of your life to maintain
the errors ;-)
http://www.dailydilbert.com
TWH, Jens Suessmeyer.
"Mike Labosh" <mlabosh@.hotmail.com> schrieb im Newsbeitrag
news:eV5wsEOVFHA.3944@.tk2msftngp13.phx.gbl...
> Thanks All.
> After a "team meeting", the boss has decided to go with my #1
> ltrim(rtrim(replace(replace(replace...
> Because, "It's the only thing you're going to get a solution on my the end
> of today. Just load the SP into notepad and change the expression with a
> find-replace."
> I have very well documented my concerns. This is one of those places
> where there solution is to do it fast and dumb, and if it runs like death,
> we'll just throw a bigger server at it. LOL!
> Sometimes there just aren't enough drugs.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||The best managers tend to handle the business rules and leave the
implementation details to the developers. After all you don't see the
President sitting in the middle of a battlefield calculating coordinates for
outbound artillery shells.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eV5wsEOVFHA.3944@.tk2msftngp13.phx.gbl...
> Thanks All.
> After a "team meeting", the boss has decided to go with my #1
> ltrim(rtrim(replace(replace(replace...
> Because, "It's the only thing you're going to get a solution on my the end
> of today. Just load the SP into notepad and change the expression with a
> find-replace."
> I have very well documented my concerns. This is one of those places
> where there solution is to do it fast and dumb, and if it runs like death,
> we'll just throw a bigger server at it. LOL!
> Sometimes there just aren't enough drugs.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
files before matching records against existing ones in the database:
UPDATE SampleSourceArchive
SET CompanyName = LTRIM(RTRIM(ISNULL(REPLACE(CompanyName,'
"',''),
''))),
CompanyPhoneArea = LTRIM(RTRIM(ISNULL(REPLACE(CompanyPhoneA
rea,'"',''),
''))),
CompanyPhoneNumber =
LTRIM(RTRIM(ISNULL(REPLACE(CompanyPhoneN
umber,'"',''), ''))),
CompanyAddress1 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s1,'"',''),
''))),
CompanyAddress2 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s2,'"',''),
''))),
CompanyAddress3 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s3,'"',''),
''))),
City = LTRIM(RTRIM(ISNULL(REPLACE(City,'"',''), ''))),
StateProvince = LTRIM(RTRIM(ISNULL(REPLACE(StateProvince
,'"',''),
''))),
PostalCode = LTRIM(RTRIM(ISNULL(REPLACE(PostalCode,'"',''), ''))),
Country = LTRIM(RTRIM(ISNULL(REPLACE(Country,'"',''), ''))),
SubIndustryCode = LTRIM(RTRIM(ISNULL(REPLACE(SubIndustryCo
de,'"',''),
''))),
SicCode = LTRIM(RTRIM(ISNULL(REPLACE(SicCode,'"',''), ''))),
RdhType = LTRIM(RTRIM(ISNULL(REPLACE(RdhType,'"',''), ''))),
RdhID = LTRIM(RTRIM(ISNULL(REPLACE(RdhID,'"',''), ''))),
WWCustomerNumber = LTRIM(RTRIM(ISNULL(REPLACE(WWCustomerNum
ber,'"',''),
''))),
DunsNumber = LTRIM(RTRIM(ISNULL(REPLACE(DunsNumber,'"',''), ''))),
EstablishmentSize =
LTRIM(RTRIM(ISNULL(REPLACE(Establishment
Size,'"',''), ''))),
ContactTitle = LTRIM(RTRIM(ISNULL(REPLACE(ContactTitle,
'"',''), ''))),
FirstName = LTRIM(RTRIM(ISNULL(REPLACE(FirstName,'"',''), ''))),
FullName = LTRIM(RTRIM(ISNULL(REPLACE(FullName,'"',''), ''))),
ContactJob = LTRIM(RTRIM(ISNULL(REPLACE(ContactJob,'"',''), ''))),
ContactPhoneNumber =
LTRIM(RTRIM(ISNULL(REPLACE(ContactPhoneN
umber,'"',''), ''))),
IbmContactNumber = LTRIM(RTRIM(ISNULL(REPLACE(IbmContactNum
ber,'"',''),
''))),
ProductVendor = LTRIM(RTRIM(ISNULL(REPLACE(ProductVendor
,'"',''),
''))),
ProductName = LTRIM(RTRIM(ISNULL(REPLACE(ProductName,'
"',''), ''))),
SoftwareVersion = LTRIM(RTRIM(ISNULL(REPLACE(SoftwareVersi
on,'"',''),
''))),
SoftwareOS = LTRIM(RTRIM(ISNULL(REPLACE(SoftwareOS,'"',''), ''))),
HardwareSeries = LTRIM(RTRIM(ISNULL(REPLACE(HardwareSerie
s,'"',''),
''))),
HardwareProductGroup =
LTRIM(RTRIM(ISNULL(REPLACE(HardwareProdu
ctGroup,'"',''), ''))),
SoftwareProductID =
LTRIM(RTRIM(ISNULL(REPLACE(SoftwareProdu
ctID,'"',''), ''))),
SoftwareComponentID =
LTRIM(RTRIM(ISNULL(REPLACE(SoftwareCompo
nentID,'"',''), ''))),
SoftwarePartNumber =
LTRIM(RTRIM(ISNULL(REPLACE(SoftwarePartN
umber,'"',''), ''))),
HardwareModel = LTRIM(RTRIM(ISNULL(REPLACE(HardwareModel
,'"',''),
''))),
HardwareMachineType =
LTRIM(RTRIM(ISNULL(REPLACE(HardwareMachi
neType,'"',''), ''))),
ContactEmail = LTRIM(RTRIM(ISNULL(REPLACE(ContactEmail,
'"',''), ''))),
CompanyNameLong = LTRIM(RTRIM(ISNULL(REPLACE(CompanyNameLo
ng,'"',''),
''))),
InterviewLanguage =
LTRIM(RTRIM(ISNULL(REPLACE(InterviewLang
uage,'"',''), ''))),
Salutation = LTRIM(RTRIM(ISNULL(REPLACE(Salutation,'"',''), ''))),
CompanyNameAbbreviation =
LTRIM(RTRIM(ISNULL(REPLACE(CompanyNameAb
breviation,'"',''), ''))),
UniversalCountryCode = LTRIM(RTRIM(ISNULL(REPLACE(EmailFlag,'"',''),
''))),
EmailFlag = LTRIM(RTRIM(ISNULL(REPLACE(EmailFlag,'"',''), ''))),
SurveyFlag = LTRIM(RTRIM(ISNULL(REPLACE(SurveyFlag,'"',''), '')))
WHERE SampleSourceKey = @.sampleSourceKey
Each of the columns is NVARCHAR(255) Don't be alarmed, the records are never
actually that big. The nature of the data is that certain records use
certain columns (not my fault).
Business Rule:
Each value has to get trimmed, replace nulls with ZLS's and drop any
existing double-quotes.
My Complaint:
Since a function gets invoked once for every record, then if there are 42
columns getting updated, and each one requires 4 nested function calls, that
seems to me that each record getting updated needs 168 function calls, and a
@.sampleSourceKey in the where clause could make it touch anywhere from a
half million records to over a million records.
My Worse Complaint:
The have just changed the business rules to also require me to remove any of
these characters from every value in the SampleSource block:
10, 13, 34, 39, 46, 145, 146, 147, 148
I *really* don't want to make this uglier than it already is, so I have
thought of three different ways to do it:
1.
ltrim(rtrim(replace(replace(replace(repl
ace(replace(...n...(isnull(column,
''))))))))) for each column in the set clause (I REALLY don't want to do
this)
2. Make a CleanGarbage(columnvalue) UDF that basically does #1 above, but at
least it encapsulates the mess (still has gobbles of function call
overhead)
3. Use sp_OACreate to get an instance of the Scripting.RegExp COM-based
regular expression object, hold onto its handle, and pass the handle and
value to be cleaned to a UDF. In this example all the UDF calls that run
the regular expression would be using the same instance of the same object,
have cleaner code, be more maintainable because when the character list to
be replaced changes, I'd only have to modify the regex pattern, and wouldn't
have the vast number of calls as #1 & #2, but on the other hand, this is
pretty obscure technique and I'm afraid that other people that come after me
might choke on it.
What are the comments about my three ideas? Feel free to suggest something
I haven't thought of -- I'd really like to have this be cleaner, and faster,
if possible. Speed is actually not a requirement, as this is about 3
percent of the work of this batch, and the stuff runs for hours at a time
because of other reasons.
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSenseiIs DTS an option in this situation?
Rick Sawtell
MCT, MCSD, MCDBA|||> 1.
> ltrim(rtrim(replace(replace(replace(repl
ace(replace(...n...(isnull(column,
> ''))))))))) for each column in the set clause (I REALLY don't want to do
> this)
urgg, i never ever want to maintain that !
> 2. Make a CleanGarbage(columnvalue) UDF that basically does #1 above, but
> at least it encapsulates the mess (still has gobbles of function call
> overhead)
As text base functions are very slow in 2000 that wouldnt make it better, I
working on a exmaple to comapre the effeciency of UDT and SQL Server 2005
.NET Assemblies, just checkout my sites from time to time, they will be on
in the next few w
s. perhaps ou can download, modify and try them withyour data.
3. Use sp_OACreate to get an instance of the Scripting.RegExp COM-based
> regular expression object, hold onto its handle, and pass the handle and
Wait a minute, that wouldnt be really nice at all. :-(
Nor of your suggestions really make me happy, if you are doing such
"immense" inserts in the database, I would write some code in .NET with some
kind of tracing. Using a serverside cursor and scrolling down the pages with
skipping the rows that doesnt really work with your translation and mark
them with some kind of (bit)flag that you can clean if some malicious data
was inserted into the database and coulnt be transformed. This application
could be run via commandprompt / winApp / WebApp or service.
If you cant code .NET well have to find something else.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
> I *really* don't want to make this uglier than it already is, so I have
> thought of three different ways to do it:
> 1.
> ltrim(rtrim(replace(replace(replace(repl
ace(replace(...n...(isnull(column,
> ''))))))))) for each column in the set clause (I REALLY don't want to do
> this)
> 2. Make a CleanGarbage(columnvalue) UDF that basically does #1 above, but
> at least it encapsulates the mess (still has gobbles of function call
> overhead)
> 3. Use sp_OACreate to get an instance of the Scripting.RegExp COM-based
> regular expression object, hold onto its handle, and pass the handle and
> value to be cleaned to a UDF. In this example all the UDF calls that run
> the regular expression would be using the same instance of the same
> object, have cleaner code, be more maintainable because when the character
> list to be replaced changes, I'd only have to modify the regex pattern,
> and wouldn't have the vast number of calls as #1 & #2, but on the other
> hand, this is pretty obscure technique and I'm afraid that other people
> that come after me might choke on it.
> What are the comments about my three ideas? Feel free to suggest
> something I haven't thought of -- I'd really like to have this be cleaner,
> and faster, if possible. Speed is actually not a requirement, as this is
> about 3 percent of the work of this batch, and the stuff runs for hours at
> a time because of other reasons.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||> Is DTS an option in this situation?
I personally am not that well versed in DTS. I would have to read up on
some of its different transform features.
Also in the end, the package would have to be invokable either from a stored
procedure, or .NET. Can that be done?
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23qP7bYNVFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||I would rather code an assembly which can be used by calling it from a
stored procedure (via XP_CMDSHELL) or just at thta time as you want to (via
WInApp, etc.). In DTS the great deal is doing things parallel, theres no
user for that in here because it will be updated as a whole.
HTH, Jens Suessmeyer.
http://www.sqlsever2005.de
--
"Mike Labosh" <mlabosh@.hotmail.com> schrieb im Newsbeitrag
news:uQcxufNVFHA.4092@.TK2MSFTNGP12.phx.gbl...
> I personally am not that well versed in DTS. I would have to read up on
> some of its different transform features.
> Also in the end, the package would have to be invokable either from a
> stored procedure, or .NET. Can that be done?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23qP7bYNVFHA.4092@.TK2MSFTNGP12.phx.gbl...
>|||I prefer to do all this in a pre-processing stage on the application side,
often via a .NET app. It has much better text processing/manipulation
capabilities.
Based on doing it in SQL 2K, I would think any of your possible solutions
would run pretty poorly... sp_OACreate especially... That seems like some
serious overkill. If you're stuck with doing this SQL Server-side, might as
well put it in a SP and/or UDF to at least keep some level of
maintainability... Can you imagine the mess in trying to update your #1
solution below when they decide they want to eliminate all ASCII character
code 9's from the data next w
?"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OwFpG9MVFHA.2664@.TK2MSFTNGP15.phx.gbl...
>I have the following SP that is the first step of cleaning bulk inserted
>files before matching records against existing ones in the database:
> UPDATE SampleSourceArchive
> SET CompanyName = LTRIM(RTRIM(ISNULL(REPLACE(CompanyName,'
"',''),
> ''))),
> CompanyPhoneArea =
> LTRIM(RTRIM(ISNULL(REPLACE(CompanyPhoneA
rea,'"',''), ''))),
> CompanyPhoneNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(CompanyPhoneN
umber,'"',''), ''))),
> CompanyAddress1 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s1,'"',''),
> ''))),
> CompanyAddress2 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s2,'"',''),
> ''))),
> CompanyAddress3 = LTRIM(RTRIM(ISNULL(REPLACE(CompanyAddres
s3,'"',''),
> ''))),
> City = LTRIM(RTRIM(ISNULL(REPLACE(City,'"',''), ''))),
> StateProvince = LTRIM(RTRIM(ISNULL(REPLACE(StateProvince
,'"',''),
> ''))),
> PostalCode = LTRIM(RTRIM(ISNULL(REPLACE(PostalCode,'"',''), ''))),
> Country = LTRIM(RTRIM(ISNULL(REPLACE(Country,'"',''), ''))),
> SubIndustryCode = LTRIM(RTRIM(ISNULL(REPLACE(SubIndustryCo
de,'"',''),
> ''))),
> SicCode = LTRIM(RTRIM(ISNULL(REPLACE(SicCode,'"',''), ''))),
> RdhType = LTRIM(RTRIM(ISNULL(REPLACE(RdhType,'"',''), ''))),
> RdhID = LTRIM(RTRIM(ISNULL(REPLACE(RdhID,'"',''), ''))),
> WWCustomerNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(WWCustomerNum
ber,'"',''), ''))),
> DunsNumber = LTRIM(RTRIM(ISNULL(REPLACE(DunsNumber,'"',''), ''))),
> EstablishmentSize =
> LTRIM(RTRIM(ISNULL(REPLACE(Establishment
Size,'"',''), ''))),
> ContactTitle = LTRIM(RTRIM(ISNULL(REPLACE(ContactTitle,
'"',''),
> ''))),
> FirstName = LTRIM(RTRIM(ISNULL(REPLACE(FirstName,'"',''), ''))),
> FullName = LTRIM(RTRIM(ISNULL(REPLACE(FullName,'"',''), ''))),
> ContactJob = LTRIM(RTRIM(ISNULL(REPLACE(ContactJob,'"',''), ''))),
> ContactPhoneNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(ContactPhoneN
umber,'"',''), ''))),
> IbmContactNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(IbmContactNum
ber,'"',''), ''))),
> ProductVendor = LTRIM(RTRIM(ISNULL(REPLACE(ProductVendor
,'"',''),
> ''))),
> ProductName = LTRIM(RTRIM(ISNULL(REPLACE(ProductName,'
"',''), ''))),
> SoftwareVersion = LTRIM(RTRIM(ISNULL(REPLACE(SoftwareVersi
on,'"',''),
> ''))),
> SoftwareOS = LTRIM(RTRIM(ISNULL(REPLACE(SoftwareOS,'"',''), ''))),
> HardwareSeries = LTRIM(RTRIM(ISNULL(REPLACE(HardwareSerie
s,'"',''),
> ''))),
> HardwareProductGroup =
> LTRIM(RTRIM(ISNULL(REPLACE(HardwareProdu
ctGroup,'"',''), ''))),
> SoftwareProductID =
> LTRIM(RTRIM(ISNULL(REPLACE(SoftwareProdu
ctID,'"',''), ''))),
> SoftwareComponentID =
> LTRIM(RTRIM(ISNULL(REPLACE(SoftwareCompo
nentID,'"',''), ''))),
> SoftwarePartNumber =
> LTRIM(RTRIM(ISNULL(REPLACE(SoftwarePartN
umber,'"',''), ''))),
> HardwareModel = LTRIM(RTRIM(ISNULL(REPLACE(HardwareModel
,'"',''),
> ''))),
> HardwareMachineType =
> LTRIM(RTRIM(ISNULL(REPLACE(HardwareMachi
neType,'"',''), ''))),
> ContactEmail = LTRIM(RTRIM(ISNULL(REPLACE(ContactEmail,
'"',''),
> ''))),
> CompanyNameLong = LTRIM(RTRIM(ISNULL(REPLACE(CompanyNameLo
ng,'"',''),
> ''))),
> InterviewLanguage =
> LTRIM(RTRIM(ISNULL(REPLACE(InterviewLang
uage,'"',''), ''))),
> Salutation = LTRIM(RTRIM(ISNULL(REPLACE(Salutation,'"',''), ''))),
> CompanyNameAbbreviation =
> LTRIM(RTRIM(ISNULL(REPLACE(CompanyNameAb
breviation,'"',''), ''))),
> UniversalCountryCode = LTRIM(RTRIM(ISNULL(REPLACE(EmailFlag,'"',''),
> ''))),
> EmailFlag = LTRIM(RTRIM(ISNULL(REPLACE(EmailFlag,'"',''), ''))),
> SurveyFlag = LTRIM(RTRIM(ISNULL(REPLACE(SurveyFlag,'"',''), '')))
> WHERE SampleSourceKey = @.sampleSourceKey
> Each of the columns is NVARCHAR(255) Don't be alarmed, the records are
> never actually that big. The nature of the data is that certain records
> use certain columns (not my fault).
> Business Rule:
> Each value has to get trimmed, replace nulls with ZLS's and drop any
> existing double-quotes.
> My Complaint:
> Since a function gets invoked once for every record, then if there are 42
> columns getting updated, and each one requires 4 nested function calls,
> that seems to me that each record getting updated needs 168 function
> calls, and a @.sampleSourceKey in the where clause could make it touch
> anywhere from a half million records to over a million records.
> My Worse Complaint:
> The have just changed the business rules to also require me to remove any
> of these characters from every value in the SampleSource block:
> 10, 13, 34, 39, 46, 145, 146, 147, 148
> I *really* don't want to make this uglier than it already is, so I have
> thought of three different ways to do it:
> 1.
> ltrim(rtrim(replace(replace(replace(repl
ace(replace(...n...(isnull(column,
> ''))))))))) for each column in the set clause (I REALLY don't want to do
> this)
> 2. Make a CleanGarbage(columnvalue) UDF that basically does #1 above, but
> at least it encapsulates the mess (still has gobbles of function call
> overhead)
> 3. Use sp_OACreate to get an instance of the Scripting.RegExp COM-based
> regular expression object, hold onto its handle, and pass the handle and
> value to be cleaned to a UDF. In this example all the UDF calls that run
> the regular expression would be using the same instance of the same
> object, have cleaner code, be more maintainable because when the character
> list to be replaced changes, I'd only have to modify the regex pattern,
> and wouldn't have the vast number of calls as #1 & #2, but on the other
> hand, this is pretty obscure technique and I'm afraid that other people
> that come after me might choke on it.
> What are the comments about my three ideas? Feel free to suggest
> something I haven't thought of -- I'd really like to have this be cleaner,
> and faster, if possible. Speed is actually not a requirement, as this is
> about 3 percent of the work of this batch, and the stuff runs for hours at
> a time because of other reasons.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||Thanks All.
After a "team meeting", the boss has decided to go with my #1
ltrim(rtrim(replace(replace(replace...
Because, "It's the only thing you're going to get a solution on my the end
of today. Just load the SP into notepad and change the expression with a
find-replace."
I have very well documented my concerns. This is one of those places where
there solution is to do it fast and dumb, and if it runs like death, we'll
just throw a bigger server at it. LOL!
Sometimes there just aren't enough drugs.
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||Reminds me of the relationshsip between Dilbert and his boss --> Just do it
quick and dirty, you will have enough time for rest of your life to maintain
the errors ;-)
http://www.dailydilbert.com
TWH, Jens Suessmeyer.
"Mike Labosh" <mlabosh@.hotmail.com> schrieb im Newsbeitrag
news:eV5wsEOVFHA.3944@.tk2msftngp13.phx.gbl...
> Thanks All.
> After a "team meeting", the boss has decided to go with my #1
> ltrim(rtrim(replace(replace(replace...
> Because, "It's the only thing you're going to get a solution on my the end
> of today. Just load the SP into notepad and change the expression with a
> find-replace."
> I have very well documented my concerns. This is one of those places
> where there solution is to do it fast and dumb, and if it runs like death,
> we'll just throw a bigger server at it. LOL!
> Sometimes there just aren't enough drugs.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||The best managers tend to handle the business rules and leave the
implementation details to the developers. After all you don't see the
President sitting in the middle of a battlefield calculating coordinates for
outbound artillery shells.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eV5wsEOVFHA.3944@.tk2msftngp13.phx.gbl...
> Thanks All.
> After a "team meeting", the boss has decided to go with my #1
> ltrim(rtrim(replace(replace(replace...
> Because, "It's the only thing you're going to get a solution on my the end
> of today. Just load the SP into notepad and change the expression with a
> find-replace."
> I have very well documented my concerns. This is one of those places
> where there solution is to do it fast and dumb, and if it runs like death,
> we'll just throw a bigger server at it. LOL!
> Sometimes there just aren't enough drugs.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
Sunday, March 11, 2012
Choosing a record by radiobutton
Please help me out:
I have some records in a sqldatasource and want to show it column wise. Now I do it with a datalist because it's easy. But other options are open.
Every item/record should have a radiobutton (in a group, so that you can only choose one from all). People advised me to do this with a html radiobutton inside the template.
After the user has selected an item and chooses the next-button I need to know what item the user has choosen.
Furthermore, when the user likes to step back, the same radiobutton should allready be selected.
Please help, this is bothering me for a while,
best regards from The Netherlands,
Gert
Addition to my question in the hope for more reply.
I have a sqldatasource with records. This I like to show to the user (when possible using paging). I need the user to select one item (I thought using a radiobutton). Somekind of radiobutton in a group, because only one could be selected.
Presenting the data with a datalist is really nice, but maybe I have to give up this nice buildin control and make my own thing.
A guy advised me to put a html radiobutton inside the template of the datalist. This works a bit, but the user has a next and previous all though the program. When I do that, and the user wants to do a previous, the choosen item should be allready selected with the radiobutton as he choose before. This is not possible for me at the moment.
this code someone provided me :
<input type="radio" name="keuze" value='<%# Eval("fruitmandnr") %>' onclick="form.submit();" <%#Hulpfunctie(Eval("fruitmandnr"))%> /
Function Hulpfunctie(ByVal obj As Object) As String
If (obj Is Nothing) Then Return ""
Dim keuze As Object = Request.Form("keuze")
If (keuze Is Nothing) Then Return ""
If (keuze.ToString() = obj.ToString) Then Return "checked"
Return ""
End Function
Wednesday, March 7, 2012
CheckPoint Pages/Sec counter
In EM, changed the checkpoint interval to 1. I monitored the CheckPoint
Pages/Sec counter and inserted 3000 records and waiting 10 minutes, I have
not seen any activity on this counter.
SQL 2000 SP4
Thanks,
Don
I'm trying to solve a problem where during the CheckPoint Pages/Sec that SQL
initates, i see in the Profiler: EventClass- "Batch Completed" has long
Duration times and impacts customers, i.e., their data that is expected to be
retrieved in a timely fashion is many, many times longer. I'm trying to gain
control of the CheckPoint interval.
Note that the frequency of checkpoints in a database does not depend on any
time-based measure. Rather, it depends on the amount of data modifications
that have been made and SQL Server's estimate of how long it may take to
rollforward these changes. If in its estimate it would take the 'recovery
interval" number of minutes (when the recovery interval is not set to 0), it
issues a checkpoint.
So in your case, one potential explanation is that you have not made enough
changes for SQL Server to think it needs one minute to rollforward the
changes, thus no checkpoint is necessary.
Linchi
"donsql22222" wrote:
> In EM, changed the checkpoint interval to 1. I monitored the CheckPoint
> Pages/Sec counter and inserted 3000 records and waiting 10 minutes, I have
> not seen any activity on this counter.
> SQL 2000 SP4
> Thanks,
> Don
> I'm trying to solve a problem where during the CheckPoint Pages/Sec that SQL
> initates, i see in the Profiler: EventClass- "Batch Completed" has long
> Duration times and impacts customers, i.e., their data that is expected to be
> retrieved in a timely fashion is many, many times longer. I'm trying to gain
> control of the CheckPoint interval.
Pages/Sec counter and inserted 3000 records and waiting 10 minutes, I have
not seen any activity on this counter.
SQL 2000 SP4
Thanks,
Don
I'm trying to solve a problem where during the CheckPoint Pages/Sec that SQL
initates, i see in the Profiler: EventClass- "Batch Completed" has long
Duration times and impacts customers, i.e., their data that is expected to be
retrieved in a timely fashion is many, many times longer. I'm trying to gain
control of the CheckPoint interval.
Note that the frequency of checkpoints in a database does not depend on any
time-based measure. Rather, it depends on the amount of data modifications
that have been made and SQL Server's estimate of how long it may take to
rollforward these changes. If in its estimate it would take the 'recovery
interval" number of minutes (when the recovery interval is not set to 0), it
issues a checkpoint.
So in your case, one potential explanation is that you have not made enough
changes for SQL Server to think it needs one minute to rollforward the
changes, thus no checkpoint is necessary.
Linchi
"donsql22222" wrote:
> In EM, changed the checkpoint interval to 1. I monitored the CheckPoint
> Pages/Sec counter and inserted 3000 records and waiting 10 minutes, I have
> not seen any activity on this counter.
> SQL 2000 SP4
> Thanks,
> Don
> I'm trying to solve a problem where during the CheckPoint Pages/Sec that SQL
> initates, i see in the Profiler: EventClass- "Batch Completed" has long
> Duration times and impacts customers, i.e., their data that is expected to be
> retrieved in a timely fashion is many, many times longer. I'm trying to gain
> control of the CheckPoint interval.
Saturday, February 25, 2012
Checking User & Expire of Update Possibility (by Trigger), How to?
Hi,
I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:
RateID, Service, Rate, DateTime, User
Want I want is a code (trigger) in the database can do the following:
If user perform an Update request the code will check:
- if this recored inserted by the same user update command will be execute.
- if this recored inserted by other user: update command will not execute and return message.
- if more than 5 minutes passed the update command will not be execute and return message.
Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.
Change the update statement from something like:
update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
to something like
|||update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())
Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?
and what if I want to put this code in Trigger?
thnx again,,,
|||Any help?|||Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.
The code idea below relies upon [User] being captured with the system_user system function (domain/username).
Code Snippet
CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
DECLARE @.User varchar(50)
IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END
GO
Hi,
I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:
CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)
END
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
Checking User & Expire of Update Possibility (by Trigger), How to?
Hi,
I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:
RateID, Service, Rate, DateTime, User
Want I want is a code (trigger) in the database can do the following:
If user perform an Update request the code will check:
- if this recored inserted by the same user update command will be execute.
- if this recored inserted by other user: update command will not execute and return message.
- if more than 5 minutes passed the update command will not be execute and return message.
Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.
Change the update statement from something like:
update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
to something like
|||update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())
Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?
and what if I want to put this code in Trigger?
thnx again,,,
|||Any help?|||Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.
The code idea below relies upon [User] being captured with the system_user system function (domain/username).
Code Snippet
CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
DECLARE @.User varchar(50)
IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END
GO
Hi,
I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:
CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)
END
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
Friday, February 24, 2012
Checking to see if a records exists before inserting - 3 million + rows
I have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.
I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.
To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.
Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.
Any thoughts would be great!
David Wynne wrote:
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.
You aren't doing a select * against the lookup table, are you?|||Of course not - just pseudo code to get across what I think I'd like to achieve.
|||
Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.
I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.
|||
Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.
I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.
Yep, the OP could use an Execute SQL task to load up a temporary table with the keys of the lookup table. Then, in the data flow, you can use the mentioned outer join to capture new records. That is, unless you need to repopulate the lookup table with the incoming "new" records before the next lookup.|||
David,
How many columns do you need to put in the lookup transformation to determine if a record exists? Unless we are taking about too many/wide columns; I don't see several millions to be a problem. Another option is to use partial cache with decent amount of memory; so the chances a row is not in cache is low.
Remember, always provide a query with only the columns that are strictly required for the join
checking the status of merge agent
My merge agent is getting stopped at midnight. because of which i have large
number of records which needs to be merged when i come in the morning.
Is it possible To create a job which will run every 3-4 hours, which will
check whether the merge agent is running or not? If the merge agent is
stopped then start the merge agent.
Is there a better way? Any suggestions?
just reschedule your merge agent to run every 5 minutes, or have step 4 on
failure return to step 1.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> My merge agent is getting stopped at midnight. because of which i have
large
> number of records which needs to be merged when i come in the morning.
> Is it possible To create a job which will run every 3-4 hours, which will
> check whether the merge agent is running or not? If the merge agent is
> stopped then start the merge agent.
> Is there a better way? Any suggestions?
>
|||> just reschedule your merge agent to run every 5 minutes,
This is a cool option...
> or have step 4 on failure return to step 1.
There is no step 4. I am using continous merge replication. There is only
one step. Hence i can't go to the first step. Or am I understanding it in a
different way?
"Hilary Cotter" wrote:
> just reschedule your merge agent to run every 5 minutes, or have step 4 on
> failure return to step 1.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> large
>
>
|||right click on your agent in the merge agents folder, select agent
properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
around to step 1 on failure. Click on the advanced tab to do this.
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> This is a cool option...
> There is no step 4. I am using continous merge replication. There is only
> one step. Hence i can't go to the first step. Or am I understanding it in
a[vbcol=seagreen]
> different way?
>
> "Hilary Cotter" wrote:
on[vbcol=seagreen]
will[vbcol=seagreen]
|||I know what you are saying.
But in my agent property i have only one step. No 3 or 4 steps.
I am using merge replication. What does the 2nd and 3rd step contain?
"Hilary Cotter" wrote:
> right click on your agent in the merge agents folder, select agent
> properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
> around to step 1 on failure. Click on the advanced tab to do this.
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> a
> on
> will
>
>
|||is this an ActiveX script you are running? Or are you pulling from SQL CE?
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...[vbcol=seagreen]
> I know what you are saying.
> But in my agent property i have only one step. No 3 or 4 steps.
> I am using merge replication. What does the 2nd and 3rd step contain?
> "Hilary Cotter" wrote:
wrap[vbcol=seagreen]
only[vbcol=seagreen]
in[vbcol=seagreen]
step 4[vbcol=seagreen]
have[vbcol=seagreen]
morning.[vbcol=seagreen]
which[vbcol=seagreen]
agent is[vbcol=seagreen]
|||I am not using ActiveX script
Also not SQL CE
"Hilary Cotter" wrote:
> is this an ActiveX script you are running? Or are you pulling from SQL CE?
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...
> wrap
> only
> in
> step 4
> have
> morning.
> which
> agent is
>
>
number of records which needs to be merged when i come in the morning.
Is it possible To create a job which will run every 3-4 hours, which will
check whether the merge agent is running or not? If the merge agent is
stopped then start the merge agent.
Is there a better way? Any suggestions?
just reschedule your merge agent to run every 5 minutes, or have step 4 on
failure return to step 1.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> My merge agent is getting stopped at midnight. because of which i have
large
> number of records which needs to be merged when i come in the morning.
> Is it possible To create a job which will run every 3-4 hours, which will
> check whether the merge agent is running or not? If the merge agent is
> stopped then start the merge agent.
> Is there a better way? Any suggestions?
>
|||> just reschedule your merge agent to run every 5 minutes,
This is a cool option...
> or have step 4 on failure return to step 1.
There is no step 4. I am using continous merge replication. There is only
one step. Hence i can't go to the first step. Or am I understanding it in a
different way?
"Hilary Cotter" wrote:
> just reschedule your merge agent to run every 5 minutes, or have step 4 on
> failure return to step 1.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> large
>
>
|||right click on your agent in the merge agents folder, select agent
properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
around to step 1 on failure. Click on the advanced tab to do this.
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> This is a cool option...
> There is no step 4. I am using continous merge replication. There is only
> one step. Hence i can't go to the first step. Or am I understanding it in
a[vbcol=seagreen]
> different way?
>
> "Hilary Cotter" wrote:
on[vbcol=seagreen]
will[vbcol=seagreen]
|||I know what you are saying.
But in my agent property i have only one step. No 3 or 4 steps.
I am using merge replication. What does the 2nd and 3rd step contain?
"Hilary Cotter" wrote:
> right click on your agent in the merge agents folder, select agent
> properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
> around to step 1 on failure. Click on the advanced tab to do this.
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> a
> on
> will
>
>
|||is this an ActiveX script you are running? Or are you pulling from SQL CE?
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...[vbcol=seagreen]
> I know what you are saying.
> But in my agent property i have only one step. No 3 or 4 steps.
> I am using merge replication. What does the 2nd and 3rd step contain?
> "Hilary Cotter" wrote:
wrap[vbcol=seagreen]
only[vbcol=seagreen]
in[vbcol=seagreen]
step 4[vbcol=seagreen]
have[vbcol=seagreen]
morning.[vbcol=seagreen]
which[vbcol=seagreen]
agent is[vbcol=seagreen]
|||I am not using ActiveX script
Also not SQL CE
"Hilary Cotter" wrote:
> is this an ActiveX script you are running? Or are you pulling from SQL CE?
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...
> wrap
> only
> in
> step 4
> have
> morning.
> which
> agent is
>
>
Sunday, February 19, 2012
Checking if anything changed
I have a series of records that I periodically sum up, and then place those
sums in a master record for easy reporting. Every time the sum changes, I
note down that a change was made by updating a datetime in the master.
The trick is knowing if anything changed or not. I do this...
DECLARE @.didChange AS boolean
SET @.didChange = FALSE
IF ((@.newFilled IS null AND @.oldFilled IS NOT null) OR (@.newFilled IS NOT
null AND @.oldFilled IS null) OR (ROUND(@.newFilled, 2) <> ROUND(@.oldFilled,
2))) @.didChange = TRUE
there's a couple more IFs following. BTW, there's a syntax error in there
somewhere, I'm still trying to find it.
My question is whether or not I can simplify the if down to something
smaller. As you can see, it tests three cases...
1) the value was changed TO null from anything
2) the value was changed FROM null to anything
3) the value simply changed
In the past I tried a greatly simplified solution...
IF ISNULL(@.newFilled, 0) <> ISNULL(@.oldFilled, 0) THEN @.didChange = TRUE
The problem with this approach is that it "misses" changes from null to zero
or back. It just didn't work right.
So can I do this...
IF ISNULL(@.newFilled, -1) <> ISNULL(@.oldFilled, -1) THEN...
The values are always positive, so they can never be -1. Do you think this
is a good approach, or is there some edge case I'm forgetting about?
MauryAhh, the syntax error...
obviously that should be a tinyint (or bit I guess) and there needs to be a
SET
those are fixed.
sums in a master record for easy reporting. Every time the sum changes, I
note down that a change was made by updating a datetime in the master.
The trick is knowing if anything changed or not. I do this...
DECLARE @.didChange AS boolean
SET @.didChange = FALSE
IF ((@.newFilled IS null AND @.oldFilled IS NOT null) OR (@.newFilled IS NOT
null AND @.oldFilled IS null) OR (ROUND(@.newFilled, 2) <> ROUND(@.oldFilled,
2))) @.didChange = TRUE
there's a couple more IFs following. BTW, there's a syntax error in there
somewhere, I'm still trying to find it.
My question is whether or not I can simplify the if down to something
smaller. As you can see, it tests three cases...
1) the value was changed TO null from anything
2) the value was changed FROM null to anything
3) the value simply changed
In the past I tried a greatly simplified solution...
IF ISNULL(@.newFilled, 0) <> ISNULL(@.oldFilled, 0) THEN @.didChange = TRUE
The problem with this approach is that it "misses" changes from null to zero
or back. It just didn't work right.
So can I do this...
IF ISNULL(@.newFilled, -1) <> ISNULL(@.oldFilled, -1) THEN...
The values are always positive, so they can never be -1. Do you think this
is a good approach, or is there some edge case I'm forgetting about?
MauryAhh, the syntax error...
obviously that should be a tinyint (or bit I guess) and there needs to be a
SET
those are fixed.
checking if a particular DATE LIES IN CURRENT QUARTER
I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name = "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name = "SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
Thanks
Use a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name = "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name = "SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
Thanks
Use a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>
checking if a particular DATE LIES IN CURRENT QUARTER
I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name
= "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name =
"SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
ThanksUse a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name
= "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name =
"SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
ThanksUse a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>
checking if a particular DATE LIES IN CURRENT QUARTER
I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name = "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name = "SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
ThanksUse a calendar table.
http://www.aspfaq.com/2519
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name = "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name = "SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
ThanksUse a calendar table.
http://www.aspfaq.com/2519
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>
Thursday, February 16, 2012
Checking for records where One value is NOT in the other?
I am trying to display results in which a value in my first result table is NOT contained in the second result table. Any idea on how to do this?.
SELECT CUSTOMERID, CUSTOMER FROM TABLE1
WHERE CUSTOMERID NOT IN TABLE2
Code Snippet
select c1.customerid, c1.customer
from table1 c1
left join table2 c2
on c1.customerid = c2.customerid
where c2.customerid is null
|||Excellent, thanks
Checking for null in Xtab query
I have the following query and it works. Where the left part of the join doe
s
not have any records i'd like to return a zero. I tried the case but, ...
thanks all
kes
select
c.calId,
c.calDate,
c.calShortDesc,
c.calDesc,
c.CalHoliday,
e3.evtAll,
CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
e3.SS,
e3.WS,
e3.C,
e3.WE,
e3.ED,
e3.NW
from cal c
LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
(select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdatetime
= e.evtdatetime) as NS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdatetime
= e.evtdatetime) as SS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdatetime
= e.evtdatetime) as WS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdatetime
= e.evtdatetime) as C,
(select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdatetime
= e.evtdatetime) as WE,
(select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdatetime
= e.evtdatetime) as ED,
(select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdatetime
= e.evtdatetime) as NW
from evt e
group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
where c.calDate >= '20050626' and c.calDate <= '20050806'
(I can post the Data def, but this is a simple question a bout returning a
zero for null)will this work?
CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
or is there a better idea?
thanks
kes
"WebBuilder451" wrote:
> I have the following query and it works. Where the left part of the join d
oes
> not have any records i'd like to return a zero. I tried the case but, ...
> thanks all
> kes
> select
> c.calId,
> c.calDate,
> c.calShortDesc,
> c.calDesc,
> c.CalHoliday,
> e3.evtAll,
> CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
> e3.SS,
> e3.WS,
> e3.C,
> e3.WE,
> e3.ED,
> e3.NW
> from cal c
> LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdateti
me
> = e.evtdatetime) as NS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdateti
me
> = e.evtdatetime) as SS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdateti
me
> = e.evtdatetime) as WS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdateti
me
> = e.evtdatetime) as C,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdateti
me
> = e.evtdatetime) as WE,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdateti
me
> = e.evtdatetime) as ED,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdateti
me
> = e.evtdatetime) as NW
> from evt e
> group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
> where c.calDate >= '20050626' and c.calDate <= '20050806'
> (I can post the Data def, but this is a simple question a bout returning a
> zero for null)
>
>|||Yes, that will work.
Why haven't you tried it?
ML|||You don't even need a Case expression. The following will work:
IsNull(e3.NS,0) as NS
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
> will this work?
> CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
> or is there a better idea?
> thanks
> kes
> "WebBuilder451" wrote:
>|||that's the answer !!!
thanks
IOU1 kes
"JT" wrote:
> You don't even need a Case expression. The following will work:
> IsNull(e3.NS,0) as NS
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
>
>
s
not have any records i'd like to return a zero. I tried the case but, ...
thanks all
kes
select
c.calId,
c.calDate,
c.calShortDesc,
c.calDesc,
c.CalHoliday,
e3.evtAll,
CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
e3.SS,
e3.WS,
e3.C,
e3.WE,
e3.ED,
e3.NW
from cal c
LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
(select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdatetime
= e.evtdatetime) as NS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdatetime
= e.evtdatetime) as SS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdatetime
= e.evtdatetime) as WS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdatetime
= e.evtdatetime) as C,
(select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdatetime
= e.evtdatetime) as WE,
(select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdatetime
= e.evtdatetime) as ED,
(select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdatetime
= e.evtdatetime) as NW
from evt e
group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
where c.calDate >= '20050626' and c.calDate <= '20050806'
(I can post the Data def, but this is a simple question a bout returning a
zero for null)will this work?
CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
or is there a better idea?
thanks
kes
"WebBuilder451" wrote:
> I have the following query and it works. Where the left part of the join d
oes
> not have any records i'd like to return a zero. I tried the case but, ...
> thanks all
> kes
> select
> c.calId,
> c.calDate,
> c.calShortDesc,
> c.calDesc,
> c.CalHoliday,
> e3.evtAll,
> CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
> e3.SS,
> e3.WS,
> e3.C,
> e3.WE,
> e3.ED,
> e3.NW
> from cal c
> LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdateti
me
> = e.evtdatetime) as NS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdateti
me
> = e.evtdatetime) as SS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdateti
me
> = e.evtdatetime) as WS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdateti
me
> = e.evtdatetime) as C,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdateti
me
> = e.evtdatetime) as WE,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdateti
me
> = e.evtdatetime) as ED,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdateti
me
> = e.evtdatetime) as NW
> from evt e
> group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
> where c.calDate >= '20050626' and c.calDate <= '20050806'
> (I can post the Data def, but this is a simple question a bout returning a
> zero for null)
>
>|||Yes, that will work.
Why haven't you tried it?
ML|||You don't even need a Case expression. The following will work:
IsNull(e3.NS,0) as NS
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
> will this work?
> CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
> or is there a better idea?
> thanks
> kes
> "WebBuilder451" wrote:
>|||that's the answer !!!
thanks
IOU1 kes
"JT" wrote:
> You don't even need a Case expression. The following will work:
> IsNull(e3.NS,0) as NS
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
>
>
Checking field within a selected record (**)
Hello,
I have sql statement that is selecting multiple invoice records and doing
multiple calculations.
Now, I need to look in each invoice record for Item_detail, any direction on
how I would do that would be appreciated?
Thanks.Hi
Can you post DDL+ sample data+ expected result?
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:976B69D5-44EA-43F9-A043-D9B9AAF4A83F@.microsoft.com...
> Hello,
> I have sql statement that is selecting multiple invoice records and doing
> multiple calculations.
> Now, I need to look in each invoice record for Item_detail, any direction
> on
> how I would do that would be appreciated?
> Thanks.|||is there a message board or something where we can put this as a disclaimer
:)|||That would be nice. Along with a post explaining how to return a
concatenated list of values for a single column in multiple rows, and a
"BEWARE OF --CELKO--" sign.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BE559B8C-7908-4E95-8C94-CAB986982C81@.microsoft.com...
> is there a message board or something where we can put this as a
disclaimer :)|||Oh come on... We all love Celko... LOL
Grant
Who gives a {censored} if I am wrong.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23akCA2OeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> That would be nice. Along with a post explaining how to return a
> concatenated list of values for a single column in multiple rows, and a
> "BEWARE OF --CELKO--" sign.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:BE559B8C-7908-4E95-8C94-CAB986982C81@.microsoft.com...
> disclaimer :)
>|||of course we love him as long as we are not in the firing end :)
--
"Grant" wrote:
> Oh come on... We all love Celko... LOL
> --
> Grant
> Who gives a {censored} if I am wrong.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23akCA2OeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>
>|||Just to explain what is meant by DDL and sample data...
http://www.aspfaq.com/etiquette.asp?id=5006
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:976B69D5-44EA-43F9-A043-D9B9AAF4A83F@.microsoft.com...
> Hello,
> I have sql statement that is selecting multiple invoice records and doing
> multiple calculations.
> Now, I need to look in each invoice record for Item_detail, any direction
on
> how I would do that would be appreciated?
> Thanks.|||Did I ask for something peculiar?
I thought this was a helping board.
"Omnibuzz" wrote:
> of course we love him as long as we are not in the firing end :)
> --
>
>
> "Grant" wrote:
>|||Sorry, we got a little off topic, it happens sometimes. Nothign at all odd
for what you asked for, only that it was very vague, and we could give a
hundred answers, most of which would be completely unrelated to what you are
trying to do. You did not include enough information for us to give you a
good answer. Please see my other post and respond with the needed
information.
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:5B83C9D2-7A2C-46DD-B0DA-39D1B6483548@.microsoft.com...
> Did I ask for something peculiar?
> I thought this was a helping board.
> "Omnibuzz" wrote:
>
and a|||Oops.. sorry.. we thought we'll keep the thread alive till you get back with
the ddls and sample data.
--
"ITDUDE27" wrote:
> Did I ask for something peculiar?
> I thought this was a helping board.
> "Omnibuzz" wrote:
>
I have sql statement that is selecting multiple invoice records and doing
multiple calculations.
Now, I need to look in each invoice record for Item_detail, any direction on
how I would do that would be appreciated?
Thanks.Hi
Can you post DDL+ sample data+ expected result?
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:976B69D5-44EA-43F9-A043-D9B9AAF4A83F@.microsoft.com...
> Hello,
> I have sql statement that is selecting multiple invoice records and doing
> multiple calculations.
> Now, I need to look in each invoice record for Item_detail, any direction
> on
> how I would do that would be appreciated?
> Thanks.|||is there a message board or something where we can put this as a disclaimer
:)|||That would be nice. Along with a post explaining how to return a
concatenated list of values for a single column in multiple rows, and a
"BEWARE OF --CELKO--" sign.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BE559B8C-7908-4E95-8C94-CAB986982C81@.microsoft.com...
> is there a message board or something where we can put this as a
disclaimer :)|||Oh come on... We all love Celko... LOL
Grant
Who gives a {censored} if I am wrong.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23akCA2OeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> That would be nice. Along with a post explaining how to return a
> concatenated list of values for a single column in multiple rows, and a
> "BEWARE OF --CELKO--" sign.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:BE559B8C-7908-4E95-8C94-CAB986982C81@.microsoft.com...
> disclaimer :)
>|||of course we love him as long as we are not in the firing end :)
--
"Grant" wrote:
> Oh come on... We all love Celko... LOL
> --
> Grant
> Who gives a {censored} if I am wrong.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23akCA2OeGHA.4276@.TK2MSFTNGP03.phx.gbl...
>
>|||Just to explain what is meant by DDL and sample data...
http://www.aspfaq.com/etiquette.asp?id=5006
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:976B69D5-44EA-43F9-A043-D9B9AAF4A83F@.microsoft.com...
> Hello,
> I have sql statement that is selecting multiple invoice records and doing
> multiple calculations.
> Now, I need to look in each invoice record for Item_detail, any direction
on
> how I would do that would be appreciated?
> Thanks.|||Did I ask for something peculiar?
I thought this was a helping board.
"Omnibuzz" wrote:
> of course we love him as long as we are not in the firing end :)
> --
>
>
> "Grant" wrote:
>|||Sorry, we got a little off topic, it happens sometimes. Nothign at all odd
for what you asked for, only that it was very vague, and we could give a
hundred answers, most of which would be completely unrelated to what you are
trying to do. You did not include enough information for us to give you a
good answer. Please see my other post and respond with the needed
information.
"ITDUDE27" <ITDUDE27@.discussions.microsoft.com> wrote in message
news:5B83C9D2-7A2C-46DD-B0DA-39D1B6483548@.microsoft.com...
> Did I ask for something peculiar?
> I thought this was a helping board.
> "Omnibuzz" wrote:
>
and a|||Oops.. sorry.. we thought we'll keep the thread alive till you get back with
the ddls and sample data.
--
"ITDUDE27" wrote:
> Did I ask for something peculiar?
> I thought this was a helping board.
> "Omnibuzz" wrote:
>
Subscribe to:
Posts (Atom)