Correct Syntax - VBA to Run SQL in DB2

[Dathan Camacho]

I'm using an Excel macro to run several SQL queries. Some are Teradata queries, and one query is a DB2 query.
To synch up all the queries to return comparable data, I’m using a column in a worksheet to define parameters for the SQL where statements. This lets me control the inputs in one place.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This simple code, where Inputs is an Excel worksheet, associates the worksheet values with a variable I'm calling SQL_Where_Parameter:

[FONT=Calibri][COLOR=black]With Inputs[/COLOR][/FONT]
 
[FONT=Calibri][COLOR=black]LastRow = .Cells(Rows.Count, "A").End(xlUp).Row<o:p></o:p>[/COLOR][/FONT]
 
[FONT=Calibri][COLOR=black]SQL_Where_Parameter = "'" & Join(Application.Transpose(Sheets("Inputs").Range("A" & FIRST_DATA_ROW & ":A" & LastRow).Value), "','") & "'"<o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]<o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Calibri]End With[/FONT][/COLOR]

<o></o>
<o></o>
Here’s the part I need help with:<o></o>
<o></o>
The queries that hit Teradata are working just fine using this method to call the worksheet inputs:<o></o>

[FONT=Calibri][COLOR=black]select… [/COLOR][/FONT]
[FONT=Calibri][COLOR=black]from…  <o:p></o:p>[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]where  t.table_column in (" & SQL_Where_Parameter & ") <o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Calibri]group by…[/FONT][/COLOR]

<o></o>
<o></o>
I used the same syntax in the DB2 query, but it doesn’t work. Here is the error returned from DB2:<o></o>

Run-time error '-2147467259 (80004005)':
[IBM][CLI Driver][DB2] SQL0401N The data types of the operands for the operation "" are not compatible. SQLSTATE=42818

Does anyone know the correct syntax? I haven’t been able to solve it through trial and error.

Any insight appreciated, thanks!

 

[ukmikeb]

Hi

I would check the "SQL_Where Parameter" definition as it seems that you're missing an & and quote after LastRow.

hth

Mike

 

[Dathan Camacho]

Mike, Last Row returns a numeric value.

 

[Norie]

Dathan

But is is a numeric value that is recognised/valid for use with DB2?

Sometimes the best way to check these things is to try running the query in the database.

Even if it still errors you might get a more descriptive message - might even tell you what operands you need to use.

One thing I can think of is that you generally only need/use single quotes for text, not for numbers.

 

[ukmikeb]

Hi

Yes, but you are not inserting it as a parameter correctly!

What I'm saying is that you have &'s before and after FIRST_DATA_ROW and I believe it should be repeated for LastRow but at the moment you only have an & before LastRow.

Apologies if I'm on the wrong track.

hth

Mike

 

[Dathan Camacho]

I think I understand what you're saying.

It's possible that Teradata is more forgiving when there's an error, so it's running the code despite the error.

But then the same code is used in DB2, which perhaps expects more perfect input, so DB2 is failing.