EM query strange behavior

In the Enterprise Manager query tool, a query (the date entered in the
criteria field as 31.03.05) displays as an SQL statement:
SELECT *
FROM [TABLE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
and yields the expected results.
However, changing query type to
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
results in the opposite of what is to be expected - all records where
TRANS_DATE < [the entered time] is deleted.
The TRANS_DATE field is of type DATETIME, and I cannot see that there are
any properties either in the field or the table that separates it from all
my other DATETIME fields in any other tables, where this bizarre delete
action does not occur.
I assume this has to do with some configuration of the automatic
timeconversion that happens in the tool itself, but how to change this
conversion setting so as to produce more desired delete results?
And how is it that it only happens with data from one single table'
Thanks for answering.
--
Message posted via http://www.sqlmonster.comHi
What happens when you run this:
DELETE FROM [SAME TABLE AS ABOVE]
WHERE (TRANS_DATE > '2005-03-31 00:00:00'
Regards
Mike
"Knut Bohn via SQLMonster.com" wrote:
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.sqlmonster.com
>|||Knut
I dont uderstand you.
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
You said that the table's column is defined as datetime datatype and data
was entered as 31.03.05 which is thrown the error
--insert into table1 values ('31.03.05')
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated."
"Knut Bohn via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:b10f4c5d93df49c6b3c31bee2a957db0@.SQLMonster.com...
> In the Enterprise Manager query tool, a query (the date entered in the
> criteria field as 31.03.05) displays as an SQL statement:
> SELECT *
> FROM [TABLE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> and yields the expected results.
> However, changing query type to
> DELETE FROM [SAME TABLE AS ABOVE]
> WHERE (TRANS_DATE > CONVERT(DATETIME, '2005-03-31 00:00:00', 102))
> results in the opposite of what is to be expected - all records where
> TRANS_DATE < [the entered time] is deleted.
> The TRANS_DATE field is of type DATETIME, and I cannot see that there are
> any properties either in the field or the table that separates it from all
> my other DATETIME fields in any other tables, where this bizarre delete
> action does not occur.
> I assume this has to do with some configuration of the automatic
> timeconversion that happens in the tool itself, but how to change this
> conversion setting so as to produce more desired delete results?
> And how is it that it only happens with data from one single table'
> Thanks for answering.
> --
> Message posted via http://www.sqlmonster.com|||Mike;
Same thing - deletes records with TRANS_DATE < entered value.
What's really bizzarre is that there's another datetime column in the
table, and running a delete with criteria filtered against those date
values yields the expected result.
Again, I really can't spot any differences in the respective data
properties of these two columns.
Uri;
I don't think I follow what you're getting at? The SELECT runs fine with
the date criteria entered as 31.03.05, but somehow things go off
differently with no other change thatn replacing SELECT with DELETE.
--
Message posted via http://www.sqlmonster.com|||Hi
I did some test
CREATE TABLE #Test
(
col DATETIME
)
INSERT INTO #Test VALUES ('20050331')--Proper format to insert datetime
INSERT INTO #Test VALUES ('20050401')
INSERT INTO #Test VALUES ('20050328')
DELETE FROM #Test WHERE col>'20050331'--Deletes 1 row
SELECT* FROM #Test--Everything is ok
DROP TABLE #Test
--Another repro
CREATE TABLE #Test
(
col VARCHAR(30)
)
INSERT INTO #Test VALUES ('31.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('28.03.05')--with datetime this insert will be
failed
INSERT INTO #Test VALUES ('01.04.05')--with datetime this insert will be
failed
DELETE FROM #Test WHERE col>CONVERT(DATETIME,'2005-03-31',102)--Error is
thrown
SELECT CONVERT(DATETIME,REPLACE(col,'.',''),102) FROM #Test
--2031-03-05 00:00:00.000
--2028-03-05 00:00:00.000
--2001-04-05 00:00:00.000
--That's why it deletes wrong data
DELETE FROM #Test WHERE CONVERT(DATETIME,REPLACE(col,'.',''),112)
>CONVERT(DATETIME,'2005-03-31',102)
SELECT* FROM #Test
DROP TABLE #Test
"Knut Bohn via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c446a5e8529b4bc49709d20ca6d2518d@.SQLMonster.com...
> Mike;
> Same thing - deletes records with TRANS_DATE < entered value.
> What's really bizzarre is that there's another datetime column in the
> table, and running a delete with criteria filtered against those date
> values yields the expected result.
> Again, I really can't spot any differences in the respective data
> properties of these two columns.
> Uri;
> I don't think I follow what you're getting at? The SELECT runs fine with
> the date criteria entered as 31.03.05, but somehow things go off
> differently with no other change thatn replacing SELECT with DELETE.
> --
> Message posted via http://www.sqlmonster.com|||Uri,
Thaanks for taking the time to test this. However, I don't think it
explains the criteria oddity that occurs when in the query changing from
SELECT to DELETE?
best,
knut
--
Message posted via http://www.sqlmonster.com

efficency

i have 3 tables that are linked together and i would like to run a search based on criteria in each table.
my tables are
t_location which is the physical location info such as address and phone
t_provider the contact information such as first and last names
t_source the person who refered that person

the only way i know how to do this now would be to query the location table get the locationid and add it to a dataset then do a search with each location id individually and the provider criteria but then i would not know how to get it to a dataset or something

i have seen the join statements but i dont know for sure how they work, would it be possible to use a join statement or something so that i could return the results so that i could later add them to a table.

if this is possible could you give me the basic structure for the join command and if you use a join statement do you use the select command in the same statement or do you run a seperate statement?

thank you for everything that you can giveThere are several types of joins...
inner - only return rows where there is data in all tables
outer - return all the data from the primary table and any linked data from the lookup tables

Here's an example of an inner join using the Northwind database
Say you wanted to know, for each product, what city it was shipped to, how many, and when.

SELECT Products.ProductName, Orders.ShipCity, [Order Details].Quantity, Orders.ShippedDate
FROM Products
INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders ON Orders.OrderID = [Order Details].OrderID
ORDER BY ProductName

This returns the following...

ProductName ShipCity Quantity ShippedDate
------------ ----- --- ----------------
Alice Mutton Strasbourg 30 1996-08-12 00:00:00.000
Alice Mutton Frankfurt a.M. 15 1996-08-16 00:00:00.000
Alice Mutton Albuquerque 15 1996-09-05 00:00:00.000
Alice Mutton Charleroi 40 1996-10-09 00:00:00.000
.
.
.

If theres a product in the database that hasn't shipped at all, it wont show up in the results at all.
If you want it in the list, you would do an outer join...

SELECT Products.ProductName, Orders.ShipCity, [Order Details].Quantity, Orders.ShippedDate
FROM Products
LEFT OUTER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
LEFT OUTER JOIN Orders ON Orders.OrderID = [Order Details].OrderID
ORDER BY ProductName

In this case, the LEFT OUTER JOIN operator sells SQL that you want all the data from the table on the left and null for any fields in the table on the right that dont have a related record. Like so...

ProductName ShipCity Quantity ShippedDate
------------ ----- --- ----------------
Alice Mutton Strasbourg 30 1996-08-12 00:00:00.000
Alice Mutton Frankfurt a.M. 15 1996-08-16 00:00:00.000
Alice Mutton Albuquerque 15 1996-09-05 00:00:00.000
Alice Mutton Versailles 6 1998-03-26 00:00:00.000
.
.
.
Alice Mutton Rio de Janeiro 12 NULL
Alice Mutton Boise 77 1998-05-04 00:00:00.000
Aniseed Syrup London 30 1996-08-28 00:00:00.000
.
.
.

For your case, you might want to use an outer join since you would want to know if a given location is in the database but doesn't have any provider or source.
For example...

SELECT address, phone, first, last, refereanceperson
FROM t_location
LEFT OUTER JOIN t_provider on t_location.locationid=t_t_provider.locationid
LEFT OUTER JOIN t_source ON t_provider.sourceid=t_source.sourceid

|||thank you this will help out alot|||alright here is an sql statement i am using

SELECT t_Provider.ProviderID
FROM t_Location INNER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID INNER JOIN
 t_Source ON t_Provider.ProviderID = t_Source.ProviderID
WHERE (t_Location.StateID = 25)

and currently i have 2 rows in my location table with the stateID of 25 and 2 of the providers have the same location id so it should bring up 3

providerid location id
112 147
151 147
114 149

but it brings up three and did before i added the center record too
and their values are
112
114
112

why is it bringing up 2 of the same and why does it ignore when i add another record
by the way the location id is the one with the stateID column|||I'm confussed. Can you post the table contents?|||i changed it to left outer join and it got all of the fields but it still duplicates the 112 provider id
the table contents are

t_Provider

ProviderIDDoNotCallInProcessLocationIDProviderContactProviderEmailProviderFNameProviderLName
11200147Natasha SmithJosephStephens@.DentistPro.comJosephStephens
11300148JodiKbroadbridge@.TeNT.comKurtBroadbridge
11400149Casey HuntJon_nedry@.newparadox.comJonathanNedry
11500150CoorsABush@.HHD.comAnheiserBush
11600151Jonathan NedryDSpeed@.AMD.comDoctorSpeed
11700147Bob Stephenshowdy@.me.comMEToo
11800152Mr Wallaceashd@.ld.comGuySmiley

t_Location

LocationIDLocationAddress1LocationAddress2LocationCityLocationDoctorsLocationFaxLocationONameLocationPhoneLocationZipStateID
1474832 Dixie HwySuite 105Waterford1248-313-4038 Dentistry Professionals248-313-4039 48038 25
14894949 Sunshine DriveOthello1Teeth N' Toothpaste234-343-2343 54343 7
149421 East OtisHazel Park1New Paradox Cleaning248-543-1983 48909 25
150148392 Small Back Rd.St. Lous1Happy Hour Dental243-333-2222 83939 26
151123 Fastest CPU Ln.Extreme Performance1AMD Dental239-343-3234 39393 22
152125 Tanglewood Tr.Ortonville1Mr.Dental248-627-46813495025

t_Source

SourceIDAdminIDCompetitorIDGroupIDLeadSourceIDPatientProviderIDSourceDate
15361641123/4/2004
15415131133/4/2004
1551112Dan Guzek1143/4/2004
15615131153/4/2004
15731541163/4/2004
15814131123/4/2004

New Sql Satement

SELECT t_Provider.ProviderID
FROM t_Location LEFT OUTER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID LEFT OUTER JOIN
 t_Source ON t_Provider.ProviderID = t_Source.ProviderID
WHERE (t_Location.LocationPhone <> '') AND (t_Location.StateID = 25)

New Returned Values

ProviderID
112
112
117
114
118

sorry if the tables dont come out correctly they are a bit bigger than the text field|||Ok it looks like it did this because i had two source records in the database which i would like to have anyway but would not like the providerto show up everytime there is a source for the provider is this possible?|||oh yeah and one more thing

1 to many
location to provider
provider to source
i dont know if this would help a solution at all|||I'm still confused on exactly what you want to get back.|||I would like it to only get back the providersID's that match ONLY once
but it seems to give me everything correctly except if there is more than one source
if there is more than one source for a provider id it returns the provider id more than once|||The providerIDs that match what?
Are you saying you want a list of the providerIDs that have only one source? Or do you want a list of the providerIDs along with their sources but only the first incident of each provider (i.e. provider 112 has two sources (153 and 158), so only return the 112/153 pair and filter out the 112/158 pair?|||in my search i searched for the state 25 and it came back with the provider id of 112 twice because there were two sources but it should not have done that because the source criteria isnt part of the search|||OK. Lets cover the basics of a join.
A join is a cross product.
For example, say you hve the following tables...

AlphaTable
AlphaID AlphaValue
--- ----
 1 A
 2 B
 3 C
PrimayTable
 ID Name AlphaID
--- --- ---
 1 Bill 2
 2 Steve 1
 3 John 3
 4 Bob 2

If you joined them on the AlphaID you would get...

 ID Name AlphaID AlphaValue
--- -- ---- ----
 1 Bill 2 B
 2 Steve 1 A
 3 John 3 C
 4 Bob 2 B

When you select from a join, you are basically selecting from a virtual table containing all the columns and all the joined data. So, if you select just the AlphaValue from this join, B is in there twice. Even though you are just selecting the AlphaValue, and B is only in the AlphaTable once, since you are selecting from the join, B is going to be in there twice.|||so basically then when you complete a join it is like having 3 seperate tables with all the same values where they are using the same records it makes duplicates
where the break occurs.
For instance
my table structure is as follows
location expands to providers expands to source

1 to many
1 to many
1 to many

so anything when searching on the provider table with present duplicates of the source table

so if i searched from the location table and i had 2 providers that used the same location i would have 2 duplicates and then each of those two providers had 2 sources
then i would have 4 duplicate Location ID's i believe if i did the math correctly?
| Location
/ \
/ \
/\ /\ Providers
/ \ / \Sources

if i am correct in assuming this from the facts that you have given me this is going to be a pain.

is there any way to limit the results to non duplicates in the sql command?|||You've got it exactly.
Yes, there are ways to limit the output, but these records are not "duplicates". Yes, the location will be listed multiple times if there are multiple providers at that location, but the rest of the result (i.e. the provider information) is different. The question to answer is "what information do you want?" If you are building a select that returns the location and provider (ignoring sources for the moment), and a given location has two providers, do you want to show the location and the first provider for that location, and filter out all the other providers at that location? Or do you want to show each location and a concatenated string of all the providers at that location? or something else?