casperkamal: Using ADO to read from Excel in Microsoft Dynamics Ax

[kashperuk]

Вырезка кода из Jobs (тестовых) проекта Gustav - ImportFromOfficeDB

    // cool real SQL-query to Excel sheet !!! have you ever dreamt about it ?!
    doc.setRecordSource(
        'SELECT * FROM [EmplTable$] WHERE Left([Name],1) IN             ' +
        '(                                                              ' +
        ' SELECT TOP 1 FirstLetter FROM                                 ' +
        '  (                                                            ' +
        '   SELECT TOP 3 FirstLetter, Count(*) AS CountOfFirstLetter    ' +
        '   FROM                                                        ' +
        '    (                                                          ' +
        '     SELECT Left([Name],1) AS FirstLetter, [EmplTable$].*      ' +
        '     FROM [EmplTable$]                                         ' +
        '    )                                                          ' +
        '   GROUP BY FirstLetter                                        ' +
        '   ORDER BY Count(*) DESC                                      ' +
        '  )                                                            ' +
        ' ORDER BY CountOfFirstLetter                                   ' +
        ')                                                              ' );
    // this query returns subset of records from worksheet EmplTable,
    //   in which every name of employee starts with character,
    //      which is on 3rd position in rank of more popular first characters in Name field (Oh Mein Got! :-))