Question about simple query..

How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.

I have the following table, which has name, address, city,state,zip
code, and phone number.

kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358

The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.

How can I erase that extra data, and get the table like this ? :

kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358

First, I used this query, but it turned out all of my data was gone. :
(

delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);

Please help !What is the criteria that you would use to pic which row to keep?
jaehwang wrote:

Quote:

Originally Posted by

How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.
>
I have the following table, which has name, address, city,state,zip
code, and phone number.
>
>
kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
>
>
The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.
>
How can I erase that extra data, and get the table like this ? :
>
kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
>
First, I used this query, but it turned out all of my data was gone. :
(
>
delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);
>
Please help !
>

|||You would have to define a criteria based on which a row will be kept or
deleted (that is another column or combination of columns that is unique).
Here is just an example based on your sample data (in this case the row with
the MIN name will be kept, but this assumes no duplicate names with the same
phone):

DELETE FROM Address
WHERE EXISTS (
SELECT *
FROM Address AS A
WHERE A.phone = Address.phone
AND A.name < Address.name)

You can easily reverse the above condition to A.name Address.name to keep
the MAX name.

After you are done you can alter the table and add UNIQUE constraint on the
phone column to prevent duplicate data in the future, something like this:

ALTER TABLE Address ADD CONSTRAINT uphone UNIQUE (phone)

HTH,

Plamen Ratchev
http://www.SQLStudio.comsql

Question about Service Pack

In query analyzer

If I run this
SELECT @.@.version
I get this result
-----------------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

(1 row(s) affected)

If I run this
SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
I get this result
-------
SP4

(1 row(s) affected)

What's the deal am I at SP2 or SP4?You are running SQL Server 2000 SP4. The SP2 in the @.@.VERSION string is the
OS service pack level.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"SQL Server" <alderran666@.gmail.com> wrote in message
news:1125853679.498850.278490@.g44g2000cwa.googlegr oups.com...
> In query analyzer
> If I run this
> SELECT @.@.version
> I get this result
> -----------------
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> (1 row(s) affected)
> If I run this
> SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
> I get this result
> -------
> SP4
> (1 row(s) affected)
> What's the deal am I at SP2 or SP4?|||Hi

To add to Dan's Reply:

Windows NT 5.1 (Build 2600: Service Pack 2)

Is Windows XP Service Pack 2.

A SQL Server version list can be found at:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37

John
"SQL Server" <alderran666@.gmail.com> wrote in message
news:1125853679.498850.278490@.g44g2000cwa.googlegr oups.com...
> In query analyzer
> If I run this
> SELECT @.@.version
> I get this result
> -----------------
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> (1 row(s) affected)
> If I run this
> SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
> I get this result
> -------
> SP4
> (1 row(s) affected)
> What's the deal am I at SP2 or SP4?

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl

trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for

ddl_database_level_events
as
--select

eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post

Time'
from

@.data.nodes('/EVENT_INSTANCE')

as EventsTable(Col)
go

Thank you in advance,

Bishoy

The path expressions in the value methods need to be relative path expressions from the node...

Try:

select
Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl

trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for

ddl_database_level_events
as
--select

eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post

Time'
from

@.data.nodes('/EVENT_INSTANCE')

as EventsTable(Col)
go

Thank you in advance,

Bishoy

Your XPATH expression for the value method is incorrect. You can do one of the following:

select
Col.value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

-- or

select
Col.value('(./EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(./PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

See Books Online EVENTDATA topics for more examples. And check out the XQuery documentation also.

sql

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl trigger to view it in result set.

I made that code but it returned null, any help please?

createtrigger DatabaseEvents
ondatabase
for ddl_database_level_events
as
--select eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)')as'Event Type'
,Col.value('(/PostTime/text())[1]','datetime')as'Post Time'
from @.data.nodes('/EVENT_INSTANCE')as EventsTable(Col)
go

Thank you in advance,

Bishoy

The path expressions in the value methods need to be relative path expressions from the node...

Try:

select
Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go

question about query part of a string

I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this? thanks

Try this

ALTER PROCEDURE [dbo].[SPU_XXXX]

@.SEARCH VARCHAR(30) = NULL

AS

SET @.SEARCH = '%'+ @.SEARCH + '%'

SELECT CLIENT.CLIENT, CLIENT.CLIENTID

FROM CLIENT

WHERE (CLIENT.CLIENT LIKE @.SEARCH)

|||

The best reference for this question is here:

http://www.sommarskog.se/arrays-in-sql.html

The Table of Numbers method is the easiest, fastest, etc. It will put the data into a table format that you can join to your table in a very straightforward fashion.

http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

|||

Code Snippet

Set @.search = 'Chris'

--To get all names start with Chris

--i.e., Chrisxxxxxxxx

select * from yourtable where ip_user like @.search + '%'

--To get all names ends with Chris

--i.e., xxxxxChris

select * from yourtable where ip_user like '%' + @.search

--To get all names containes Chris at anyplace

--i.e., xxxxxxChrisxxxxxxxx or xxxxxChris or Chrisxxxxxxxx

select * from yourtable where ip_user like '%' + @.search + '%'

|||

You really should look into Fulltext, as that is its strong feature. Another option using sql engine is to use charindex() function.

e.g.

select *

from tb

where charindex(@.var,your_column)>0

|||

Thank you that was very helpful and it worked great. I have another quick question, and although this isn't technically an access forum it involves access. I have someone who runs access and with an odbc connection, connects to the sql server database. Is there any sql that can be used in accesses queries to replicate this? I apologize ahead of time for posting this here, I couldn't find an access forum...

|||In Access, mark the Query as a 'Pass Though' query, and the query as above will be sent to execute on SQL Server without Access attempting to check syntax and compile.

question about like

I have a table that looks like this

tbl1:
nvchValue nvarchar(100) NOT NULL
biVal1 bigint NOT NULL
biVal2 bigint NOT NULL

If I run this query against it:
SELECT nvchValue
FROM tbl1
WHERE nvchValue LIKE '%<%'

I get a result set that doesn't make any sense to me:
Term

--
?
?
?
fr?it

Those values are in the database, but I don't understand how the match
is working. Any help would be appriciated.It has to do with the collation you are using. Just like an 'a' is
considered equal to an 'A' in a collation that is not case sensitive,
the '<' is apparently considered equal to some of your ? characters.

If you explicitely mention Binary Collation in the WHERE clause, then
you will only get exact matches.

Gert-Jan

alternative_to_what@.yahoo.com wrote:
> I have a table that looks like this
> tbl1:
> nvchValue nvarchar(100) NOT NULL
> biVal1 bigint NOT NULL
> biVal2 bigint NOT NULL
> If I run this query against it:
> SELECT nvchValue
> FROM tbl1
> WHERE nvchValue LIKE '%<%'
> I get a result set that doesn't make any sense to me:
> Term
> --
> ?
> ?
> ?
> fr?it
> Those values are in the database, but I don't understand how the match
> is working. Any help would be appriciated.

Question about JOIN syntax

