TRANSFORM in SQL Statement

[lmonaghan]

I searched the forums and I tried the advice from one string which did not work like a charm. Please help in assigning the problem with the following statement:

    SQL = "TRANSFORM SUM(LOAD.DITMIL) AS SUMOFDITMIL " _
        & "SELECT LOAD.DIUNIT, LOADEXT.DIDV# " _
        & "FROM I93FILE.LOAD, I93FILE.LOADEXT " _
        & "WHERE LOAD.DIODR# = LOADEXT.DEODR# AND LOAD.DIDISP = LOADEXT.DEDISP " _
        & " AND LOAD.DIDATE BETWEEN 85261 AND 85267 " _
        & " AND LOAD.DIMULT<>'S' " _
        & " AND LOAD.DICONT IN ('0','1') " _
        & " AND LOADEXT.DIDV#<>'LOG' " _
        & "GROUP BY LOAD.DIUNIT, LOADEXT.DIDV# " _
        & "ORDER BY LOAD.DIUNIT " _
        & "PIVOT LOAD.DIDATE; "

Thanks in advance for your help!

 

[lmonaghan]

Fazza-

The simpler queries work. I tried the TRANSFORM with and without the ";" to no avail.

I did try to import the simpler query into Excel and then saved that to a workbook. In another workbook, I built a new database query using the saved workbook and wrote a TRANSFORM statement in which it worked fine. It was nice to finally get it to work.

I think the problem must lie somewhere within the ICC tables or the way the ODBC connects to that data via Excel. I have no idea why it would be an issue. Our AS/400 system has been "bastardized" by our IT department with many in-house solutions patched together so it most likely is related to that.

Thanks for you help.

Lita

 

[Fazza]

I'm glad you sorted it out, Lita - after initially posting ~9 months' ago - and am pleased to have been able to help a little bit. Regards, Fazza