Parameter Query in Excel ODBC

General
Enthusiast

Parameter Query in Excel ODBC

Parameter Query in Excel ODBC

Hi,

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

(2113035761,2113515774,21283100000,21233900000)

 

 

Please help

2Z

11 REPLIES
Senior Apprentice

Re: Parameter Query in Excel ODBC

Hi,

 

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:

InSKU (VARCHAR(200)

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).

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: Parameter Query in Excel ODBC

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

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Parameter Query in Excel ODBC

Hi,

 

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)

 

 

 

 

 

Thanks

Z

Senior Apprentice

Re: Parameter Query in Excel ODBC

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

'1,2,3,4'

where 1,2,3,4 are the SKU codes from your cell.

 

Does that work?

 

Cheers,

Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Parameter Query in Excel ODBC

Hi Dave,

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

 

Senior Apprentice

Re: Parameter Query in Excel ODBC

Hi,

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.

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Parameter Query in Excel ODBC

Step 1Step 1

 

Step 3Step 3Step 4Step 4Step 2Step 2

Enthusiast

Re: Parameter Query in Excel ODBC

The code is not visible after is in Excel I can’t view how the values are transposed

Senior Apprentice

Re: Parameter Query in Excel ODBC

Hi,

 

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.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com