[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.6

SQL2000 and SQL 2005 - Return Different Values from a SELECT statement

Asked by PaulEll in SQL Server 2005, MS SQL Server

Tags: SQL2000 SQL2005

Hi there,
I am developing an VB application that connects to an SQL backend that will be used as a sort of data corruption checker.

I have no control over the database design as we are connecting to an existing accounts package that runs under both SQL 2000 and SQL2005.

We have created two VIEWS that collect header and line information:-
PMALLHD - Collects header information
PMALLGL - Collects line information.
We then use a SQL select statement to get the info we need from these views.

My problem is that the SQL code below returns different figures depending on which database engine it is connected to.  The data returned by SQL2005 is correct whereas the data returned by SQL 2000 is wrong.

Can any one point me in the right direction and explain where the compatibility conflict is.

Thanks
Paul
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
PMALLHD - Header View
 
SELECT     dbo.PM20000.VCHRNMBR, dbo.PM20000.DOCTYPE, dbo.PM20000.DOCNUMBR, dbo.PM20000.DOCAMNT, dbo.PM20000.PORDNMBR, 
                      dbo.PM20000.TRXSORCE, dbo.PM20000.TRXDSCRN, dbo.PM20000.VOIDED, dbo.PM20000.CURNCYID, dbo.PM20000.VENDORID, 
                      dbo.PM00200.VENDNAME
FROM         dbo.PM20000 INNER JOIN
                      dbo.PM00200 ON dbo.PM20000.VENDORID = dbo.PM00200.VENDORID
UNION ALL
SELECT     dbo.PM30200.VCHRNMBR, dbo.PM30200.DOCTYPE, dbo.PM30200.DOCNUMBR, dbo.PM30200.DOCAMNT, dbo.PM30200.PORDNMBR, 
                      dbo.PM30200.TRXSORCE, dbo.PM30200.TRXDSCRN, dbo.PM30200.VOIDED, dbo.PM30200.CURNCYID, dbo.PM30200.VENDORID, 
                      PM00200_1.VENDNAME
FROM         dbo.PM30200 INNER JOIN
                      dbo.PM00200 AS PM00200_1 ON dbo.PM30200.VENDORID = PM00200_1.VENDORID
 
PMALLGL - Lines View
 
SELECT     VCHRNMBR, DEBITAMT - CRDTAMNT AS AMOUNT, DSTINDX, DISTTYPE, VENDORID, TRXSORCE, PSTGDATE, PSTGSTUS
FROM         dbo.PM10100
UNION ALL
SELECT     VCHRNMBR, DEBITAMT - CRDTAMNT AS AMOUNT, DSTINDX, DISTTYPE, VENDORID, TRXSORCE, PSTGDATE, PSTGSTUS
FROM         dbo.PM30600
 
SQL - SELECT - STATEMENT THAT IS NOT WORKING
 
SELECT   PMALLGL.DISTTYPE,
   (CASE DISTTYPE
		WHEN 1 THEN 'CASH'
	    WHEN 2 THEN 'PAY'
		WHEN 3 THEN 'AVAIL'
		WHEN 4 THEN 'TAKEN'
        WHEN 5 THEN ' FNCHG' 
		WHEN 6 THEN 'PURCH'
		WHEN 7 THEN 'TRADE'
		WHEN 8 THEN 'MISC'
		WHEN 9 THEN 'FREIGHT'
		WHEN 10 THEN 'TAXES'
		WHEN 11 THEN 'WRITE'
		WHEN 12 then 'OTHER'
		WHEN 13 then 'GST'
		WHEN 14 then 'PPS'
		WHEN 15 then 'UNIT'
		WHEN 16 then 'ROUND'
		WHEN 17 then 'CURRENCY 1'
		WHEN 18 then 'CURRENCY 2'
		Else 'UNKNOWN'
      END)
        AS zDISTTYPE,
     (CASE DISTTYPE
		WHEN 1 THEN  'Cash Paid / Cash Out'
		WHEN 2 THEN  'Payables Control'
		WHEN 3 THEN  'Settlement Discount Available'
		WHEN 4 THEN  'Settlement Discount Taken'
		WHEN 5 THEN  'Finance Charges'
		WHEN 6 THEN  'Purchases GL Analysis'
		WHEN 7 THEN  'Trade Discount'
		WHEN 8 THEN  'Miscellaneous'
		WHEN 9 THEN  'Freight / Carriage'
		WHEN 10 THEN 'Taxes & VAT'
		WHEN 11 THEN  'Write Offs'
		WHEN 12 then  'Other Costs'
		WHEN 13 then  'GST'
		WHEN 14 then  'PPS'
		WHEN 15 then  'UNIT'
		WHEN 16 then  'Rounding'
		WHEN 17 then  'Currency Reval 1'
		WHEN 18 then  'Currency Reval 2'
		else  'UNKNOWN'
      END)
		AS DISTNAME,
        
		zAMOUNT = convert(money,(sum(PMALLGL.AMOUNT)))
 		 
 FROM { oj ((PMALLHD PMALLHD INNER JOIN PMALLGL PMALLGL ON PMALLHD.VCHRNMBR = PMALLGL.VCHRNMBR)  INNER JOIN GL00105 GL00105 ON PMALLGL.DSTINDX = GL00105.ACTINDX)  INNER JOIN GL00100 GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX}
 
 WHERE PMALLHD.VOIDED = 0 AND /* Main Selection Criteria */
       PMALLGL.PSTGDATE >= {ts '1900-01-01 00:00:00.00'} AND /* Param 1*/
       PMALLGL.PSTGDATE <= {ts '2009-02-23 00:00:00.00'}
 
GROUP BY DISTTYPE
ORDER BY DISTTYPE ASC
[+][-]02/24/09 01:40 AM, ID: 23719686Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: SQL Server 2005, MS SQL Server
Tags: SQL2000 SQL2005
Sign Up Now!
Solution Provided By: imran_fast
Participating Experts: 3
Solution Grade: A
 
[+][-]02/24/09 01:45 AM, ID: 23719711Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]02/24/09 01:48 AM, ID: 23719726Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02/24/09 01:51 AM, ID: 23719743Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]02/24/09 01:52 AM, ID: 23719747Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]02/24/09 01:54 AM, ID: 23719762Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]02/24/09 02:22 AM, ID: 23719930Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02/24/09 02:28 AM, ID: 23719949Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]02/24/09 03:48 AM, ID: 23720458Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]02/24/09 09:29 AM, ID: 23723936Assisted Solution

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
 
Loading Advertisement...
20091021-EE-VQP-81 - Hierarchy / EE_QW_3_20080625