Cognos SQL help with ODBC

[jimicron]

Hey guys...

Wondering if you know...

Here is the dealio... I am creating an ODBC to pull info from an Excel CSV file to Cognos.  I know that Cognos uses Native SQL.  When I create ODBC's pulling directly from a server (versus csv), I use Oracle SQL b/c the tables I pull from reside on an oracle server.  However, pulling from a CSV requires me to tweak the SQl a bit.

I have a column in the CSV file that I am pulling from.  It contains a list like this (this is just a sample)


790-11403      
790-11400      
790-11380      
790-11279      
790-11212      
790-10914      
790-11221      
790-11238      
790-11383      
790-11172      
93084      
93069      
93081      
93080      
95000      
94988      
93560      
93564      
93082      
96098      
79035      
79036      
79033      
79038      
79031      
93563   


The SQL statement is:

SELECT DISTINCT
     Material
FROM
     "ZFCCORD.csv"

The problem is that it's only pulling the 5 digit numeric instead of also pulling those with a hyphen in it.

I am guessing it is b/c it see's the 5 digit as a number and since excel doesn't store the ones with hyphen's as numbers, it isn't seeing them.  SO, my thought is that I need to change "Material" in my SQL statement to a character or text or something...

Any ideas?

Thanks~!

JIM

[wokan]

You may want to check whether special character is allowed in Planning Analyst.

There is hyphen (-) in your material code.

Hope that helps.

[Hitman]

Hi,

Even I faced the same issue,it happens when we use ODBC text drivers.
In my case I converted the file to excel and used the Excel ODBC drive.

If you have the liberty to convert the file into exel I hope your problem will be solved.