what is the syntax to join a table with the result of antoher query.
For example i have two tables
Create Table Customers (
CustomerID int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
PaymentDate Datetime )
What query will bring me the customers whose lastpaymentdate in the
customers table is not correct.
That can only be checked by comparing it with the max paymentdate for each
customer in the payments table.
I want this to be done by ansi standard sql. Not using any specific feature
of sql server.
thxSELECT *
FROM Customers AS C
WHERE lastpaymentdate <>
(SELECT MAX(paymentdate)
FROM Payments AS P
WHERE P.customerid=C.customerid)
--
David Portas
--
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:2vydnRekZvROvmiiRVn-sw@.giganews.com...
> SELECT *
> FROM Customers AS C
> WHERE lastpaymentdate <>
> (SELECT MAX(paymentdate)
> FROM Payments AS P
> WHERE P.customerid=C.customerid)
>
oh may be my example was not the best!
what if I have
Create Table Customers (
CustomerID int,
LastPaymentAmount int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )
and I want to see if the LastPaymentAmount field holds correct values.
I couldnt use the max function for this obviously.
thx|||> I couldnt use the max function for this obviously.
Why not? My query should still work.
If you want a query that uses a join then try this:
SELECT C.*
FROM Customers AS C
LEFT JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
WHERE C.lastpaymentdate IS NOT NULL
AND P.customerid IS NULL
This has a possible advantage over the correlated subquery version. It will
include Customers who have a Lastpaymentdate but don't have any non-NULL
dates in the Payments table.
--
David Portas
--
Please reply only to the newsgroup
--|||Oops, you wanted the *correct* values:
SELECT C.*
FROM Customers AS C
JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
--
David Portas
--
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:4eOdnfkpJ63Bqmii4p2dnA@.giganews.com...
> Oops, you wanted the *correct* values:
> SELECT C.*
> FROM Customers AS C
> JOIN
> (SELECT customerid, MAX(paymentdate) AS lastpaymentdate
> FROM Payments
> GROUP BY customerid) AS P
> ON C.customerid=P.customerid
> AND C.lastpaymentdate=P.lastpaymentdate
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
I want the LastPaymentAmount of Customers table to be compaired with the
Amount of the last payment in Payments table.
Ofcourse the last payment is the one with the maximum PaymentDate but the
Amount fields should be compared in both tables to see if they are equal.|||You didn't include any keys or constraints with your DDL but I'll have to
assume that (customerid, paymentdate) is unique in Payments - otherwise how
will you define which payment is the latest?
SELECT C.customerid, C.lastpaymentamount, C.lastpaymentdate
FROM
(SELECT customerid,
MAX(paymentdate) AS paymentdate
FROM Payments
GROUP BY customerid) AS P1
JOIN Payments AS P2
ON P1.customerid = P2.customerid
AND P1.paymentdate = P2.paymentdate
JOIN Customers AS C
ON P2.customerid = C.customerid
AND P2.paymentdate = C.lastpaymentdate
AND P2.amount = C.lastpaymentamount
--
David Portas
--
Please reply only to the newsgroup
--|||>> what query will bring me the customers whose last_payment_date in
the
customers table is not correct. <<
Your design is wrong; this is a computed value and you do not store
computed values in an RDBMS. Your Customers table should not exist at
all! And you do not have keys or anything that makes payments into a
table. Let's get the basics right:
CREATE TABLE Payments
(payment_id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers
ON DELETE CASCADE
ON UPDATE CASCADE,
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);
Now create a VIEW which will always be correct:
CREATE VIEW CurrentPayments (payment_id, customer_id, payment_date)
AS
SELECT payment_id, customer_id, payment_date
FROM Payments AS P1
WHERE P1.payment_date
= (SELECT MAX(payment_date)
FROM Payments AS P2
WHERE P1.customer_id = P2.customer_id);|||--CELKO-- (joe.celko@.northface.edu) writes:
> Your design is wrong; this is a computed value and you do not store
> computed values in an RDBMS.
Maybe in some small ideal model of a database you don't.
In real-world databases, you often find that you can save a lot of
cycles by storing computed data. For instance, if you have a transactions
table for a bank account, you probably want to store the balance on the
account after each transaction, because you will have several functions
in you system where you display this information. Having it computed
means that this function will run faster, and that the likelyhood that
they will show the wrong value because of bugs is less.
The one problem with storing computed values, is that if an underlying
value changes, the computed value changes too. But in the example I
just gave, this never happens due to business rules.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||>> this is a computed value and you do not store computed values in an
RDBMS. <<
May I know why so? Snapshots and stored joins, as recommended by the
relational model are typical examples where computed values are stored in an
RDBMS.
In SQL Server, you can materialize computed columns and views by physically
clustering them and I don't see why this should not be done, when required.
--
- Anith
( Please reply to newsgroups only )

Question about JOIN syntax

what is the syntax to join a table with the result of antoher query.

For example i have two tables

Create Table Customers (
CustomerID int,
LastPaymentDate Datetime )

Create Table Payments (
PaymentID int,
CustomerID int,
PaymentDate Datetime )

What query will bring me the customers whose lastpaymentdate in the
customers table is not correct.

That can only be checked by comparing it with the max paymentdate for each
customer in the payments table.

I want this to be done by ansi standard sql. Not using any specific feature
of sql server.

thxSELECT *
FROM Customers AS C
WHERE lastpaymentdate <>
(SELECT MAX(paymentdate)
FROM Payments AS P
WHERE P.customerid=C.customerid)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:2vydnRekZvROvmiiRVn-sw@.giganews.com...
> SELECT *
> FROM Customers AS C
> WHERE lastpaymentdate <>
> (SELECT MAX(paymentdate)
> FROM Payments AS P
> WHERE P.customerid=C.customerid)

oh may be my example was not the best!

what if I have

Create Table Customers (
CustomerID int,
LastPaymentAmount int,
LastPaymentDate Datetime )

Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )

and I want to see if the LastPaymentAmount field holds correct values.
I couldnt use the max function for this obviously.

thx|||> I couldnt use the max function for this obviously.

Why not? My query should still work.

If you want a query that uses a join then try this:

SELECT C.*
FROM Customers AS C
LEFT JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
WHERE C.lastpaymentdate IS NOT NULL
AND P.customerid IS NULL

This has a possible advantage over the correlated subquery version. It will
include Customers who have a Lastpaymentdate but don't have any non-NULL
dates in the Payments table.

--
David Portas
----
Please reply only to the newsgroup
--|||Oops, you wanted the *correct* values:

SELECT C.*
FROM Customers AS C
JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:4eOdnfkpJ63Bqmii4p2dnA@.giganews.com...
> Oops, you wanted the *correct* values:
> SELECT C.*
> FROM Customers AS C
> JOIN
> (SELECT customerid, MAX(paymentdate) AS lastpaymentdate
> FROM Payments
> GROUP BY customerid) AS P
> ON C.customerid=P.customerid
> AND C.lastpaymentdate=P.lastpaymentdate
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --

I want the LastPaymentAmount of Customers table to be compaired with the
Amount of the last payment in Payments table.
Ofcourse the last payment is the one with the maximum PaymentDate but the
Amount fields should be compared in both tables to see if they are equal.|||You didn't include any keys or constraints with your DDL but I'll have to
assume that (customerid, paymentdate) is unique in Payments - otherwise how
will you define which payment is the latest?

SELECT C.customerid, C.lastpaymentamount, C.lastpaymentdate
FROM
(SELECT customerid,
MAX(paymentdate) AS paymentdate
FROM Payments
GROUP BY customerid) AS P1
JOIN Payments AS P2
ON P1.customerid = P2.customerid
AND P1.paymentdate = P2.paymentdate
JOIN Customers AS C
ON P2.customerid = C.customerid
AND P2.paymentdate = C.lastpaymentdate
AND P2.amount = C.lastpaymentamount

--
David Portas
----
Please reply only to the newsgroup
--|||>> what query will bring me the customers whose last_payment_date in
the
customers table is not correct. <<

Your design is wrong; this is a computed value and you do not store
computed values in an RDBMS. Your Customers table should not exist at
all! And you do not have keys or anything that makes payments into a
table. Let's get the basics right:

CREATE TABLE Payments
(payment_id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers
ON DELETE CASCADE
ON UPDATE CASCADE,
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);

Now create a VIEW which will always be correct:

CREATE VIEW CurrentPayments (payment_id, customer_id, payment_date)
AS
SELECT payment_id, customer_id, payment_date
FROM Payments AS P1
WHERE P1.payment_date
= (SELECT MAX(payment_date)
FROM Payments AS P2
WHERE P1.customer_id = P2.customer_id);|||--CELKO-- (joe.celko@.northface.edu) writes:
> Your design is wrong; this is a computed value and you do not store
> computed values in an RDBMS.

