Database

17-Jul-06 10:45

select count(*) should give you the results you want. Count only counts the number of rows in a set. Using the field really doesn't do much unless you use distinct.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Actually try

Ennis Ray Lynch, Jr.17-Jul-06 10:47

17-Jul-06 10:47

SELECT
a.officer as officer,
e.open_date as opened,
e.close_date as closed,
j.can_date as cancelled,
count(*) as ct
FROM
escrow e inner join a10 a on e.escrow = a.escrow inner join e120 j on e.escrow = j.escrow group by a.officer, close_date, open_date, j.can_date
GROUP BY
a.officer,
a.open_date,
a.close_date,
a.can_date

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Nino_117-Jul-06 11:02

Nino_1

17-Jul-06 11:02

Thanks for your post,

Actually i'm attempting to get the count for each individual row and display it through QA.

Thanks,

Nino

Colin Angus Mackay17-Jul-06 11:56

Colin Angus Mackay

17-Jul-06 11:56

Nino_1 wrote:
Actually i'm attempting to get the count for each individual row and display it through QA

Then the count will always be 1.

So I suspect there is a gulf of comprehension somewhere: What do you mean by "each individual row"?

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Nino_117-Jul-06 12:10

Nino_1

17-Jul-06 12:10

Hi Colin,

here's what i came up with:

select distinct a.officer as Officer, d.close_date as [Date], count(d.open_date)as [Open], count(d.close_date) as Closed, count(can_date)as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrow inner join e120 j on j.escrow = d.escrow where a.officer is not null group by a.officer, d.close_date

Nino

Colin Angus Mackay17-Jul-06 20:41

Colin Angus Mackay

17-Jul-06 20:41

That doesn't help me much. For each of the columns you have a COUNT() on it will always return 0 or 1 (counting each non-null, and since you don't have anything to group the aggregation by it there will always be just one row to be counted)

Give me an example with data in your table and what you'd like the output to be - That will be helpful.

How to Rollover a Table

Robert M Greene17-Jul-06 9:49

Robert M Greene

17-Jul-06 9:49

I am looking for some direction about how to keep a table in a SQL server db (2000 or above) of fixed size.

Requirements:
Keep n records, Keep t date/time range of records, keep priority records etc...

a) Should I delete old records on insert, ie. stored proc?
b) Should I delete old records with a Job/External process?
c) Should I just use the the old "DELETE FROM tablename" command?

Since I will be filling the table to it's limit often, and deleting the old contents should I clean up the transaction logs as well? When? How?

I am just looking for some general direction.

.............................
There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life.

Fiat justitia, et ruat cælum

A combined approach

Ennis Ray Lynch, Jr.17-Jul-06 10:48

17-Jul-06 10:48

Use stored procedures to only return the set size.

Depending on tps requirements you can then write a trigger or a job to run and clear the table removing old elements. I would use the job if you can get a way with it. The trigger, however, will always keep the table the right size.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: A combined approach

Frank Kerrigan18-Jul-06 2:05

Frank Kerrigan

18-Jul-06 2:05

You can use the following script to keep the table 10000 rows.

using Indentity

<br />
DELETE from BigTable<br />
Where ID NOT in ( SELECT Top(10000) from BigTable ORDER BY ID DESC )<br />

Please test first with a test table.

Look where you want to go not where you don't want to crash.

Bikers Bible

Hold on there skippy

Ennis Ray Lynch, Jr.18-Jul-06 2:40

Re: Hold on there skippy [modified]

18-Jul-06 2:40

Are you sure he should first test on a test test table before testing on a test table. One can never be to careful :)

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Hold on there skippy

Robert M Greene18-Jul-06 3:02

Robert M Greene

18-Jul-06 3:02

Thanks all I will give the suggestions a try.

.............................
There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life.

Fiat justitia, et ruat cælum

Frank Kerrigan18-Jul-06 3:54

Frank Kerrigan

18-Jul-06 3:54

Quite Quite Sure !

Look where you want to go not where you don't want to crash.

Bikers Bible

