Oracle SQL Query converted into VBA yet no longer produces results

Question

Having become incredibly rusty I was given some SQL to turn into a Macro and have made the below which unlike its original SQL doesn't produce any result.

The VBA is a rehash of most of my old tricks learned ages ago and it runs, yet produces no result, yet running the original SQL, with the same CRN involved, gets over a hundred rows of results.

What am I not seeing?

Sub Balance()

Dim DB_CONNECTION           As ADODB.Connection
Dim DB_RECORDSET            As ADODB.Recordset
Dim CRN                     As Variant
Dim ANSWER                  As String
Dim SQL_STRING              As String

Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Application.ScreenUpdating = True
Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveSheet.Name = "Data"

Set DB_CONNECTION = New ADODB.Connection
Set DB_RECORDSET = New ADODB.Recordset

DB_CONNECTION.ConnectionString = "Driver={Oracle in OraClient18Home1_32bit}; Dbq=XXXXX; Uid=USER; Pwd=XXXXXXXX;QTO=F;"

DB_CONNECTION.Open

Sheets("Data").Activate

Range("A5").Select

CRN = ActiveCell.Value

SQL_STRING = ""
SQL_STRING = SQL_STRING + "SELECT * "
SQL_STRING = SQL_STRING + "FROM "
SQL_STRING = SQL_STRING + "(SELECT DISTINCT tran.bill_no AS ""IINV/BILL#"", tran.date_posted   AS ""DATE POSTED"", "
SQL_STRING = SQL_STRING + "DECODE(tran.trans_type, '60', 'BILLING_CHARGES', '50', 'ADJUSTMENT/INTEREST', '10', 'RECEIPTS', '40', 'REFUND', '80', "
SQL_STRING = SQL_STRING + " 'WRITE_OFF', 'OTHER') AS ""TRANSACTION TYPE"", "
SQL_STRING = SQL_STRING + "CASE WHEN tran.batch LIKE ( 'EF%' ) THEN 'EFT_PAYMENT' "
SQL_STRING = SQL_STRING + "WHEN tran.batch LIKE ( 'ef%' ) THEN 'EFT_PAYMENT' "
SQL_STRING = SQL_STRING + "WHEN tran.batch = 'CB%' THEN 'BPAY' "
SQL_STRING = SQL_STRING + "WHEN tran.batch LIKE ( 'CQ%' ) THEN 'CHEQUE' "
SQL_STRING = SQL_STRING + "WHEN tran.batch LIKE ( 'ECS%' ) THEN 'AUSTRALIA_POST' "
SQL_STRING = SQL_STRING + "WHEN tran.batch LIKE ( 'ecs%' ) THEN 'AUSTRALIA_POST' "
SQL_STRING = SQL_STRING + "WHEN tran.batch LIKE ( 'bp%' ) THEN 'AUSTRALIA_POST' "
SQL_STRING = SQL_STRING + "WHEN tran.batch LIKE ( 'BP%' ) THEN 'AUSTRALIA_POST' "
SQL_STRING = SQL_STRING + "END AS ""PAYMENT TYPE"", "
SQL_STRING = SQL_STRING + "SUM(tran.trans_value) AS debit, 0 AS credit "
SQL_STRING = SQL_STRING + "FROM "
SQL_STRING = SQL_STRING + "bcsttran tran "
SQL_STRING = SQL_STRING + "WHERE tran.u##cust_ref = 'CRN' "
SQL_STRING = SQL_STRING + "Group BY tran.bill_no, tran.date_posted, "
SQL_STRING = SQL_STRING + "tran.u##cust_ref,tran.trans_type,tran.batch) tbl1 "
SQL_STRING = SQL_STRING + "WHERE "
SQL_STRING = SQL_STRING + " tbl1.debit > 0 Union ALL "
SQL_STRING = SQL_STRING + "SELECT tbl2.bill_no,tbl2.date_billed,tbl2.bill_type, "
SQL_STRING = SQL_STRING + "CASE WHEN tbl2.batch LIKE ( 'EF%' ) THEN 'EFT_PAYMENT' "
SQL_STRING = SQL_STRING + "WHEN tbl2.batch LIKE ( 'ef%' ) THEN 'EFT_PAYMENT' "
SQL_STRING = SQL_STRING + "WHEN tbl2.batch LIKE ( 'CQ%' ) THEN 'CHEQUE' "
SQL_STRING = SQL_STRING + "WHEN tbl2.batch LIKE ( 'ef%' ) THEN 'EFT_PAYMENT' "
SQL_STRING = SQL_STRING + "WHEN tbl2.batch LIKE ( 'CB%' ) THEN 'BPAY' "
SQL_STRING = SQL_STRING + "WHEN tbl2.batch LIKE ( 'ECS%' ) THEN 'AUSTRALIA_POST' "
SQL_STRING = SQL_STRING + "WHEN tbl2.batch LIKE ( 'ecs%' ) THEN 'AUSTRALIA_POST' "
SQL_STRING = SQL_STRING + "WHEN tbl2.batch LIKE ( 'bp%' ) THEN 'AUSTRALIA_POST' "
SQL_STRING = SQL_STRING + "WHEN tbl2.batch LIKE ( 'BP%' ) THEN 'AUSTRALIA_POST' "
SQL_STRING = SQL_STRING + "END AS ""Payment Type"", "
SQL_STRING = SQL_STRING + "tbl2.debit, abs(tbl2.credit) AS credit "
SQL_STRING = SQL_STRING + "FROM "
SQL_STRING = SQL_STRING + "( SELECT DISTINCT tran.u##cust_ref,tran.bill_no, "
SQL_STRING = SQL_STRING + "DECODE(tran.trans_type, '60', 'BILLING_CHARGES', "
SQL_STRING = SQL_STRING + "'50', 'ADJUSTMENT/INTEREST', '10', 'RECEIPTS', '40', 'REFUND', '80', "
SQL_STRING = SQL_STRING + "'WRITE_OFF', 'OTHER') AS bill_type, "
SQL_STRING = SQL_STRING + "tran.date_posted AS date_billed, "
SQL_STRING = SQL_STRING + "0 AS debit, "
SQL_STRING = SQL_STRING + "SUM(tran.trans_value) AS credit, tran.batch "
SQL_STRING = SQL_STRING + "FROM "
SQL_STRING = SQL_STRING + "bcsttran tran "
SQL_STRING = SQL_STRING + "WHERE "
SQL_STRING = SQL_STRING + "tran.u##cust_ref = 'CRN' "
SQL_STRING = SQL_STRING + "Group BY tran.u##cust_ref, tran.bill_no, "
SQL_STRING = SQL_STRING + "tran.date_posted,tran.batch, "
SQL_STRING = SQL_STRING + "tran.trans_type,tran.u##trans_code) tbl2 "
SQL_STRING = SQL_STRING + "WHERE "
SQL_STRING = SQL_STRING + "tbl2.credit < 0 "
SQL_STRING = SQL_STRING + "Order BY 2 DESC, 3 DESC "

