January 16, 2008 at 9:34 am
I am trying to create an application that will run on both SQL2000 and 2005. I have a view that runs fine in 2005, but that will not run in 2000 until I remove the group by clause. I don't see anything wrong syntactically that would prevent this from running. Any thoughts?
Your ideas are appreciated.
SELECT TOP 100 PERCENT dbo.CheckingAccountTransaction.CheckingAccountTransactionId, dbo.CheckingAccountTransaction.CheckingAccountId,
dbo.CheckingAccountTransaction.TransactionDate, dbo.CheckingAccountTransaction.CheckNumber,
dbo.CheckingAccountTransaction.TransactionStatus, dbo.CheckingAccountTransaction.ReviewDate, dbo.CheckingAccountTransaction.ReviewStatus,
dbo.CheckingAccountTransaction.TransactionDebit, dbo.CheckingAccountTransaction.TransactionCredit,
dbo.CheckingAccountTransaction.TransactionDescription,
(SELECT COALESCE (dbo.CheckingAccount.PrevCheckingAccountBalance, 0) + SUM(COALESCE (CheckingAccountTransaction1.TransactionCredit,
0)) - SUM(COALESCE (CheckingAccountTransaction1.TransactionDebit, 0)) AS Expr1
FROM dbo.CheckingAccount INNER JOIN
dbo.CheckingAccountTransaction AS CheckingAccountTransaction1 ON
dbo.CheckingAccount.CheckingAccountId = dbo.CheckingAccountTransaction.CheckingAccountId AND
dbo.CheckingAccount.InstitutionId = CheckingAccountTransaction1.InstitutionId AND
dbo.CheckingAccount.PrevCheckingAccountBalanceDate <= CheckingAccountTransaction1.TransactionDate AND
CheckingAccountTransaction1.CheckingAccountTransactionId <= dbo.CheckingAccountTransaction.CheckingAccountTransactionId AND
dbo.CheckingAccount.PrevCheckingAccountBalanceDate <= CheckingAccountTransaction1.TransactionDate AND
dbo.CheckingAccount.CheckingAccountId = CheckingAccountTransaction1.CheckingAccountId
WHERE (CheckingAccountTransaction1.TransactionStatus IN (1, 2)) AND
(CheckingAccountTransaction1.TransactionDate <= dbo.CheckingAccountTransaction.TransactionDate)
GROUP BY dbo.CheckingAccount.PrevCheckingAccountBalance) AS StatementBalance, dbo.CheckingAccountTransaction.InstitutionId
FROM dbo.CheckingAccount AS CheckingAccount_1 INNER JOIN
dbo.CheckingAccountTransaction ON CheckingAccount_1.CheckingAccountId = dbo.CheckingAccountTransaction.CheckingAccountId AND
CheckingAccount_1.InstitutionId = dbo.CheckingAccountTransaction.InstitutionId AND
CheckingAccount_1.PrevCheckingAccountBalanceDate <= dbo.CheckingAccountTransaction.TransactionDate AND
CheckingAccount_1.PrevCheckingAccountBalanceDate <= dbo.CheckingAccountTransaction.TransactionDate
WHERE (dbo.CheckingAccountTransaction.TransactionStatus IN (1, 2, 3, 4))
GROUP BY CheckingAccount_1.PrevCheckingAccountBalance, CheckingAccount_1.CheckingAccountId,
dbo.CheckingAccountTransaction.CheckingAccountTransactionId, dbo.CheckingAccountTransaction.CheckingAccountId,
dbo.CheckingAccountTransaction.TransactionDate, dbo.CheckingAccountTransaction.CheckNumber,
dbo.CheckingAccountTransaction.TransactionStatus, dbo.CheckingAccountTransaction.ReviewDate, dbo.CheckingAccountTransaction.ReviewStatus,
dbo.CheckingAccountTransaction.TransactionDebit, dbo.CheckingAccountTransaction.TransactionCredit,
dbo.CheckingAccountTransaction.TransactionDescription, dbo.CheckingAccountTransaction.InstitutionId
ORDER BY CheckingAccount_1.CheckingAccountId, dbo.CheckingAccountTransaction.CheckingAccountTransactionId, StatementBalance
January 16, 2008 at 9:39 am
Do you get any errors? And which Group By are you specifically referring to?
January 16, 2008 at 9:41 am
Also if you could - post code inside of a code block, so that if formats a little. That's awfully hard to read.
(click on the IFCODE option in the editor, then pick code. Paste code between the tags).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 10:18 am
Sorry about the confusion. The 'group by' clause which is causing the problem is the very last use of 'group by', just before the 'order by' clause at the end of the Query. I now have this in bold
SELECT TOP 100 PERCENT
dbo.CheckingAccountTransaction.CheckingAccountTransactionId,
dbo.CheckingAccountTransaction.CheckingAccountId,
dbo.CheckingAccountTransaction.TransactionDate,
dbo.CheckingAccountTransaction.CheckNumber,
dbo.CheckingAccountTransaction.TransactionStatus,
dbo.CheckingAccountTransaction.ReviewDate,
dbo.CheckingAccountTransaction.ReviewStatus,
dbo.CheckingAccountTransaction.TransactionDebit,
dbo.CheckingAccountTransaction.TransactionCredit,
dbo.CheckingAccountTransaction.TransactionDescription,
(SELECT COALESCE
dbo.CheckingAccount.PrevCheckingAccountBalance, 0) + SUM
(COALESCE (CheckingAccountTransaction1.TransactionCredit,
0)) - SUM(COALESCE
(CheckingAccountTransaction1.TransactionDebit, 0)) AS Expr1
FROM dbo.CheckingAccount INNER JOIN
dbo.CheckingAccountTransaction AS CheckingAccountTransaction1 ON
dbo.CheckingAccount.CheckingAccountId =
dbo.CheckingAccountTransaction.CheckingAccountId AND
dbo.CheckingAccount.InstitutionId =
CheckingAccountTransaction1.InstitutionId AND
dbo.CheckingAccount.PrevCheckingAccountBalanceDate <=
CheckingAccountTransaction1.TransactionDate AND
CheckingAccountTransaction1.CheckingAccountTransactionId <=
dbo.CheckingAccountTransaction.CheckingAccountTransactionId AND
dbo.CheckingAccount.PrevCheckingAccountBalanceDate <=
CheckingAccountTransaction1.TransactionDate AND
dbo.CheckingAccount.CheckingAccountId =
CheckingAccountTransaction1.CheckingAccountId
WHERE (CheckingAccountTransaction1.TransactionStatus IN (1, 2)) AND
(CheckingAccountTransaction1.TransactionDate <=
dbo.CheckingAccountTransaction.TransactionDate)
GROUP BY dbo.CheckingAccount.PrevCheckingAccountBalance)
AS StatementBalance,
dbo.CheckingAccountTransaction.InstitutionId
FROM dbo.CheckingAccount AS CheckingAccount_1 INNER JOIN
dbo.CheckingAccountTransaction ON
CheckingAccount_1.CheckingAccountId =
dbo.CheckingAccountTransaction.CheckingAccountId AND
CheckingAccount_1.InstitutionId =
dbo.CheckingAccountTransaction.InstitutionId AND
CheckingAccount_1.PrevCheckingAccountBalanceDate <=
dbo.CheckingAccountTransaction.TransactionDate AND
CheckingAccount_1.PrevCheckingAccountBalanceDate <=
dbo.CheckingAccountTransaction.TransactionDate
WHERE (dbo.CheckingAccountTransaction.TransactionStatus IN (1, 2, 3,
4))
GROUP BY CheckingAccount_1.PrevCheckingAccountBalance, CheckingAccount_1.CheckingAccountId,
dbo.CheckingAccountTransaction.CheckingAccountTransactionId, dbo.CheckingAccountTransaction.CheckingAccountId,
dbo.CheckingAccountTransaction.TransactionDate, dbo.CheckingAccountTransaction.CheckNumber,
dbo.CheckingAccountTransaction.TransactionStatus, dbo.CheckingAccountTransaction.ReviewDate, dbo.CheckingAccountTransaction.ReviewStatus,
dbo.CheckingAccountTransaction.TransactionDebit, dbo.CheckingAccountTransaction.TransactionCredit,
dbo.CheckingAccountTransaction.TransactionDescription, dbo.CheckingAccountTransaction.InstitutionId
ORDER BY CheckingAccount_1.CheckingAccountId, dbo.CheckingAccountTransaction.CheckingAccountTransactionId, StatementBalance
January 16, 2008 at 10:21 am
Sorry, I don't see the Code option in the IfCODE shortcuts
January 16, 2008 at 10:35 am
I think there is a problem with your code. It looks like in the posted code you may be missing a '(' in the first subquery in your select statement. You have (SELECT COALESCE, but there doesn't appear to be a open paren after the COALESCE.
I noticed this as i was trying to format your code to make it more readable.
January 16, 2008 at 12:18 pm
You are correct, the ( open paren was dropped as I tried to format the data. If you look at my previous post, it was in fact a part of the original query.
January 16, 2008 at 12:23 pm
Going back,
1 did you get any errors
2 did it just keep running
3 did you just gt no results
?
January 16, 2008 at 12:29 pm
This seems to get past the parser - care to give it a whirl?
SELECT TOP 100 PERCENT
dbo.CheckingAccountTransaction.CheckingAccountTransactionId,
dbo.CheckingAccountTransaction.CheckingAccountId,
dbo.CheckingAccountTransaction.TransactionDate,
dbo.CheckingAccountTransaction.CheckNumber,
dbo.CheckingAccountTransaction.TransactionStatus,
dbo.CheckingAccountTransaction.ReviewDate,
dbo.CheckingAccountTransaction.ReviewStatus,
dbo.CheckingAccountTransaction.TransactionDebit,
dbo.CheckingAccountTransaction.TransactionCredit,
dbo.CheckingAccountTransaction.TransactionDescription,
(SELECT
COALESCE(dbo.CheckingAccount.PrevCheckingAccountBalance, 0) +
SUM(COALESCE(CheckingAccountTransaction1.TransactionCredit,0 )) -
SUM(COALESCE(CheckingAccountTransaction1.TransactionDebit, 0)) AS Expr1
FROM
dbo.CheckingAccount
INNER JOIN dbo.CheckingAccountTransaction AS CheckingAccountTransaction1
ONdbo.CheckingAccount.CheckingAccountId = dbo.CheckingAccountTransaction.CheckingAccountId AND
dbo.CheckingAccount.InstitutionId = CheckingAccountTransaction1.InstitutionId AND
dbo.CheckingAccount.PrevCheckingAccountBalanceDate <= CheckingAccountTransaction1.TransactionDate AND
CheckingAccountTransaction1.CheckingAccountTransactionId <= dbo.CheckingAccountTransaction.CheckingAccountTransactionId AND
dbo.CheckingAccount.PrevCheckingAccountBalanceDate <= CheckingAccountTransaction1.TransactionDate AND
dbo.CheckingAccount.CheckingAccountId = CheckingAccountTransaction1.CheckingAccountId
WHERE
(CheckingAccountTransaction1.TransactionStatus IN (1, 2)) AND
(CheckingAccountTransaction1.TransactionDate <= dbo.CheckingAccountTransaction.TransactionDate)
GROUP BY dbo.CheckingAccount.PrevCheckingAccountBalance
) AS StatementBalance,
dbo.CheckingAccountTransaction.InstitutionId
FROM
dbo.CheckingAccount AS CheckingAccount_1
INNER JOIN dbo.CheckingAccountTransaction
ONCheckingAccount_1.CheckingAccountId = dbo.CheckingAccountTransaction.CheckingAccountId AND
CheckingAccount_1.InstitutionId = dbo.CheckingAccountTransaction.InstitutionId AND
CheckingAccount_1.PrevCheckingAccountBalanceDate <= dbo.CheckingAccountTransaction.TransactionDate AND
CheckingAccount_1.PrevCheckingAccountBalanceDate <= dbo.CheckingAccountTransaction.TransactionDate
WHERE
(dbo.CheckingAccountTransaction.TransactionStatus IN (1, 2, 3,4))
GROUP BY
CheckingAccount_1.PrevCheckingAccountBalance,
CheckingAccount_1.CheckingAccountId,
dbo.CheckingAccountTransaction.CheckingAccountTransactionId,
dbo.CheckingAccountTransaction.CheckingAccountId,
dbo.CheckingAccountTransaction.TransactionDate,
dbo.CheckingAccountTransaction.CheckNumber,
dbo.CheckingAccountTransaction.TransactionStatus,
dbo.CheckingAccountTransaction.ReviewDate,
dbo.CheckingAccountTransaction.ReviewStatus,
dbo.CheckingAccountTransaction.TransactionDebit,
dbo.CheckingAccountTransaction.TransactionCredit,
dbo.CheckingAccountTransaction.TransactionDescription,
dbo.CheckingAccountTransaction.InstitutionId
ORDER BY
CheckingAccount_1.CheckingAccountId,
dbo.CheckingAccountTransaction.CheckingAccountTransactionId,
StatementBalance
The second coalesce was missing a parameter
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 12:32 pm
I get an the following when I run the query.
[Microsoft][ODBC SQL Server Driver][Sql Server]Internal SQL Server error.
When I use the code formated above I get the following
Server: Msg 8624, Level 16, State 16, Line 1
Internal SQL Server error.
January 16, 2008 at 12:35 pm
What does, "... will not run ..." mean in this context? Do you get an error message?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 12:39 pm
I think I may see the problem. Here is the code as I have reformatted it;
SELECT TOP 100 PERCENT
dbo.CheckingAccountTransaction.CheckingAccountTransactionId,
dbo.CheckingAccountTransaction.CheckingAccountId,
dbo.CheckingAccountTransaction.TransactionDate,
dbo.CheckingAccountTransaction.CheckNumber,
dbo.CheckingAccountTransaction.TransactionStatus,
dbo.CheckingAccountTransaction.ReviewDate,
dbo.CheckingAccountTransaction.ReviewStatus,
dbo.CheckingAccountTransaction.TransactionDebit,
dbo.CheckingAccountTransaction.TransactionCredit,
dbo.CheckingAccountTransaction.TransactionDescription,
(SELECT
COALESCE(dbo.CheckingAccount.PrevCheckingAccountBalance, 0) +
SUM(COALESCE(CheckingAccountTransaction1.TransactionCredit, 0)) -
SUM(COALESCE(CheckingAccountTransaction1.TransactionDebit, 0)) AS Expr1
FROM
dbo.CheckingAccount
INNER JOIN dbo.CheckingAccountTransaction AS CheckingAccountTransaction1
ON (dbo.CheckingAccount.CheckingAccountId = dbo.CheckingAccountTransaction.CheckingAccountId
AND dbo.CheckingAccount.InstitutionId = CheckingAccountTransaction1.InstitutionId
AND dbo.CheckingAccount.PrevCheckingAccountBalanceDate <= CheckingAccountTransaction1.TransactionDate
AND CheckingAccountTransaction1.CheckingAccountTransactionId <= dbo.CheckingAccountTransaction.CheckingAccountTransactionId
AND dbo.CheckingAccount.PrevCheckingAccountBalanceDate <= CheckingAccountTransaction1.TransactionDate
AND dbo.CheckingAccount.CheckingAccountId = CheckingAccountTransaction1.CheckingAccountId)
WHERE
(CheckingAccountTransaction1.TransactionStatus IN (1, 2)) AND
(CheckingAccountTransaction1.TransactionDate <= dbo.CheckingAccountTransaction.TransactionDate)
GROUP BY
dbo.CheckingAccount.PrevCheckingAccountBalance) AS StatementBalance,
dbo.CheckingAccountTransaction.InstitutionId
FROM
dbo.CheckingAccount AS CheckingAccount_1
INNER JOIN dbo.CheckingAccountTransaction
ON (CheckingAccount_1.CheckingAccountId = dbo.CheckingAccountTransaction.CheckingAccountId
AND CheckingAccount_1.InstitutionId = dbo.CheckingAccountTransaction.InstitutionId
AND CheckingAccount_1.PrevCheckingAccountBalanceDate <= dbo.CheckingAccountTransaction.TransactionDate
AND CheckingAccount_1.PrevCheckingAccountBalanceDate <= dbo.CheckingAccountTransaction.TransactionDate)
WHERE
(dbo.CheckingAccountTransaction.TransactionStatus IN (1, 2, 3, 4))
GROUP BY
CheckingAccount_1.PrevCheckingAccountBalance,
CheckingAccount_1.CheckingAccountId,
dbo.CheckingAccountTransaction.CheckingAccountTransactionId,
dbo.CheckingAccountTransaction.CheckingAccountId,
dbo.CheckingAccountTransaction.TransactionDate,
dbo.CheckingAccountTransaction.CheckNumber,
dbo.CheckingAccountTransaction.TransactionStatus,
dbo.CheckingAccountTransaction.ReviewDate,
dbo.CheckingAccountTransaction.ReviewStatus,
dbo.CheckingAccountTransaction.TransactionDebit,
dbo.CheckingAccountTransaction.TransactionCredit,
dbo.CheckingAccountTransaction.TransactionDescription,
dbo.CheckingAccountTransaction.InstitutionId
ORDER BY
CheckingAccount_1.CheckingAccountId,
dbo.CheckingAccountTransaction.CheckingAccountTransactionId,
StatementBalance
I think the problem is with the subquery in the initial select statement. It is not in an aggregate nor is it included in the group by. It may help to see the code that works from SQL Server 2005 to see if there are any differences.
😎
January 16, 2008 at 12:43 pm
Also, it would help if you could provide the DDL for the tables and some sample data. This would allow us to test what we come up with.
This article provides you with good information on how to ask for help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply