Case Statement Within A Select Where 2 or More Instances Of The Record Exist.

Ok,
I have a data warehouse that I am pulling records from using Oracle
SQL. I have a select statement that looks like the one below. Now what
I need to do is where the astrics are **** create a case statement or
whatever it is in Oracle to say that for this record if a 1/19/2005
record exists then End_Date needs to be=1/19/2005 else get
End_Date=12/31/9999. Keep in mind that a record could have both a
1/19/2005 and 12/31/9999 instance of that account record. If 1/19
exists that takes presedent if it doesnt then 12/31/9999. The problem
is that the fields I pull from the table where the end_date is in
question change based on which date I pull(12/31/9999 being the most
recient which in some cases as you see I dont want.) so they are not
identical. This is tricky.
Please let me know if you can help.

SELECT
COLLECTOR_RESULTS.USER_ID,
COLLECTOR_RESULTS.LETTER_CODE,
COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,
COLLECTOR_RESULTS.ACTIVITY_DATE,
COLLECTOR_RESULTS.BEGIN_DATE,
COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,
COLLECTOR_RESULTS.PLACE_CALLED,
COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,
COLLECTOR_RESULTS.ORIG_FUNC_AREA,
COLLECTOR_RESULTS.ORIG_STATE_NUMBER,
COLLECTOR_RESULTS.CACS_FUNCTION_CODE,
COLLECTOR_RESULTS.CACS_STATE_NUMBER,
COLLECTOR_RESULTS.STATE_POSITION,
COLLECTOR_RESULTS.TIME_OBTAINED,
COLLECTOR_RESULTS.TIME_RELEASED,
COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,
sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,
FROM
COLLECTOR_RESULTS,
COLLECT_ACCT_SYS_DATA,
COLLECT_ACCOUNT
WHERE
COLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)
AND
COLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)
AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)
AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)
AND COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''01/19/2005'',''mm/dd/yyyy'')
AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')
AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************On 20 Jan 2005 11:13:31 -0800, philipdm@.msn.com wrote:

>Ok,
>I have a data warehouse that I am pulling records from using Oracle
>SQL.

Hi philipdm,

You posted this question in a newsgroup for MS SQL Server. I doubt you'll
get any specific Oracle help here.

But if I understand your requirements correctly, I think you can solve it
using only ANSI-standard SQL: correlated subquery, group by and aggregate
functions. I assume Oracle will have no trouble running those! I'm not
sure if the NULL handling requires sppecial attention (see notes down
below).

First, let me check if I correctly understand your requirements:

> Keep in mind that a record could have both a
>1/19/2005 and 12/31/9999 instance of that account record. If 1/19
>exists that takes presedent if it doesnt then 12/31/9999.

The way I read this is: check collect_acct_sys_data for a particular
acct_num / location_code combinations. If there's a row for 1/19/2005, use
that. If there's a row for 12/31/9999 but no row for 1/19/2005, use the
12/31/9999 row. If there's no row for 1/19/2005 and no row for 12/31/9999,
use no row at all - the join will fail and the rows for the other tables
that use this acct_num / location_code combination won't be included in
the result set either.

I'd use something like the following. Note: I've used table aliasses and
converted the table and column names to lower case to improve readability;
I kept the (+) symbols you included and didn't change the format of the
to_date function calls - neither of these are known in MS SQL Server, so I
have no idea if they're right or wrong.

SELECT CR.User_ID,
... (lots of other columns)
FROM Collector_Results AS CR,
Collector_Acct_Sys_Date AS CASD,
Collect_Account AS CA
WHERE CA.Acct_Num = CASD.Acct_Num(+)
AND CA.Location_Code = CASD.Location_Code(+)
AND CA.Acct_Num = CR.Acct_Num(+)
AND CA.Location_Code = CR.Location_Code(+)
AND CR.Activity_Date = to_date(''01/19/2005'',''mm/dd/yyyy'')
AND CA.End_Date = to_date(''12/31/9999'',''mm/dd/yyyy'')
AND CASD.End_Date =
(SELECT MIN(CASD2.End_Date)
FROM Collector_Acct_Sys_Date AS CASD2
WHERE CASD2.Acct_Num = CASD.Acct_Num(+)-- Do you need the
(+) here?
AND CASD2.Location_Code = CASD.Location_Code(+)-- Do you
need the (+) here?
AND ( CASD2.End_Date = to_date(''01/19/2005'',''mm/dd/yyyy'')
OR CASD2.End_Date = to_date(''12/31/9999'',''mm/dd/yyyy'')))

