Can someone help me?
I am trying to pass multiple SKUs through ODBC in exce with no luck, I can use cast (? As integer) to pass one UPC
I can put UPC’s in SQL statement but I would like to use parameter to grab all UPC’s from single cell
Just to check that I've understood your requirements:
- in excel you have multiple values (in your case SKU numbers)
- you want to pass those values into a Teradata macro and have a result set returned using any of those values in a WHERE clause (i.e. selection criteria).
Here is one way of doing that. Define the macro parameter as:
In the Teradata macro code your WHERE clause using the POSITION function
WHERE POSITION(SKU_NUMBER IN :insku) > 0
In this example, 'sku_number' is a character column in the table that contains the value you're searching for.
The data values that you pass in (i.e. the content of the Excel cell must have delimited values. Use anything you like that is not a character found in the 'sku_number' column).
Just to be clear.
In my example above, the 'sku_number' column needs to be variable length character data, so if it is not defined in the dbms as VARCHAR the Teradata macro code will need to change.
If that column is defined as CHAR then use:
WHERE POSITION(TRIM(SKU_NUMBER) IN :insku) > 0
If that column is defined as a numeric type then try:
POSITION( TRIM(SKU_NUMBER (FORMAT 'z(9)9')) IN :insku) > 0
Sorry but my SQL level is very low, can you advise how I should change the code?
Here is portion of the code where i use ? to referance cell in excel with the SKU's,
WHERE UPC.CORPORATION_ID = 17
AND STR.DIVISION_ID = 565
AND UPC.UPC_ID IN (?)
AND UPC.DEPARTMENT_ID = ?
AND DTE.D_DATE BETWEEN cast(? as integer) AND cast(? as integer)
So this looks to be an Excel macro rather than a Teradata macro. And I'm assuming that what you've got is code to build up a string variable which is the SQL that you send to Teradata.
I think what you want is:
AND position(UPC.UPC_ID IN (?)) > 0
When your code/Excel replaces the '?' character in the above it needs to be of the form
where 1,2,3,4 are the SKU codes from your cell.
Does that work?
The code did not help resolving the issue, I did not get any values returned even with single SKU.
To clarify, the code is done in SQL and pass-through EXCEL Microsoft query ODBC then the I replace hardcoded integers like date or SKU with ?, this tells SQL/excel to use value in specific cell as parameter and replace ? with the value. It works for the date if i use cast(? as integer) and works for single SKU but not when using multiple SKU's
After the ? are replaced with values, what does the built SQL look like? Can you post that here? This will tell us what is being sent to TD and therefore why it is not doing what you want.
You could try using the ODBC trace facility. This should show us what we want:
- start / programs / odbc
- choose either 32-bit or 64-bit administrator (which one depends on whether you're using 32 or 64 bit excel).
- choose the 'Tracing' tab and click the 'start tracing now' button
Note the value in 'log file path'.
Start excel, try the query and then don't do anything.
Look for the log file named in 'log file path'. Open it in Notepad etc and find your sql.
If you can't do that then you might need to talk to your Teradata DBA. Teradata has a 'query logging' feature (aka DBQL) which can be used to record the SQL that is sent to the system. If that is that turned on for your Teradata userid then you (or someone can help you) find the sql that is sent to TD.