ODBC Output using Update Function

Post Reply
CRP0021
Posts: 20
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

ODBC Output using Update Function

Post by CRP0021 » Thu Nov 04, 2021 3:06 am

Hi all,
Looking for a bit of advice on an ODBC Output to a SQL table.
Here are the details:

I have a column in the the table called [Date] format is Date, Null

There are dummy records in this date field which are populated with 1900-01-01.

I would like to convert all of those dates to a NULL.

When I run a simple query in SSMS it works as expected, but when executing it from my TI it fails with the following message:
Operand type clash: date is incompatible with int

Here is the query in SSMS which is working correctly that I'd like to enable running from the Prolog of my TI process:

Update [My Table]
Set [Date] = null
Where [Date] = '1900-01-01'

Here's the TI code:
sSQL = 'UPDATE [My Table] SET [Date] = NULL WHERE [Date] = 1901-01-01';
OdbcOutput ( cODBC, sSQL );

Seems pretty simple enough but not sure where I'm going wrong. I've looked through the forums but haven't come across any posts that could help this specific case.

Thanks in advance for any advice.

CRP0021
Posts: 20
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: ODBC Output using Update Function

Post by CRP0021 » Thu Nov 04, 2021 3:43 am

I got this sorted.
Rather than delete the post I'll paste what I did here in case it's useful for anyone else.
After searching through the posts on here I initially tried the expand Function in my statement but didn't work without realizing I needed to declare a variable for the Expand function to work.
Here's what I got working:

vDate = '1900-01-01';
sSQL = EXPAND('Update [My Table] SET [Date] = NULL WHERE [Date] = ''' | vDate | '''');
OdbcOutput ( cODBC, sSQL );

MarenC
Regular Participant
Posts: 178
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: ODBC Output using Update Function

Post by MarenC » Thu Nov 04, 2021 8:48 am

Hi,

Do you really need the expand function here, wouldn't we see a % sign if you were utilising it properly?

Wouldn't this equally work:

Code: Select all

sSQL = 'Update [My Table] SET [Date] = NULL WHERE [Date] = ''' | vDate | '''';
In other words, wasn't the issue that in your first attempt you didn't put the date in quotes and this is why it failed?

Maren

lotsaram
MVP
Posts: 3535
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: ODBC Output using Update Function

Post by lotsaram » Thu Nov 04, 2021 8:50 am

Clearly you don't really understand how Expand works. Becasue the way you used it is doing precisly ... nothing at all.

Expand "expands" a variable name into the value. The variable must be encased in % sign. One of the advantages of using Expand for strig concatenation is that it is a little bit more efficient to write as you don't need the pipe.

This should also work and is how you actually use Expend.

Code: Select all

vDate = '1900-01-01';
sSQL = EXPAND('Update [My Table] SET [Date] = NULL WHERE [Date] = "%vDate%"');
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

CRP0021
Posts: 20
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: ODBC Output using Update Function

Post by CRP0021 » Thu Nov 04, 2021 1:45 pm

Thanks guys for your replies.
I followed the example from this post below.
viewtopic.php?t=9490

In any case with or without the Expand function It's working which is what I care about.
Thanks again.
Cheers!

Post Reply