Final note: if the possibility exists that no row in CASD for a given
acct_num / location_code with either of the two dates, the subquery should
return NULL; the clause "AND CASD.End_Date = (subquery)" will evaluate to
"AND CASD.End_Date = NULL", which should not be true for any value of
CASD.End_Date (not even if CASD.End_Date is NULL!!). This is how NULLS
should be treated according to ANSI standard. If Oracle treats the result
of an empty subquery or comparison to NULL differently, then you should
tweak the query to get the correct results.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

case statement in where clause

I would like to know that may I use case statement in where clause?
If not, are there any other solution to filter my records depends on my
field.
For example
MyType integer, MyDate datetime
MyType MyDate
1 03/24/2005
1 03/20/2005
2 03/24/2005
I want to show MyType one date before today like record 2,but I want to
everything for MyType 2
Are there any solution for this?
Any information is appreciated,
Souris,Try this
Select * from Tablename where id in (select max(id) from tablename
group by date)
Madhivanan|||You can use a case statement in a where clause, but you don't need to for
this example. If you just want to look for myDate on the previous day and
myType = 2 than you would do this:
select * from myTable
where MyDate = convert(varchar,dateadd(d,-1,getdate()),101)
and MyType = 2
If you are looking for ANY myDate prior to today and myType = 2 than:
select * from myTable
where MyDate < convert(varchar,getdate(),101)
and MyType = 2
"souris" wrote:

> I would like to know that may I use case statement in where clause?
> If not, are there any other solution to filter my records depends on my
> field.
> For example
> MyType integer, MyDate datetime
> MyType MyDate
> 1 03/24/2005
> 1 03/20/2005
> 2 03/24/2005
> I want to show MyType one date before today like record 2,but I want to
> everything for MyType 2
> Are there any solution for this?
> Any information is appreciated,
> Souris,
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
<<
No. This is because there is no such statement in SQL. There is a
CASE expression which returns a scalar value.
I want to
everything for MyType 2 <<
SELECT my_type, my_date
FROM Foobar
WHERE my_type = 2
OR my_date < CURRENT_TIMESTAMP;
Rows are not records, columns are not fields. You are still using
terminology from a procedural language with a file system and not
thinking in RDBMS yet.|||Thanks millions,
Souris,
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1111763824.117387.95340@.o13g2000cwo.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
>
> <<
> No. This is because there is no such statement in SQL. There is a
> CASE expression which returns a scalar value.
>
> I want to
> everything for MyType 2 <<
> SELECT my_type, my_date
> FROM Foobar
> WHERE my_type = 2
> OR my_date < CURRENT_TIMESTAMP;
> Rows are not records, columns are not fields. You are still using
> terminology from a procedural language with a file system and not
> thinking in RDBMS yet.
>

Case Sensitive Object Names?

My new DB seems to be "case-sensitive". I have a table named Bld_List.

Select * From Bld_List

returns all records in QA. However, if I don't follow the correct case

Select * From bld_list

I get this error: Invalid object name 'bld_list'.

Also, when I try to get rid of some unnecessary records by using this

DELETE
FROM Bld_List
WHERE (PRODUCT IN
(SELECT DISTINCT Product
FROM BldOff_Inv_Daily))

I get this error: Cannot resolve collation conflict for equal to operation.

I imagine I set something up wrong when I first built the DB, but I don't know what to look for.

ThanksI'd suspect you are not using the default character set collations. Character sets can be either case-sensitive or case-insensitive, and I think this applies not just to the data but to the object names as well.|||So is that something I can change?

I used the Collation Name that my other databases are set to:
SQL_Latin1_General_Cp1_CI_AS
or is it some other setting?|||Hi,

CI is pointing that the collation is Case Insensitive.

If you take a create script of your tables, you can see if a non-default collation is used.

Eralper
http://www.kodyaz.com|||What does sp_helpsort tell you?|||Hi,

CI is pointing that the collation is Case Insensitive.

If you take a create script of your tables, you can see if a non-default collation is used.

