none
ODBC Query Returns Unexpected Results RRS feed

  • General discussion

  • Hi,

    We have a legacy Paradox 4.X Database that I am querying using the System.Data.ODBC.ODBCConnection

    The query was behaving as expected until I added a LEFT OUTER JOIN to the SQL Statement and SUM'd the results of a column in the outer joined table.  The query still runs but the results are not being grouped correctly.  I.E. I get

    I have used MS Access to Link to the same Paradox Database Tables.  When I execute the same piece of SQL there the results are returning as expected.

    Any ideas on what could cause different results for the same piece of SQL?

    SELECT T0.Company, 
    	T0.CommonReportingDate, 
    	T0.JournalRemarks,
    	SUM(T0.Debit) AS Debit, 
    	SUM(T0.Credit) AS Credit, 
    	T0.NaturalAccount + T0.Segment1 + T0.Segment2 AS FormatCode,
    	T0.ProfitCenter,
    	T0.AutoVAT,
    	SUM(T0.ExtendedHours) AS Quantity
    FROM
    (
    	<STATEMENT 1>
    	UNION ALL
    	<STATEMENT 2>
    	UNION ALL
    	<STATEMENT 3>
    	UNION ALL
    	<STATEMENT 4>
    	UNION ALL
    	<STATEMENT 5>
    	UNION ALL
    	<STATEMENT 6>	
    ) T0 LEFT OUTER JOIN TimesheetHistory T2 ON (T2.PeriodDate = T0.CommonReportingDate) AND (T2.CostCentre = T0.CostCode) 
    WHERE T0.Company IN ('BLM','GRP') AND T0.CommonReportingDate BETWEEN ? AND ?
    GROUP BY
    	T0.Company, 
    	T0.CommonReportingDate, 
    	T0.JournalRemarks,
    	T0.NaturalAccount + T0.Segment1 + T0.Segment2,
    	T0.ProfitCenter,
    	T0.AutoVAT
    ORDER BY 1, 2, 7
    The .NET Code I am using is shown below...
            ImportState = ImportStateEnum.is_GetData;
            Connect(); /* Handles Connection to Database */
            XmlNode xmlSQLStatement = XMLWrapper.GetFirstLevelElementNodeFromXMLDocument(ImportDefinition.Details.ImportSource, JournalImportTemplate.constXML_JournalImportTemplateNamespace, constXML_ParametersElement);
    
            OdbcCommand SelectCommand = new OdbcCommand(xmlSQLStatement.InnerText, _Connection);
            JournalImportParameter Param;
            OdbcParameter NewParameter;
            for (int i = 1; i <= this.Parameters.Count; i++)
            {
              Param = this.Parameters[i.ToString().PadLeft(JournalImportParameters.constParameterKeyLength, '0')];
              NewParameter = SelectCommand.Parameters.AddWithValue(@"@" + Param.Name, Param.Value);
            }
            GOWEKALogger.Log(SelectCommand.CommandText, eLogLvl.llHigh);        
            OdbcDataReader ResultsReader = SelectCommand.ExecuteReader(CommandBehavior.CloseConnection);
    
            ImportState = ImportStateEnum.is_LoadData;        
            while (ResultsReader.Read())
            {
              _DataTable.Rows.Add();
              for (int i = 0; i < this.Columns.Count; i++)
              {
                if(ResultsReader[i] != DBNull.Value)
                  _DataTable.SetValue(i, RowCount, ResultsReader[i]);
              }
              RowCount++;
            }

    Cheers, Sean

    Wednesday, November 10, 2010 1:23 AM

All replies

  • I would check the below MS KB article and see if it applies to you. Access implements a different SQL standard to that could account for the results.

    Also, you may want to try running the SQL statement against the linked Paradox table in Access using System.Data.OleDb to see if it makes any difference, or by using the Jet or ACE OLEDB Provider and Paradox ISAM:

    You receive incorrect results from an Outer Join query

    http://www.connectionstrings.com/paradox

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, November 10, 2010 1:25 PM
  • Thanks for the suggestions Paul.

    Using OLEDB worked significantly better producing the desired results.

    The Connection String I used was: 

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<dbpath>;Extended Properties=Paradox 4.x;
    

    The same SQL statement returned with the results grouped as desired.

    Cheers,

    Sean

    Thursday, November 11, 2010 4:04 AM