-- modified at 9:54 Tuesday 18th July, 2006

Re: Hold on there skippy

Frank Kerrigan18-Jul-06 3:55

Frank Kerrigan

18-Jul-06 3:55

Please call me skippy

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: A combined approach

S Douglas23-Jul-06 21:17

S Douglas

23-Jul-06 21:17

Frank Kerrigan wrote:
Please test first with a test table.

Test? I think you mean test in Production! (is it Friday yet?) Smile | :)

I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:

DataSet WriteXml -> ReadXml loses NULL values

Ashley van Gerven17-Jul-06 2:24

Re: DataSet WriteXml -> ReadXml loses NULL values

17-Jul-06 2:24

Not sure if this is a widely-know issue, but the XML structure for a DataSet written by calling WriteXml() method does not write elements for NULL values. Now when calling ReadXml() an integer column for example will have a value of 0 instead of NULL.

Is anyone aware of this issue?

And is there a simple solution?

thanks

"Nothing ever changes by staying the same." - David Brent (BBC's The Office)

~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid

Ennis Ray Lynch, Jr.17-Jul-06 3:22

Re: DataSet WriteXml -> ReadXml loses NULL values

17-Jul-06 3:22

I don't think MS supports null. Their XSD.exe application likes to use xSpecified where x is the name of the attribute. Which is SO much better in my opinion.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Ashley van Gerven17-Jul-06 3:43

17-Jul-06 3:43

.NET has a DbNull class for NULL values in datareaders and datasets... I just don't get why they're dropped in the serialisation.

"Nothing ever changes by staying the same." - David Brent (BBC's The Office)

~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid

Yeah but

Ennis Ray Lynch, Jr.17-Jul-06 3:55

17-Jul-06 3:55

That doesn't mean they work for MS XML. I use dbnull all the time against oracle and SQL server, however.

Do you get a generated Specified attribute or element?

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Yeah but

Ashley van Gerven17-Jul-06 4:10

17-Jul-06 4:10

Ennis Ray Lynch, Jr. wrote:
Specified attribute or element?

Not sure what you mean by this... but I don't think so. The schema has this:

<xs:element name="TxnID" type="xs:long" minOccurs="0" />

Now normally for each row you would get the values- e.g.: <TxnID>2</TxnID>

But if it's NULL you don't get this element. And then when reading it into a DataSet it will automatically have the value: 0 - which is not what the original dataset had.

The solution seems to be to read the schema, set a default value, and then read the data. But is there a simpler solution? Is this a bug, or intentional behaviour by microsoft experts? .NET 2.0 seems to have the same behaviour.

"Nothing ever changes by staying the same." - David Brent (BBC's The Office)

~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid

If I use XSD.exe I get

Ennis Ray Lynch, Jr.17-Jul-06 4:27

Re: If I use XSD.exe I get

17-Jul-06 4:27

Using XSD.exe MS will give the following results which leads me to believe they do not like null.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">

<xs:element name="sample">
<xs:complexType>
<xs:attribute name="maxQuantity" type="xs:int" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>

will generate

//------------------------------------------------------------------------------
// <autogenerated>
// This code was generated by a tool.
// Runtime Version: 1.1.4322.2032
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </autogenerated>
//------------------------------------------------------------------------------

//
// This source code was auto-generated by xsd, Version=1.1.4322.2032.
//
using System.Xml.Serialization;

/// <remarks/>
[System.Xml.Serialization.XmlRootAttribute(Namespace="", IsNullable=false)]
public class sample {

/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public int maxQuantity;

/// <remarks/>
[System.Xml.Serialization.XmlIgnoreAttribute()]
public bool maxQuantitySpecified;
}

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Ashley van Gerven17-Jul-06 4:43

17-Jul-06 4:43

Interesting, thanks. They must equate NULL with 'not specified'.

"Nothing ever changes by staying the same." - David Brent (BBC's The Office)

~ ScrollingGrid: A cross-browser freeze-header control for the ASP.NET DataGrid

Thats what I think

Ennis Ray Lynch, Jr.17-Jul-06 4:45