Eralper
http://www.kodyaz.com

hmmm...
[PRODUCT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL

Do I need to rebuild and repopulate the table? I only have a few so far, so it wouldn't be a huge deal.|||What does sp_helpsort tell you?

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data|||Ok, I think I've got it. I did change the collation at some point over the weekend. Any tables I created after that are okay.
If I just use the Alter Table Collation clause on the first tables I created, that should take care of the problem, right?

CASE and INSERT statements

Hi,
I need to write a simple script which i am getting rather on how to
write. Basically, what i need to do is:
If no records exist for a particular condition i.e. TableID=3 and
TableTypeID=4, then insert a row into Tabel1.
I have tried all sorts but I think i have got the order mixed up and that’
s
why it is not working.
This is what I have tried so far:
select * ,
case
when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
then insert into Table1 (TableID, TableTypeID) values (3,14)
end
from Table1
I have tried the above in various different formats but with no success.
Any help/advice much appreciated.
Thanks,
JJens
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
IF NOT EXISTS (SELECT * FROM Table WHERE TableID=3 and TableTypeID=4)
INSERT INTO Table balblabala alaba
ELSE
Do it something else here
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:57ED03B3-B064-4793-B1AC-C5F4E891EA5E@.microsoft.com...
> Hi,
> I need to write a simple script which i am getting rather on how
> to
> write. Basically, what i need to do is:
> If no records exist for a particular condition i.e. TableID=3 and
> TableTypeID=4, then insert a row into Tabel1.
> I have tried all sorts but I think i have got the order mixed up and that
s
> why it is not working.
> This is what I have tried so far:
> select * ,
> case
> when not exists (select * from Table1 where TableID=3 and TableTypeID=14)
> then insert into Table1 (TableID, TableTypeID) values (3,14)
> end
> from Table1
> I have tried the above in various different formats but with no success.
> Any help/advice much appreciated.
> Thanks,
> J
>|||
This will never evaluate to true
-TableId can=B4t be 3 AND 14 at the same time
select * from Table1 where TableID=3D3 and TableTypeID=3D14
For an inline Query (with an OR rather than the AND which is causing
the above issue)
insert into Table1
(
TableID,
TableTypeID
)
SELECT 3,14
WHERE NOT EXISTS
(
select * from Table1 where TableID=3D3
OR TableTypeID=3D14
)=20
HTH, Jens SUessmeyer.

CASE - How to show groups with count(*) = 0 (zero) ?

I would like to show even the rows where number of records are zero (in the Case statement ).

I couldn't find the solution either in the microsoft newsgroup, dbforums or Google. I fear I am overlooking something very very basic!
Thanks in advance.
:)

SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end,
Count(*) 'Number of Invoices'

FROM AP

GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end
ORDER BY Min(Amount)
----------
Suppose these are the Amounts (99,50,75,201,230)
The CURRENT OUTPUT is
<= 100 3
>200 2

The REQUIRED OUTPUT is
<= 100 3
<= 200 0
> 200 2select 'Unknown', count(*)
from AP
where Amount IS NULL
UNION ALL
select '<= 100 ', count(*)
from AP
where Amount <= 100
UNION ALL
select '<=200', count(*)
from AP
where Amount <= 100 AND Amount > 100
UNION ALL
select '>200', count(*)
from AP
where Amount > 200|||correction:
where Amount <= 200 AND Amount > 100|||Thanks HanafiH.

Using union will cause a pass through the table each time. That will generate a performance overhead which would increase as the number of divisions increase. I am generating this CASE statement using dynamic SQL. The application can provide any value to the number of divisions.

I want to take advantage of the performance benefit by using CASE.

Is there a solution using CASE itself?

Thanks again in advance.
:)|||declare @.GroupingTable table
(GroupingValue varchar(10),
SortOrder int)

insert into @.GroupingTable (GroupingValue, SortOrder) values('Unknown', 1)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 100 ', 2)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 118 ', 3)
insert into @.GroupingTable (GroupingValue, SortOrder) values('> 200', 4)

select GroupingTable.GroupingValue
from
@.GroupingTable GroupingTable
left outer join
(SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end GroupingValue
Count(*) 'Number of Invoices'
FROM AP
GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end) SummaryData
on GroupingTable.GroupingValue = SummaryData.GroupingValue
order by GroupingTable.SortOrder