Maybe in some small ideal model of a database you don't.

In real-world databases, you often find that you can save a lot of
cycles by storing computed data. For instance, if you have a transactions
table for a bank account, you probably want to store the balance on the
account after each transaction, because you will have several functions
in you system where you display this information. Having it computed
means that this function will run faster, and that the likelyhood that
they will show the wrong value because of bugs is less.

The one problem with storing computed values, is that if an underlying
value changes, the computed value changes too. But in the example I
just gave, this never happens due to business rules.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> In real-world databases, you often find that you can save a lot of
cycles by storing computed data. <<

OLAP versus versus OLTP databases. What I found is that the summary
data is usually processed by many applications at the same time (i.e.
end of the month, end of the quarter, end of the year), so you can
materialize the VIEWs and share them at that point in time instead of
always updating a redundant permanent table.

Unfortunately, SQL Server is not designed with that kind of sharing in
mind; it is more of a mainframe, heavy batch processing system approach.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> this is a computed value and you do not store computed values in an
RDBMS. <<

May I know why so? Snapshots and stored joins, as recommended by the
relational model are typical examples where computed values are stored in an
RDBMS.

In SQL Server, you can materialize computed columns and views by physically
clustering them and I don't see why this should not be done, when required.

--
- Anith
( Please reply to newsgroups only )|||I agree with Joe. (Not that he needs my agreement!) Your customers
table should hold information that describes each customer. The
customer's payment history doesn't belong there. You can have a view
that holds each customer's latest payment if that's something you use
a lot. And why do you have a table for payments? You should have a
table for transactions, of which payments are only one flavor.

On Fri, 2 Jan 2004 13:45:29 +0500, "Mahmood" <a29zitkitwe@.yahoo.com>
wrote:

>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>news:2vydnRekZvROvmiiRVn-sw@.giganews.com...
>> SELECT *
>> FROM Customers AS C
>> WHERE lastpaymentdate <>
>> (SELECT MAX(paymentdate)
>> FROM Payments AS P
>> WHERE P.customerid=C.customerid)
>>
>oh may be my example was not the best!
>what if I have
>Create Table Customers (
>CustomerID int,
>LastPaymentAmount int,
>LastPaymentDate Datetime )
>
>Create Table Payments (
>PaymentID int,
>CustomerID int,
>Amount int,
>PaymentDate Datetime )
>and I want to see if the LastPaymentAmount field holds correct values.
>I couldnt use the max function for this obviously.
>thx
>

Question about insert query

Shortly why this works

insert dbo.t_Shopping_cart (Product_code,Name,Price,Group,Quantity,Total)
select Product_code,Name,Price,Group,Quantity=1,Total=1
FROM dbo.t_Product
WHERE Group=6

But this doesn't

insert dbo.t_Shopping_cart (Product_code,Name,Price,Group,Quantity,Total)
select Product_code,Name,Price,Group,Quantity=1,Total=Quantity*Price --this is actually line 4
FROM dbo.t_Product
WHERE Group=6

It gives this error
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Quantity'. It does not help if Quantity*Price is in brackets. Probably a simple thing, but I don't know it. Is it even possible to do this kind of query?

Regards

Leif

Your problem stems from the fact that there is no Quantity field on the Product table.. from the declaration "Total=Quantity*Price" it does not infer the Quantity from the previous "Quantity=1" - it will look for it on the product table.

You already know your Quantity when building your sql, so you need not use the reference, you can simply put the number in.
e.g.
select Product_code,Name,Price,Group,Quantity=1,Total=1*Price
or
select Product_code,Name,Price,Group,Quantity=245,Total=245*Price

|||

Aha. You mean it should be something like

select Product_code,Name,Price,Group,Quantity=@.Quantity,Total=@.Quantity*Price --this is actually line 4

Leif

Question about implicit string/datetime conversion.

(SQL Server 2000, SP3a)
Hello all!
If I have a query like:
select * from MyTable where MyDateField < '10/01/2003'
Does SQL Server implicitly convert that date string to a DATETIME datatype, or is it
implicitly converting all records' MyDateField to a string datatype? Since the VARCHAR(X)
is likely longer than the 8-byte datetime field, I suspect the latter, but how could I
find out for certain?
I suppose I could eliminate the ambiguity by explicitly converting:
select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
Thanks for any help you can provide!
John PetersonJohn,
It follows the precedency rule...
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> If I have a query like:
> select * from MyTable where MyDateField < '10/01/2003'
> Does SQL Server implicitly convert that date string to a DATETIME
datatype, or is it
> implicitly converting all records' MyDateField to a string datatype?
Since the VARCHAR(X)
> is likely longer than the 8-byte datetime field, I suspect the latter, but
how could I
> find out for certain?
> I suppose I could eliminate the ambiguity by explicitly converting:
> select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> Thanks for any help you can provide!
> John Peterson
>|||Thanks, oj!
As I read this, that seems to suggest that the lower precedence string (varchar)
'10/01/2003' would be implicitly converted to the higher precedence datetime. In essence,
it would be equivalent to the second statement in my original post.
Thanks again for the information. I'll keep that link handy! :-)
"oj" <nospam_ojngo@.home.com> wrote in message
news:udw67nbkDHA.3700@.TK2MSFTNGP11.phx.gbl...
> John,
> It follows the precedency rule...
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > If I have a query like:
> >
> > select * from MyTable where MyDateField < '10/01/2003'
> >
> > Does SQL Server implicitly convert that date string to a DATETIME
> datatype, or is it
> > implicitly converting all records' MyDateField to a string datatype?
> Since the VARCHAR(X)
> > is likely longer than the 8-byte datetime field, I suspect the latter, but
> how could I
> > find out for certain?
> >
> > I suppose I could eliminate the ambiguity by explicitly converting:
> >
> > select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||Hi John!
Just be aware that SQL7 did it differently. SQL7 converted the "data side" to the "column side".
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"John Peterson" <j0hnp@.comcast.net> wrote in message news:udwiCubkDHA.1656@.tk2msftngp13.phx.gbl...
> Thanks, oj!
> As I read this, that seems to suggest that the lower precedence string (varchar)
> '10/01/2003' would be implicitly converted to the higher precedence datetime. In essence,
> it would be equivalent to the second statement in my original post.
> Thanks again for the information. I'll keep that link handy! :-)
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:udw67nbkDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > John,
> >
> > It follows the precedency rule...
> >
> > http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > If I have a query like:
> > >
> > > select * from MyTable where MyDateField < '10/01/2003'
> > >
> > > Does SQL Server implicitly convert that date string to a DATETIME
> > datatype, or is it
> > > implicitly converting all records' MyDateField to a string datatype?
> > Since the VARCHAR(X)
> > > is likely longer than the 8-byte datetime field, I suspect the latter, but
> > how could I
> > > find out for certain?
> > >
> > > I suppose I could eliminate the ambiguity by explicitly converting:
> > >
> > > select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>

Question about GridView update query

I try to calculate a field value and update other. It almost works

UPDATE t_Shopping_cart SET [Product_code] = @.Product_code, [Quantity] = @.Quantity, [Total] = Quantity * Price WHERE ([Product_code] = @.Product_code)

I try to update t_Shopping_cart table with quantity values from grid view and calculate sum of products (quantity*Price). Best I can get is that quantity values are old. Some comments about my query:Price is also a field in table and control. [Product_code] = @.Product_code part is probably not needed.

Any ideas?

Leif

So there is a column in t_Shopping_cart with the name Price?
Then maybe you can use this:

UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)

|||

"So there is a column in t_Shopping_cart with the name Price?" Yes. I'll try that "UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)" tomorrow.

Thanks

Leif

|||

Np, feel free to get back to me if it doesn't work!

|||

Hi. A short question. Is this a good way to this. The more I read about ways to do this the more ways there seems to be calculating sub totals and probably checking duplicates too.

Regards

Leif

Question about functions

