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

Do you have an example of what your source data looks like and what your expected return value looks like? — Shawn, May 02 '18 at 13:44
In your WHERE clause you have `WHERE SLBGDTPF.BGPSDT='20180420'` which should give you the transactions for only April 20. If you want an entire week you will need to do something like `WHERE SLBGDTPF.BGPSDT>='20180420' AND SLBGDTPF.BGPSDT<'20180428'` if I am understanding correctly. You may want to use a field on the Excel sheet for this input (that's a bit more code though but it will save you from modifying the query every time). — Jacob H, May 02 '18 at 13:46
Ha! The table I'm querying has around 14 million records so squeezing it into a pivot table isn't an option. I'm expecting there to be around 1,000 jobs with around 4 transactions each job to be returned in total — TouringCanary, May 02 '18 at 13:49
Jacob H, confusingly, that date field will only ever have a Friday date hence no need to give it a range — TouringCanary, May 02 '18 at 13:51
A quick example of what the output might look like (obviously for many more records (and fields): ORDNO BGDSVL BGCD ADBBG BGPSDT 30203743 60 10 3498321 20180420 30203743 12.5 10 2374720 20180420 30203743 12.5 10 2374725 20180406 30203743 374.4 10 2491687 20180406 30203743 72 10 2313994 20180126 30221220 48 10 3401111 20180420 30221220 40 10 2424871 20180202 30221220 40 10 3401527 20180202 30221220 120 10 2313994 20180202 30221220 80 10 3401111 20171208 — TouringCanary, May 02 '18 at 13:59
There is no need for this line: `& Chr(13) & "" & Chr(10) & _`. On the line above and/or below simply include an extra space within the quoted string as a delimiter before `FROM`, `WHERE`, and `GROUP BY`. — FreeMan, May 02 '18 at 14:32
@TouringCanary Can you add that sample output into the question so it can be formatted? — Shawn, May 02 '18 at 15:45

Ian Peters · Accepted Answer · 2018-05-02T14:24:30.063

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

score 0 · Answer 2 · answered May 02 '18 at 14:58

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

SQL NOOB - nested/subqueires

2 Answers2