2

The below code is producing different record count when running in two different environments that are Oracle and SQL Server 2008. The table in both databases are identical.

Is there any way in SQL to check record count in each step of the code using either breakpoint like mechanism, so I could find the error?

Code:

SELECT 
    col1, col2, col3
    CASE 
       WHEN col4 IS NULL THEN SUM(xamt) ELSE 0 
    END) AS xamt,
FROM 
    MYTABLE as A
LEFT JOIN 
    LTDTABLE as B ON A.COL1 = B.COL1
WHERE   
    A.RECORD_ID = 'S1'
    AND B.ZSTATUS = ''
    AND (A..REFERRED_BY = '')
    OR (B.ZSTATUS = '')
    AND A.REFERRED_BY <> 'ZED'

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
akhrot
  • 415
  • 2
  • 7
  • 18
  • 1
    My guess is that your `OR` is being interpreted differently between the two servers. Try contain your logic in brackets. Eg `...AND B.ZSTATUS='' AND (A.REFERRED_BY='' OR B.ZSTATUS='') AND ...` – RToyo Nov 23 '17 at 16:54
  • 1
    most likely reason is case sensitive related issues. Oracle is default case sensitive. Whereas SQL server is not. To eliminate that try Convert your Where clauses to UPPER like this: Upper(A.RECORD_ID) = 'S1' Upper(A.REFERRED_BY)<>'ZED' and see if that resolves your issue. Another possibility is that comparison with empty string could behave differently on Oracle and SQL. – objectNotFound Nov 23 '17 at 16:57
  • @RToyota: Thanks a lot – akhrot Nov 23 '17 at 16:57
  • @objectNotFound: Thanks a lot – akhrot Nov 23 '17 at 16:58
  • 1
    https://stackoverflow.com/a/36796819/73226 – Martin Smith Nov 23 '17 at 17:02

1 Answers1

0

The group functions operate differently on Oracle and MSSQL. Oracle won't ignore null values, unlike MSSQL.

It's also worth checking collation, and what the coatings actually are being their name (utf8 in MySQL isn't full utf8, for example).

SEoF
  • 1,092
  • 14
  • 26