Using SQL syntax to do a JOIN of two .csv files in TM1

[jimicron]

Hi all,

I am wondering... does anyone know what the subtle difference is that I need to do with my syntax to have a successful INNER JOIN of two .csv's in Tm1?

I did this in Cognos Analyst and was successful!! So, I know an INNER JOIN can be done between two .csv's.

My syntax that works successfully in Analyst is:

Code: Select all

SELECT
    A.MPN as vMPN , 
    A.Date as vMonth ,
    A.Version as vVersion ,
    A."Revenue Actual" as vRevenue ,
    B."Profit Center" as vProfitCenter
FROM
     "Load SSD Revenue Actuals.csv" A  INNER JOIN "SSD MPN.csv" B
ON
    A.MPN = B."SSD MPN"
WHERE
     (A.MPN like 'MTFD%'
OR
     A.MPN like 'CT%')
AND
     B."Profit Center" like 'MT600A%'

And then I slightly tweak it for TM1 because in TM1, I have to use brackets versus a double quote but this syntax is NOT working in TM1, I just get the error "SQL statement failed" when I click "Preview"

Code: Select all

SELECT
    [A.MPN] as vMPN , 
    [A.Date] as vMonth ,
    [A.Version] as vVersion ,
    [A.Revenue Actual] as vRevenue ,
    [B.Profit Center] as vProfitCenter
FROM
     [Load SSD Revenue Actuals.csv] A  INNER JOIN [SSD MPN.csv] B
ON
    [A.MPN] = [B.SSD MPN]
WHERE
     ([A.MPN] like 'MTFD%'
OR
     [A.MPN] like 'CT%')
AND
     [B.Profit Center] like 'MT600A%'

Anyone else experience this sort of issue when trying to do this?

I've reached out to various folks at my company, but unfortunately, no one knows what the issue may be. Thanks a lot for any help/guidance you can provide!!

[David Usherwood]

Have you set up an ODBC DSN for your files? I'm going to admit that though I have tried that in the past with flat files it's somewhat weird given that TM1 reads flat files directly, and I don't know whether joins are supported with a text-based ODBC DSN. If you can do the join from Excel then it should work in TM1 - just bear in mind that Excel wants a 32 bit ODBC DSN and your TM1 server is likely to be 64 bit and thus want a 64 bit DSN.
I'd also say that I can't at present think of a reason to want to do it at all - why not just read both files in in 2 separate TIs?

[tomok]

As David mentioned, you have to have the text ODBC driver installed in order for this to work. if this is 64-bit TM1 you are talking about then I am pretty certain you are out of luck trying to go this route as no 64-bit text ODBC driver exists, to the best of my knowledge.

[David Usherwood]

Oops, should have thought of that.

[jimicron]

This brings up a whole other topic that we battled with for quite a while via tickets with IBM and Microsoft about the drivers...

Bottom line: Correct, "Microsoft Text Driver" does not exist per MS for 64-bit. Our server IS 64-bit so this caused issues originally. Our Information Systems department finally got it to work. We are now using it extensively - successfully. Meaning, we ping .csv's quite a bit with ODBC using... "Microsoft Access Text Driver (*.txt, *.csv)" I know it sounds crazy... b/c I am completely fluent with "Microsoft Text Driver (*.txt, *.csv)" from Analyst. I use a TON of sql against .csv's so it was a real disappointment until they figured this out. It (Microsoft Access Text Driver) has been working just like "Microsoft Text Driver" when pinging .csv's normally. Meaning, using SELECT... FROM... WHERE.

However, using the INNER JOIN is not working.

But yes, , I do have a System DSN set up to ping these .csv's. When I ping them separately, the SQL works fine:

To "Load SSD Revenue Actuals.csv" file:

Code: Select all

SELECT
    A.MPN as vMPN , 
    A.Date as vMonth ,
    A.Version as vVersion ,
    A."Revenue Actual" as vRevenue 
FROM
     "Load SSD Revenue Actuals.csv" A  
WHERE
     (A.MPN like 'MTFD%'
OR
     A.MPN like 'CT%')

And to the "SSD MPN.csv" file:

Code: Select all

SELECT
    [B.Profit Center] as vProfitCenter
FROM
      [SSD MPN.csv] B
WHERE
     [B.Profit Center] like 'MT600A%'

BUT, when I do an INNER JOIN (that works in Analyst), then it doesn't work in TM1

Here are screenshots that show the SQL working separately and then the ODBC sources.

[tomok]

You realize, of course, that TM1 doesn't interpret your SQL syntax, or actually do anything with it at all. It simply passes your query string to the ODBC driver and deals with the result. So, this means your ODBC driver is either returning an error to TM1 (you failed to specify what the error is, if any) or an empty result set. BTW, why do you have the Use Unicode box checked?

[jimicron]

Hi Tomok,

Thank you.

Honestly, the "Use Unicode" is checked merely because by default, it is checked. I do not know what this does so just left it.

And the "error" is simply "SQL statement failed"

NOW, what's VERY bizzare... is this.

In typing up this response, I copied the code below (select all) where I gave the full INNER JOIN syntax. I then went back to my process (which was still open from the screenshots earlier) and pasted into the Query section. I was going to get a screenshot of the "error" but that syntax (which wasn't working earlier nor was it working yesterday) - JUST WORKED!!! So, I didn't get my screenshot, but for some very strange reason, the exact same code that is below that wasn't working an hour ago, just worked!!!

I am happy it's working... but it completely is confusing me why it didn't work earlier and is now?

[jimicron]

<tail between my legs>

Well, wanted to update on this. Why it didn't work before and why it did when I went to go get the error... b/c I used the Analyst syntax in TM1 (from the code below in this thread) and not the TM1 syntax. In other words, the syntax that uses " instead of [ ].

I thought in prior testing that we found that you had to use [ ] for multiple words in TM1, whereas in Analyst you use " - not brackets. However, the " are working in TM1 as well. So, something about the brackets with the INNER JOIN that it's not liking but the Analyst syntax is working. I'm totally fine with using the same syntax as I do in Analyst since that is what I am most familiar with.

Anyway, a bit embarassing, but wanted to provide that clarity

Thanks again!!

[tomok]

I thought in prior testing that we found that you had to use [ ] for multiple words in TM1

You're giving TM1 too much credit. As I said earlier, there is no "syntax" for SQL in a TI process. TM1 literally passes the EXACT string you put in the query box to the ODBC driver. Putting brackets around column names, preceding table names with DB names, etc., all depends on the driver, not TM1.