1

I have the following query to put some data from rows in columns:

string qry3 =
            "SELECT TABLENAME1.SLONO AS ORDER, TABLENAME1.SLLNNO AS LINE " +
            ",  MAX(CASE WHEN TABLENAME2.CZVRNM in ('SLEEVEDEPTH', 'LENGTH') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) END) AS SLEEVE " +
            ",  MAX(CASE WHEN TABLENAME2.CZVRNM in ('INSTALLATION') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) END) AS DAMPER_AI " +
            "FROM LOCATION.LOCATION2.TABLENAME1 TABLENAME1 LEFT JOIN LOCATION.LOCATION2.TABLENAME2 TABLENAME2 " +
            "     ON (TABLENAME1.SLONO = TABLENAME2.SPONO AND TABLENAME1.SLLNNO = TABLENAME2.SPLNNO)  " +
            "GROUP BY TABLENAME1.SLONO, TABLENAME1.SLLNNO ";

But when I try to run it in the following code:

        try
        {
            conAS400.Open();

            iDB2Command command = conAS400.CreateCommand();
            command.CommandText = qry3;

            command.CommandTimeout = 0;

            iDB2DataReader reader = command.ExecuteReader();
            dtAS400 = new DataTable();
            dtAS400.Load(reader); //<-- The exception occurs here. I can't even catch it

            conAS400.Close();

            retrieved = true;
        }

        catch (Exception ex)
        {
            MessageBox.Show("Could not retrive the information. Exception: " + ex.Message);

            retrieved = false;
        }
        finally
        {
            conAS400.Close();
        }

I get this exception:

"IBM.Data.DB2.iSeries.iDB2DCFunctionErrorException: 'An unexpected exception occurred. Type: System.AccessViolationException, Message: Attempted to read or write protected memory. This is often an indication that other memory is corrupt..'"

I have other queries that work just fine, but this one does not. I even tried the query on SQL as follows (credit to Gordon Linoff for providing this code):

SELECT 
    A.IDA, A.SomeInfo,
    MAX(CASE WHEN VarName in ('Depth', 'Depth2') THEN VarValue END) AS Depth,
    MAX(CASE WHEN VarName in ('Length') THEN VarValue END) AS Length
FROM 
    A
LEFT JOIN 
    B ON A.IDA = B.IDA
GROUP BY 
    A.IDA, A.SomeInfo

And it works. What can I do?

Drivers:

Client Access ODBC Driver (32-bit) 12.00.00.00

iSeries Access ODBC Driver 12.00.00.00

EDIT: Here's the sql query without being a string.

SELECT TABLENAME1.SLONO AS ORDER, TABLENAME1.SLLNNO AS LINE, 
    MAX(CASE WHEN TABLENAME2.CZVRNM in ('SLEEVEDEPTH', 'LENGTH') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) END) AS SLEEVE, 
    MAX(CASE WHEN TABLENAME2.CZVRNM in ('INSTALLATION') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) END) AS DAMPER_AI 
FROM LOCATION.LOCATION2.TABLENAME1 TABLENAME1 LEFT JOIN LOCATION.LOCATION2.TABLENAME2 TABLENAME2 
    ON (TABLENAME1.SLONO = TABLENAME2.SPONO AND TABLENAME1.SLLNNO = TABLENAME2.SPLNNO)  
GROUP BY TABLENAME1.SLONO, TABLENAME1.SLLNNO
ArminAH
  • 113
  • 10
  • please edit your question (do not use comments) to add the version of the Db2-driver that you are using, and which type of driver it is. Often it is necessary to ensure you have the __latest__ fixpack of the IBM supplied driver (if your supplier is IBM for the driver). – mao Aug 25 '21 at 16:21
  • 1
    This question seems to be administration/provisioning related and could find better answers at dba.stackexchange.com. – The Impaler Aug 25 '21 at 16:25
  • Thanks for your comments. I have already edited my question and will post it on dba.stackexchange.com. – ArminAH Aug 25 '21 at 16:44
  • I have version 12.00.02.00 of the ODBC driver and do this kind of thing all the time when pivoting. As an experiment, you could change your connection to either the OLE DB driver, or even better, the IBM.Data.DB2.iSeries.dll driver. – Mike Aug 25 '21 at 21:03
  • @Mike I think I have the IBM.Data.DB2.iSeries.dll driver? I added that dll as a reference directly in visual studio. This is my first time dealing with an as400 (and first job as a developer too) and I'm having a lot of trouble dealing with this. How do you pivot this kind of stuff? – ArminAH Aug 25 '21 at 21:13
  • I didn't notice earlier. You are already using the iSeries objects for your connection, not an ODBC connection. You can tell by the object types. Pivoting should be a different question. It uses techniques of GROUP, MAX, and CASE that looks very similar to your SQL. https://stackoverflow.com/a/23207503/3175562 – Mike Aug 25 '21 at 23:48
  • @Mike I just adapted the code from that comment (it ended very similar to the one I posted) and I'm still getting the same exception. Something weird is happening within the MAX CASE. – ArminAH Aug 26 '21 at 14:11

1 Answers1

0

OK, so I finally found out what was the problem. For some reason, I'm not able to CAST to float inside the MAX. If I remove both CAST from the query, the exception is gone and everything works. I'm not sure why this happens, and if anyone could explain that, it would be very helpful as I need to cast those values to float. Also, if I SELECT more columns, it is necessary to add said columns into the GROUP BY statement. Anyways, here's the modified code that works:

EDIT: Found out the way to cast them. It turns out I was getting some nulls, some blank fields, and I was trying to assign a number to a string. This is the code now:

SELECT TABLENAME1.SLONO AS ORDER, TABLENAME1.SLLNNO AS LINE, TABLENAME1.PROMISEDT,
    CAST(MAX(CASE WHEN RTRIM(TABLENAME2.CZREFD) IS NULL OR RTRIM(TABLENAME2.CZREFD) = '' THEN '0.000' WHEN TABLENAME2.CZVRNM = 'SLEEVEDEPTH' OR TABLENAME2.CZVRNM = 'LENGTH' THEN RTRIM(TABLENAME2.CZREFD) END) AS FLOAT(53)) AS SLEEVE,  
    MAX(CASE WHEN TABLENAME2.CZVRNM in ('INSTALLATION') THEN RTRIM(TABLENAME2.CZREFD) END) AS DAMPER_AI 
FROM LOCATION.LOCATION2.TABLENAME1 TABLENAME1 LEFT JOIN LOCATION.LOCATION2.TABLENAME2 TABLENAME2 
    ON (TABLENAME1.SLONO = TABLENAME2.SPONO AND TABLENAME1.SLLNNO = TABLENAME2.SPLNNO)  
GROUP BY TABLENAME1.SLONO, TABLENAME1.SLLNNO, TABLENAME1.PROMISEDT --remember to add every column from the SELECT here
ArminAH
  • 113
  • 10
  • why not just use something like `MAX(CASE WHEN TABLENAME2.CZVRNM in ('SLEEVEDEPTH', 'LENGTH') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) ELSE 0 END) AS SLEEVE,`. That will get rid of the nulls and the max function should return a float. – jmarkmurphy Aug 27 '21 at 13:26
  • @jmarkmurphy I just tried that and got the exact same exception. For some reason I can't cast inside the `MAX`. – ArminAH Aug 27 '21 at 16:18
  • Well, It works for me. perhaps your system is way behind on PTF's? Talk to your admin and see if they will install the latest cum tape, or at a minimum install the latest database group ptf's. – jmarkmurphy Aug 28 '21 at 02:30