Hi,
I need to call a function in a sql query in a stored procedure to
calculate time differences between various dates. I have a function
that uses a cursor to sum up the totals of these numbers, but it runs
very, very slowly. I can accomplish the same results without a cursor
by using a temporary table and several queries, but when I try to put
this in a stored procedure and call the stored procedure from the
function, I get the following error:
Only functions and extended stored procedures can be executed from
within a function.
Any suggestions?
Thanks,
Amy Bolden>> Any suggestions?
The message clearly states what you can do with a function. So you will have
to find an alternative to calling procedures from functions. Either make the
calling routine a procedure or make the called routine a function.
Anith|||You must supply more information about that, did you try to use
datediff in a correlated query (don=B4t know where you get the data from
?).
Jens Suessmeyer.|||Sorry, I thought maybe an explanation would be enough.
Here is the top query in the stored procedure. The @.StartDate and
@.EndDate are
parameters that are supplied by a web application:
SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
POSUM.UserName,
TT.UserId,
dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
FROM vw_TTLH TT INNER JOIN
UserMaster POSUM ON TT.UserID = POSUM.UserNum
WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
Here is the function that calculates all the time spent in a truck for a
particular day:
CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
@.ActivityDate DateTime = NULL)
RETURNS int
AS
BEGIN
DECLARE @.TotalTimeInTruck INT
Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
RETURN @.TotalTimeInTruck
END
Here is the stored procedure that I am trying to call from the function
to get the total time
spent in a truck on a single day:
CREATE PROCEDURE spGetTotalTimeInTruck
@.UserID INT,
@.ActivityDate VARCHAR(10),
@.TotalTimeInTruck INT OUTPUT
AS
DECLARE @.ActivityDatePlusOne VARCHAR(10)
SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
CREATE TABLE #tempTruck
(
SeqNum int NULL,
InTruck datetime NULL,
OutTruck datetime NULL
)
INSERT INTO #tempTruck
(OrderNumber, InTruck)
SELECT OrderNumber, DateTS
FROM vw_TTLH
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= @.ActivityDatePlusOne
AND ActID in (200)
UPDATE #tempTruck
SET OutTruck = (
SELECT DateTS
FROM vw_TTLH
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= @.ActivityDatePlusOne
AND OrderNumber = #tempTruck.OrderNumber + 1)
SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
DROP TABLE #tempTruck
RETURN @.TotalTimeInTruck
Thanks,
Amy Bolden
*** Sent via Developersdex http://www.examnotes.net ***|||Are you talking about something similar to this?
http://www.eggheadcafe.com/articles/20030626.asp
Robbe Morris - 2004/2005 Microsoft MVP C#
Free Source Code for ADO.NET Object Mapper To DataBase Tables And Stored
Procedures
http://www.eggheadcafe.com/articles...e_generator.asp
"Amy" <abolden@.eastridge.net> wrote in message
news:1127400856.741551.164370@.z14g2000cwz.googlegroups.com...
> Hi,
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
> Any suggestions?
> Thanks,
> Amy Bolden
>|||You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)
CREATE PROCEDURE spGetTotalTimeInTruck @.UserID INT, @.ActivityDate
VARCHAR(10), @.TotalTimeInTruck INT OUTPUT
AS
SELECT -- Should "@.TotalTimeInTruck = " go here'
SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM ( SELECT OrderNumber, DateTS AS InTruck,
( SELECT DateTS
FROM vw_TTLH ttlh2
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
FROM vw_TTLH ttlh1
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND ActID in (200)
) ttlh_d
RETURN @.TotalTimeInTruck
You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.
Good luck.
Payson
Amy Bolden wrote:
> Sorry, I thought maybe an explanation would be enough.
> Here is the top query in the stored procedure. The @.StartDate and
> @.EndDate are
> parameters that are supplied by a web application:
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
> POSUM.UserName,
> TT.UserId,
> dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
> FROM vw_TTLH TT INNER JOIN
> UserMaster POSUM ON TT.UserID = POSUM.UserNum
> WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
> GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
> CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
> Here is the function that calculates all the time spent in a truck for a
> particular day:
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
> @.ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
> DECLARE @.TotalTimeInTruck INT
>
> Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
> RETURN @.TotalTimeInTruck
> END
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
> CREATE PROCEDURE spGetTotalTimeInTruck
> @.UserID INT,
> @.ActivityDate VARCHAR(10),
> @.TotalTimeInTruck INT OUTPUT
> AS
> DECLARE @.ActivityDatePlusOne VARCHAR(10)
> SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND ActID in (200)
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
> DROP TABLE #tempTruck
> RETURN @.TotalTimeInTruck
> Thanks,
> Amy Bolden
> *** Sent via Developersdex http://www.examnotes.net ***|||You might try eliminating the temp table and using a derived table in
its place. Something like this: (COMPLETELY UNTESTED)
CREATE PROCEDURE spGetTotalTimeInTruck @.UserID INT, @.ActivityDate
VARCHAR(10), @.TotalTimeInTruck INT OUTPUT
AS
SELECT -- Should "@.TotalTimeInTruck = " go here'
SUM(DateDiff(n, InTruck, OutTruck)) AS
FROM ( SELECT OrderNumber, DateTS AS InTruck,
( SELECT DateTS
FROM vw_TTLH ttlh2
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND OrderNumber = ttlh1.OrderNumber + 1) AS OutTruck
FROM vw_TTLH ttlh1
WHERE USerID = @.UserID
AND DateTimeStamp >= @.ActivityDate
AND DateTimeStamp <= DATEADD(d, 1, @.ActivityDate)
AND ActID in (200)
) ttlh_d
RETURN @.TotalTimeInTruck
You might then consider making the function an inline function (put the
select inline - "return select ..."). The optimizer seems to like that
better than procedural code.
Good luck.
Payson
Amy Bolden wrote:
> Sorry, I thought maybe an explanation would be enough.
> Here is the top query in the stored procedure. The @.StartDate and
> @.EndDate are
> parameters that are supplied by a web application:
> SELECT DISTINCT(CONVERT(VARCHAR(10), TT.DateTS, 101)) AS ActivityDate,
> POSUM.UserName,
> TT.UserId,
> dbo.fnTimeInTruckInMinutes(TT.UserId,TT.DateTS) AS TimeInTruck
> FROM vw_TTLH TT INNER JOIN
> UserMaster POSUM ON TT.UserID = POSUM.UserNum
> WHERE DateTS BETWEEN @.StartDate AND @.EndDate)
> GROUP BY POSUM.UserName, TT.UserID, POSUM.UserNum,
> CONVERT(VARCHAR(10), TT.DateTS, 101), TT.DateTS
> Here is the function that calculates all the time spent in a truck for a
> particular day:
> CREATE FUNCTION dbo.fnTimeInTruckInMinutes (@.UserID int = NULL,
> @.ActivityDate DateTime = NULL)
> RETURNS int
> AS
> BEGIN
> DECLARE @.TotalTimeInTruck INT
>
> Exec spGetTotalTimeInTruck @.UserID, @.ActivityDate, @.TotalTimeInTruck
> RETURN @.TotalTimeInTruck
> END
> Here is the stored procedure that I am trying to call from the function
> to get the total time
> spent in a truck on a single day:
> CREATE PROCEDURE spGetTotalTimeInTruck
> @.UserID INT,
> @.ActivityDate VARCHAR(10),
> @.TotalTimeInTruck INT OUTPUT
> AS
> DECLARE @.ActivityDatePlusOne VARCHAR(10)
> SET @.ActivityDatePlusOne = DATEADD(d, 1, @.ActivityDate)
> CREATE TABLE #tempTruck
> (
> SeqNum int NULL,
> InTruck datetime NULL,
> OutTruck datetime NULL
> )
> INSERT INTO #tempTruck
> (OrderNumber, InTruck)
> SELECT OrderNumber, DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND ActID in (200)
> UPDATE #tempTruck
> SET OutTruck = (
> SELECT DateTS
> FROM vw_TTLH
> WHERE USerID = @.UserID
> AND DateTimeStamp >= @.ActivityDate
> AND DateTimeStamp <= @.ActivityDatePlusOne
> AND OrderNumber = #tempTruck.OrderNumber + 1)
> SELECT SUM(DateDiff(n, InTruck, OutTruck)) FROM #tempTruck
> DROP TABLE #tempTruck
> RETURN @.TotalTimeInTruck
> Thanks,
> Amy Bolden
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi
I think if you omitt using of CURSUR in your Function then it will be some
more faster. If you are using cursor for itration purpose than I have an Ide
a
that may help you.
There is no concept of Arrays in SQL Server I think so, but we can create
our own Psedu Arrays, and we can itrate in these arrays.
Define a local variable as varchar and insert the Primary key in the varable
COMMA seprated.
And then by a while loop get the ID and select the field(s) you want from
the orignal table and colculate it.
Run This code It may open your Mind
Declare @.var varchar(100)
SET @.var = '120,20,23,32,23234,,3,5,6,'
WHILE @.var <> ''
BEGIN
Declare @.id int
SET @.id = CAST(SUBSTRING(@.var,0, CharIndex(',', @.var,0)) as int)
SET @.var = SUBSTRING(@.var, CharIndex(',', @.var,0)+1, LEN(@.var))
PRINT @.id
END
________________________________________
__________________
"Amy" wrote:

> Hi,
> I need to call a function in a sql query in a stored procedure to
> calculate time differences between various dates. I have a function
> that uses a cursor to sum up the totals of these numbers, but it runs
> very, very slowly. I can accomplish the same results without a cursor
> by using a temporary table and several queries, but when I try to put
> this in a stored procedure and call the stored procedure from the
> function, I get the following error:
> Only functions and extended stored procedures can be executed from
> within a function.
> Any suggestions?
> Thanks,
> Amy Bolden
>

question about dummy constraint

I've written code that dynamically builds an sql query based on
various constraint possibilities.

The problem is the code would have been very complex had I not come up
with a dummy constraint as a kind of place holder in the statement.

To avoid complex logic that determines if there was another constraint
before any other constraint and hence the need to add, say, AND or
not, I came up with a dummy constraint so that every subsequent
constraint will begin with AND. There's no need to determine whether
to add AND or not. This makes the coding much simpler because all
that needs to be done is ask if a certain condition exists then add
the constraint along with AND in front every time.

So what I did was create the statement like this:

SELECT elapsed_time AS ET from Table1 WHERE 1 > 0 AND 1stConstraint
AND 2nd Constraint and so on.

See if the 1 > 0 condition were not there it would be necessary to
first determine the first actual contraint and not add AND in front of
it and then add the rest with ANDs in front of every one.

I should add that the user does not have to select any constraint and
that's the problem. I need to stick that WHERE 1>0 in there so that
there doesn't need to be a determination of which other, if any,
constraints are selected.

Even if my explanation above is not well understood, believe me the
front-end coding is much easier this way.

My question is does the 1 > 0 conditional check present the database
with any significant overhead or as far was dummy constraints go is
this as good as any other?

-David"wireless" <wireless200@.yahoo.com> wrote in message
news:90446ee7.0309220903.44559456@.posting.google.c om...
> I've written code that dynamically builds an sql query based on
> various constraint possibilities.
> The problem is the code would have been very complex had I not come up
> with a dummy constraint as a kind of place holder in the statement.
> To avoid complex logic that determines if there was another constraint
> before any other constraint and hence the need to add, say, AND or
> not, I came up with a dummy constraint so that every subsequent
> constraint will begin with AND. There's no need to determine whether
> to add AND or not. This makes the coding much simpler because all
> that needs to be done is ask if a certain condition exists then add
> the constraint along with AND in front every time.
> So what I did was create the statement like this:
> SELECT elapsed_time AS ET from Table1 WHERE 1 > 0 AND 1stConstraint
> AND 2nd Constraint and so on.
> See if the 1 > 0 condition were not there it would be necessary to
> first determine the first actual contraint and not add AND in front of
> it and then add the rest with ANDs in front of every one.
> I should add that the user does not have to select any constraint and
> that's the problem. I need to stick that WHERE 1>0 in there so that
> there doesn't need to be a determination of which other, if any,
> constraints are selected.
> Even if my explanation above is not well understood, believe me the
> front-end coding is much easier this way.
> My question is does the 1 > 0 conditional check present the database
> with any significant overhead or as far was dummy constraints go is
> this as good as any other?
> -David

In itself, adding such a trivial filter condition shouldn't affect anything,
but building dynamic query strings for every query isn't as robust as
executing stored procedures. If it's possible in your situation, you might
want to consider something like this:

http://www.algonet.se/~sommar/dyn-search.html

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> In itself, adding such a trivial filter condition shouldn't affect
> anything, but building dynamic query strings for every query isn't as
> robust as executing stored procedures. If it's possible in your
> situation, you might want to consider something like this:
> http://www.algonet.se/~sommar/dyn-search.html

And if you read that article, you will see that the author uses
precisely that kind of dummy condition to simplify building the
dynamic query!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 22 Sep 2003 21:50:36 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:

>In itself, adding such a trivial filter condition shouldn't affect anything,
>but building dynamic query strings for every query isn't as robust as
>executing stored procedures. If it's possible in your situation, you might
>want to consider something like this:
>http://www.algonet.se/~sommar/dyn-search.html

Simon, thanks. This is interesting. I had not used stored procedures
before except to implement a data loading program.

-David|||David,

I have dealt with this problem before and it isn't too complex to
check your SQL string for a ' where ' piece and then performing either
an operation that adds a logical operator or not adding one.

Try using a sql server function called PATINDEX.

Cliff

David Brown <wireless200@.NOSPAMyahoo.com> wrote in message news:<t36gqvkohci7pule7m0gq2djot5hj0or8k@.4ax.com>...
> On Mon, 22 Sep 2003 21:50:36 +0200, "Simon Hayes" <sql@.hayes.ch>
> wrote:
>
> >In itself, adding such a trivial filter condition shouldn't affect anything,
> >but building dynamic query strings for every query isn't as robust as
> >executing stored procedures. If it's possible in your situation, you might
> >want to consider something like this:
> >http://www.algonet.se/~sommar/dyn-search.html
>
> Simon, thanks. This is interesting. I had not used stored procedures
> before except to implement a data loading program.
> -David

question about different query results with wildcard

Hi, I'm working with a third party app on SQL Server 2000, and from what I can gather, programmed in C# & VisualFoxPro.

When we search with
Note contains 94949
we get 571 results, when we search with
Note contains 94949*
we get 575 results.

There should be at least a hundred different entries that start with "94949-1" so I expected the query with the wildcard to return something like 680 results, not an additional four rows.

Searching with
Note contains 94949-1*
got 483 results
Note contains 94949-10*
got 0 results

Could someone explain or point me to more documentation on the difference results we get?
Thanks

To see the 'wildcards' used with T-SQL, look up topic: LIKE in Books Online.

The asterisk is not a T-SQL wildcard. You queries using an asterisk are most likely returning you counts where the last character is an asterisk. AND you MUST use the LIKE equality instead of equals (=).

Here is a small example of using Wildcards in T-SQL:

SET NOCOUNT ON

CREATE TABLE #MyTable
( MyTableID int IDENTITY,
MyString varchar(50)
)

INSERT INTO #MyTable VALUES ( '1234' )
INSERT INTO #MyTable VALUES ( '1234-1' )
INSERT INTO #MyTable VALUES ( '1234*' )
INSERT INTO #MyTable VALUES ( '1234-45' )
INSERT INTO #MyTable VALUES ( '1234-ab' )

SELECT '1234', count(1) FROM #MyTable WHERE MyString = '1234'
SELECT '1234*', count(1) FROM #MyTable WHERE MyString = '1234*'
SELECT 'LIKE ''1234''', count(1) FROM #MyTable WHERE MyString LIKE '1234'
SELECT 'LIKE ''1234%''', count(1) FROM #MyTable WHERE MyString LIKE '1234%'

DROP TABLE #MyTable

|||Hi Arnie, thanks for your response.

My situation is more basic than what appears in your example. Unfortunately, I am working solely through this application so I do not have access to the SQL to test out what you supplied, so my question is more of a need for an explanation of the search results. I do find lots of references to LIKE, Wildcards and pattern matching, but I don't find a way to explain to the users the best and most complete way to search. They mostly need a "this will always get us the results without missing anything" search technique and then to be able to select from a smaller group. I guess I need a basic course in understanding search results: how to get different ones and what they mean.

Using
Note contains 94949-'%'
returned one more result than when using an asterisk. I don't understand this difference.

Note contains 94949-'%1' or
Note contains 94949-'1%'
brings nothing nor does not using quotes. But there are hundreds of records which have the string starting with 94949-1 and a varying number of characters after that.
?Does the dash read not as a character in the string but as an expression?

When I use WITHIN 3 characters, I get too few results (eight). If I use AND, I get text unrelated to the account number I am looking for.

Again when I tried to narrow the search by adding one digit to the string to be matched, I did not get any results, but 500 results from the more general search is too much to scan by opening individual records.

Thanks for pondering this with me.

|||Assuming that the application is using SQL Server Full-Text Indexing, the rules would be defined here
http://msdn2.microsoft.com/en-us/library/ms187787.aspx

Question about Count (*)

How to include a Count?
I have a table A, B C
TableA TableB TableC
idA idA idA
I need to query against the TableB where TableB.dt_Date = "date"
returns me the all fields including idA.
Here its fine.
But, I want to know how many idA related with the results from the query I
will find in the TableC.
take a look:
for instance
results from my query...
idA name total ( I need this field total)
18 nondn 2
239 djdjjd 4
383 djdjdj 5
382 test
Where total is the total idA I will find on the TableC, for instance above
idA 18 has 2
on tableC
Any help?
thanks in advance!Luiz wrote:
> How to include a Count?
> I have a table A, B C
> TableA TableB TableC
> idA idA idA
> I need to query against the TableB where TableB.dt_Date = "date"
> returns me the all fields including idA.
> Here its fine.
> But, I want to know how many idA related with the results from the query I
> will find in the TableC.
> take a look:
> for instance
> results from my query...
> idA name total ( I need this field total)
> 18 nondn 2
> 239 djdjjd 4
> 383 djdjdj 5
> 382 test
> Where total is the total idA I will find on the TableC, for instance above
> idA 18 has 2
> on tableC
Your question is irritatingly vague and hard to understand. But perhaps
this is what you mean:
SELECT TableB.idA, TableB.name, COUNT(TableC.idA) AS total
FROM TableB
INNER JOIN TableC ON TableB.idA = TableC.idA
WHERE TableB.dt_Date = "date"|||It sounds like you're just needing the syntax (though you half posted
it in your title)
Select idA, [name], Count(*) as Total
FROM TableC
Group By idA, [Name]
Cheers
Will|||Will your example parse rpresser? I would have thought you'd need a
group by clause.|||I'll try to be clear...
more simple:
I select from table Cinema using a criteria whatever.
so, I will get a list
CinemaNome IDCinema
djdjdjjd 19
dkdkdk 20
sksks 23
Thats fine. But I have another TableB. who has n IDCinema
I want to join a count from the tableB based on the results.
TableB
ID IDCInema
1 19
2 19
3 20
3 23
N 23
like this:
CinemaNome IDCinema Total
djdjdjjd 19 2
dkdkdk 20 1
sksks 23 2
I hope its clear.
Thanks in advance!
"rpresser" wrote:

> Luiz wrote:
> Your question is irritatingly vague and hard to understand. But perhaps
> this is what you mean:
> SELECT TableB.idA, TableB.name, COUNT(TableC.idA) AS total
> FROM TableB
> INNER JOIN TableC ON TableB.idA = TableC.idA
> WHERE TableB.dt_Date = "date"
>|||As will pointed out, this query needs a group by clause, otherwise what
rpresser posted should work, if I understand your post correctly.
SELECT TableB.idA, TableB.name, COUNT(TableC.idA) AS total
FROM TableB
INNER JOIN TableC ON TableB.idA = TableC.idA
WHERE TableB.dt_Date = "date"
group by TableB.idA, TableB.name|||Hi Jim,
thanks, it works with two tables!
I want to add one more table.
because the "date " is on tableA, and the tableA also has the "IDCinema".
if you have any suggestion...comments.
Thanks!
"Jim Underwood" wrote:

> As will pointed out, this query needs a group by clause, otherwise what
> rpresser posted should work, if I understand your post correctly.
> SELECT TableB.idA, TableB.name, COUNT(TableC.idA) AS total
> FROM TableB
> INNER JOIN TableC ON TableB.idA = TableC.idA
> WHERE TableB.dt_Date = "date"
> group by TableB.idA, TableB.name
>
>|||just add an inner join on table A, and change the tableb.dt_date to
tablea.dt_date.
INNER JOIN TableA ON TableB.idA = TableC.idA
Also, check out BOL for more information on joins, or try these sites..
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
"Luiz" <Luiz@.discussions.microsoft.com> wrote in message
news:F94EAF81-3699-4820-A930-EBA59948B549@.microsoft.com...
> Hi Jim,
> thanks, it works with two tables!
>
> I want to add one more table.
> because the "date " is on tableA, and the tableA also has the "IDCinema".
> if you have any suggestion...comments.
> Thanks!
> "Jim Underwood" wrote:
>|||Will wrote:
> Will your example parse rpresser? I would have thought you'd need a
> group by clause.
Oops. Haste makes waste.
It does indeed need a group by clause.