blindman|||Originally posted by HornOkPlease
Thanks HanafiH.

Using union will cause a pass through the table each time. That will generate a performance overhead which would increase as the number of divisions increase. I am generating this CASE statement using dynamic SQL. The application can provide any value to the number of divisions.

I want to take advantage of the performance benefit by using CASE.

Is there a solution using CASE itself?

Thanks again in advance.
:)

The CASE may well be efficient, but the GROUP BY and ORDER BY necessary to make use of it is not. UNION *ALL* is very efficient. On my system your original case query has a 0.0604 total query cost, my query runs at 0.0192 total query cost, which is roughly three times faster than the CASE case (as it were).

I shudder to think about what insertion into a temp will cost.|||try this:select count(*) as TotalNumber
, sum(case when amount is null
then 1 else 0 end) as Unknown
, sum(case when amount <= 100
then 1 else 0 end) as "<= 100"
, sum(case when amount <= 200
then 1 else 0 end) as "<= 200"
, sum(case when amount > 200
then 1 else 0 end) as "> 200"
from ap
you guys with the cpu timers, please let me know how this compares to the other solutions

rudy
http://r937.com/|||r937, I think you hit the nail on the head.

blindman|||Hats off to you blindman!
Thanks, HanafiH, i've taken note of your message too.

Regards,
HornOkPlease|||Originally posted by r937
try this:select count(*) as TotalNumber
, sum(case when amount is null
then 1 else 0 end) as Unknown
, sum(case when amount <= 100
then 1 else 0 end) as "<= 100"
, sum(case when amount <= 200
then 1 else 0 end) as "<= 200"
, sum(case when amount > 200
then 1 else 0 end) as "> 200"
from ap
you guys with the cpu timers, please let me know how this compares to the other solutions

rudy
http://r937.com/

0.0376|||Originally posted by blindman
declare @.GroupingTable table
(GroupingValue varchar(10),
SortOrder int)

insert into @.GroupingTable (GroupingValue, SortOrder) values('Unknown', 1)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 100 ', 2)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 118 ', 3)
insert into @.GroupingTable (GroupingValue, SortOrder) values('> 200', 4)

select GroupingTable.GroupingValue
from
@.GroupingTable GroupingTable
left outer join
(SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end GroupingValue
Count(*) 'Number of Invoices'
FROM AP
GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end) SummaryData
on GroupingTable.GroupingValue = SummaryData.GroupingValue
order by GroupingTable.SortOrder

blindman

0.1158 and the slowest solution proposed.|||I think r937 was being modest. You don't need to time these solutions to see that his is better. It's the solution I was trying to get to, but I guess I was one or two cups of coffee short this morning.

blindman

Cascade Delete Not Working Correctly?

I have a master table that is updated. The master has
child records that are referenced by a foreign key with
the CASCADE DELETE option.
In the transaction log, an update of the master appears
as a DELETE/INSERT. The primary key is not being updated.
For the child record, the same update appears to
DELETE/INSERT the child.
Occassionaly, when a master record is updated, the child
record is deleted but not inserted.
Can anyone explain why?
TIA,
HarryIn order to do this you will need to enable Cascade Update as well as
Cascade Delete...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
> I have a master table that is updated. The master has
> child records that are referenced by a foreign key with
> the CASCADE DELETE option.
> In the transaction log, an update of the master appears
> as a DELETE/INSERT. The primary key is not being updated.
> For the child record, the same update appears to
> DELETE/INSERT the child.
> Occassionaly, when a master record is updated, the child
> record is deleted but not inserted.
> Can anyone explain why?
> TIA,
> Harry|||My apologies, I've not made myself clear.
I'm not trying to do this.
Firstly, I'm trying to understand why an update of a
master record results in an delete/insert of the child.
Secondly, why the delete part sometimes fails.
TIA.
>--Original Message--
>In order to do this you will need to enable Cascade
Update as well as
>Cascade Delete...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
>news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
updated.
>
>.
>|||What exactly are you auditing to see this?
I cannot replicate this on a sample db I have...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
> Secondly, why the delete part sometimes fails.
> TIA.
> Update as well as
> message
> updated.|||I'm using the transaction log explorer
tool from Lumigent.
It shows that some updates of the master keep
the child and others do not.
>--Original Message--
>What exactly are you auditing to see this?
>I cannot replicate this on a sample db I have...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
has
with
appears
child
>
>.
>|||"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
Has the table got triggers associated with it? An Update trigger results in
updates being converted to a delete followed by an insert (so the trigger
can reference the before and after values in the INSERTED and DELETED
tables)
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||The only triggers on the table are SQL Server merge
replication triggers.
Interesting point though. I was not aware of that
behaviour.
>--Original Message--
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
>Has the table got triggers associated with it? An Update
trigger results in
>updates being converted to a delete followed by an insert
(so the trigger
>can reference the before and after values in the INSERTED
and DELETED
>tables)
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.614 / Virus Database: 393 - Release Date:
05/03/2004
>
>.
>

