distributed transaction: dbo

Showing posts with label dbo. Show all posts

Thursday, March 29, 2012

distributed partitioned view + procedure

If i have a view such as
Create view Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
And if one server say server 2 is unavailable, will the view fail to run ?
If so , how can i still let the stored proc run
and same for a stored procedure
Create proc Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
What happens in this case if server2 is unavailable ? And also a way to let
it run should any server be made unavailable> And if one server say server 2 is unavailable, will the view fail to run ?
For a query where the optimizer realizes it has to hit server 2 will fail.
> If so , how can i still let the stored proc run
Have redundancy on the servers.
Same goes for stored procedures.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OHe5POhkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> If i have a view such as
> Create view Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
>
> And if one server say server 2 is unavailable, will the view fail to run ?
> If so , how can i still let the stored proc run
> and same for a stored procedure
> Create proc Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
> What happens in this case if server2 is unavailable ? And also a way to let
> it run should any server be made unavailable
>
>

Sunday, March 25, 2012

DISTINCT QUERY

This is probably easy but I can't work it out. I have this statement

SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
FROM dbo.CIF_Departures INNER JOIN
dbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint
= dbo.CIF_PlaceReference.PlaceID
ORDER BY dbo.CIF_PlaceReference.Name

This results in a column of placenames which is OK. There are also multiple
'time of day' values against each placename however I only want to return
the one nearest to the current time. If I do this...

SELECT DISTINCT TOP 100 PERCENT
dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
FROM dbo.CIF_Departures INNER JOIN
dbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint
= dbo.CIF_PlaceReference.PlaceID
ORDER BY dbo.CIF_PlaceReference.Name

... I get multiple place names.

Any ideas?"Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> This is probably easy but I can't work it out. I have this statement
> SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
> FROM dbo.CIF_Departures INNER JOIN
> dbo.CIF_PlaceReference ON
> dbo.CIF_Departures.EndPoint >= dbo.CIF_PlaceReference.PlaceID
> ORDER BY dbo.CIF_PlaceReference.Name
> This results in a column of placenames which is OK. There are also
> multiple 'time of day' values against each placename however I only want
> to return the one nearest to the current time. If I do this...
> SELECT DISTINCT TOP 100 PERCENT
> dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
> FROM dbo.CIF_Departures INNER JOIN
> dbo.CIF_PlaceReference ON
> dbo.CIF_Departures.EndPoint>= dbo.CIF_PlaceReference.PlaceID
> ORDER BY dbo.CIF_PlaceReference.Name
> ... I get multiple place names.

Of course. If you would get disctinct names, how do you think SQL Server
would be able to find out which StartTimes you want? DISTINCT applies
to all columns.

Your requirement is not wholly clear, so I present a simple solution,
you simply get the latest starttime:

SELECT pr.Name, MAX(d.StartTime)
FROM dbo.CIF_Departures d
JOIN dbo.CIF_PlaceReference pr ON d.EndPoint = pr.PlaceID
GROUP BY pr.Name
ORDER BY pr.Name

If this does meet your requirement, please post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o Desired output from this sample.

This reduces the amount of guessing that anyone that helps you has
to do, and it also makes it simple to provide a tested solution.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94A426F0918EYazorman@.127.0.0.1...
> "Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> > This is probably easy but I can't work it out. I have this statement
> > SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
> > FROM dbo.CIF_Departures INNER JOIN
> > dbo.CIF_PlaceReference ON
> > dbo.CIF_Departures.EndPoint >= dbo.CIF_PlaceReference.PlaceID
> > ORDER BY dbo.CIF_PlaceReference.Name
> > This results in a column of placenames which is OK. There are also
> > multiple 'time of day' values against each placename however I only want
> > to return the one nearest to the current time. If I do this...
> > SELECT DISTINCT TOP 100 PERCENT
> > dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
> > FROM dbo.CIF_Departures INNER JOIN
> > dbo.CIF_PlaceReference ON
> > dbo.CIF_Departures.EndPoint>= dbo.CIF_PlaceReference.PlaceID
> > ORDER BY dbo.CIF_PlaceReference.Name
> > ... I get multiple place names.
> Of course. If you would get disctinct names, how do you think SQL Server
> would be able to find out which StartTimes you want? DISTINCT applies
> to all columns.
> Your requirement is not wholly clear, so I present a simple solution,
> you simply get the latest starttime:
> SELECT pr.Name, MAX(d.StartTime)
> FROM dbo.CIF_Departures d
> JOIN dbo.CIF_PlaceReference pr ON d.EndPoint = pr.PlaceID
> GROUP BY pr.Name
> ORDER BY pr.Name
> If this does meet your requirement, please post:
> o CREATE TABLE statements for your tables.
> o INSERT statements with sample data.
> o Desired output from this sample.
> This reduces the amount of guessing that anyone that helps you has
> to do, and it also makes it simple to provide a tested solution.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks Erland