question about cached queries

Hi
I am trying to understand SQL Server's internals and I was wondering
how syscacheobjects works with memory.
If an application sends a query to the database, does the execution plan
stay in both memory and syscacheobjects or is syscacheobjects
a table representation of what's in memory? When the optimizer
looks to try to re use a plan, where does it look?
TIA
Dave
syscacheobjects is not a real table:
SELECT OBJECTPROPERTY(id, 'TableIsFake')
FROM sysobjects
WHERE id = OBJECT_ID('syscacheobjects')
It is essentially a view over the procedure cache, which is the area in
memory where procedures are cached (good name for it, isn't it?) The
optimizer looks at the memory structures, not at the table.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Dodo Lurker" <none@.noemailplease> wrote in message
news:3cudnTXSXpYJJgzeRVn-hg@.comcast.com...
> Hi
> I am trying to understand SQL Server's internals and I was wondering
> how syscacheobjects works with memory.
> If an application sends a query to the database, does the execution plan
> stay in both memory and syscacheobjects or is syscacheobjects
> a table representation of what's in memory? When the optimizer
> looks to try to re use a plan, where does it look?
> TIA
> Dave
>

question about cached queries

Hi
I am trying to understand SQL Server's internals and I was wondering
how syscacheobjects works with memory.
If an application sends a query to the database, does the execution plan
stay in both memory and syscacheobjects or is syscacheobjects
a table representation of what's in memory? When the optimizer
looks to try to re use a plan, where does it look?
TIA
Davesyscacheobjects is not a real table:
SELECT OBJECTPROPERTY(id, 'TableIsFake')
FROM sysobjects
WHERE id = OBJECT_ID('syscacheobjects')
It is essentially a view over the procedure cache, which is the area in
memory where procedures are cached (good name for it, isn't it?) The
optimizer looks at the memory structures, not at the table.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Dodo Lurker" <none@.noemailplease> wrote in message
news:3cudnTXSXpYJJgzeRVn-hg@.comcast.com...
> Hi
> I am trying to understand SQL Server's internals and I was wondering
> how syscacheobjects works with memory.
> If an application sends a query to the database, does the execution plan
> stay in both memory and syscacheobjects or is syscacheobjects
> a table representation of what's in memory? When the optimizer
> looks to try to re use a plan, where does it look?
> TIA
> Dave
>

question about cached queries

Hi
I am trying to understand SQL Server's internals and I was wondering
how syscacheobjects works with memory.
If an application sends a query to the database, does the execution plan
stay in both memory and syscacheobjects or is syscacheobjects
a table representation of what's in memory? When the optimizer
looks to try to re use a plan, where does it look?
TIA
Davesyscacheobjects is not a real table:
SELECT OBJECTPROPERTY(id, 'TableIsFake')
FROM sysobjects
WHERE id = OBJECT_ID('syscacheobjects')
It is essentially a view over the procedure cache, which is the area in
memory where procedures are cached (good name for it, isn't it?) The
optimizer looks at the memory structures, not at the table.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Dodo Lurker" <none@.noemailplease> wrote in message
news:3cudnTXSXpYJJgzeRVn-hg@.comcast.com...
> Hi
> I am trying to understand SQL Server's internals and I was wondering
> how syscacheobjects works with memory.
> If an application sends a query to the database, does the execution plan
> stay in both memory and syscacheobjects or is syscacheobjects
> a table representation of what's in memory? When the optimizer
> looks to try to re use a plan, where does it look?
> TIA
> Dave
>

Question about an aggregate query

Hi All
Using SQL Server 2000.
Given an hypothetical table (this is similar to a table that exists but
I am omitting details such as contraints and indexes and other fields
for simplicity) of:
Table1
[Field_Index] {int32, unique, not null}
[Field_A] {float, not null, (-999)}
[Field_B] {float, not null, (-999)}
[Field_C] {float, not null, (-999)}
Say I want to create an aggregate view such as the following (I
actually want to use AVG but for simplicity will use SUM):
SELECT SUM([Field_A]) AS SUM_A FROM Table1 WHERE [Field_Index] IN
(1,2,3) AND [Field_A] <> -999;
I am using -999 to mark "bad" data. This works fine and dandy as
expected.
Suppose now I have data that looks like this:
[Field_Index] [Field_A] [Field_B] [Field_C]
1 1 10 100
2 -999 20 200
3 2 -999 300
Now my query above would return SUM_A=3. Absolutely correct.
BUT, Say I want to execute this query:
SELECT SUM([Field_A]) AS SUM_A, SUM([Field_B]) AS SUM_B, SUM([Field_B])
AS SUM_B FROM Table1 WHERE [Field_Index] IN (1,2,3) AND ?;
I have placed the (?) because I am not sure what to do. In words I
want per column the sum of all rows that <> -999 to give the following
output in one results:
3 30 600
My question is how can I do this? If I use a where clause then I can
either "AND" or "OR" the wheres which would result in:
"AND"
1 10 100
"OR"
Not sure since I don't have access or sql server here at work, but I
think this would not work either.
Now, I have many fields (about 200) that I would want to aggregate this
over.
Can anyone provide suggestions or comments?
thanks
dabenTry using a "case" expression.
select
sum(case when field_a != -999 then field_a else 0 end) as sum_a,
sum(case when field_b != -999 then field_b else 0 end) as sum_b,
sum(case when field_c != -999 then field_c else 0 end) as sum_c
from
table1
WHERE
[Field_Index] IN (1,2,3)
go
AMB
"daben" wrote:

> Hi All
> Using SQL Server 2000.
> Given an hypothetical table (this is similar to a table that exists but
> I am omitting details such as contraints and indexes and other fields
> for simplicity) of:
> Table1
> [Field_Index] {int32, unique, not null}
> [Field_A] {float, not null, (-999)}
> [Field_B] {float, not null, (-999)}
> [Field_C] {float, not null, (-999)}
>
> Say I want to create an aggregate view such as the following (I
> actually want to use AVG but for simplicity will use SUM):
> SELECT SUM([Field_A]) AS SUM_A FROM Table1 WHERE [Field_Index] IN
> (1,2,3) AND [Field_A] <> -999;
> I am using -999 to mark "bad" data. This works fine and dandy as
> expected.
> Suppose now I have data that looks like this:
> [Field_Index] [Field_A] [Field_B] [Field_C]
> 1 1 10 100
> 2 -999 20 200
> 3 2 -999 300
> Now my query above would return SUM_A=3. Absolutely correct.
> BUT, Say I want to execute this query:
> SELECT SUM([Field_A]) AS SUM_A, SUM([Field_B]) AS SUM_B, SUM([Field_B])
> AS SUM_B FROM Table1 WHERE [Field_Index] IN (1,2,3) AND ?;
> I have placed the (?) because I am not sure what to do. In words I
> want per column the sum of all rows that <> -999 to give the following
> output in one results:
> 3 30 600
> My question is how can I do this? If I use a where clause then I can
> either "AND" or "OR" the wheres which would result in:
> "AND"
> 1 10 100
> "OR"
> Not sure since I don't have access or sql server here at work, but I
> think this would not work either.
>
> Now, I have many fields (about 200) that I would want to aggregate this
> over.
> Can anyone provide suggestions or comments?
> thanks
> daben
>|||Daben,
Try:
SELECT AVG(NULLIF(FIELD_A,-999))AS 'AVG FIELD1',AVG(NULLIF(FIELD_B,-999))AS
'AVG FIELD2',AVG(NULLIF(FIELD_C,-999))AS 'AVG FIELD3'
FROM YOURTABLENAME
HTH
Jerry
"daben" <dabenpb@.yahoo.com> wrote in message
news:1129058732.374912.227020@.o13g2000cwo.googlegroups.com...
> Hi All
> Using SQL Server 2000.
> Given an hypothetical table (this is similar to a table that exists but
> I am omitting details such as contraints and indexes and other fields
> for simplicity) of:
> Table1
> [Field_Index] {int32, unique, not null}
> [Field_A] {float, not null, (-999)}
> [Field_B] {float, not null, (-999)}
> [Field_C] {float, not null, (-999)}
>
> Say I want to create an aggregate view such as the following (I
> actually want to use AVG but for simplicity will use SUM):
> SELECT SUM([Field_A]) AS SUM_A FROM Table1 WHERE [Field_Index] IN
> (1,2,3) AND [Field_A] <> -999;
> I am using -999 to mark "bad" data. This works fine and dandy as
> expected.
> Suppose now I have data that looks like this:
> [Field_Index] [Field_A] [Field_B] [Field_C]
> 1 1 10 100
> 2 -999 20 200
> 3 2 -999 300
> Now my query above would return SUM_A=3. Absolutely correct.
> BUT, Say I want to execute this query:
> SELECT SUM([Field_A]) AS SUM_A, SUM([Field_B]) AS SUM_B, SUM([Field_B])
> AS SUM_B FROM Table1 WHERE [Field_Index] IN (1,2,3) AND ?;
> I have placed the (?) because I am not sure what to do. In words I
> want per column the sum of all rows that <> -999 to give the following
> output in one results:
> 3 30 600
> My question is how can I do this? If I use a where clause then I can
> either "AND" or "OR" the wheres which would result in:
> "AND"
> 1 10 100
> "OR"
> Not sure since I don't have access or sql server here at work, but I
> think this would not work either.
>
> Now, I have many fields (about 200) that I would want to aggregate this
> over.
> Can anyone provide suggestions or comments?
> thanks
> daben
>|||Try this:
SELECT
SUM(CASE WHEN col_a <> -999 THEN col_a END),
SUM(CASE WHEN col_b <> -999 THEN col_b END),
SUM(CASE WHEN col_c <> -999 THEN col_c END)
FROM table1 ;
You specified you want an average. Does that mean you want to treat the
-999 values as nulls or zeros for the purposes of the average? That
makes a big difference. Compare the following results:
SELECT
AVG(CASE WHEN col_a <> -999 THEN col_a ELSE 0 END),
AVG(CASE WHEN col_b <> -999 THEN col_b ELSE 0 END),
AVG(CASE WHEN col_c <> -999 THEN col_c ELSE 0 END)
FROM table1 ;
SELECT
AVG(CASE WHEN col_a <> -999 THEN col_a END),
AVG(CASE WHEN col_b <> -999 THEN col_b END),
AVG(CASE WHEN col_c <> -999 THEN col_c END)
FROM table1 ;
That last query can also be written:
SELECT
AVG(NULLIF(col_a,-999)),
AVG(NULLIF(col_b,-999)),
AVG(NULLIF(col_c,-999))
FROM table1 ;
By the way, 200 columns of numerics suggests a very poor table design.
Are you sure this is properly normalized?
Also, it's more conventional to refer to "columns" rather than "fields"
in SQL Server. Fields are usually understood to be a quite different
concept.
David Portas
SQL Server MVP
--|||What about this
I am not sure if it will create problems (I am sure one of the MVP's can
answer that)
Take a look at the code below
--testing
declare @.c float
select @.c =-999
select @.c,replace(Field_A,-999,0)
--try this
SELECT SUM(replace(Field_A,-999,0)) AS SUM_A,
SUM(replace(Field_B,-999,0)) AS SUM_B,
SUM(replace(Field_C,-999,0)) AS SUM_C
FROM Table1
WHERE [Field_Index] IN (1,2,3)
----
--
“I sense many useless updates in you... Useless updates lead to
defragmentation... Defragmentation leads to downtime...Downtime leads to
suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG an
d
DBCC DBREINDEX are the force...May the force be with you"
-- http://sqlservercode.blogspot.com/
"daben" wrote:

> Hi All
> Using SQL Server 2000.
> Given an hypothetical table (this is similar to a table that exists but
> I am omitting details such as contraints and indexes and other fields
> for simplicity) of:
> Table1
> [Field_Index] {int32, unique, not null}
> [Field_A] {float, not null, (-999)}
> [Field_B] {float, not null, (-999)}
> [Field_C] {float, not null, (-999)}
>
> Say I want to create an aggregate view such as the following (I
> actually want to use AVG but for simplicity will use SUM):
> SELECT SUM([Field_A]) AS SUM_A FROM Table1 WHERE [Field_Index] IN
> (1,2,3) AND [Field_A] <> -999;
> I am using -999 to mark "bad" data. This works fine and dandy as
> expected.
> Suppose now I have data that looks like this:
> [Field_Index] [Field_A] [Field_B] [Field_C]
> 1 1 10 100
> 2 -999 20 200
> 3 2 -999 300
> Now my query above would return SUM_A=3. Absolutely correct.
> BUT, Say I want to execute this query:
> SELECT SUM([Field_A]) AS SUM_A, SUM([Field_B]) AS SUM_B, SUM([Field_B])
> AS SUM_B FROM Table1 WHERE [Field_Index] IN (1,2,3) AND ?;
> I have placed the (?) because I am not sure what to do. In words I
> want per column the sum of all rows that <> -999 to give the following
> output in one results:
> 3 30 600
> My question is how can I do this? If I use a where clause then I can
> either "AND" or "OR" the wheres which would result in:
> "AND"
> 1 10 100
> "OR"
> Not sure since I don't have access or sql server here at work, but I
> think this would not work either.
>
> Now, I have many fields (about 200) that I would want to aggregate this
> over.
> Can anyone provide suggestions or comments?
> thanks
> daben
>|||Hi David
Thanks for the response.
The answer to your first question is; I want to treat -999 values as
Null: ie, I don't want them to influence the average.
In regards to you second statement: Well, yes I am sure I am not
properly normalized. But I think that I am to best that the structure
of the data allows. The data is Earth Science data and a table
represents measurements for a given instrument. Each instrument can
have multiple channels of data: these are the columns. Probably a
little too much info, but the data needs dictate the following type of
logic:
Project Table
Station Table (multiple stations per project)
Event Table (multiple events per station, at its simplest each
event corresponds to a unique measurement suite)
Meas Table Log (each event can be replicated)
Meas Table Data (this is where the data is as columns,
rows are depth in this case or just time)
Now the query I am specifying essentially aggregates an avg of all meas
table data per time for each station. Such as:
Select AVG(Meas_1) FROM tblMeasData (IGNORING JOINS) GROUP BY Project,
Station, Depth WHERE Project='x', Station='y', Depth='z'
I use -999 to indicate data that is essentially bad for whatever
reason. I want to avg all data that is not -999.
daben|||Hi Jerry
Am I correct in interpreting that NULLS will not be included in
averaging then based on your post and the other posts?
ie AVG(2,4,NULL) = 3
NOT 2
daben|||Daben,
Per your data:
Field_Index] [Field_A] [Field_B] [Field_C]
1 1 10 100
2 -999 20 200
3 2 -999 300
The resultset of the query would be:
1 15 200
HTH
Jerry
"daben" <dabenpb@.yahoo.com> wrote in message
news:1129063783.398342.67020@.z14g2000cwz.googlegroups.com...
> Hi Jerry
> Am I correct in interpreting that NULLS will not be included in
> averaging then based on your post and the other posts?
> ie AVG(2,4,NULL) = 3
> NOT 2
> daben
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Your personal narrative languagfe is nice and I am sure
that you can read it and compile it, but It is very
hard to debug code when you do not let us see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. If someone can guess what you
meant, that is nice, but you are every programmers nightmare. You have
no specs and do not even know the vocabulary for RDBMS.
Yeah, right (snort, snort). Why is there any bad data in the first
place? Missing data I understand and I woudl use a NULL that is
dropped out of an aggregate by definition, but why are you keeping data
that is known to be bad? This insane design will need to use CASE
expressions to clean out the dirty data you never should have in the
first place.
Also why don't you know enough SQL to use "<>" instead of "!=" for
non-equality? And why do you use proprietary square brackets? In 20+
years of SQL programming, I have seldom used FLOAT or REAL data types
-- are you doing an engineering application where that choice would
make sense?|||Um. Where to begin. Not sure why this needed your "input", since it
was absoutely nothing, but to answer a few of your statements in no
particular order:
+ I never used "!=" in any of my posts. If you are going to post a
flame get your facts right.
+ I never said a table was a file.
+ In real world data (ie data collected in the field -- ie out of the
lab, out of the house, in this case definetly not a column) you can
collect data with a sensor and it may be a stream of data with 3
channels (fields, columns, results). Well Channel 1 and 2 can be
"good" but a power spike could make channel 3 bad for that given record
(row, data for that given time). One would obviously not throw away
channels 1 and 2 of good data now would they, but if you left a NULL
there who knows why you did it. So, -999 indicates that one is a smart
monkey and knows for a fact that that piece of data is bad.
+ I believe I started with the statement "Given an hypothetical table
(this is similar to a table that exists but
I am omitting details such as contraints and indexes and other fields
for simplicity) of:" which clearly indicated that this was an academic
question should be treated as such. It seems that noone else had
problems interpretting, maybe it is in the eye of the beholder.
+ I use square brackets to indicate to me that this is a field of
abstract data (as opposed to a value -- the keyword was being
abstract). Clearly you (as well as everyone else) understood what I
meant.
+ As far as float data type. We use float because it is easy for the
scale of our date to have a large range in magnitude. And yes, I
belive once again that I did specify that this was a scientific
application. Maybe you should have spent 20+ years learning to read.
Now, some may be wondering why I even responding to you post. Good
question. The main reason is to answer in case SOMEONE else was
interested. And finally, in case anyone is still interested, it seems
like Joe likes to fly off the hook:
http://groups.google.com/group/micr...b38c28f3f3a22fd
http://groups.google.com/group/micr...b38c28f3f3a22fd