Cascade Delete

Is there a way to turn off cascade delete temporarily? Yes, I want to leave
orphan records in this instance. I tried the following:
ALTER TABLE tForm NOCHECK CONSTRAINT
Delete From tformDerek Hart (derekmhart@.yahoo.com) writes:
> Is there a way to turn off cascade delete temporarily? Yes, I want to
> leave orphan records in this instance. I tried the following:
> ALTER TABLE tForm NOCHECK CONSTRAINT
> Delete From tform
You would have to disable the constraints on the referring tables, not
the table you are deleting from.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Why' I see that you name tables with a "t-" prefix, so I am pretty
sure that you do not understada how to design an RDBMS. But that is
only based on 20+ years of education and experience in RDBMS and SQL.
Can you explain this?|||Naming tables with a 't' prefix means I don't know how to design an RDBMS.
Well, my commercial application that I sell for $100,000 that has 110 tables
and 1500 stored procedures sure must be having problems!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1146871618.195792.312780@.y43g2000cwc.googlegroups.com...
> Why' I see that you name tables with a "t-" prefix, so I am pretty
> sure that you do not understada how to design an RDBMS. But that is
> only based on 20+ years of education and experience in RDBMS and SQL.
> Can you explain this?
>|||Since you want to leavew orphans for some reason, yes, it probalby is
havngh problems -- like orphans! That is what indicates that you don't
know how to design an RDBMS. The leading "t-" and other things are
just symptoms of OO design, ignorance of ISO Standards, etc.
And I have worked for companies in the dor-bomb era that cost muxh more
and were much larger. The reason that the databases were so large was
poor design and lots of orphans -- both tables and rows.|||Orphans are only available for seconds until all the data is filled up in
the database. This conversation is complete.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1146887407.304043.143670@.u72g2000cwu.googlegroups.com...
> Since you want to leavew orphans for some reason, yes, it probalby is
> havngh problems -- like orphans! That is what indicates that you don't
> know how to design an RDBMS. The leading "t-" and other things are
> just symptoms of OO design, ignorance of ISO Standards, etc.
> And I have worked for companies in the dor-bomb era that cost muxh more
> and were much larger. The reason that the databases were so large was
> poor design and lots of orphans -- both tables and rows.
>|||Derek Hart (derekmhart@.yahoo.com) writes:
> Orphans are only available for seconds until all the data is filled up in
> the database. This conversation is complete.
You mean that for several seconds you commit a breach against the
principles of database design and insult Joe Celko! How dare you?
On a more serious note, when you reapply the constraint, be sure to
use this funny syntax:
ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT fk_xxxx
WITH CHECK instructs SQL Server to actually validate the constraint.
If you leave it out, you get WITH NOCHECK. This is obviously faster,
but there is a price to pay: If the constraint is reenabled WITH
NOCHECK, the constraint is not trusted as far as the optimizer is
concerned. This can lead to poorer execution plans, as the optimizer
can not use the constraint to rule out certain conditions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am doing a table update of over 100 tables. I apply this when I begin:
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And when I am done I apply
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
I believe this properly re-enables the constraints, but does it lead to
poorer execution plans.
And did you mean to have CHECK CHECK CONSTRAINT or just one CHECK?
Derek Hart
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BBC7AA7F30Yazorman@.127.0.0.1...
> Derek Hart (derekmhart@.yahoo.com) writes:
> You mean that for several seconds you commit a breach against the
> principles of database design and insult Joe Celko! How dare you?
> On a more serious note, when you reapply the constraint, be sure to
> use this funny syntax:
> ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT fk_xxxx
> WITH CHECK instructs SQL Server to actually validate the constraint.
> If you leave it out, you get WITH NOCHECK. This is obviously faster,
> but there is a price to pay: If the constraint is reenabled WITH
> NOCHECK, the constraint is not trusted as far as the optimizer is
> concerned. This can lead to poorer execution plans, as the optimizer
> can not use the constraint to rule out certain conditions.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||IMO it might be easier to insert new parent rows, redirect the child
rows to their new parents, and delete old parents. Also it might be way
faster then re-enabling a constraint on a big table.|||Derek Hart (derekmhart@.yahoo.com) writes:
> I am doing a table update of over 100 tables. I apply this when I begin:
> SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> And when I am done I apply
> SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
> I believe this properly re-enables the constraints, but does it lead to
> poorer execution plans.
This reenables the constraint without verifying that existing data
complies with the constraint.