DB_RECORDSET.Open SQL_STRING, DB_CONNECTION

Sheets("Balances").Select

Sheets("Balances").Range("A1").CopyFromRecordset DB_RECORDSET

        DB_RECORDSET.Close


       


End Sub

Original Query is

SELECT
    *
FROM
    (
        SELECT DISTINCT
            tran.bill_no       AS "IINV/BILL#",
            tran.date_posted   AS "DATE POSTED",
            DECODE(tran.trans_type, '60', 'BILLING_CHARGES', '50', 'ADJUSTMENT/INTEREST', '10', 'RECEIPTS', '40', 'REFUND', '80',
            'WRITE_OFF', 'OTHER') AS "TRANSACTION TYPE",
            CASE
                WHEN tran.batch LIKE ( 'EF%' ) THEN
                    'EFT_PAYMENT'
                WHEN tran.batch LIKE ( 'ef%' ) THEN
                    'EFT_PAYMENT'
                WHEN tran.batch = 'CB%' THEN
                    'BPAY'
                WHEN tran.batch LIKE ( 'CQ%' ) THEN
                    'CHEQUE'
                                        WHEN tran.batch LIKE ( 'ECS%' ) THEN
            'AUSTRALIA_POST'
                                WHEN tran.batch LIKE ( 'ecs%' ) THEN
            'AUSTRALIA_POST'
            WHEN tran.batch LIKE ( 'bp%' ) THEN
            'AUSTRALIA_POST'
                                WHEN tran.batch LIKE ( 'BP%' ) THEN
            'AUSTRALIA_POST'
            END AS "PAYMENT TYPE",
            SUM(tran.trans_value) AS debit,
            0 AS credit
        FROM
            bcsttran tran
        WHERE
            tran.u##cust_ref = '10423949'
        GROUP BY
            tran.bill_no,
            tran.date_posted,
            tran.u##cust_ref,
            tran.trans_type,
            tran.batch
    ) tbl1
WHERE
    tbl1.debit > 0
UNION ALL
SELECT
    tbl2.bill_no,
    tbl2.date_billed,
    tbl2.bill_type,
    CASE
        WHEN tbl2.batch LIKE ( 'EF%' ) THEN
            'EFT_PAYMENT'
        WHEN tbl2.batch LIKE ( 'ef%' ) THEN
            'EFT_PAYMENT'
        WHEN tbl2.batch LIKE ( 'CQ%' ) THEN
            'CHEQUE'
        WHEN tbl2.batch LIKE ( 'ef%' ) THEN
            'EFT_PAYMENT'
        WHEN tbl2.batch LIKE ( 'CB%' ) THEN
            'BPAY'
                    WHEN tbl2.batch LIKE ( 'ECS%' ) THEN
            'AUSTRALIA_POST'
                                WHEN tbl2.batch LIKE ( 'ecs%' ) THEN
            'AUSTRALIA_POST'
                        WHEN tbl2.batch LIKE ( 'bp%' ) THEN
            'AUSTRALIA_POST'
                                WHEN tbl2.batch LIKE ( 'BP%' ) THEN
            'AUSTRALIA_POST'
 
    END AS "Payment Type",
    tbl2.debit,
    abs(tbl2.credit) AS credit
