|
Every SQL based engine has slightly different syntax. On the DDL side the syntax differences are even more varied.
You will also find many differences on the syntax for JOINS between TSQL and Access queries.
SQL should expand to 'Suggested Query Language'.
|
|
|
|
|
True, but I looked the syntax up in msdn (even copy/paste the damn code) and it didn't work 
A student knows little about a lot.
A professor knows a lot about little.
I know everything about nothing.
|
|
|
|
|
Try:
http://support.microsoft.com/default.aspx?scid=kb;en-us;180841
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q275561
The syntax is a bit more wordy but, serves its purpose.
|
|
|
|
|
Unfortunately those SQL statements don't work. I guess that I have to test my DBase on a complete DBMS instead of access 2k..
Thanks for your help 
A student knows little about a lot.
A professor knows a lot about little.
I know everything about nothing.
|
|
|
|
|
|
Thanks,
I solved the problem. I did a minimum installation. I figured that the MSDE (Microsoft Database Engine) was needed for the complexe queries.
Everything works fine since I fully installed MS Acces 2k.
A student knows little about a lot.
A professor knows a lot about little.
I know everything about nothing.
|
|
|
|
|
hi,
why after execution of 'begin transaction' the @@trancount=2(original value is 0)?
thanks!
|
|
|
|
|
When ever a transaction is initiated the @@trancount increases by 1. when a rollback is issued (Even with in nested transaction) the value reset to 0. i am not sure what happens during commit trans (may be it decremented by 1)
Cheers,
Venkatraman Kalyanam
Bangalore - India
Reality bites: I am reality
|
|
|
|
|
yes,commit trans decremented by 1,but after this command ,the @@trancount is
1,not 0,the problem is what makes the strange,the @@trancount incremented by 2?
|
|
|
|
|
william_zhou wrote:
the problem is what makes the strange,the @@trancount incremented by 2?
Do you work in Implicit Transactions mode which may be causing this effect ?
|
|
|
|
|
no,i assure i had not used this mode
|
|
|
|
|
oh,i'm sorry,
i did work in Implicit Transactions mode,but this mode was not setted by me,
now how can i cancel this mode
|
|
|
|
|
SET IMPLICIT_TRANSACTIONS OFF
good luck
|
|
|
|
|
thanks,
but a new problem appears,when i call this proc in my embeded c sql,the return
value is -266,which is the same problem @@trancount,i am nearly insane for this bad luck,why?
|
|
|
|
|
i set implicit_transactions off at the beginning of the proc,such as:
'alter proc sp_myproc @i_input
as
set implicit_transactions
declare @tmp int
...
'
but when i debug it,i find behind the 'set implicit_transactions' is a strange
message:'set implicit_transactions on',now who active this event,why can it happen?
|
|
|
|
|
use SET IMPLICIT_TRANSACTIONS OFF before beginning any transactions not inside a transaction.
then start the transaction explicitly using BEGIN TRAN
|
|
|
|
|
I have a Linux server, with Samba, running MySQL.
I need to access this from a Windows 2000 machine.
What is the best way to do this with MFC/C++ ?
|
|
|
|
|
I thought MySQL had a c++ client class for this (even a 'c' one would do) - in which case you would link it into your code, make a connection with the class 'across the network' to the MySQL server, presto !!!!
the other option would be to see if there's an ODBC driver for MySQL on Linux
'G'
|
|
|
|
|
I send @DD(a date) to the procedure and I want to get only the TesterIDs that have a ScheduleStamp(a date)on or before @DD. Each person(designated by TesterID) can have multiple ScheduleStamps, I only want to see the person if their ScheduleStamps exists on or before @DD, but I don't want to see them if they have a ScheduleStamp before and after @DD.
I know it may sound confusing, but thank you to anyone who takes on this challenge.
CREATE PROCEDURE usp_tp_GetPKCall
@SexID char,
@RaceID char,
@LBirth datetime,
@UBirth datetime,
@SiteID varchar(5),
@DD datetime
AS
if @SexID = '*' and @RaceID <> '*' and @SiteID <> '90000'
SELECT tGroup.ScheduleStamp, tResultQue.TesterID, tTester.LastName,tTester.FirstName, tTester.WorkNo, tTester.WorkExtension, tTester.HomeNo,tTester.SexID, tTester.Birth, tTester.RaceID, tTester.SiteID
FROM tGroup INNER JOIN
tResultQue ON tGroup.GroupID = tResultQue.GroupID INNER JOIN
tTester ON tResultQue.TesterID = tTester.TesterID
where tTester.RaceID = @RaceID and tTester.Birth >= @LBirth and tTester.Birth <= @UBirth and tTester.SiteID = @SiteID and tGroup.ScheduleStamp >= @DD
GO
|
|
|
|
|
(1) Your logic does not make sense:
- I only want to see the person if their ScheduleStamps exists on or before @DD
- I don't want to see them if they have a ScheduleStamp before and after @DD
I think you only want people who do not have a ScheduleStamp prior to @DD
(2) Find the people who have a schedule stamp prior to @DD
<br />
SELECT tTester.TesterID<br />
FROM tTester<br />
INNER JOIN tResultQue<br />
ON (tTester.TesterID = tResultQue.TesterID)<br />
INNER JOIN tGroup<br />
ON (tResultQue.GroupID = tGroup.GroupID)<br />
WHERE tGroup.ScheduleStamp < @DD<br />
GROUP BY tTester.TesterID<br />
(3) Add it to the WHERE clause
<br />
AND tTester.TesterID NOT IN <br />
(SELECT tTester.TesterID<br />
FROM tTester<br />
INNER JOIN tResultQue<br />
ON (tTester.TesterID = tResultQue.TesterID)<br />
INNER JOIN tGroup<br />
ON (tResultQue.GroupID = tGroup.GroupID)<br />
WHERE tGroup.ScheduleStamp < @DD<br />
GROUP BY tTester.TesterID)<br />
|
|
|
|
|
That's half of it. The problem is, each person can be listed in the table multiple times because they may have multiple ScheduleStamps. I do want to see the person if ScheduleStamp <= @DD, but if they also have one > @DD, I don't want to see them at all.
Thanks again
|
|
|
|
|
That's half of it. The problem is, each person can be listed in the table multiple times because they may have multiple ScheduleStamps. I do want to see the person if ScheduleStamp <= @DD, but if they also have one > @DD, I don't want to see them at all.
Thanks again
P.S. using SQL Server
|
|
|
|
|
(1) Now I think I understand your logic:
Give me everyone who has any ScheduleStamp <= @DD and does not have have a ScheduleStamp > @DD. Your original query is just not eliminating invalid Testers (ScheduleStamp > @DD).
(2) Find the people who have a schedule stamp after to @DD.
SELECT tTester.TesterID
FROM tTester
INNER JOIN tResultQue
ON (tTester.TesterID = tResultQue.TesterID)
INNER JOIN tGroup
ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID
(3) Add it to the WHERE clause.
CREATE PROCEDURE usp_tp_GetPKCall
(
@SexID char,
@RaceID char,
@LBirth datetime,
@UBirth datetime,
@SiteID varchar(5),
@DD datetime
)
AS
IF @SexID = '*' AND
@RaceID <> '*' AND
@SiteID <> '90000'
BEGIN
SELECT
tGroup.ScheduleStamp,
tResultQue.TesterID,
tTester.LastName,
tTester.FirstName,
tTester.WorkNo,
tTester.WorkExtension,
tTester.HomeNo,
tTester.SexID,
tTester.Birth,
tTester.RaceID,
tTester.SiteID
FROM tGroup
INNER JOIN tResultQue
ON (tGroup.GroupID = tResultQue.GroupID)
INNER JOIN tTester
ON (tResultQue.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND
tTester.Birth >= @LBirth AND
tTester.Birth <= @UBirth AND
tTester.SiteID = @SiteID AND
tGroup.ScheduleStamp <= @DD AND
tTester.TesterID NOT IN
(SELECT tTester.TesterID
FROM tTester
INNER JOIN tResultQue
ON (tTester.TesterID = tResultQue.TesterID)
INNER JOIN tGroup
ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
END
GO
|
|
|
|
|
Thank You! Works great! However, there is a follow up here. In the case that a person has more than one ScheduleStamp <= @DD and not > @DD, we will see them listed multiple times. How can I supress the multiples and only see the most recent ScheduleStamp <= @DD for each person?
Thank you for your continued support
|
|
|
|
|
I would normally break a query that is getting this large. It really depends upon the size of your data set and your indexes. Only testing can tell.
Given the current query you can simply add a correlated sub query to the WHERE Clause.
AND tGroup.ScheduleStamp =
(SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester
INNER JOIN tResultQue cor_tResultQue
ON (cor_tTester.TesterID = cor_tResultQue.TesterID)
INNER JOIN tGroup cor_tGroup
ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TestID)
I think I would use a memory temp table to solve the total issue (syntax not tested).
DECLARE @tbl TABLE
(
TesterID INTEGER NOT NULL,
ScheduleStamp DATETIME NOT NULL
)
--*******************************************************
-- find the correct testers and their last ScheduleStamp
--*******************************************************
INSERT INTO @tbl
(
TesterID,
ScheduleStamp
)
SELECT
tTester.TesterID,
MAX(tGroup.ScheduleStamp)
FROM tGroup
INNER JOIN tResultQue
ON (tGroup.GroupID = tResultQue.GroupID)
INNER JOIN tTester
ON (tResultQue.TesterID = tTester.TesterID)
GROUP BY TesterID
HAVING MAX(tGroup.ScheduleStamp) <= @DD
--***************************
-- final select for results
--***************************
SELECT
@tbl.ScheduleStamp,
@tbl.TesterID,
tTester.LastName,
tTester.FirstName,
tTester.WorkNo,
tTester.WorkExtension,
tTester.HomeNo,
tTester.SexID,
tTester.Birth,
tTester.RaceID,
tTester.SiteID
FROM @tbl
INNER JOIN tTester
ON (@tbl.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND
tTester.Birth >= @LBirth AND
tTester.Birth <= @UBirth AND
tTester.SiteID = @SiteID AND
These two queries can even be put into one if you want. I find it easier to maintain this way. Testing for speed might change my mind.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