This goes some way to helping except instead of the MAX(d.starttime), i need
the the nearest record to the current time.

CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
'10:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
'10:00:00')
CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')

If the time now is 21:59. I need a query that returns:
Here 22:00:00
There 22:00:00

If the time now is 22:01

Here Null
There Null

Regards
Richard|||"Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> This goes some way to helping except instead of the MAX(d.starttime), i
> need the the nearest record to the current time.
> CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> '10:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> '10:00:00')
> CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
> INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')
> If the time now is 21:59. I need a query that returns:
> Here 22:00:00
> There 22:00:00
> If the time now is 22:01
> Here Null
> There Null

Your definition of "nearest record" still eludes me. From the narrative,
it is not obvious why 22:00:00 should not be returned when current time
is 22:01. But the sample output makes it clear what you want.

Here is a query that almost gives the desired output. Almost, because
it is impossible to return 22:00:00 for There, as this time is not given
for There.

declare @.now datetime
select @.now = '20040306 21:59'

SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
FROM dbo.tmpPlaceReference pr
LEFT JOIN dbo.tmpDepartures d
ON d.endpoint = pr.PlaceID
AND d.starttime > convert(char(8), @.now, 108)
GROUP BY pr.Name
ORDER BY pr.Name

The convert stuff is need because there is no time data type in SQL
Server. SQL Server accepts '10:00:00' for input to a datetime value,
but that actually means '19000101 10:00:00'. Convert takes a couple
of format codes for datetime values, 108 is for time only.

Note that if @.now is 23:59 and there is a departure at midnight, that
depature will not be listed.

Finally a note about your script: it's a good idea to run it and check
before you post. I can tell that you hadn't, because the datetime(8)
gave me a syntax error.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Earliest StartTime greater than or equal to now for each Placeid:

SELECT P.name,
(SELECT MIN(starttime)
FROM tmpDepartures
WHERE endpoint = P.placeid
AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
FROM tmpPlaceReference AS P

This assumes you are only interested in times not dates. DATETIME stores
both but apparently you are using the "default" date value of 1900-01-01. If
the date is in fact significant then just replace the CONVERT expression
with CURRENT_TIMESTAMP.

--
David Portas
SQL Server MVP
--|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94A51662B5A8Yazorman@.127.0.0.1...
> "Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> > This goes some way to helping except instead of the MAX(d.starttime), i
> > need the the nearest record to the current time.
> > CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > '22:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > '22:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> > '10:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> > '10:00:00')
> > CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
> > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')
> > If the time now is 21:59. I need a query that returns:
> > Here 22:00:00
> > There 22:00:00
> > If the time now is 22:01
> > Here Null
> > There Null
> Your definition of "nearest record" still eludes me. From the narrative,
> it is not obvious why 22:00:00 should not be returned when current time
> is 22:01. But the sample output makes it clear what you want.
> Here is a query that almost gives the desired output. Almost, because
> it is impossible to return 22:00:00 for There, as this time is not given
> for There.
> declare @.now datetime
> select @.now = '20040306 21:59'
> SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> FROM dbo.tmpPlaceReference pr
> LEFT JOIN dbo.tmpDepartures d
> ON d.endpoint = pr.PlaceID
> AND d.starttime > convert(char(8), @.now, 108)
> GROUP BY pr.Name
> ORDER BY pr.Name
> The convert stuff is need because there is no time data type in SQL
> Server. SQL Server accepts '10:00:00' for input to a datetime value,
> but that actually means '19000101 10:00:00'. Convert takes a couple
> of format codes for datetime values, 108 is for time only.
> Note that if @.now is 23:59 and there is a departure at midnight, that
> depature will not be listed.
> Finally a note about your script: it's a good idea to run it and check
> before you post. I can tell that you hadn't, because the datetime(8)
> gave me a syntax error.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Despite my errors with the datetime in CREATETABLE and the fact I got the
INSERT wrong also..should have been:

INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('2','22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('1,'10:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('2','10:00:00')

... you did well to give me the answer (sorry about that).

All seems to work well .. Many thanks for your help.

Regards
Richard|||"Richard >" <richard.spare@.ntlworld.com<nospam> wrote in message
news:Sct2c.24058$gC2.23350@.newsfe5-gui.server.ntli.net...
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns94A51662B5A8Yazorman@.127.0.0.1...
> > "Richard" <richard.spare@.ntlworld.com (nospam>) writes:
> > > This goes some way to helping except instead of the MAX(d.starttime),
i
> > > need the the nearest record to the current time.
> > > > CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > > '22:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > > '22:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> > > '10:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> > > '10:00:00')
> > > CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> > > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1',
'Here')
> > > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2',
'There')
> > > > If the time now is 21:59. I need a query that returns:
> > > Here 22:00:00
> > > There 22:00:00
> > > > If the time now is 22:01
> > > > Here Null
> > > There Null
> > Your definition of "nearest record" still eludes me. From the narrative,
> > it is not obvious why 22:00:00 should not be returned when current time
> > is 22:01. But the sample output makes it clear what you want.
> > Here is a query that almost gives the desired output. Almost, because
> > it is impossible to return 22:00:00 for There, as this time is not given
> > for There.
> > declare @.now datetime
> > select @.now = '20040306 21:59'
> > SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> > FROM dbo.tmpPlaceReference pr
> > LEFT JOIN dbo.tmpDepartures d
> > ON d.endpoint = pr.PlaceID
> > AND d.starttime > convert(char(8), @.now, 108)
> > GROUP BY pr.Name
> > ORDER BY pr.Name
> > The convert stuff is need because there is no time data type in SQL
> > Server. SQL Server accepts '10:00:00' for input to a datetime value,
> > but that actually means '19000101 10:00:00'. Convert takes a couple
> > of format codes for datetime values, 108 is for time only.
> > Note that if @.now is 23:59 and there is a departure at midnight, that
> > depature will not be listed.
> > Finally a note about your script: it's a good idea to run it and check
> > before you post. I can tell that you hadn't, because the datetime(8)
> > gave me a syntax error.
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp
> Despite my errors with the datetime in CREATETABLE and the fact I got the
> INSERT wrong also..should have been:
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('2','22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('1,'10:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('2','10:00:00')
> ... you did well to give me the answer (sorry about that).
> All seems to work well .. Many thanks for your help.
> Regards
> Richard
>
Now I want to add a new variable

table tmpDepartures has a new column called 'StartPoint'. I need to refinne
the resulting rows to a specific 'StartPoint'|||> table tmpDepartures has a new column called 'StartPoint'. I need to
refinne
> the resulting rows to a specific 'StartPoint'

You mean just an extra predicate in the WHERE clause?

My solution:

SELECT P.name,
(SELECT MIN(starttime)
FROM tmpDepartures
WHERE endpoint = P.placeid
AND startpoint = /* something */
AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
FROM tmpPlaceReference AS P

Erland's solution:

SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
FROM dbo.tmpPlaceReference pr
LEFT JOIN dbo.tmpDepartures d
ON d.endpoint = pr.PlaceID
AND d.startpoint = /* something */
AND d.starttime > convert(char(8), @.now, 108)
GROUP BY pr.Name
ORDER BY pr.Name

If that doesn't answer your question, please post revised DDL, sample data
and show your required result.

--
David Portas
SQL Server MVP
--|||Thats great guys. Thanks very much.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:9uCdnWYkyPYc89fdRVn-gQ@.giganews.com...
> > table tmpDepartures has a new column called 'StartPoint'. I need to
> refinne
> > the resulting rows to a specific 'StartPoint'
> You mean just an extra predicate in the WHERE clause?
> My solution:
> SELECT P.name,
> (SELECT MIN(starttime)
> FROM tmpDepartures
> WHERE endpoint = P.placeid
> AND startpoint = /* something */
> AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
> FROM tmpPlaceReference AS P
> Erland's solution:
> SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> FROM dbo.tmpPlaceReference pr
> LEFT JOIN dbo.tmpDepartures d
> ON d.endpoint = pr.PlaceID
> AND d.startpoint = /* something */
> AND d.starttime > convert(char(8), @.now, 108)
> GROUP BY pr.Name
> ORDER BY pr.Name
> If that doesn't answer your question, please post revised DDL, sample data
> and show your required result.
> --
> David Portas
> SQL Server MVP
> --

Distinct problems.

OK heres what I have so far:

SELECT TOP (100) PERCENT dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,
dbo.EVENTS.DEVID, CASE WHEN (dbo.EVENTS.DEVID = '23' OR
dbo.EVENTS.DEVID = '24' OR
dbo.EVENTS.DEVID = '25' OR
dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUS
FROM dbo.READER INNER JOIN
dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
dbo.UDFEMP INNER JOIN
dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)
ORDER BY dbo.EVENTS.EVENTIME

Works great, however, I need to display only one instance of each employee and that instance should be the latest instance found.

So instead of several differant emplyees with several different "IN" and "OUT" times:
EvenTime FirstName Last Name Ext ReaderDesc DevID Status

12/28/2006 7:22:01 AM Rechie Michael 75766 SE Glass Door 21 IN

12/28/2006 7:10:01 AM Rechie Michael 75766 SE Glass Door 21 OUT

12/28/2006 7:01:01 AM Rechie Michael 75766 SE Glass Door 21 IN

I just want the latest record for any given employee regardless of wether its status is "IN" or "OUT":

EvenTime FirstName Last Name Ext ReaderDesc DevID Status

12/28/2006 7:22:01 AM Rechie Michael 75766 SE Glass Door 21 IN

EVENTS.EMPID would be what I would want to be DISTINCT but I dont know how to use it in the code above and I dont know how Id specify DISTINCT based on latest time found.

Any help/direction would be greatly appreciated.

TIA,
Stue

You can do a group by on the firstname, lastname, and do a MAX(eventtime) and either a MIN or MAX on the status.|||

Thanks ndinakar,

but I am still having problems:

SELECT TOP (100) PERCENT dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,
dbo.EVENTS.DEVID, MAX(dbo.EVENTS.EMPID) AS EMPI, CASE WHEN (dbo.EVENTS.DEVID = '23' OR
dbo.EVENTS.DEVID = '24' OR
dbo.EVENTS.DEVID = '25' OR
dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUS
FROM dbo.READER INNER JOIN
dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
dbo.UDFEMP INNER JOIN
dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)
GROUP BY dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVID

I am using MAX(dbo.EVENTS.EMPID) instead of LASTNAME because we have 2 employees with the same last name and EMPID is unique.

With the above I am still getting the same results... Im totally confused.
Any ideas on what I am doing wrong?

TIA,
Stue

|||PS. using "MAX(eventime)" in place of MAX(dbo.EVENTS.EMPID) gave me the same results as well. Sad

|||Remove the EVENTIME from the GROUP BY list and use a MAX(EVENTIME) in the SELECT.|||

ndinakar:
Remove the EVENTIME from the GROUP BY list and use a MAX(EVENTIME) in the SELECT.

SELECT TOP (100) PERCENT dbo.EVENTS.EMPID, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,
dbo.EVENTS.DEVID, MAX(dbo.EVENTS.EVENTIME) AS TIME, CASE WHEN (dbo.EVENTS.DEVID = '23' OR
dbo.EVENTS.DEVID = '24' OR
dbo.EVENTS.DEVID = '25' OR
dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUS
FROM dbo.READER INNER JOIN
dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
dbo.UDFEMP INNER JOIN
dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID
WHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)
GROUP BY dbo.EVENTS.EMPID, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVID

Thanks Almost there. I still get dupe Employees but the difference is it just lists one instance per door entered. So if the employee enters one door A 10 times and door B 5 it list 2 records for that one employee 1 for each door. I am trying to cut it down to just one (the latest) instance regardless of door.

Thanks ndinakar,

Stue

|||Remove that column too from the GROUP BY and use a MAX or MIN on it in the SELECT.|||

OK here is what I did.(And I'm not impressed.)

View A:
SELECT TOP (100) PERCENT dbo.EVENTS.EVENTIME, dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC,
dbo.EVENTS.DEVID, CASE WHEN (dbo.EVENTS.DEVID = '23' OR
dbo.EVENTS.DEVID = '24' OR
dbo.EVENTS.DEVID = '25' OR
dbo.EVENTS.DEVID = '26') THEN 'OUT' ELSE 'IN' END AS STATUS, dbo.EVENTS.EMPID, dbo.DEPT.NAME
FROM dbo.READER INNER JOIN
dbo.EVENTS ON dbo.READER.READERID = dbo.EVENTS.DEVID INNER JOIN
dbo.UDFEMP INNER JOIN
dbo.EMP ON dbo.UDFEMP.ID = dbo.EMP.ID ON dbo.EVENTS.EMPID = dbo.EMP.ID INNER JOIN
dbo.DEPT ON dbo.UDFEMP.DEPT = dbo.DEPT.ID
WHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) = CONVERT(CHAR, GETDATE(), 101)) AND (dbo.EVENTS.EMPID <> 0)
ORDER BY dbo.EVENTS.EVENTIME

View B:
SELECT TOP (100) PERCENT InOut.EMPID, InOut.EVENTIME, InOut.LASTNAME, InOut.FIRSTNAME, InOut.EXT, InOut.READERDESC, InOut.DEVID, InOut.STATUS,
InOut.NAME AS MName
FROM dbo.[VW-InOut] AS InOut INNER JOIN
(SELECT MAX(EVENTIME) AS maxET, EMPID
FROM dbo.EVENTS
GROUP BY EMPID) AS maxresults ON InOut.EMPID = maxresults.EMPID AND InOut.EVENTIME = maxresults.maxET
ORDER BY InOut.EVENTIME DESC

I couldnt figure out the double MAX that you suggested ndinakar so thats why I have the 2 views.

Problem is it works.... but works sloooooooooooooow... Im assuming due to the nested query.
Any ideas on how I can speed it up?

Any help would be much appreciated,
Thanks,

Stue

|||

How about this:

SELECT MAX(dbo.EVENTS.EVENTIME), dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVID,CASEWHEN (dbo.EVENTS.DEVID ='23'OR dbo.EVENTS.DEVID ='24'OR dbo.EVENTS.DEVID ='25'OR dbo.EVENTS.DEVID ='26')THEN'OUT'ELSE'IN'END AS STATUSFROM dbo.READERINNERJOIN dbo.EVENTSON dbo.READER.READERID = dbo.EVENTS.DEVIDINNERJOIN dbo.UDFEMPINNERJOIN dbo.EMPON dbo.UDFEMP.ID = dbo.EMP.IDON dbo.EVENTS.EMPID = dbo.EMP.IDWHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) =CONVERT(CHAR,GETDATE(), 101))AND (dbo.EVENTS.EMPID <> 0)GROUP BY dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME, dbo.UDFEMP.EXT, dbo.READER.READERDESC, dbo.EVENTS.DEVIDORDER BY dbo.EVENTS.EVENTIME

