SQL NOOB - nested/subqueires

Question

I will begin by clearly stating that I am not a programmer, I am an accountant at heart!

I have a need to return into excel all transactions relating to jobs that have had transactions in a given week (i.e. so that I can see an in week amount and total to date amount).

I'm reasonably proficient with VBA in excel (as accountants go anyway!) but I have always just copied and bodged the same old SQL statement. Essentially, what I think I need to do is a sub query in place of the order number of the WHERE statement in the following:

    With Sheet1.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=JADE;DBQ=QGPL LIVDTALIB;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,"), _
            Array("512;QRYSTGLMT=-1;")), Destination:=Sheet1.Range("A1"))
            .CommandText = Array( _
            "SELECT SLBGDTPF.BGMCU, SLBGDTPF.BGDSDT, SLBGDTPF.ORTYPE, SLBGDTPF.ORDNO, SLBGDTPF.BGDSVL, SLBGDTPF.BGCD, ", _
            "SLBGDTPF.ADBBG, SLBGDTPF.BGRMK" _
            & Chr(13) & "" & Chr(10) & _
            "FROM RCHASE5C.LIVDTALIB.SLBGDTPF SLBGDTPF" _
            & Chr(13) & "" & Chr(10) & _
            "WHERE (SLBGDTPF.ORDNO='30214884')")
        .Name = "TEST Query"
        .FieldNames = True
        .RefreshStyle = xlOverwriteCells
        .Refresh BackgroundQuery:=False
    End With

As a standalone query, what the sub-query element looks like is as follows:

    With Sheet2.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=JADE;DBQ=QGPL LIVDTALIB;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,"), _
            Array("512;QRYSTGLMT=-1;")), Destination:=Sheet2.Range("A1"))
            .CommandText = Array( _
            "SELECT SLBGDTPF.ORDNO" _
            & Chr(13) & "" & Chr(10) & _
            "FROM RCHASE5C.LIVDTALIB.SLBGDTPF SLBGDTPF" _
            & Chr(13) & "" & Chr(10) & _
            "WHERE SLBGDTPF.BGPSDT='20180420'" _
            & Chr(13) & "" & Chr(10) & _
            "GROUP BY SLBGDTPF.ORDNO")
        .Name = "TEST Query"
        .FieldNames = True
        .RefreshStyle = xlOverwriteCells
        .Refresh BackgroundQuery:=False
    End With

I'm open to all suggestions, including alternate approaches (I did try using IN and referencing a range in a sheet but I need to pass up to 1,000 different job numbers)

Just as an FYI, this is a template that will be sent out to people within the business to update themselves hence the need to build the connection, rather than just have them refresh an existing query(s) within the workbook.

All the best,

Joe

Answer 1

As @IanPeters already mentioned, there are a few ways to handle this in SQL. I would like to add two versions that use a join instead of one of the WHERE clauses. You might want to test which version performs best on your database. This will depend on the index structure on the database and on how well the query optimizer handles the query.

Subquery in join:

With Sheet1.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=JADE;DBQ=QGPL LIVDTALIB;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,"), _
        Array("512;QRYSTGLMT=-1;")), Destination:=Sheet1.Range("A1"))
    .CommandText = Array( _
        "SELECT SLBGDTPF.BGMCU, SLBGDTPF.BGDSDT, SLBGDTPF.ORTYPE, SLBGDTPF.ORDNO, SLBGDTPF.BGDSVL, SLBGDTPF.BGCD, SLBGDTPF.ADBBG, SLBGDTPF.BGRMK" & vbCrLf & _
        "FROM RCHASE5C.LIVDTALIB.SLBGDTPF SLBGDTPF" & vbCrLf & _
            "INNER JOIN" & vbCrLf & _   
                "(SELECT S.ORDNO" & vbCrLf & _
                "FROM RCHASE5C.LIVDTALIB.SLBGDTPF S" & vbCrLf & _
                "WHERE WeekOrders.BGPSDT='20180420') WeekOrders" & vbCrLf & _
            "ON SLBGDTPF.ORDNO = WeekOrders.ORDNO") 
    .Name = "TEST Query"
    .FieldNames = True
    .RefreshStyle = xlOverwriteCells
    .Refresh BackgroundQuery:=False
End With

Condition outside of join:

With Sheet1.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=JADE;DBQ=QGPL LIVDTALIB;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,"), _
        Array("512;QRYSTGLMT=-1;")), Destination:=Sheet1.Range("A1"))
    .CommandText = Array( _
        "SELECT SLBGDTPF.BGMCU, SLBGDTPF.BGDSDT, SLBGDTPF.ORTYPE, SLBGDTPF.ORDNO, SLBGDTPF.BGDSVL, SLBGDTPF.BGCD, SLBGDTPF.ADBBG, SLBGDTPF.BGRMK" & vbCrLf & _
        "FROM RCHASE5C.LIVDTALIB.SLBGDTPF SLBGDTPF" & vbCrLf & _
            "INNER JOIN RCHASE5C.LIVDTALIB.SLBGDTPF WeekOrders" & vbCrLf & _
                "ON SLBGDTPF.ORDNO = WeekOrders.ORDNO" & vbCrLf & _
        "WHERE WeekOrders.BGPSDT='20180420'") 
    .Name = "TEST Query"
    .FieldNames = True
    .RefreshStyle = xlOverwriteCells
    .Refresh BackgroundQuery:=False
End With

Answer 2

This can be achieved a few ways, but presuming the database can handle subqueries, I would try a WHERE IN term. I've also made some other cursory edits for clarity. The ultimate GROUP BY term is redundant in the subquery as there is only a single subquery SELECT field and no aggregation going on.

With Sheet1.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=JADE;DBQ=QGPL LIVDTALIB;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,"), _
        Array("512;QRYSTGLMT=-1;")), Destination:=Sheet1.Range("A1"))
        .CommandText = Array( _
        "SELECT SLBGDTPF.BGMCU, SLBGDTPF.BGDSDT, SLBGDTPF.ORTYPE, SLBGDTPF.ORDNO, SLBGDTPF.BGDSVL, SLBGDTPF.BGCD, SLBGDTPF.ADBBG, SLBGDTPF.BGRMK" & vbCrLf & _
        "FROM RCHASE5C.LIVDTALIB.SLBGDTPF SLBGDTPF" & vbCrLf & _
        "WHERE SLBGDTPF.ORDNO IN (" & vbCrLf & _
            "SELECT SLBGDTPF.ORDNO" & vbCrLf & _
            "FROM RCHASE5C.LIVDTALIB.SLBGDTPF SLBGDTPF" & vbCrLf & _
            "WHERE SLBGDTPF.BGPSDT='20180420')")
    .Name = "TEST Query"
    .FieldNames = True
    .RefreshStyle = xlOverwriteCells
    .Refresh BackgroundQuery:=False
End With