> And did you mean to have CHECK CHECK CONSTRAINT or just one CHECK?
The syntax is indeed
as I wrote. Just check Books Online. :-) Yes, it's really a horrible
syntax.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Capturing Invalid Records for XML Inserts

Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-ak
Using SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/

Capturing Invalid Records for XML Inserts

Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-akUsing SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/

Capture Records That Failed Insertion From XML

Does anyone know an effective way to capture any records that failed to
insert into a table when inserting from an xml document in SQL Svr
2000?

thanks.

-akAyron (akitchen@.lrs.com) writes:
> Does anyone know an effective way to capture any records that failed to
> insert into a table when inserting from an xml document in SQL Svr
> 2000?

It would certainly help if you told us how you insert your records.
Openxml or XML bulk load?

Particularly in the matter case, I would recommend trying
microsoft.public.sqlserver.xml.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the reply, Erland. I'm new to the XML bulk insert. I'll look
into that method. I've also transitioned the topic to the group that
you mentioned.

-ak|||"Ayron" <akitchen@.lrs.com> wrote in message
news:1108507580.773174.268630@.l41g2000cwc.googlegr oups.com...
> Does anyone know an effective way to capture any records that failed to
> insert into a table when inserting from an xml document in SQL Svr
> 2000?

I have used sqxmlbulkload.
Not really investigated it in depth but the default behaviour is to crash
and burn if a record contains invalid data.
I wrote an access app which a support user investigates problems with.
They import the xml file to a table and a screen iterates through all fields
comparing values with the datatypes.
It marks records as bad.
The screen shows only bad records and uses conditional highlighting to
highlight the bad field in a record.

--
Regards,
Andy O'Neill

Capture MAC address in trigger

I have a simple trigger that writes out records to another table when a user
deletes a record. I am looking for a way to capture the Network Address valu
e
that is associated with the process. Is that possible ?
Here is the trigger. I want to insert the Network Address value into the
Lastuser coulumn in the Contact1del table
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
ThanksHOST_NAME() maybe?|||jenks wrote:
> I have a simple trigger that writes out records to another table when
> a user deletes a record. I am looking for a way to capture the
> Network Address value that is associated with the process. Is that
> possible ?
> Here is the trigger. I want to insert the Network Address value into
> the Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT, TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2,
> EXT3, EXT4, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY,
> DEAR, SOURCE, KEY1, KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES,
> CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1,
> KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid
This will give you the mac address for the current spid. In your case,
you could use an embedded SELECT or grab the mac address before the
insert. For example:
Insert into #ttt (col1, col2)
Select 1, (
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid )
David Gugick - SQL Server MVP
Quest Software|||this might help:
select net_address from master..sysprocesses where spid=@.@.spid
dean
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:8B260AE5-DAF4-43CD-8A29-9232573A39EE@.microsoft.com...
>I have a simple trigger that writes out records to another table when a
>user
> deletes a record. I am looking for a way to capture the Network Address
> value
> that is associated with the process. Is that possible ?
> Here is the trigger. I want to insert the Network Address value into the
> Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
>|||Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
problem with that ? Thanks again!!
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
where spid=@.@.spid), GETDATE(), LASTTIME, RECID
from deleted|||I ordinarily advise against using WITH(NOLOCK), because it can cause queries
to return incorrect answers, but this is a special case in which the row in
sysprocesses tied to @.@.SPID cannot be changed by activity on any another
spid, so it makes better sense to eliminate the extra shared lock.
I would save off net_address and GETDATE() in local variables before
executing the INSERT. That minimizes the number of database accesses and
could make it possible to identify what was changed by the statement that
caused the trigger to fire. Of course, that would work only if you stored
@.@.SPID, too.
DECLARE @.net_address nchar(12), @.getdate DATETIME
SELECT @.net_address = net_address, @.getdate = GETDATE() from
master..sysprocesses WITH(NOLOCK) WHERE spid = @.@.SPID
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:F382F996-E346-4815-ACCF-42AD3845008F@.microsoft.com...
> Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
> problem with that ? Thanks again!!
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
> where spid=@.@.spid), GETDATE(), LASTTIME, RECID
> from deleted
>
>