|||

:( nope.

I get the error:

"Column "dbo.EVENTS.EVENTIME" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP By clause."

So I drop dbo.EVENTS.EVENTIME into the GROUP BY line and execute and it runs but with dupe names and a total of 641 records (should only be about 50 returned). I cant see what, if anything, the code did here.

I also tried getting rid of the ORDER BY dbo.EVENTS.EVENTIME line while making no other changes and it still returns dupes and a total of 207 rows (no dupe READERDESC data per employee, but still dupe employee records)

Thanks again ndinakar,
Stue

|||Can you post some sample data (15-20 rows)?|||

Eventime in the GROUP BY delete ORDER BY line:

Time LNAME FNAME EXT READERDESC DEVID STATUS

1/8/2007 10:27:19 AM BADGE TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:12:30 AM BADGE #2 TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:07:55 AM BADGE (VickyT) TEMPORARY 79777 Front Door 22 IN
1/8/2007 10:16:07 AM BADGE (VickyT) TEMPORARY 79777 IT Room 15 IN
1/8/2007 10:01:33 AM BADGE (VickyT) TEMPORARY 79777 NW Glass Dr 14 IN
1/8/2007 9:10:01 AM BADGE (VickyT) TEMPORARY 79777 SE Glass Door 21 IN
1/8/2007 10:05:39 AM BADGE (VickyT) TEMPORARY 79777 SE Glass Door 21 IN
1/8/2007 11:50:57 AM BADGE (VickyT) TEMPORARY 79777 SE Glass Door 21 IN
1/8/2007 11:48:43 AM BADGE (VickyT) TEMPORARY 79777 SW Glass Dr 1 IN
1/8/2007 12:35:22 PM BADGE (VickyT) TEMPORARY 79777 SW Glass Dr 1 IN
1/8/2007 8:39:16 AM Belt Michael 75721 Front Door 22 IN
1/8/2007 12:43:00 PM Belt Michael 75721 Front Door 22 IN
1/8/2007 10:03:39 AM Belt Michael 75721 NE Glass Doors 5 IN
1/8/2007 10:46:40 AM Belt Michael 75721 NE Glass Doors 5 IN
1/8/2007 8:39:44 AM Belt Michael 75721 NW Glass Dr 14 IN
1/8/2007 9:02:10 AM Belt Michael 75721 NW Glass Dr 14 IN
1/8/2007 10:47:23 AM Belt Michael 75721 NW Glass Dr 14 IN
1/8/2007 12:36:26 PM Belt Michael 75721 Rdr 20 out Front Door 24 OUT
1/8/2007 12:43:07 PM Belt Michael 75721 SE Glass Door 21 IN
1/8/2007 8:34:11 AM Berm Ricardo 75750 NE Glass Doors 5 IN
1/8/2007 9:43:01 AM Berm Ricardo 75750 NE Glass Doors 5 IN
1/8/2007 10:52:38 AM Berm Ricardo 75750 NE Glass Doors 5 IN
1/8/2007 12:08:49 PM Berm Ricardo 75750 NE Glass Doors 5 IN

Delete EVENTIME reference from GROUP BY and take out ORDER BY as well:

Time LNAME FNAME EXT READERDESC DEVID STATUS

1/8/2007 10:27:19 AM BADGE TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:12:30 AM BADGE #2 TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:07:55 AM BADGE (VickyT) TEMPORARY 79777 Front Door 22 IN
1/8/2007 10:16:07 AM BADGE (VickyT) TEMPORARY 79777 IT Room 15 IN
1/8/2007 10:01:33 AM BADGE (VickyT) TEMPORARY 79777 NW Glass Dr 14 IN
1/8/2007 11:50:57 AM BADGE (VickyT) TEMPORARY 79777 SE Glass Door 21 IN
1/8/2007 12:35:22 PM BADGE (VickyT) TEMPORARY 79777 SW Glass Dr 1 IN
1/8/2007 12:43:00 PM Belt Michael 75721 Front Door 22 IN
1/8/2007 10:46:40 AM Belt Michael 75721 NE Glass Doors 5 IN
1/8/2007 10:47:23 AM Belt Michael 75721 NW Glass Dr 14 IN
1/8/2007 12:36:26 PM Belt Michael 75721 Rdr 20 out Front Door 24 OUT
1/8/2007 12:43:07 PM Belt Michael 75721 SE Glass Door 21 IN
1/8/2007 12:08:49 PM Berm Ricardo 75750 NE Glass Doors 5 IN
1/8/2007 12:14:40 PM Berm Ricardo 75750 NW Glass Dr 14 IN
1/8/2007 12:32:08 PM Berm Ricardo 75750 Rdr 20 out Front Door 24 OUT
1/8/2007 12:31:29 PM Berm Ricardo 75750 Rdr 7 out North Stairwell 25 OUT
1/8/2007 8:23:57 AM Berm Ricardo 75750 SE Glass Door 21 IN
1/8/2007 9:18:10 AM Boley Rick 75723 Front Door 22 IN
1/8/2007 11:53:19 AM Boley Rick 75723 IT Room 15 IN
1/8/2007 11:54:34 AM Boley Rick 75723 Rdr 20 out Front Door 24 OUT
1/8/2007 9:18:37 AM Boley Rick 75723 SW Glass Dr 1 IN

Thanks again,
Stue

|||And what is the result you expect from your query based on your second set of data?|||

The 1st and second sets were results I got while trying to implement the suggestion you had given.

Ultimately I would like to get from my view (see second set above as reference):
1/8/2007 10:27:19 AM BADGE TEMPORARY NULL Server Rm 19 IN
1/8/2007 9:12:30 AM BADGE #2 TEMPORARY NULL Server Rm 19 IN
1/8/2007 12:35:22 PM BADGE (VickyT) TEMPORARY 79777 SW Glass Dr 1 IN
1/8/2007 12:43:07 PM Belt Michael 75721 SE Glass Door 21 IN
1/8/2007 12:32:08 PM Berm Ricardo 75750 Rdr 20 out Front Door 24 OUT
1/8/2007 11:54:34 AM Boley Rick 75723 Rdr 20 out Front Door 24 OUT

So just the latest date/time instance for ea employee, regardless of where, status, etc... is displayed. So if there are 50 employees that day there should only be a total of 50 records returned.

Thanks,
Stue

|||

Looks like you only need to group by first and last names and put min or max around the other columns.

sample:

SELECT MAX(dbo.EVENTS.EVENTIME), dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAME,min(dbo.UDFEMP.EXT),min(dbo.READER.READERDESC), min(dbo.EVENTS.DEVID),CASEWHEN (Min(dbo.EVENTS.DEVID) ='23'ORmin(dbo.EVENTS.DEVID) ='24'ORmin(dbo.EVENTS.DEVID) ='25'ORmin(dbo.EVENTS.DEVID) ='26')THEN'OUT'ELSE'IN'END AS STATUSFROM dbo.READERINNERJOIN dbo.EVENTSON dbo.READER.READERID = dbo.EVENTS.DEVIDINNERJOIN dbo.UDFEMPINNERJOIN dbo.EMPON dbo.UDFEMP.ID = dbo.EMP.IDON dbo.EVENTS.EMPID = dbo.EMP.IDWHERE (CONVERT(CHAR, dbo.EVENTS.EVENTIME, 101) =CONVERT(CHAR,GETDATE(), 101))AND (dbo.EVENTS.EMPID <> 0)GROUP BY dbo.EMP.LASTNAME, dbo.EMP.FIRSTNAMEORDER BY dbo.EVENTS.EVENTIME

Wednesday, March 21, 2012

distinct

How do i make this return only distinct records...
SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
dbo.tbl_purchase_orders.date_expected
FROM dbo.tbl_purchase_order_lines INNER JOIN
dbo.tbl_purchase_orders ON
dbo.tbl_purchase_order_lines.purchase_order_id = dbo.tbl_purchase_orders.id
WHERE (dbo.tbl_purchase_orders.delivery_complete = 0) AND
(dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
ORDER BY dbo.tbl_purchase_order_lines.stock_code,
dbo.tbl_purchase_orders.date_expected
help appreciated!!!!
chrisSELECT distinct
ol.stock_code,
ol.qty_ordered AS stock_ordered,
l.date_expected
FROM dbo.tbl_purchase_order_lines as ol INNER JOIN dbo.tbl_purchase_orders as l
ON ol.purchase_order_id =3D l.id
WHERE (l.delivery_complete =3D 0) AND
(l.date_expected > GETDATE() - 1)
ORDER BY stock_code, date_expected
ie just add the distinct, the top 100 percent looks effectively =redundant anyway, also corelation names (table aliases) make it easier =to read and less typing
Mike John
"Chris Dangerfield" <webmaster@.planetmicro.co.uk> wrote in message =news:p1iNa.219$CO4.17@.news-binary.blueyonder.co.uk...
> How do i make this return only distinct records...
> > > > SELECT TOP 100 PERCENT dbo.tbl_purchase_order_lines.stock_code,
> dbo.tbl_purchase_order_lines.qty_ordered AS stock_ordered,
> dbo.tbl_purchase_orders.date_expected
> FROM dbo.tbl_purchase_order_lines INNER JOIN
> dbo.tbl_purchase_orders ON
> dbo.tbl_purchase_order_lines.purchase_order_id =3D =dbo.tbl_purchase_orders.id
> WHERE (dbo.tbl_purchase_orders.delivery_complete =3D 0) AND
> (dbo.tbl_purchase_orders.date_expected > GETDATE() - 1)
> ORDER BY dbo.tbl_purchase_order_lines.stock_code,
> dbo.tbl_purchase_orders.date_expected
> > > help appreciated!!!!
> chris
> >=20

Friday, February 24, 2012

Display subtotals and grand total

Hi,

I have a table:

CREATE TABLE [dbo].[TBL_REPORT1](

[Source] [varchar](3) NULL,

[Contract No] [varchar](15) NOT NULL,

[Business Group] [varchar](4) NULL,

[Customer Name] [varchar](50) NULL,

[Equipment Description] [varchar](20) NULL,

[Lease Type] [varchar](2) NULL,

[Term] [int] NULL,

[Booking Date] [datetime] NULL,

[# of Assets] [int] NULL,

[Equipment Cost] [money] NULL,

[Restructured] [varchar](3) NOT NULL

)

Sample Data

INSERT INTO [TBL_REPORT1] VALUES('SFS','319-0010146-001','SEF','NorthBay Healthcare Group','SBT Performance Cont','LP',132,'Apr 4 2007 12:00:00:000AM',1,2612000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015625-023','SEF','Black Diamond Properties, Inc.','Kubota L48 TLB Tract','OL',60,'Apr 3 2007 12:00:00:000AM',1,36000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015648-007','SEF','The River Wilderness Club, Inc.','Honda Salsco Greens','OL',48,'Apr 5 2007 12:00:00:000AM',1,11401.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0013599-020','VEN','THYSSENKRUPP BUDD COMPANY','COMPUTER GEAR','CS',30,'Apr 5 2007 12:00:00:000AM',1,232965.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0016965-002','VEN','GREEN OAK TOWNSHIP','COMPUTER GEAR','CS',33,'Apr 5 2007 12:00:00:000AM',1,56789.9100,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0052401-001','VEN','Zircon Corp.','INJECTION MOLDING MC','CS',70,'Apr 11 2007 12:00:00:000AM',1,74380.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','766-0001804-007','IGP','Helena Chemical Company','1800 GAL','TL',36,'Apr 18 2007 12:00:00:000AM',17,292147.7000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-001','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,276928.4500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-002','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,377415.3500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002053-001','CBF','VIH Helicopters USA, Inc.','Sikorsky S-61N','CS',84,'Apr 6 2007 12:00:00:000AM',1,4612500.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0014680-024','CPM','SUN MICROSYSTEMS, INC.','trade receivable','CS',2,'Apr 5 2007 12:00:00:000AM',1,20177632.2300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0015956-014','CPM','Autozone Inc.','trade receivable','CS',11,'Apr 3 2007 12:00:00:000AM',1,2128173.3600,'No')

I want to display subtotals - sum of [Equipment Cost] for each [Business Group] and also the grand total.

Sample Output:

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Count 3 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Count 2

I tried with CUBE and ROLLUP but then with multiple fields it was not giving me the right output. Can anyone help. Thanks.

Posted above was the sample count subtotal generated in Excel. I am interested in getting only the subtotal count/sum and the grand total. Labels like CBF Count, CPM Count and not required.

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Total 5266844 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Total 22305806 SFS 766-0001804-007 IGP Helena Chemical Company 1800 GAL TL 36 00:00.0 17 292147.7 No IGP Total 292147.7 Grand Total 27864797

|||

If using SQL Server 2005, have you looked at COMPUTE?

http://msdn2.microsoft.com/en-us/library/ms181708.aspx

Dan

|||Thanks.|||I hope it does what you need!

Sunday, February 19, 2012

display on duplicate records

I have the following query:
SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
FROM dbo.cbt_BillingAddress
WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
ORDER BY ProductCode, BillingCustID, BillingPeriod
which returns the following columns:
BillingPeriod CustomerID ProductCode BillingCustID
-- -- -- --
200502 205022338 APX 79110
200503 205022338 APX 79110
200504 205022338 BRW 79110
200505 205022338 BRW 79110
200506 205027355 APX 79110
200506 205022338 APX 79110
200507 205027355 BRW 79110
200507 205022338 BRW 79110
As you can see from the result, there are duplicates under the billingperiod
column for the same billingcustID. Essentially, there should only ever be on
e
CustomerID associated with the same ProductCode and BillingCustID and
BillingPeriod.
How can I rewrite my query to only display only the duplicates?
Thanks for your help in advance.Try this...
SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
FROM dbo.cbt_BillingAddress A
WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
AND EXISTS (
SELECT 1 FROM dbo.cbt_BillingAddress B
WHERE A.BillingCustID = B.BillingCustID
AND A.ProductCode = B.ProductCode
AND A.BillingPeriod = B.BillingPeriod
AND A.CustomerID <> B.CustomerID)
ORDER BY ProductCode, BillingCustID, BillingPeriod
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D1DDD109-45A5-479C-B784-EE86856B14B0@.microsoft.com...
> I have the following query:
> SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
> FROM dbo.cbt_BillingAddress
> WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
> ORDER BY ProductCode, BillingCustID, BillingPeriod
> which returns the following columns:
> BillingPeriod CustomerID ProductCode BillingCustID
> -- -- -- --
> 200502 205022338 APX 79110
> 200503 205022338 APX 79110
> 200504 205022338 BRW 79110
> 200505 205022338 BRW 79110
> 200506 205027355 APX 79110
> 200506 205022338 APX 79110
> 200507 205027355 BRW 79110
> 200507 205022338 BRW 79110
> As you can see from the result, there are duplicates under the
billingperiod
> column for the same billingcustID. Essentially, there should only ever be
one
> CustomerID associated with the same ProductCode and BillingCustID and
> BillingPeriod.
> How can I rewrite my query to only display only the duplicates?
> Thanks for your help in advance.|||Awesome. Thanks for your help. One last question: what is the significance o
f
the statement:
SELECT 1...
What does this do and where can I get more info on its usage. Thanks again.
"Jim Underwood" wrote:

> Try this...
> SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
> FROM dbo.cbt_BillingAddress A
> WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
> AND EXISTS (
> SELECT 1 FROM dbo.cbt_BillingAddress B
> WHERE A.BillingCustID = B.BillingCustID
> AND A.ProductCode = B.ProductCode
> AND A.BillingPeriod = B.BillingPeriod
> AND A.CustomerID <> B.CustomerID)
> ORDER BY ProductCode, BillingCustID, BillingPeriod
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:D1DDD109-45A5-479C-B784-EE86856B14B0@.microsoft.com...
> billingperiod
> one
>
>|||Honestly, I am not sure if this serves a purpose in SQL Server, but I use it
out of habit from my Oracle 7/8 experience.
where exists (Select 1 from table1 where column1 = 'MyData')
is functionally the same as
where exists (Select column1 from table1 where column1 = 'MyData')
It simply verifies that a row exists in either case. I use the literal 1
for performance reasons.
In Oracle 7/8 (and likely 9 and 10) selecting a literal uses less memory
than selecting a value from a table. You could select a character ('x' for
example) but I was always told using a number was more efficient than a
character. Essentially, you don't need to retrieve a data value from disk
or memory, save what you use in your where clause. The SQL engine can
evaluate the where clause without returning any data in the process.
SQL server may very well ignore the select columns in an exist clause, I
really don't know. Maybe someone with more experience can validate or
correct me on this point.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:0155F28A-3A76-4159-8157-EFC6E2A45C0E@.microsoft.com...
> Awesome. Thanks for your help. One last question: what is the significance
of
> the statement:
> SELECT 1...
> What does this do and where can I get more info on its usage. Thanks
again.
>
> "Jim Underwood" wrote:
>
be|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uCH2N0pJGHA.984@.tk2msftngp13.phx.gbl...
> Honestly, I am not sure if this serves a purpose in SQL Server, but I use
> it
> out of habit from my Oracle 7/8 experience.
> where exists (Select 1 from table1 where column1 = 'MyData')
> is functionally the same as
> where exists (Select column1 from table1 where column1 = 'MyData')
> It simply verifies that a row exists in either case. I use the literal 1
> for performance reasons.
> In Oracle 7/8 (and likely 9 and 10) selecting a literal uses less memory
> than selecting a value from a table. You could select a character ('x'
> for
> example) but I was always told using a number was more efficient than a
> character. Essentially, you don't need to retrieve a data value from disk
> or memory, save what you use in your where clause. The SQL engine can
> evaluate the where clause without returning any data in the process.
> SQL server may very well ignore the select columns in an exist clause, I
> really don't know. Maybe someone with more experience can validate or
> correct me on this point.
I'm sure that I don't have more experience but from what I've seen in this
newsgroup,
select *
is the norm in Exists clauses.