FROM
    (
        SELECT DISTINCT
            tran.u##cust_ref,
            tran.bill_no,
            DECODE(tran.trans_type, '60', 'BILLING_CHARGES', '50', 'ADJUSTMENT/INTEREST', '10', 'RECEIPTS', '40', 'REFUND', '80',
            'WRITE_OFF', 'OTHER') AS bill_type,
            tran.date_posted AS date_billed,
            0 AS debit,
            SUM(tran.trans_value) AS credit,
            tran.batch
        FROM
            bcsttran tran
        WHERE
            tran.u##cust_ref = '10423949'
        GROUP BY
            tran.u##cust_ref,
            tran.bill_no,
            tran.date_posted,
            tran.batch,
            tran.trans_type,
            tran.u##trans_code
    ) tbl2
WHERE
    tbl2.credit < 0
ORDER BY
    2 DESC,
    3 DESC

The formatting of the Query is basically consistent with another Query I have converted into a Macro but given it uses the Case Statements I am not sure if there is an issue with how VBA handles it

Does the final `SQL_STRING` have the expected query string? ... from what I recall, and it's been a little while for me, that string concatenation in VBA uses `&` and not `+` ? — Paul T., Mar 03 '23 at 03:26
The old ones are the best! .. https://stackoverflow.com/questions/1727699/how-can-i-concatenate-strings-in-vba — user10186832, Mar 03 '23 at 07:15
Aside, please see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)? — Parfait, Mar 06 '23 at 00:19

score 0 · Answer 1 · answered Mar 06 '23 at 00:55

Fundamentally, you need a parameterized query for the CRN variable which is read literally in SQL. Hence, the reason for your empty results is that no record fits the condition: WHERE tran.u##cust_ref = 'CRN'.

Simply, avoid any VBA rebuilding of SQL but read directly from file. This avoids the double quotes, line breaks, and spacing that can affect processing. And to parameterize the query, use ADO.Command requiring a qmark in the WHERE clause.

SQL (save exact same single query in .sql with below change)

...
FROM 
    bcsttran tran 
WHERE 
    tran.u##cust_ref = ? 
GROUP BY
...

VBA (read query from .sql file and bind parameter and avoid all Select/Activate calls)

DB_CONNECTION.ConnectionString = "Driver={Oracle in OraClient18Home1_32bit}; Dbq=XXXXX; Uid=USER; Pwd=XXXXXXXX;QTO=F;" 
DB_CONNECTION.Open  

' READ IN SQL 
With CreateObject("Scripting.FileSystemObject") 
    SQL_STRING = .OpenTextFile("C:\Path\To\My\SQL\Query.sql", 1).readall
End With 

' PROCESS QUERY 
Set DB_COMMAND = New ADODB.Command

With DB_COMMAND 
    .CommandText = SQL_STRING 

    ' BIND ? PARAMETER
    CRN = Sheets("Data").Range("A5").Value
    .Parameters.Append .CreateParameter("cn_param", adVarChar, adParamInput, , CRN) 

    ' CREATE RECORDSET 
    Set DB_RECORDSET = .Execute 
End With 

' WRITE HEADERS
For i = 1 To DB_RECORDSET.Fields.Count - 1 
    Sheets("Balances").Cells(1, i) = DB_RECORDSET.Fields(i).Name
Next i

' WRITE ROWS
Sheets("Balances").Range("A2").CopyFromRecordset DB_RECORDSET

DB_RECORDSET.Close

As this is for distribution to others to run the query needs to be built into a template as there is no guarantee that even if we store the query as sql in a specific location the other users will have access to. This is the model we work from for all our queries into Templates to be run with VBA (We have lost those who were experts in the business) — Vukoneti, Mar 06 '23 at 01:16
Got it. You can save the SQL in an Excel cell with same formatting and assign SQL string to that cell value. Otherwise, continue as you do with the long VBA string still using qmark (`?`) for parameter. — Parfait, Mar 06 '23 at 16:59

score 0 · Accepted Answer · answered Mar 27 '23 at 04:57

Problem was the parameter configuration. For the input was given answer to another similar problem.

Code changes required were:-

Dim c As Range

Set c = ThisWorkbook.Sheets("Data").Range("A6")

'" & c.Value & "' "

Refer answer to this question:-

Oracle SQL Looping VBA for number not working

Oracle SQL Query converted into VBA yet no longer produces results

2 Answers2

Linked