Capacity Planning

Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
PeterThis information is in SQL Server 2000 Books Online. Look up the chapter:
"Estimating the size of a database"
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:f97601c3f222$4697f700$a001280a@.phx.gbl...
Dear All
My company has asked me to come up with the amount of
space a new database will use based upon X number of
records in tables.
Is there some sort of recognised matrix I can follow, or
will I have to wing it based upon my own interpretation of
the tables and relationships ?
Thanks
Peter|||Thank you
Peter
>--Original Message--
>This information is in SQL Server 2000 Books Online. Look
up the chapter:
>"Estimating the size of a database"
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f97601c3f222$4697f700$a001280a@.phx.gbl...
>Dear All
>My company has asked me to come up with the amount of
>space a new database will use based upon X number of
>records in tables.
>Is there some sort of recognised matrix I can follow, or
>will I have to wing it based upon my own interpretation of
>the tables and relationships ?
>Thanks
>Peter
>
>.
>

Cant work out this SQL to return 4 random records

I've got a product table in SQL 2000 that contains say these rows:

ProductID int
ProductName varchar
IsSpecial bit

I want to always return 4 random specials from a query and can do this fine by using:

SELECT TOP 4 ProductID,ProductName
FROM Products WHERE IsSpecial = 1
ORDER BY NEWID()

This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.

What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?

Is this possible?

Thanks

Hello my friend,

The following SQL should work. If you have any questions or problems with it, please let me know.

Kind regards

Scotty

SELECT TOP 4 * FROM
(
SELECT TOP 4 ProductID, ProductName FROM Products WHERE IsSpecial = 1

UNION ALL

SELECT ProductID, ProductName FROM Products WHERE IsSpecial = 0

) myTable

ORDER BY IsSpecial DESC, NewID()

|||

Excellent Scotty, that does exactly what i was looking for!

Cheers.

Cant unlock records in SQL Server

I have an Access97 front end using ODBC to communicate with an
SQLServer 7.0 back end on a different machine. Most of the work I do
in the front end uses forms bound to linked tables that reside on the
back end. In one instance though I have to create some new records
programmatically and I use code in a procedure in a general module in
the front end that looks like this:

Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]",
dbOpenDynaset, dbAppendOnly + dbSeeChanges)
newrec.AddNew ' assign the workshop
newrec![ComboWS] = WSKey
newrec![Participant] = IndividualID
newrec![Assigned] = True
newrec.Update
newrec.Close

The problem occurs later when I am in a form that views those records
that were just added. For some reason SQLServer still has those
records locked, and I am not allowed to make any changes to them. I
can't even just delete them. In fact, even when I exit out of all my
forms, go straight into the table window, straight to the table itself
(but still in the front end), I cannot delete or change those records
directly. I've tried taking down the front end and bringing it back
up. I've tried restarting the whole computer where the front end
resides. I've also tried restarting the SQL Server. I still can't
change those records. Oddly enough, I can change the records within
SQL Server itself. The Access97 front end will see the new values, but
still is unable to do anything with them. How can I fix this problem?
Thank you for any help you can give me,
Rebecca JaxonOK, we've solved it. Sorry to bother you all. The problem was that I
had some other boolean fields I was just ignoring. It used to work
fine when the back end was still in Access97. Apparently, when
Access97 sees a null in a boolean field it just assumes that it means
false, but when SQL Server sees such a thing, it causes this problem.
I was not filling in all the false values, and hadn't set the default
to false. Now I know.

Rebecca